On Wed, Mar 20, 2024 at 1:26 PM Étienne BERSAC <etienne.ber...@dalibo.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
