Re: ExecutorCheckPerms() hook

(2010/05/25 10:13), Stephen Frost wrote:
> KaiGai,
>
> * KaiGai Kohei (kaigai(at)ak(dot)jp(dot)nec(dot)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 <kaigai(at)ak(dot)jp(dot)nec(dot)com>