Trigger execution role

From: Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Trigger execution role
Date: 2021-02-12 17:35:55
Message-ID: CAMsGm5f1mKFyegp3iDa65knihngBfXpq4pQb_JJgHu6DV1Wu1A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I was trying to use triggers, and ran into something I hadn't realized
until now: triggers run, not as the owner of the table, but as the user who
is doing the insert/update/delete.

It seems to me that for a lot of the suggested uses of triggers this is not
the desired behaviour. For example, in the canonical "logging table"
example, we want to be able to allow users to make changes to the base
table, but we don't want them to be able to insert to the log table except
indirectly by causing the trigger to fire. Even in cases where a trigger is
just checking whether the update is permissible, it seems to me that it
might be useful to refer to information not accessible to the user doing
the changes.

Has there been any discussion of this before? I know Postgres has had
triggers for a long time but at the same time I don't see how anybody could
do a lot of work with triggers without finding this to be a problem at some
point. I haven't found any discussion of this in the documentation.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2021-02-12 17:44:54 Re: pg13.2: invalid memory alloc request size NNNN
Previous Message Tom Lane 2021-02-12 17:19:43 Re: Detecting pointer misalignment (was Re: pgsql: Implementation of subscripting for jsonb)