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.


1 Answer 1


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 (Postgres > 9.6)

For any remotely modern postgres, you can read the value and return null if it is not set. Thanks @motcke for the update in the comments.

-- pass `true` to return null if missing (see original answer)
userid := current_setting('app.user_id', true) 

Original answer, for (Postgres <= 9.5)

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:

For versions <= 9.5 (as the question was concerned):

DECLARE userid integer;
        userid := current_setting('app.userid');
        WHEN OTHERS THEN userid := NULL;
    return userid;
$$ LANGUAGE plpgsql;
  • for getting the setting value, no need for the exception. you can use current_setting('app.userid', true) which return null if missing
    – motcke
    Jan 18, 2023 at 13:09
  • 1
    True, on 9.6+ that is definitely correct. I wrote the question for 9.1 it seems, but part of my answer (6 years later) mentions 9.6. Very confusing. I'll update the answer. Thanks for catching that.
    – Andrew
    Jan 18, 2023 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.