(2010/05/25 10:13), Stephen Frost wrote: > KaiGai, > > * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: >> postgres=# ALTER TABLE pk_tbl OWNER TO ymj; >> ALTER TABLE >> postgres=# ALTER TABLE fk_tbl OWNER TO ymj; >> ALTER TABLE >> postgres=# REVOKE ALL ON pk_tbl, fk_tbl FROM ymj; >> REVOKE >> postgres=# GRANT REFERENCES ON pk_tbl, fk_tbl TO ymj; >> GRANT >> >> At that time, the 'ymj' has ownership and REFERENCES permissions on >> both of pk_tbl and fk_tbl. In this case, RI_Initial_Check() shall return >> and the fallback-seqscan will run. But, > > ymj may be considered an 'owner' on that table, but in this case, it > doesn't have SELECT rights on it. Now, you might argue that we should > assume that the owner has SELECT rights (since they're granted by > default), even if they've been revoked, but that's a whole separate > issue.
Yes, it is entirely separate issue. I don't intend to argue whether we can assume the default PG permission allows owner to SELECT on the table, or not. >> postgres=> ALTER TABLE fk_tbl ADD FOREIGN KEY (x) REFERENCES pk_tbl (a); >> ERROR: permission denied for relation pk_tbl >> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."pk_tbl" x WHERE "a" >> OPERATOR(pg_catalog.=) $1 FOR SHARE OF x" > > I think you've got another issue here that's not related. Perhaps > something wrong with a patch you've applied? Otherwise, what version of > PG is this? Using 8.2, 8.3, 8.4 and a recent git checkout, I get: > > postgres=# CREATE USER ymj; > CREATE ROLE > postgres=# CREATE TABLE pk_tbl (a int primary key, b text); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_tbl_pkey" > for table "pk_tbl" > CREATE TABLE > postgres=# CREATE TABLE fk_tbl (x int, y text); > CREATE TABLE > postgres=# ALTER TABLE pk_tbl OWNER TO ymj; > ALTER TABLE > postgres=# ALTER TABLE fk_tbl OWNER TO ymj; > ALTER TABLE > postgres=# REVOKE ALL ON pk_tbl, fk_tbl FROM ymj; > REVOKE > postgres=# GRANT REFERENCES ON pk_tbl, fk_tbl TO ymj; > GRANT > postgres=# SET ROLE ymj; > SET > postgres=> ALTER TABLE fk_tbl ADD FOREIGN KEY (x) REFERENCES pk_tbl (a); > ALTER TABLE > postgres=> Sorry, I missed to copy & paste INSERT statement just after CREATE TABLE. The secondary RI_FKey_check_ins() is invoked during the while() loop using heap_getnext(), so it is not called for empty table. For correctness, postgres=# CREATE USER ymj; CREATE ROLE postgres=# CREATE TABLE pk_tbl (a int primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_tbl_pkey" for table "pk_tbl" CREATE TABLE postgres=# CREATE TABLE fk_tbl (x int, y text); CREATE TABLE | postgres=# INSERT INTO pk_tbl VALUES (1,'aaa'), (2,'bbb'), (3,'ccc'); | INSERT 0 3 | postgres=# INSERT INTO fk_tbl VALUES (1,'xxx'), (2,'yyy'), (3,'zzz'); | INSERT 0 3 postgres=# ALTER TABLE pk_tbl OWNER TO ymj; ALTER TABLE postgres=# ALTER TABLE fk_tbl OWNER TO ymj; ALTER TABLE postgres=# REVOKE ALL ON pk_tbl, fk_tbl FROM ymj; REVOKE postgres=# GRANT REFERENCES ON pk_tbl, fk_tbl TO ymj; GRANT postgres=# SET ROLE ymj; SET postgres=> ALTER TABLE fk_tbl ADD FOREIGN KEY (x) REFERENCES pk_tbl (a); ERROR: permission denied for relation pk_tbl CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."pk_tbl" x WHERE "a" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x" I could reproduce it on the 8.4.4, but didn't try on the prior releases. Thanks, -- KaiGai Kohei <kai...@ak.jp.nec.com> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
