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 1pT3zj-0006qE-Cg for pgsql-hackers@arkaria.postgresql.org; Fri, 17 Feb 2023 16:53:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pT3zi-0004J0-5W for pgsql-hackers@arkaria.postgresql.org; Fri, 17 Feb 2023 16:53:58 +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 1pT3zh-0004Iq-NB for pgsql-hackers@lists.postgresql.org; Fri, 17 Feb 2023 16:53:57 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pT3zc-0004j2-Q5 for pgsql-hackers@lists.postgresql.org; Fri, 17 Feb 2023 16:53:56 +0000 Received: by mail-ed1-x52f.google.com with SMTP id i28so6116491eda.8 for ; Fri, 17 Feb 2023 08:53:52 -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=FMGLqgrFJmty2DEI6chQZ8d2IXBFc0h99/bQ/Z9I4oQ=; b=RvBUUu1PvK67UaKClSvlCYy6xQeyRgwf88AQ57BTQFjGxOMxPg5uD0heai76fSbvg/ H/xlom3iIiKxUNGnrihrLSyVVFKtrQ6tgJ76trt9PxBxf/3/ricOxMLzFeedIgVlw0uw dGpKOyRyOyPNCY8blYnkQQfPXxtgB0aVeWTBUMPKg57g7RpRkG6vK/znvEc4NB7729WE aRtvXOf7est+9EGVNkA6NGcYs6ce7385fnPgOJayQz7QWm6SvsYC0b7NqH8Kmb2+bkMg dncgLPYXIJ2g9rZstznO9r+HJf5GMZE660q1XnwdzR32XrposSbWWPlg8zhh/cRzHr99 RGOg== 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=FMGLqgrFJmty2DEI6chQZ8d2IXBFc0h99/bQ/Z9I4oQ=; b=B1npMM7/LpQxc5PbgtOIa14r/eC1WkRLNbwTKZgCUa/+PcXxHzUqyQtqddvZXxmh07 5BslOl93E9eJpYPL3Bs4UBCb3ScBBIf9rcmlJhwvm/U8/qYobEXdK5F5xvEBpkXmSYK+ JMKNqkOEs7XFImz3DRhik8MvMWIygA63i70lqmbMVOGnokOCGh4bizUH1HnAbHH67GKt u832Xkpy+3H+5a2bgOlozOrw94fqbALtUuIRgINpNzcHWUYQqii5MeGSaBES3tVAYu5m Fr1Cix1JAC38/NHVWyS/a5+MI20UNnllhyzAFhLWoh5JGLGMLcSTrm/AuAWDZl3YY/kV mkDA== X-Gm-Message-State: AO0yUKUlf9w8gzP+T4lP8dJcj1+cPzXriZYIR1V888bRII6R/vNwpgXY MdMiC78ik9wOd6A9HNqrDlvleFBlkPXPvocAmRc= X-Google-Smtp-Source: AK7set/zyC8aA/ldvi1UfQEqFnDlcT6VWSscgz0iNY+an5F9mvLctmPI1OpOkK0V6NaqV07gMX6RdsbWjXhnxZYSnr0= X-Received: by 2002:a50:a446:0:b0:4ad:6e3e:7da6 with SMTP id v6-20020a50a446000000b004ad6e3e7da6mr2773865edb.6.1676652831317; Fri, 17 Feb 2023 08:53:51 -0800 (PST) MIME-Version: 1.0 References: <01620d6b-9efe-b199-b619-8450d865ec83@postgrespro.ru> In-Reply-To: From: "David G. Johnston" Date: Fri, 17 Feb 2023 09:53:35 -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="0000000000006c24ed05f4e82886" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006c24ed05f4e82886 Content-Type: text/plain; charset="UTF-8" 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. Their purpose is to communicate the current effective state to the user, not facilitate full inspection of the configuration, possibly to aid in issuing GRANT and REVOKE commands. One thing I found, and I plan to bring this up independently once I've collected my thoughts, is that pg_has_role() uses the terminology "USAGE" and "MEMBER" for "INHERIT" and "SET" respectively. It's annoying that "member" has been overloaded here. And the choice of USAGE just seems arbitrary (though I haven't researched it) given the related syntax. https://www.postgresql.org/docs/15/functions-info.html --0000000000006c24ed05f4e82886 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov <p.luzanov@postgrespro.ru> wrote= :
=20 =20 =20
=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 Lis= t 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 | {bo= b,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, Create DB, Replication, Bypass RLS | {}

First 'grant bob to admin' command issued immediately af= ter 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 b= e 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 res= ult.=C2=A0 Their purpose is to communicate the current effective state to t= he user, not facilitate full inspection of the configuration, possibly to a= id in issuing GRANT and REVOKE commands.

One thing I f= ound, and I plan to bring this up independently once I've collected my = thoughts, is that pg_has_role() uses the terminology "USAGE" and = "MEMBER" for "INHERIT" and "SET" respectively= .

It's annoying that "member" has been o= verloaded here.=C2=A0 And the choice of USAGE just seems arbitrary (though = I haven't researched it) given the related syntax.

https:/= /www.postgresql.org/docs/15/functions-info.html

<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif= ">
--0000000000006c24ed05f4e82886--