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
toaccess the referenced tables. I traced this down to the RI checks, specifically the portion in ri_PlanCheck() where
itcalls SetUserIdAndSecContext() and then later runs the queries in the context of the owner of the relation. Since
theowner "a" lacks SELECT and UPDATE privileges on the table, it is not able to take the ShareLock, and spits out the
aboveerror. This behavior does not occur if the object owner is a database superuser. This is presumably because the
superuserbypasses 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
david@endpoint.com