* 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>