Horton

Re: role self-revocation

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
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-07T15:12:54Z
Lists: pgsql-hackers 
On Sun, Mar 6, 2022 at 11:34 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

It appears so: https://www.postgresql.org/message-id/2073b6a9-7f79-5a00-5f26-cd19589a52c7%402ndquadrant.com

It doesn't seem like that would be hard to fix. Maybe we should just do that.

> 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.

OK.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

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