We did something like this, by using postgresql's `set_config()` before every transaction. The trigger functions can read that value using `current_setting()`. It works for us, but for a relatively low-traffic internal application.

I have experimented with mapping application users 1:1 to individual database users, and using SET ROLE at the start of every transaction.

I got it working in a PoC, and with the right configuration of roles, this pattern would give you user identity and audit right down to the database itself.

Sadly I haven’t pulled it off in production environment yet, but if I ever get the chance to work at this level in an enterprise app again, I’ll definitely do it.

I implemented in the app layer so had the user available to implement the new audit record. I suppose doing it in the DB layer would impose a challenge. The few ways to solve that that I’m aware of would require a) your app to kick off a function with the user id of the mutation or b) pushing user auth into the DB layer and principal switching your DB connection. Both have downsides IMO.

I don’t typically favor DB level solutions because bleeding app logic beyond basic schema and integrity means you lose some portability and/or your architecture messes separation of concerns pretty quick. Maybe that means you don’t get the full benefit of the DB but it’s a trade I usually make.

We do this in SQL Server by running a SET CONTEXT_INFO command every time we open a connection. This makes the current user ID available to any SQL that runs.