News for hackers. Stuff that matters.
Re:Easy, alternative soft deletion: `deleted_record_insert` by hdra on Sunday January 01, 2023 @02:25AM (#34204375)
I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change..

how did solve that particular issue? or was it not a requirement in your case?

Re:Easy, alternative soft deletion: `deleted_record_insert` by nkrumm on Sunday January 01, 2023 @02:40AM (#34204435)
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.
Re:Easy, alternative soft deletion: `deleted_record_insert` by doctor_eval on Sunday January 01, 2023 @04:45AM (#34205030)
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.

Re:Easy, alternative soft deletion: `deleted_record_insert` by drittich on Sunday January 01, 2023 @09:23AM (#34206452)
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.
Re:Easy, alternative soft deletion: `deleted_record_insert` by rubyfan on Sunday January 01, 2023 @01:04PM (#34208847)
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.