Horton

Re: role self-revocation

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>, Joshua Brindle <joshua.brindle@crunchydata.com>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-03-06T16:34:29Z
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:
>> Agreed, this is not something to move on quickly.  We might want
>> to think about adjusting pg_dump to use explicit GRANTED BY
>> options in GRANT/REVOKE a release or two before making incompatible
>> changes.

> Uggh. I really want to make some meaningful progress here before the
> heat death of the universe, and I'm not sure that this manner of
> proceeding is really going in that direction. That said, I do entirely
> see your point. Are you thinking we'd actually add a GRANTED BY clause
> to GRANT/REVOKE, vs. just wrapping it in SET ROLE incantations of some
> sort?

I was thinking the former ... however, after a bit of experimentation
I see that we accept "grant foo to bar granted by baz" a VERY long
way back, but the "granted by" option for object privileges is
(a) pretty new and (b) apparently restrictively implemented:

regression=# grant delete on alices_table to bob granted by alice;
ERROR:  grantor must be current user

That's ... surprising.  I guess whoever put that in was only
interested in pro-forma SQL syntax compliance and not in making
a usable feature.

So if we decide to extend this change into object privileges
it would be advisable to use SET ROLE, else we'd be giving up
an awful lot of backwards compatibility in dump scripts.
But if we're only talking about role grants then I think
GRANTED BY would work fine.

			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