Thread: Row-Trigger implicitly allows users ACL_SELECT

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

KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
> * 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.

Granting TRIGGER privilege already implies an exceedingly high trust
level, since a trigger can do arbitrary damage to your data.  I don't
find this concern interesting, and your solution wouldn't work anyway
(AFAICS it would check the permissions of the user doing the UPDATE,
not those of the user who created the trigger).
        regards, tom lane

Tom Lane wrote:
> KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
>> * 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.
> 
> Granting TRIGGER privilege already implies an exceedingly high trust
> level, since a trigger can do arbitrary damage to your data.  I don't
> find this concern interesting, and your solution wouldn't work anyway
> (AFAICS it would check the permissions of the user doing the UPDATE,
> not those of the user who created the trigger).

I think we have two attitudes/options.
The one considers ACL_TRIGGER just as a privilege to create a trigger,
so we need to check when the backend delivers OLD/NEW into triggers,
as I noted today.
The other considers triggers are high trusted process and ACL_TRIGGER
privilege includes a right to set up such a trusted one, as you noted.

From the point of SE-PostgreSQL, it can accept either of them.
(If we take the later option, it will need db_procedure:{install}
checks (in the future revision), because of it means user defined
triggers installed as a part of system internal stuff.)

The current implementation adopt the former stance, but it may
be better to adopt the later one, in the sense of consistency.

Thanks,
-- 
KaiGai Kohei <kaigai@kaigai.gr.jp>