permissions bug in RI checks?

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
david@endpoint.com