Thread: [HACKERS] Where is it documented what role executes constraint triggers?

From a little experimenting in 9.5, it seems that a referential
integrity trigger is executed with the identity of the referencED
table's owner, but I have not been able to find this covered in
the docs. Is this a documentation oversight, or is it explained
somewhere I didn't look (or may have skimmed right over it)?

The question came up at $work after the departure of $colleague,
who had created some tables as himself and not changed their
ownership. His role had the superuser bit at the time, so
RI checks involving those tables never incurred 'permission denied'
errors until he left. Then, his role was not dropped, only disabled
for login and made no longer superuser, and that's when RI checks
started incurring 'permission denied'.

-Chap


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Nov 03, 2017 at 02:09:00PM -0400, Chapman Flack wrote:
> From a little experimenting in 9.5, it seems that a referential
> integrity trigger is executed with the identity of the referencED
> table's owner, but I have not been able to find this covered in
> the docs. Is this a documentation oversight, or is it explained
> somewhere I didn't look (or may have skimmed right over it)?
> 
> The question came up at $work after the departure of $colleague,
> who had created some tables as himself and not changed their
> ownership. His role had the superuser bit at the time, so
> RI checks involving those tables never incurred 'permission denied'
> errors until he left. Then, his role was not dropped, only disabled
> for login and made no longer superuser, and that's when RI checks
> started incurring 'permission denied'.

Are the trigger functions SECURITY DEFINER?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Chapman Flack <chap@anastigmatix.net> writes:
> From a little experimenting in 9.5, it seems that a referential
> integrity trigger is executed with the identity of the referencED
> table's owner, but I have not been able to find this covered in
> the docs. Is this a documentation oversight, or is it explained
> somewhere I didn't look (or may have skimmed right over it)?

Don't know if it's documented anywhere user-facing, but a look into
the code in ri_triggers.c says we run RI queries as the owner of
whichever table the query touches (the referenced table for verification
of FK inserts, the referencing table when cascading a PK change).
        regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers