Обсуждение: New SET privilege for pg_has_role() in v16+
Hi. And happy new year (for those using the Gregorian calendar).
pg_has_role() from 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
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?
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
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
On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne <ddevienne@gmail.com> 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
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 commandI'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?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.
Membership no longer does anything by itself. Both inherit and set capabilities are now individually controlled permissions related to membership. It is indeed possible, but not useful, to grant membership but then disallow both set and inherit permissions.
David J.
On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/2/24 07:24, Dominique Devienne wrote:
> 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:
https://www.postgresql.org/docs/current/sql-set-role.html
"[...], if the role was granted WITH SET TRUE [...]"
That seems to be the important part of your RFTM answer, which is apparently new in v16.
On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne <ddevienne@gmail.com> wrote:pg_has_role() from 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:
Membership no longer does anything by itself.
OK! That's news to me, I must go back to the v16 (?) release notes and learn more about this.
Both inherit and set capabilities are now individually controlled permissions related to membership.
Hmmm, what drove this change? (I guess I'm getting back to the rationale from earlier).
The previous model was not granular enough?
And the new one is as granular as it gets?
It is indeed possible, but not useful, to grant membership but then disallow both set and inherit permissions.
OK. Yet another thing I'll need to study.
As I wrote earlier, we use ROLEs extensively, some INHERIT and others NOT INHERIT,
to map an existing C/C++ enforce security model in mid-tier services, to a ROLE/GRANT-based
one enforced by PostgreSQL itself, thus understanding why these changes were made in v16 matters to me a lot.
Thanks, --DD
On Tue, Jan 2, 2024 at 9:21 AM Dominique Devienne <ddevienne@gmail.com> wrote:
On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne <ddevienne@gmail.com> wrote:pg_has_role() from 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:Membership no longer does anything by itself.OK! That's news to me, I must go back to the v16 (?) release notes and learn more about this.Both inherit and set capabilities are now individually controlled permissions related to membership.Hmmm, what drove this change? (I guess I'm getting back to the rationale from earlier).The previous model was not granular enough?And the new one is as granular as it gets?
Essentially yes. Inherit used to be a property of a role and not a specific membership which was deemed undesirable. We were fixing up the broken CREATEROLE attribute and felt these improvements were needed as well. Once inherit became optional per-membership it made sense to treat set the same way.
David J.
On 1/2/24 08:21, Dominique Devienne wrote: > On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne > <ddevienne@gmail.com <mailto:ddevienne@gmail.com>> wrote: > > 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: > > Membership no longer does anything by itself. > > > OK! That's news to me, I must go back to the v16 (?) release notes and > learn more about this. > > Both inherit and set capabilities are now individually controlled > permissions related to membership. > > > Hmmm, what drove this change? (I guess I'm getting back to the rationale > from earlier). > The previous model was not granular enough? > And the new one is as granular as it gets? > > It is indeed possible, but not useful, to grant membership but then > disallow both set and inherit permissions. > > > OK. Yet another thing I'll need to study. > > As I wrote earlier, we use ROLEs extensively, some INHERIT and others > NOT INHERIT, > to map an existing C/C++ enforce security model in mid-tier services, to > a ROLE/GRANT-based > one enforced by PostgreSQL itself, thus understanding why these changes > were made in v16 matters to me a lot. If you want the rationale see: https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html > > Thanks, --DD -- Adrian Klaver adrian.klaver@aklaver.com
On 1/2/24 08:15, Dominique Devienne wrote: > On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 1/2/24 07:24, Dominique Devienne wrote: > > pg_has_role() from > > https://www.postgresql.org/docs/current/functions-info.html > <https://www.postgresql.org/docs/current/functions-info.html> > > <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: > > https://www.postgresql.org/docs/current/sql-set-role.html > <https://www.postgresql.org/docs/current/sql-set-role.html> > > "[...], if the role was granted WITH SET TRUE [...]" > > > That seems to be the important part of your RFTM answer, which is > apparently new in v16. There also seems to an override: https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-CREATEROLE-SELF-GRANT "createrole_self_grant (string) If a user who has CREATEROLE but not SUPERUSER creates a role, and if this is set to a non-empty value, the newly-created role will be granted to the creating user with the options specified. The value must be set, inherit, or a comma-separated list of these. The default value is an empty string, which disables the feature. The purpose of this option is to allow a CREATEROLE user who is not a superuser to automatically inherit, or automatically gain the ability to SET ROLE to, any created users. Since a CREATEROLE user is always implicitly granted ADMIN OPTION on created roles, that user could always execute a GRANT statement that would achieve the same effect as this setting. However, it can be convenient for usability reasons if the grant happens automatically. A superuser automatically inherits the privileges of every role and can always SET ROLE to any role, and this setting can be used to produce a similar behavior for CREATEROLE users for users which they create. " -- Adrian Klaver adrian.klaver@aklaver.com