Horton

Re: pg_auth_members.grantor is bunk

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-06-02T19:15:13Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 4, 2022 at 4:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If we are not tracking the grantors of role authorizations,
>> then we are doing it wrong and we ought to fix that.

> So let's talk about how we could fix this. In a vacuum I'd say this is
> just a feature that never got finished and we should rip the whole
> thing out. That is, remove pg_auth_members.grantor entirely and at
> most keep some do-nothing syntax around for backward compatibility.
> However, what Tom is saying in the text quoted above is that we ought
> to have something that actually works, which is more challenging.
> Apparently, the desired behavior here is for this to work like grants
> on non-role objects, where executing "GRANT SELECT ON TABLE s1 TO foo"
> under two different user accounts bar and baz that both have
> permissions to grant that privilege creates two independent grants
> that can be independently revoked.

Maybe.  What I was pointing out is that this is SQL-standard syntax
and there are SQL-standard semantics that it ought to be implementing.
Probably those semantics match what you describe here, but we ought
to dive into the spec and make sure before we spend a lot of effort.
It's not quite clear to me whether the spec defines any particular
unique key (identity) for the set of role authorizations.

			regards, tom lane

Commits

  1. Make role grant system more consistent with other privileges.

  2. Ensure that pg_auth_members.grantor is always valid.

  3. Remove the ability of a role to administer itself.

  4. Add tests of the CREATEROLE attribute

  5. Replace explicit PIN entries in pg_depend with an OID range test.

  6. Shore up ADMIN OPTION restrictions.

  7. Add pg_has_role() family of privilege inquiry functions modeled after the

  8. Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion