v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
Hi,
This is more curiosity than anything else. In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership gain any ability it didn't have before in that case?
On Mon, Jul 8, 2024 at 12:23 PM Christophe Pettus <xof@thebuild.com> wrote:
This is more curiosity than anything else. In the v16 role system, is
there actually any reason to grant membership in a role to a different
role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role
granted membership gain any ability it didn't have before in that case?
That scenario is allowed but provides no useful in-server behavior. I
suppose the membership presence/absence could be given external meaning by
a DBA. Personally, I'd just add a metric on the server counting these and
alert if it is ever non-zero.
David J.
On Jul 8, 2024, at 12:58, David G. Johnston <david.g.johnston@gmail.com> wrote:
That scenario is allowed but provides no useful in-server behavior.
That was my conclusion as well. Thanks!
On Mon, 2024-07-08 at 12:22 -0700, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is there actually any
reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE,
and ADMIN FALSE? Does the role granted membership gain any ability it didn't have before
in that case?
I didn't test it, but doesn't that allow the member rule to drop objects owned
be the role it is a member of?
Yours,
Laurenz Albe
On Jul 8, 2024, at 13:25, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I didn't test it, but doesn't that allow the member rule to drop objects owned
be the role it is a member of?
No, apparently not.
On Jul 8, 2024, at 13:29, Christophe Pettus <xof@thebuild.com> wrote:
On Jul 8, 2024, at 13:25, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I didn't test it, but doesn't that allow the member rule to drop objects owned
be the role it is a member of?No, apparently not.
Just from a quick check, it looks like you need INHERIT to inherit the ability to drop objects. The documentation strongly implies this, although it doesn't quite come out and say it.
Christophe Pettus <xof@thebuild.com> writes:
On Jul 8, 2024, at 13:25, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
I didn't test it, but doesn't that allow the member rule to drop objects owned
be the role it is a member of?
No, apparently not.
IIUC, you need at least one of SET TRUE and INHERIT TRUE to be able to
access the privileges of the role you are nominally a member of. This
extends to ownership checks as well as grantable privileges.
regards, tom lane
On Monday, July 8, 2024, Christophe Pettus <xof@thebuild.com> wrote:
On Jul 8, 2024, at 13:29, Christophe Pettus <xof@thebuild.com> wrote:
On Jul 8, 2024, at 13:25, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
I didn't test it, but doesn't that allow the member rule to drop
objects owned
be the role it is a member of?
No, apparently not.
Just from a quick check, it looks like you need INHERIT to inherit the
ability to drop objects. The documentation strongly implies this, although
it doesn't quite come out and say it.
Are you referring to this:
The right to modify or destroy an object is inherent in being the object's
owner, and cannot be granted or revoked in itself. (However, like all
privileges, that right can be inherited by members of the owning role; see
Section 22.3 <https://www.postgresql.org/docs/current/role-membership.html>
.)
https://www.postgresql.org/docs/current/ddl-priv.html
It can be argued that is more than strong implication though a different
more obvious (technical) wording could be in order.
David J.
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership gain any ability it didn't have before in that case?
Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
Pavel Luzanov <p.luzanov@postgrespro.ru> writes:
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership gain any ability it didn't have before in that case?
Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.
Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.
regards, tom lane
On 09.07.2024 00:16, Tom Lane wrote:
Pavel Luzanov<p.luzanov@postgrespro.ru> writes:
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role granted membership gain any ability it didn't have before in that case?
Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.regards, tom lane
My test scenario:
postgres@demo(16.3)=# select * from pg_hba_file_rules ;
rule_number | file_name | line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------------------------------------+-------------+-------+----------+------------+---------+---------+-------------+---------+-------
1 | /etc/postgresql/16/main/pg_hba.conf | 118 | local | {all} | {postgres} | | | trust | |
2 | /etc/postgresql/16/main/pg_hba.conf | 121 | local | {all} | {+bob} | | | trust | |
3 | /etc/postgresql/16/main/pg_hba.conf | 122 | local | {all} | {alice} | | | reject | |
(3 rows)
postgres@demo(16.3)=# \drg
List of role grants
Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
alice | bob | | postgres
(1 row)
postgres@demo(16.3)=# \c - alice
You are now connected to database "demo" as user "alice".
alice@demo(16.3)=>
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
On Mon, Jul 8, 2024 at 2:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pavel Luzanov <p.luzanov@postgrespro.ru> writes:
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is
there actually any reason to grant membership in a role to a different
role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role
granted membership gain any ability it didn't have before in that case?Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.
The code doesn't support that claim. It seems quite intentional that this
check is purely on membership - what with ACL having a dedicated function
for the purpose of checking plain recursive membership that only this and
the circularity check code use. I suppose it makes sense too - at least
unless you also check for SET - since it seems desirable to allow login as
a member role to a group you can only SET to and don't inherit from.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Mon, Jul 8, 2024 at 2:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pavel Luzanov <p.luzanov@postgrespro.ru> writes:
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is
there actually any reason to grant membership in a role to a different
role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does the role
granted membership gain any ability it didn't have before in that case?
Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.
Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.
The code doesn't support that claim.
That doesn't make it not a bug. Robert, what do you think? If this
is correct behavior, why is it correct?
regards, tom lane
On Mon, Jul 8, 2024 at 3:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Mon, Jul 8, 2024 at 2:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pavel Luzanov <p.luzanov@postgrespro.ru> writes:
On 08.07.2024 22:22, Christophe Pettus wrote:
This is more curiosity than anything else. In the v16 role system, is
there actually any reason to grant membership in a role to a different
role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE? Does therole
granted membership gain any ability it didn't have before in that
case?
Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.The code doesn't support that claim.
That doesn't make it not a bug.
Fair, the code was from a time when membership implied SET permission which
apparently was, IMO, still is, a sufficient reason to allow a member of
that group to login.
By making SET optional we removed this presumption and broke this code and
now need to decide what is a valid setup to allow logging in. So, yes, a
bug.
So long as we document that being a member of a group grants you the right
to login if that group is named in pg_hba.conf I don't see how that is an
invalid specification. It also isn't self-evidently correct. If we do
agree that the status quo is undesirable the change I'd expect is to
require at least one of the membership options to be true in order to be
allowed to login.
But as we documented that membership is the only determinant here, by
design or happenstance, we are behaving exactly as our documentation says.
As we are talking about logging in I'd be fine with potential breakage and
implementing the "At least one" requirement. I wish we could break even
allowing all-false as an active state but that seems too late to try and
do. Better just to truly make it pointless and thus let there be no
surprises.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Mon, Jul 8, 2024 at 3:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
That doesn't make it not a bug.
Fair, the code was from a time when membership implied SET permission which
apparently was, IMO, still is, a sufficient reason to allow a member of
that group to login.
By making SET optional we removed this presumption and broke this code and
now need to decide what is a valid setup to allow logging in. So, yes, a
bug.
Yeah, that's what I'm thinking --- I also think the current behavior
was an oversight rather than an intentional decision. Possibly Robert
remembers differently though.
So long as we document that being a member of a group grants you the right
to login if that group is named in pg_hba.conf I don't see how that is an
invalid specification. It also isn't self-evidently correct. If we do
agree that the status quo is undesirable the change I'd expect is to
require at least one of the membership options to be true in order to be
allowed to login.
I'd argue that INHERIT TRUE should be required. The point of SET TRUE
with INHERIT FALSE is that you must *explicitly* do SET ROLE or
equivalent in order to have access to the privileges of the referenced
role. There is no opportunity to issue SET ROLE before logging in,
but ISTM that means you don't get the privilege, not that you
magically get it without asking. Otherwise, why did we build this
additional level of grant specificity at all?
I follow the use-case of wanting to be able to log into a database
where your only useful privileges are locked behind SET requirements.
But I'd still argue that you should need to be able to log into that
database using your own privileges. An analogous case is that having
some privileges on table(s) in some schema does not give you the right
to bypass needing USAGE on the schema in order to access those tables.
I wish we could break even
allowing all-false as an active state but that seems too late to try and
do.
I suppose that was intentional, but I too wonder about the use-case.
I guess it's analogous to, for example, zero-column tables. That is,
it's not terribly useful as a static state but it might be a state
you pass through in a series of manipulations. Forbidding it would
just create a roadblock that'd have to be worked around, for example
by being very careful about the ordering of GRANT/REVOKE commands.
regards, tom lane
On Mon, Jul 8, 2024 at 3:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'd argue that INHERIT TRUE should be required. The point of SET TRUE
with INHERIT FALSE is that you must *explicitly* do SET ROLE or
equivalent in order to have access to the privileges of the referenced
role.
I think that blast radius is too large for v16. Arguable for v17. All
pre-v-16 roles encountered during upgrade will have SET true and those
roles are able to login today but a non-trivial number I would expect to be
unable to if we require INHERIT.
It would be nicer to do away with the default connect grant to public,
expect the group role to have the connect privilege on the desired
database, and let the user role inherit that in lieu of granting it out
explicitly, if we are into v18 feature requests. By the time the newbie
stops using postgres superuser for everything they can add a connect grant
here or there.
There is no opportunity to issue SET ROLE before logging in,
but ISTM that means you don't get the privilege, not that you
magically get it without asking. Otherwise, why did we build this
additional level of grant specificity at all?
Mostly for the pre-defined roles, and just general explicitness. So
inherit could be per-role-membership instead per-role. HBA group
specification rules wasn't a motivating drive if it was considered at all.
Unbreaking CREATEROLE.
An analogous case is that having
some privileges on table(s) in some schema does not give you the right
to bypass needing USAGE on the schema in order to access those tables.
That sounds like the Connect privilege noted above. While +role could be
seen as a sImple text-expander for pg_hba.conf, not a privilege check in
its own right. We don't explicitly exclude users lacking the login
attribute from the 'all' specification. They are allowed to match and then
their login attribute and database-specific connect privilege is checked.
David J.
On Mon, Jul 8, 2024 at 6:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.The code doesn't support that claim.
That doesn't make it not a bug. Robert, what do you think? If this
is correct behavior, why is it correct?
Correct is debatable, but it's definitely intentional. I didn't think
that referencing a group in pg_hba.conf constituted either (a) the
group inheriting the privileges of the role -- which would make it
governed by INHERIT -- or (b) the group being able to SET ROLE to the
role -- which would make it controlled by SET. I guess you're arguing
for INHERIT which is probably the more logical of the two, but I'm not
really sold on it. I think the pg_hba.conf matching is just asking
whether X is in set S, not whether S has the privileges of X.
For contemporaneous evidence of my thinking on this subject see
/messages/by-id/CA+TgmobhEYYnW9vrHvoLvD8ODsPBJuU9CbK6tms6Owd70hFMTw@mail.gmail.com
particularly the paragraph that starts with "That's it".
--
Robert Haas
EDB: http://www.enterprisedb.com