pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Dominique Devienne <ddevienne@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: New SET privilege for pg_has_role() in v16+
Date: Tue, 2 Jan 2024 08:09:22 -0800
Message-ID: <ab446f99-0bf3-4ff1-b118-02625aa9d4a2@aklaver.com> (raw)
In-Reply-To: <CAFCRh--XHhyKgkSZKDtYSxZ2Aqf7VkVzZu2_P2BeSmeYE8y-Jw@mail.gmail.com>
References: <CAFCRh--XHhyKgkSZKDtYSxZ2Aqf7VkVzZu2_P2BeSmeYE8y-Jw@mail.gmail.com>

On 1/2/24 07:24, Dominique Devienne wrote:
> Hi. And happy new year (for those using the Gregorian calendar).
> 
> pg_has_role() from 
> https://www.postgresql.org/docs/current/functions-info.html 
> <https://www.postgresql.org/docs/current/functions-info.html;
> added the 'SET' privilege in v16, and on top of the existing 'MEMBER' 
> and 'USAGE' ones:
> 
>  > MEMBER denotes direct or indirect membership in the role [...]
>  > USAGE denotes whether the privileges of the role are immediately 
> available without doing SET ROLE
>  > SET denotes whether it is possible to change to the role using the 
> SET ROLE command
> 
> I'd like to know if possible why SET was added; the rationale for it.
> Does it not imply that MEMBER and USAGE weren't enough somehow before?
> 
> If `pg_has_role(..., 'MEMBER')` is true, isn't `pg_has_role(..., 'SET')` 
> implied?
> If not, why? (and is that related to NOT INHERIT roles in the graph 
> between the two roles?)
> 
> Asked differently I guess, when does being a MEMBER of a role (directly 
> or not),
> NOT allow SET ROLE to that role?


https://www.postgresql.org/docs/current/sql-set-role.html

"Using this command, it is possible to either add privileges or restrict 
one's privileges. If the session user role has been granted memberships 
WITH INHERIT TRUE, it automatically has all the privileges of every such 
role. In this case, SET ROLE effectively drops all the privileges except 
for those which the target role directly possesses or inherits. On the 
other hand, if the session user role has been granted memberships WITH 
INHERIT FALSE, the privileges of the granted roles can't be accessed by 
default. However, if the role was granted WITH SET TRUE, the session 
user can use SET ROLE to drop the privileges assigned directly to the 
session user and instead acquire the privileges available to the named 
role. If the role was granted WITH INHERIT FALSE, SET FALSE then the 
privileges of that role cannot be exercised either with or without SET 
ROLE."


> 
> We use ROLEs extensively in our PostgreSQL-based apps,
> and I've read a lot about them, but at times I feel I'm missing something.
> 
> Thanks, --DD

-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (8+ messages)  latest in thread

Message-ID: <ab446f99-0bf3-4ff1-b118-02625aa9d4a2@aklaver.com>
Permalink:  ../ab446f99-0bf3-4ff1-b118-02625aa9d4a2@aklaver.com/
Also on:    postgresql.org/message-id/ab446f99-0bf3-4ff1-b118-02625aa9d4a2@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, ddevienne@gmail.com
  Subject: Re: New SET privilege for pg_has_role() in v16+
  In-Reply-To: <ab446f99-0bf3-4ff1-b118-02625aa9d4a2@aklaver.com>
  References: <CAFCRh--XHhyKgkSZKDtYSxZ2Aqf7VkVzZu2_P2BeSmeYE8y-Jw@mail.gmail.com> <ab446f99-0bf3-4ff1-b118-02625aa9d4a2@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox