Thread

Add "current_setting('role');" to trigger function examples of audit tables

The Post Office <noreply@postgresql.org> — 2025-12-02T16:06:07Z

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/18/plpgsql-trigger.html
Description:

The doc page https://www.postgresql.org/docs/18/plpgsql-trigger.html
contains examples of using trigger functions to make an audit table. When
SECURITY DEFINER is used in conjunction with SET ROLE it was not clear to me
how to store the acting role in the audit table rather than the owner of the
trigger function (i.e. current_user, session_user).

I found an old mailing list message
https://www.postgresql.org/message-id/flat/000801c64143%24313bba50%241e01a8c0%40weasel
that suggests using "current_setting('role');", and seems to do exactly what
I need. This seems to be little known information as searching the web
suggests all sorts of different workarounds for this scenario with BEFORE
triggers or creating custom settings. Therefore I suggest this knowledge is
rediscovered and mentioned in the official documentation in
plpgsql-trigger.html.