Konstantin Knizhnik <[email protected]> writes:
> I found some very confusing behaviour of REVOKE PRIVILEGE.
> ...
> where <ADMIN_ROLE> is any role with admin permissions under which you
> logged in.
"admin permissions" is not well-defined terminology in Postgres.
I thought perhaps you meant "superuser", but some experimentation
indicates that that role doesn't need any special permissions, it
only has to be the table owner to produce the strange behavior.
> So granted read privilege was not revoked.
This seems like it may be a consequence of this statement in
the REVOKE man page:
If the role executing REVOKE holds privileges indirectly via more
than one role membership path, it is unspecified which containing
role will be used to perform the command. In such cases it is best
practice to use SET ROLE to become the specific role you want to
do the REVOKE as. Failure to do so might lead to revoking
privileges other than the ones you intended, or not revoking
anything at all.
In this example, "somebody" is a member of the table owner role
as well as having some privilege granted directly, so the ambiguity
does exist. I didn't dig further than that. It does seem sad
if we fail to revoke a privilege that is an exact match, though.
regards, tom lane
