Pass additional data in connection for use by PGSQL Trigger

Question

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.

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):

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;