5 ms·

I really like this approach, however I have a use case where the application user id that made the operation must be saved in the audit table, unfortunately, I

by sususu 5y ago

I really like this approach, however I have a use case where the application user id that made the operation must be saved in the audit table, unfortunately, I cannot see how to do that with a pure SQL solution. Has anyone done something similar with SQL only?

oliverrice 5y ago
if `user_id` is in the table you're auditing you could do ``` alter table audit.record_version add column user_id bigint default (coalesce(record, old_record) ->> 'user_id'); ``` or if you meant the SQL `current_user` you could extend the trigger to track it. but if the user_id is only available in application space (not in the db) it will not be possible using straight SQL
timwis 5y ago
PostgREST has an interesting approach to authentication that might give you some ideas: https://postgrest.org/en/stable/auth.html https://postgrest.org/en/stable/auth.html Supabase is built on top of PostgREST, but I can’t say for sure it uses the same means of authentication (I think it has its own Auth layer).
- oliverrice 5y ago
  Supabase passes all the JWT info to postgrest so it is still available in transaction local config
netcraft 5y ago
we do that by setting a local variable before the query and then reading that in the triggers: ``` SET LOCAL foo.who_id = 'some-uuid'; UPDATE table SET ... ``` ``` -- function to make getting the setting easier DROP FUNCTION IF EXISTS get_who_id (text); CREATE OR REPLACE FUNCTION get_who_id (default_value text DEFAULT null::text) RETURNS text AS $get_who_id$ DECLARE who_id text; BEGIN BEGIN who_id := current_setting('foo.who_id'); EXCEPTION WHEN SQLSTATE '42704' THEN RETURN default_value; END; IF (length(who_id) = 0) THEN RETURN default_value; END IF; return who_id; END $get_who_id$ LANGUAGE plpgsql VOLATILE; ``` ``` CREATE OR REPLACE FUNCTION some_table_audit () RETURNS TRIGGER AS $some_table_audit$ DECLARE who_id text; BEGIN who_id := get_who_id(null::text); ... ``` Identifiers changed, but hopefully will give you the idea.
- sususu 5y ago
  That's really interesting and gave me some (hopefully) good ideas. Thank you very much!
- tda 5y ago
  I've experiment with a very similar solution and it felt a bit dirty but so far it seems to be working just fine. I have made an integration for auditing with sqlmodel which I intend to share, but it is kind of rough and I was a bit stuck trying to clean it up. The idea is that you add e.g. a HeroHistory model derived from HeroBase and a HistoryMixin that creates the triggers and relationships to have an Audit log of the Hero table. If anyone is interested give me a shout out

by sususu 5y ago

oliverrice 5y ago

if `user_id` is in the table you're auditing you could do ``` alter table audit.record_version add column user_id bigint default (coalesce(record, old_record) ->> 'user_id'); ``` or if you meant the SQL `current_user` you could extend the trigger to track it. but if the user_id is only available in application space (not in the db) it will not be possible using straight SQL

timwis 5y ago

PostgREST has an interesting approach to authentication that might give you some ideas: https://postgrest.org/en/stable/auth.html https://postgrest.org/en/stable/auth.html Supabase is built on top of PostgREST, but I can’t say for sure it uses the same means of authentication (I think it has its own Auth layer).

oliverrice 5y ago
Supabase passes all the JWT info to postgrest so it is still available in transaction local config

netcraft 5y ago

we do that by setting a local variable before the query and then reading that in the triggers: ``` SET LOCAL foo.who_id = 'some-uuid'; UPDATE table SET ... ``` ``` -- function to make getting the setting easier DROP FUNCTION IF EXISTS get_who_id (text); CREATE OR REPLACE FUNCTION get_who_id (default_value text DEFAULT null::text) RETURNS text AS $get_who_id$ DECLARE who_id text; BEGIN BEGIN who_id := current_setting('foo.who_id'); EXCEPTION WHEN SQLSTATE '42704' THEN RETURN default_value; END; IF (length(who_id) = 0) THEN RETURN default_value; END IF; return who_id; END $get_who_id$ LANGUAGE plpgsql VOLATILE; ``` ``` CREATE OR REPLACE FUNCTION some_table_audit () RETURNS TRIGGER AS $some_table_audit$ DECLARE who_id text; BEGIN who_id := get_who_id(null::text); ... ``` Identifiers changed, but hopefully will give you the idea.

sususu 5y ago
That's really interesting and gave me some (hopefully) good ideas. Thank you very much!
tda 5y ago
I've experiment with a very similar solution and it felt a bit dirty but so far it seems to be working just fine. I have made an integration for auditing with sqlmodel which I intend to share, but it is kind of rough and I was a bit stuck trying to clean it up. The idea is that you add e.g. a HeroHistory model derived from HeroBase and a HistoryMixin that creates the triggers and relationships to have an Audit log of the Hero table. If anyone is interested give me a shout out