Hey -hackers,
In doing a schema upgrade, we noticed the following behavior, which certainly
seems like a bug. Steps to reproduce:
CREATE USER a;
CREATE USER b;
CREATE TABLE t1 (id serial primary key);
CREATE TABLE t2 (id int references t1(id));
ALTER TABLE t1 OWNER TO a;
ALTER TABLE t2 OWNER TO a;
\c - a
REVOKE ALL ON t1 FROM a;
REVOKE ALL ON t2 FROM a;
GRANT ALL ON t1 TO b;
GRANT ALL ON t2 TO b;
\c - b
INSERT INTO t2 (id) VALUES (1);
ERROR: permission denied for relation t1
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."t1" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
The bug in this case is that "b" has full permissions on all of the underlying
tables, but runs into issues when trying to access the referenced tables. I
traced this down to the RI checks, specifically the portion in ri_PlanCheck()
where it calls SetUserIdAndSecContext() and then later runs the queries in the
context of the owner of the relation. Since the owner "a" lacks SELECT and
UPDATE privileges on the table, it is not able to take the ShareLock, and spits
out the above error. This behavior does not occur if the object owner is a
database superuser. This is presumably because the superuser bypasses the
regular ACL checks and succeeds regardless.
The behavior was originally noted in 8.1.21, but exists as well in HEAD.
No real resolution proposed, but I wanted to understand the reason behind the
restrictions if it was intentional behavior.
Thanks,
David
Regards,
David
--
David Christensen
End Point Corporation
da...@endpoint.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
