Postgresql – Pass additional data in connection for use by PGSQL Trigger

postgresqltrigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com – 9.1+ audit trigger ), which gets me started.

What I would like to do is pass along some additional data automatically, such as the user-id or other key, so that the trigger can link the change back to the application user that made it. The basic flow would go like this:

  • Get a connection for the current request
  • Run custom SQL to set a context variable (set conn_context = {userid};)
  • Read / parse that variable inside the trigger
  • Write the value to a log table along with the other information

When using MS SQL I was able to execute a statement that set the context for the connection by executing SET CONTEXT_INFO ...data.... I am hoping there is a way to do this for PG; I haven't found one yet, but that may just be bad google-fu.

Best Answer

Old question, but still valid, so here is what I ended up using.

On 9.6 and above, to set the values either of the following will work:

  • select set_config('app.userid', '3', true)
  • SET LOCAL "app.userid" = 3; (or '3')

To read the value out,

  • select current_value('app.userid')

I ended up wrapping the lookup for the userid or other field in a function, to handle the type conversion(s) and the fact that it might not be set:

CREATE OR REPLACE FUNCTION app_get_userid() RETURNS INT AS $$
DECLARE userid integer;
BEGIN
    BEGIN
        userid := current_setting('app.userid');
    EXCEPTION
        WHEN OTHERS THEN userid := NULL;
    END;
    return userid;
END;
$$ LANGUAGE plpgsql;
Related Question