Database Audit Log for Web Application Requires User ID

Question

I have a web application that currently supports PostgreSQL as a backend only and has its own user management. Connection to the database is performed using a common user account using Postgres authorization mechanisms. This application should now get an audit trail for some created and modified data, which I hope is based only on triggers and stored procedures, and for performance reasons it would be nice if everything worked both asynchronously and independently of the web application.

There is one main problem: we want to know which user of the web application made some changes or created data, but this part of the information is usually not available in stored procedures out of the box, because we connect to Postgres using a common Account. What I would like to avoid is calling stored procedures in the web application and providing user identifiers in this way, since that would mean adding the appropriate calls to possible places in the web application. In addition, we make updates to the data model directly, using a database without a web application, that is, we need triggers anyway.

Currently, the web application is working with a transaction launched on each request and committed from its end. Therefore, I want to create a temporary table inside a transaction in each query, which always gets the current query user identifier, which should be available for stored procedures executed by triggers when creating or changing data.

Some things that I don’t know are if the stored procedures that are being executed can even access the current database transaction and therefore can retrieve the current user ID from the temporary table? The table is not part of the modified data for which the trigger is created. How to affect performance if each query creates a temporary table to store only one whole in the worst case? If the trigger has access to the temporary table, the request can be completed at any time because it is executed or errors. How will this affect a trigger that will have access to a temporary table that only leaves the transaction, which will be committed or canceled, because stored procedures want to read the user ID?

Is there any other way by which I can map the user ID to some unique transaction ID that the trigger has access to? In addition to the temporary table, I could create a regular table in which I map user IDs to transaction IDs at the start of each query, but regardless of the current transaction. If the trigger now receives a transaction identifier that is responsible for executing the trigger, stored procedures can use the transaction identifier to find the identifier of the user who used the transaction.

Any thoughts? Thanks!

I added a question about the trigger lifetime and the execution context that is appropriate for this question:

PostgreSQL database trigger execution context

Answer 1

A temporary table seems redundant when you can just use session setup.

Prior to version 9.1, user session variables had to be declared in postgresql.conf via custom_variable_classes , which made it a little cumbersome, deploying.

Starting with 9.2, this is no longer necessary. Thus, you can simply specify the connection time:

SET myapp.myusername='foobar';

and then in a trigger, or virtually anywhere in the SELECT current_setting('myapp.myusername') session, SELECT current_setting('myapp.myusername') or SHOW myapp.myusername will return a value.