REVOKE's CASCADE protection doesn't work with INHERITed table owners

From: Jacob Champion <jacob(dot)champion(at)enterprisedb(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: REVOKE's CASCADE protection doesn't work with INHERITed table owners
Date: 2026-06-24 21:57:13
Message-ID: CAOYmi+=KTLd+XsEP=TDiZ48iVf-CEc7JrZd5uhWPYWKEfOgyyQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Previous Message Zsolt Parragi 2026-06-24 20:31:53 Re: uuidv7 improperly accepts dates before 1970-01-01