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

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: mark(at)clow(dot)es
Subject: Add "current_setting('role');" to trigger function examples of audit tables
Date: 2025-12-02 16:06:07
Message-ID: 176469156745.2921403.7483097841149354308@wrigleys.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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.

Browse pgsql-docs by date

  From Date Subject
Next Message Michael Paquier 2025-12-03 00:39:04 Re: restart point vs restartpoint in the docs
Previous Message Laurenz Albe 2025-12-02 11:31:50 Re: restart point vs restartpoint in the docs