MIME-Version: 1.0
From: Jacob Champion <jacob.champion@enterprisedb.com>
Date: Wed, 24 Jun 2026 14:57:13 -0700
Message-ID: 
 <CAOYmi+=KTLd+XsEP=TDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ@mail.gmail.com>
Subject: REVOKE's CASCADE protection doesn't work with INHERITed table owners
To: pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOYmi%2B%3DKTLd%2BXsEP%3DTDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ%40mail.gmail.com>
Precedence: bulk

Hi all,

I'm pretty sure the following is unintended behavior. It looks
potentially related to [1] as well.

TL;DR: The protection in recursive_revoke() against broken GRANT
OPTION chains doesn't seem to work properly when the grantee also
holds the privileges of the grantor.

= Setup =

With the following (contrived, minimized from the actual) setup:

    -- Set up a role hierarchy (as superuser "jacob")
    CREATE ROLE admins;
    CREATE ROLE bob;
    GRANT admins TO bob;

    -- Create two tables with different ownership
    CREATE TABLE my_table(i int);
    CREATE TABLE admin_table(i int);
    ALTER TABLE admin_table OWNER TO admins;

    -- Create a grant option chain on both tables
    GRANT ALL ON TABLE my_table TO bob WITH GRANT OPTION;
    GRANT ALL ON TABLE admin_table TO bob WITH GRANT OPTION;

    SET ROLE bob;
    GRANT ALL ON TABLE my_table TO bob;
    GRANT ALL ON TABLE admin_table TO bob;
    RESET ROLE;

Now we have the following ACLs:

    =# SELECT relname, relowner::regrole, relacl FROM pg_class
        WHERE relname LIKE '%_table';
    -[ RECORD 1
]-------------------------------------------------------------------
    relname  | my_table
    relowner | jacob
    relacl   | {jacob=arwdDxtm/jacob,bob=a*r*w*d*D*x*t*m*/jacob,bob=arwdDxtm/bob}
    -[ RECORD 2
]-------------------------------------------------------------------
    relname  | admin_table
    relowner | admins
    relacl   | {admins=arwdDxtm/admins,bob=a*r*w*d*D*x*t*m*/admins,bob=arwdDxtm/bob}

= Bug =

With that grant option chain, we try to prevent REVOKE [RESTRICT]
invocations that would cause problems:

    =# REVOKE ALL ON TABLE my_table FROM bob;
    ERROR:  dependent privileges exist
    HINT:  Use CASCADE to revoke them too.

But this protection doesn't work for the admin_table...

    =# REVOKE ALL ON TABLE admin_table FROM bob;
    REVOKE

...resulting in an orphaned ACL.

    -[ RECORD 2
]----------------------------------------------------------------
    relname  | admin_table
    relowner | admins
    relacl   | {admins=arwdDxtm/admins,bob=arwdDxtm/bob}

Dump/restores of this situation result in complaints, since user "bob"
isn't able to recreate the grant.

I think the issue is in recursive_revoke()'s usage of aclmask(), which
in turn uses has_privs_of_role(). It doesn't seem like that's what was
wanted in this particular case... thoughts?

Thanks,
--Jacob

[1] https://postgr.es/m/CAM6Zo8wD7RtQNhbQHODc9DobiW+GpT=tnqOSMz4+mnzA9m0zMg@mail.gmail.com