Hacker News Reader

Comment by sususu | Hacker News Reader

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?

View on HN

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.

That's really interesting and gave me some (hopefully) good ideas.

Thank you very much!

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

PostgREST has an interesting approach to authentication that might give you some ideas:

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).

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

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

Reply on news.ycombinator.com