Hi,
I reproduced the superuser case on PostgreSQL 18.4 and master
(5e592904025). Starting as a superuser:
CREATE ROLE o;
CREATE ROLE g;
CREATE ROLE r;
GRANT CREATE ON SCHEMA public TO o;
SET SESSION AUTHORIZATION o;
CREATE TABLE t (a int);
GRANT SELECT ON t TO g WITH GRANT OPTION;
SET SESSION AUTHORIZATION g;
GRANT SELECT ON t TO r;
RESET SESSION AUTHORIZATION;
ALTER ROLE g SUPERUSER;
REVOKE GRANT OPTION FOR SELECT ON t FROM g; -- no error
ALTER ROLE g NOSUPERUSER;
The REVOKE succeeds and leaves r=r/g in relacl, although g no longer
has the grant option. pg_dump emits the dependent grant under
SET SESSION AUTHORIZATION g. Restoring into a fresh database reports
"WARNING: no privileges were granted" and leaves r without SELECT.
With your patch, REVOKE fails with "dependent privileges exist";
with CASCADE, it removes r's grant.
Without GRANTED BY, a superuser's GRANT is recorded under the object
owner, which is why the sequence above has g grant before it is
promoted. On master, GRANTED BY g can also record the grant under g
while it is already a superuser.
I added a test for this case next to the atest4_groupowned tests.
Reverting just the acl.c change makes it fail: REVOKE succeeds, and
r keeps SELECT after CASCADE. The attached 0001 is your v1 rebased
onto master, with no changes to the code or tests; 0002 adds the
superuser test. Please feel free to fold 0002 into your patch.
I labeled them v2 only so the two apply as a set; renumber as you
like.
The pair applies with git am on REL_16_STABLE through REL_18_STABLE.
On 14 and 15, the acl.c prototype hunk needs git am -3. The core
regression suite passes on all five branches, and on master.
This sequence does not self-grant, so it does not exercise the
check_circularity issue discussed earlier in the thread.
Regards,
Paul Kim
