* 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'
[kai...@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 exception
because these are built-in, so obviously it is not malicious.)
Thanks,
--
OSS Platform Development Division, NEC
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
