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.


Your Answer

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

Browse other questions tagged or ask your own question.