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

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