| 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
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Zsolt Parragi | 2026-06-24 20:31:53 | Re: uuidv7 improperly accepts dates before 1970-01-01 |