Re: REVOKE FROM warning on grantor

Hi Tom,

Thanks for your anwser.

> It does not say that that set must be nonempty.  Admittedly it's not
> very clear from this one point.  However, if you look around in the
> standard it seems clear that they expect no-op revokes to be no-ops
> not errors.

Postgres actually identifies memberhips to revoke. The list is not
empty. Event if revoker has USAGE privilege on parent role, the
membership is protected by a new check on grantor of membership. This
is a new semantic for me. I guess this may obfuscate other people too.

I would compare denied revoking of role with revoking privilege on
denied table:

> REVOKE SELECT ON TABLE toto FROM PUBLIC ;
ERROR: permission denied for table toto

> Even taking the position that this is an unspecified point that we
> could implement how we like, I don't think there's a sufficient
> argument for changing behavior that's stood for a couple of decades.

In Postgres 15, revoking a membership granted by another role is
accepted. I suspect this is related to the new CREATEROLE behaviour
implemented by Robert Haas (which is great job anyway). Attached is a
script to reproduce.

Here is the output on Postgres 15:

SET
DROP ROLE
DROP ROLE
DROP ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT ROLE
SET
REVOKE ROLE
DO

Here is the output of the same script on Postgres 16:


SET
DROP ROLE
DROP ROLE
DROP ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT ROLE
SET
psql:ldap2pg/my-revoke.sql:12: WARNING: role "r" has not been granted membership in role "g" by role "m"
REVOKE ROLE
psql:ldap2pg/my-revoke.sql:18: ERROR: REVOKE failed
CONTEXTE : PL/pgSQL function inline_code_block line 4 at RAISE

Can you confirm this ?

Regards,
Étienne