public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jacob Champion <[email protected]>
To: [email protected]
Subject: REVOKE's CASCADE protection doesn't work with INHERITed table owners
Date: Wed, 24 Jun 2026 14:57:13 -0700
Message-ID: <CAOYmi+=KTLd+XsEP=TDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ@mail.gmail.com> (raw)

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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: REVOKE's CASCADE protection doesn't work with INHERITed table owners
  In-Reply-To: <CAOYmi+=KTLd+XsEP=TDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox