Horton

Re: pg_auth_members.grantor is bunk

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>, Tom Lane <tgl@sss.pgh.pa.us>, Mark Dilger <mark.dilger@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-06-24T20:46:42Z
Lists: pgsql-hackers 
On Fri, Jun 24, 2022 at 4:30 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>> Upthread, I proposed that "drop role baz" should fail here
>
> I concur with this.
>
> I think that the grantor owns the grant, and that REASSIGNED OWNED should be able to move those grants to someone else.
>
> By extension, DROP OWNED should remove them.

Interesting. I hadn't thought about changing the behavior of DROP
OWNED BY and REASSIGN OWNED BY. A quick experiment supports your
interpretation:

rhaas=# grant select on table foo to bar;
GRANT
rhaas=# revoke select on table foo from bar;
REVOKE
rhaas=# grant select on table foo to bar with grant option;
GRANT
rhaas=# set role bar;
SET
rhaas=> grant select on table foo to baz;
GRANT
rhaas=> reset role;
RESET
rhaas=# drop role bar;
ERROR:  role "bar" cannot be dropped because some objects depend on it
DETAIL:  privileges for table foo
rhaas=# drop owned by bar;
DROP OWNED
rhaas=# drop role bar;
DROP ROLE

So, privileges on tables (and presumably all other SQL objects)
already work the way that you propose here. If we choose to make role
memberships work in some other way then the two will be inconsistent.
Probably we shouldn't do that. There is still the question of what the
SQL specification says about this, but I would guess that it mandates
the same behavior for all kinds of privileges rather than treating
role memberships and table permissions in different ways. I could be
wrong, though.

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