Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUaHh-0003NB-O1 for pgsql-hackers@arkaria.postgresql.org; Tue, 21 Feb 2023 21:34:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUaHg-0004uT-IG for pgsql-hackers@arkaria.postgresql.org; Tue, 21 Feb 2023 21:34:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUaHg-0004uK-3D for pgsql-hackers@lists.postgresql.org; Tue, 21 Feb 2023 21:34:48 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pUaHd-00027f-Fq for pgsql-hackers@lists.postgresql.org; Tue, 21 Feb 2023 21:34:47 +0000 Received: by mail-ed1-x52b.google.com with SMTP id eg37so18917853edb.12 for ; Tue, 21 Feb 2023 13:34:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=1XoHX2pttuvuxIWjR6kzn0QbNpE+SyARdbKhwETD4Ts=; b=dPZpa6NeGSd+9LU00CQK4539nLg6BrPSCEEgt3orzTxaVWalNBubWHqlBipLcZCmOU l1tx90X6KwO1Nj4eVw+UcdQ2I+pYsemZR8ZgbcLA4AXLbMo0mnjVy0qkyr4jsvieqQ10 lIqGESeG9BrFuKarJh+ZbHj7/uU6fMwRDK761NH8XJOcWUXG0DgTHHN4apDU+ndJ7zZy Hl3/fc2cmz/vq4Orv+f2MPAyoLRn9trYLWdBz0UIQV6wsNvzwJp3Pae8qwgHIKiUuYPu J2wRuXVzRVZMQSrlwQ/btieNxUnkqP0LekAsFwyuakrWWcMUew0Aqo7qBI1dJYehOZXY qcIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=1XoHX2pttuvuxIWjR6kzn0QbNpE+SyARdbKhwETD4Ts=; b=WeelZvrxmeik+m6LLd8bqZo9KO5BB4gzBybScQVA8ikJskmGipHQRhONdG6OX4pxeF lB4NxqqzjHE0clbtgn4gug3vo0VZsNndBrzgTjLoxFqm4z22FUwQqrt/MjWZH2xNUu2T cEeRw1NVy2LwPIgDit/b43BP8oAoOVNVbaSVsD5Ncblw05p8cCDXwTUP/TRf/EgdcLqa 6c9OlrBaeSOvUGikA1cJxitCPLn4Ex9JgO76HLW5YmiaQ63oKIrnTJluUiyRiWsKaLDm kvowK0fxiphRcqX+d2+1p9jtFqMc0uhKy58eMr64kG9uJjjeGQ82LjDrPel4tM93PkHT Y1fA== X-Gm-Message-State: AO0yUKUcJCb4URyrMiHW6FuGoZ/QEuXGr44SqBDinyUcQyFDKBuYTj5O lWG1r6QPsbYO3vHFISDlXjl9fJqISuAyltrztHs= X-Google-Smtp-Source: AK7set/YxN258ivESWxcJtY+M4UxTRCCgUmKlZHWAm0zKwOZAUHZYZf0LTEtB21IhBSmf8kNRMovNbWphtwLRKs7STA= X-Received: by 2002:a17:906:aac6:b0:8b2:fa6d:45e3 with SMTP id kt6-20020a170906aac600b008b2fa6d45e3mr6754814ejb.1.1677015283742; Tue, 21 Feb 2023 13:34:43 -0800 (PST) MIME-Version: 1.0 References: <01620d6b-9efe-b199-b619-8450d865ec83@postgrespro.ru> <94ce7392-fa90-bc1c-4dc2-677161bbff58@postgrespro.ru> In-Reply-To: <94ce7392-fa90-bc1c-4dc2-677161bbff58@postgrespro.ru> From: "David G. Johnston" Date: Tue, 21 Feb 2023 14:34:27 -0700 Message-ID: Subject: Re: psql: Add role's membership options to the \du+ command To: Pavel Luzanov Cc: David Zhang , "pgsql-hackers@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000453dd405f53c8cd8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000453dd405f53c8cd8 Content-Type: text/plain; charset="UTF-8" On Tue, Feb 21, 2023 at 2:14 PM Pavel Luzanov wrote: > On 17.02.2023 19:53, David G. Johnston wrote: > > On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov > wrote: > >> List of roles >> Role name | Attributes | >> Member of >> >> -----------+------------------------------------------------------------+----------- >> admin | Create role | >> {bob,bob} >> bob | | >> {} >> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | >> {} >> >> First 'grant bob to admin' command issued immediately after creating role >> bob by superuser(grantor=10). Second command issues by admin role and set >> membership options SET and INHERIT. >> If we don't ready to display membership options with \du+ may be at least >> we must group records in 'Member of' column for \du command? >> > > I agree that these views should GROUP BY roleid and use bool_or(*_option) > to produce their result. > > > Ok, I'll try in the next few days. But what presentation format to use? > > This is the format I've gone for (more-or-less) in my RoleGraph view (I'll be sharing it publicly in the near future). bob from grantor (a, s, i) \n adam from postgres (a, s, i) \n emily from postgres (empty) I don't think first-letter mnemonics will be an issue - you need to learn the syntax anyway. And it is already what we do for object grants besides. Based upon prior comments going for something like the following is undesirable: bob=asi/grantor So I converted the "/" into "from" and stuck the permissions on the end instead of in the middle (makes reading the "from" fragment cleaner). To be clear, this is going away from grouping but trades verbosity and deviation from what is done today for better information. If we are going to break this I suppose we might as well break it thoroughly. > > I didn't even know this function existed. But I see that it was changed in > 3d14e171 with updated documentation: > > https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS > Maybe that's enough. > > I think that should probably have ADMIN as one of the options as well. Also curious what it reports for an empty membership. David J. --000000000000453dd405f53c8cd8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Feb 21, 2023 at 2:14 PM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote= :
=20 =20 =20
On 17.02.2023 19:53, David G. Johnston wrote:
=20
On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 List of roles
 =C2=A0Role name |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Attributes=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 | Member of
-----------+---------------------------------------= ---------------------+-----------
 =C2=A0admin=C2=A0=C2=A0=C2=A0=C2=A0 | Create role=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 | {bob,bob}
 =C2=A0bob=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | {}
 =C2=A0postgres=C2=A0 | Superuser, Create role, Crea= te DB, Replication, Bypass RLS | {}

First 'grant bob to admin' command issued immediately after creating role bob by superuser(grantor=3D10). Second command issues by admin role and set membership options SET and INHERIT.

If we don't ready to display membership options with \du+ may be at least we must group records in 'Member of' column for \du command?

I agree that these views should GROUP BY roleid and use bool_or(*_option) to produce their result.=C2=A0

Ok, I'll try in the next few days. But what presentation format to use?


This is the format I've gone for (more-or-less) in my RoleGrap= h view (I'll be sharing it publicly in the near future).

bob from grantor (a, s, i) \n
adam from postgres (a,= s, i) \n
emily from postgres (empty)
I don't = think first-letter mnemonics will be an issue - you need to learn the synta= x anyway.=C2=A0 And it is already what we do for object grants besides.

Based upon prior comments going for something like the fo= llowing is undesirable:=C2=A0 bob=3Dasi/grantor

So I c= onverted the "/" into "from" and stuck the permissions = on the end instead of in the middle (makes reading the "from" fra= gment cleaner).

To be clear, this is going away from g= rouping but trades verbosity and deviation from what is done today for bett= er information.=C2=A0 If we are going to break this I suppose we might as w= ell break it thoroughly.



I didn't even know this function existed. But I see that i= t was changed in 3d14e171 with updated documentation:
https://www.postgresql.org/= docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS
Maybe that's enough.


I think that should probably have ADMIN as one of the = options as well.=C2=A0 Also curious what it reports for an empty membership= .

David J.

--000000000000453dd405f53c8cd8--