On 22/01/2026 6:35 PM, Tom Lane wrote:
Konstantin Knizhnik <[email protected]> writes:
But I wonder if we do refactoring of this revoke privileges stuff,
should we also provide correct (expected) behaviour in case of missing
grantor specification. i.e.
revoke all privileges on table <T> from <role>;
If privileges to access this table were granted to this role by multiple
grantors, then it is natural to expect that the statement above will
remove all such grants and so as a result <role> can not access this
table any more, rather than try to find best grantor and finally still
leave privileges for this role, isn't it?
Unfortunately, the SQL spec is quite clear that REVOKE revokes only
privileges granted directly by the calling user (or the GRANTED BY
role, if that's given). We're already far outside the spec by
allowing select_best_grantor to locate an inherited role to do the
revoke as. I can't see reinterpreting it as "revoke all privileges
granted by anybody", even assuming that the calling user has
sufficient permissions to do that.
regards, tom lane
Can I ask one more question.
What do you think about the following (similar) scenario:
create role creator superuser;
set role creator;
create role reader;
create role somebody;
grant reader to somebody;
grant creator to somebody;
create table t(x integer);
grant select on table t to somebody with grant option;
begin;
set local role somebody;
grant select on table t to reader;
commit;
drop owned by reader cascade;
drop role reader;
ERROR: role "reader" cannot be dropped because some objects depend on it
DETAIL: privileges for table t
What standard is saying about DROP OWNER BY ... CASCADE?
Should it delete reader's privileges in this case?
There is simple "know-how" in Postgres how to drop role having
dependent objects:
REASSIGN OWNED BY ... TO ...;
DROP OWNED BY ...;
But it doesn't work in the case above.
It it necessary to manually locate and drop all granted privileges.
And there are more than 10 kind of objects in Postgres to which
privileges is granted.
So if you need to write procedure which is guaranteed to drop any role,
then there is no simple solution, is it?
