Horton
Menu

Thread

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

    PG Bug reporting form <noreply@postgresql.org> — 2025-05-20T13:07:47Z

    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.

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

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-05-21T06:17:53Z

    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

  3. Re: BUG #18936: Trigger enable users to modify the tables which hedoesn't have privilege

    ZhangChi <798604270@qq.com> — 2025-05-24T03:06:24Z

    Thanks for your reply!


However, it is common in some database servers for an attacker to gain minimal privileges on a single table within a target database. For instance, when registering an account on a service, the system might grant the user access to a dedicated table. Using the TRIGGER mechanism as I showed, such an attacker could then delete or exfiltrate data from other tables beyond their authorized access. Notably, this attack doesn't require superuser privileges - only access to the two relevant tables.


Permitting users to create triggers that can affect tables beyond their privilege scope appears to be a problematic design choice. Such triggers may be inadvertently executed by privileged users without their knowledge, creating potential security vulnerabilities.

  4. Re: BUG #18936: Trigger enable users to modify the tables which hedoesn't have privilege

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-05-24T05:09:56Z

    On Sat, 2025-05-24 at 11:06 +0800, ZhangChi wrote:
> However, it is common in some database servers for an attacker to gain minimal privileges
> on a single table within a target database. For instance, when registering an account on a
> service, the system might grant the user access to a dedicated table. Using the TRIGGER
> mechanism as I showed, such an attacker could then delete or exfiltrate data from other
> tables beyond their authorized access. Notably, this attack doesn't require superuser
> privileges - only access to the two relevant tables.
> 
> Permitting users to create triggers that can affect tables beyond their privilege scope
> appears to be a problematic design choice. Such triggers may be inadvertently executed
> by privileged users without their knowledge, creating potential security vulnerabilities.

The effects of a trigger are limited by the permissions of the executing user or
(in the case of SECURITY DEFINER) the owner of the trigger function.

Therefore, as I said, it is commendable never to do DML as a superuser.

There are cases where superusers perform DML, like restoring a pg_dump.
PostgreSQL takes great care that nothing can go wrong in these cases.

Yours,
Laurenz Albe