Re: pg_auth_members.grantor is bunk

On Fri, Jun 24, 2022 at 4:30 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)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