Row-Trigger implicitly allows users ACL_SELECT - Mailing list pgsql-hackers

* Row-Update/Delete trigger mechanism allows user defined triggers to refer the older tuple updated/deleted.
* The ACL_TRIGGER privilege allows normal users to set up triggers on the relation allowed.

It means someone with ACL_TRIGGER can set up a trigger which write
out the given older tuple into somewhere.
In logically, it also means users with ACL_TRIGGER and either of
ACL_UPDATE or ACL_DELETE are allowed to read the table without
ACL_SELECT permission.
It has been my concern. Basically, I don't think it is a good
design a permission implicitly contains different meanings.

See the following steps.
A normal user 'ymj' allows 'tak' ACL_UPDATE and ACL_TRIGGER on
the table 't1', but ACL_SELECT is not allowed
'tak' tries to define his function which write out the OLD tuple
into his table, and he also set up this function as a trigger of
't1'.
Then, his invoke unconditional UPDATE on 't1' and can read whole
of the table.

(1) Create normal users: 'ymj' and 'tak'
 postgres=# CREATE USER ymj; CREATE ROLE postgres=# CREATE USER tak; CREATE ROLE postgres=# \q

(2) 'ymj' create a table and grant UPDATE and TRIGGER to 'tak'
 [kaigai@saba ~]$ psql postgres -U ymj psql (8.4devel) Type "help" for help.
 postgres=> CREATE TABLE t1 (a int, b text); CREATE TABLE postgres=> INSERT INTO t1 VALUES (1,'aaa'), (2,'bbb'),
(3,'ccc');INSERT 0 3 postgres=> GRANT UPDATE ON t1 TO tak; GRANT postgres=> GRANT TRIGGER ON t1 TO tak; GRANT
postgres=>\q
 

(3) 'tak' create a function and set a trigger
 postgres=> CREATE TABLE t2 (x text); CREATE TABLE postgres=> CREATE OR REPLACE FUNCTION f1() RETURNS trigger
postgres->    language 'plpgsql' as' postgres'>     BEGIN postgres'>         INSERT INTO t2 VALUES(OLD::text);
postgres'>        RETURN NEW; postgres'>     END'; CREATE FUNCTION postgres=> CREATE TRIGGER tg1 BEFORE UPDATE ON t1
postgres->    FOR ROW EXECUTE PROCEDURE f1(); CREATE TRIGGER
 

(4) 'tak' update the table t1. He can also read 't1'   without ACL_SELECT
 postgres=> BEGIN; BEGIN postgres=> UPDATE t1 SET a = 1; UPDATE 3 postgres=> SELECT * FROM t2;     x ---------  (1,aaa)
(2,bbb)  (3,ccc) (3 rows)
 
 postgres=> ABORT; ROLLBACK


In a practical sense, we seldom assign users writer-permission without
reader-permission, because they cannot specify what tuple should be
updated/delete. But it does not mean we can overlook the access control
facility does not work well.

I think we should assign ACL_SELECT on rte->requiredPerms and set
a bit for attno=0 on rte->selectedCols, when the target relation has
user defined Row-Update/Delete triggers and CmdType matches them.

(But the triggers to check FK constraints can be an exceptionbecause these are built-in, so obviously it is not
malicious.)

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>