Re: REVOKE FROM warning on grantor

On Wed, Mar 20, 2024 at 1:26 PM Étienne BERSAC
<etienne(dot)bersac(at)dalibo(dot)com> wrote:
> The usual case is: a superuser grants writers role to alice. In
> directory, alice is degraded to readers. ldap2pg is not superuser but
> has CREATEROLE. ldap2pg applies the changes. In Postgres 15, revocation
> is completed. In Postgres 16, alice still has writers privileges and
> ldap2pg is not aware of this without clunky checks.

In previous versions of PostgreSQL, the grantor column of
pg_auth_members was not meaningful. Commit
ce6b672e4455820a0348214be0da1a024c3f619f changed that, for reasons
explained in the commit message. So now, to revoke a particular grant,
ldap2pg really ought to issue REVOKE x FROM y GRANTED BY z. Notice
that pg_auth_members now has a unique constraint on (roleid, member,
grantor) instead of (roleid, member) as it did previously, so if you
specify all of those things, you're identifying a unique grant; if you
specify only two of them, you aren't. I think in a case like the one
you described here, the REVOKE would fail with an error, because
ldap2pg, as a non-superuser, would not have permission to revoke a
superuser's grant. That's intentional.

I don't really understand why you describe the checks as "clunky". The
structure of pg_auth_members is straightforward. If you issue a
command to try to make a row go away from that table, it shouldn't be
that difficult to figure out whether or not it actually vanished. I
*think* that if you use GRANTED BY and don't have any other mistakes
in your SQL construction, you'll either succeed in getting rid of the
row or you'll get an error; the non-error case is when REVOKE would
have targeted a row that's not actually present. But even if I'm wrong
about that, running the REVOKE and then checking whether the row you
wanted to eliminate is gone seems straightforward. The main thing, to
me, seems like you want to make sure that you actually are targeting a
specific row.

--
Robert Haas
EDB: http://www.enterprisedb.com