Re: REVOKE FROM warning on grantor - Mailing list pgsql-hackers

From Étienne BERSAC
Subject Re: REVOKE FROM warning on grantor
Date
Msg-id f1bab43170c714c7e2591d3e0e2c07bce7e34a1d.camel@dalibo.com
Whole thread Raw
In response to Re: REVOKE FROM warning on grantor  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Daniel Gustafsson
Date:
Subject: Re: What about Perl autodie?
Next
From: "Sean"
Date:
Subject: Re: Is there still password max length restrictions in PG?