Message-ID: <ca81922f585aeedfb33eeeecb805dfa4a61e072b.camel@cybertec.at>
Subject: Re: BUG #18936: Trigger enable users to modify the tables which
 hedoesn't have privilege
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: ZhangChi <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Date: Sat, 24 May 2025 07:09:56 +0200
In-Reply-To: <tencent_6FB22713DA38A8D08B25F3DDD06B9C792B07@qq.com>
References: <18936-197af1cbbd49156f@postgresql.org>
		<c842110a59d8c273c2edecc3510e2c3a4bca3d3c.camel@cybertec.at>
	 <tencent_6FB22713DA38A8D08B25F3DDD06B9C792B07@qq.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.1 (3.56.1-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ca81922f585aeedfb33eeeecb805dfa4a61e072b.camel%40cybertec.at>
Precedence: bulk

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

The effects of a trigger are limited by the permissions of the executing us=
er 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