Re: Mystery with REVOKE PRIVILEGE

On 22/01/2026 6:35 PM, Tom Lane wrote:
> Konstantin Knizhnik <knizhnik(at)garret(dot)ru> 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?