Re: ExecutorCheckPerms() hook

(2010/05/25 10:13), Stephen Frost wrote:
> KaiGai,
> 
> * KaiGai Kohei (kaigai@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
TABLEpostgres=# REVOKE ALL ON pk_tbl, fk_tbl FROM ymj; REVOKE postgres=# GRANT REFERENCES ON pk_tbl, fk_tbl TO ymj;
GRANTpostgres=# SET ROLE ymj; SET postgres=> ALTER TABLE fk_tbl ADD FOREIGN KEY (x) REFERENCES pk_tbl (a); ERROR:
permissiondenied 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 <kaigai@ak.jp.nec.com>