5

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
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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