On Fri, Jun 24, 2022 at 4:30 PM David G. Johnston <david.g.johns...@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
