Best Practice for User Context Tracking in PostgreSQL Audit Logs - Multi-Statement Limitation

[appbuildernyc]

1. My goal:

I'm building a comprehensive audit logging system for my vehicle inventory management platform. I need to track every database change (INSERT, UPDATE, DELETE) with the correct user identity in a PostgreSQL history table. The audit trail must be tamper-proof - users should not be able to manipulate or fake who performed an action.

My PostgreSQL trigger function uses current_setting('app.current_user') to capture the user who made each change, but I need to set this session variable from Retool before each query executes.

2. Issue:

Retool SQL queries don't support multiple statements separated by semicolons, so I cannot do this in a single query:

This doesn't work in Retool
SET LOCAL app.current_user = 'John Smith';
UPDATE vehicles SET status = 'Sold' WHERE id = 123;

I've tried using CTEs (Common Table Expressions) to set the user context and run the query:

WITH _user AS (
    SELECT set_config('app.current_user', '{{ current_user.fullName }}', false)
)
UPDATE vehicles SET status = {{ newStatus }} WHERE id = {{ vehicleId }}
RETURNING *;

However, the PostgreSQL trigger fires BEFORE the session variable is set by the CTE, resulting in "User context not set" errors.

3. Steps I've taken to troubleshoot:

Created a separate setUserContext query that I call before each update:

SELECT set_config('app.current_user', {{ author }}, true) as user_set;

Modified my event handlers to chain queries:

await setUserContext.trigger({ author: current_user.fullName });
await updateVehicleStatus.trigger({ vehicleId, newStatus });

This works, but I have 50+ queries across my app that modify data, and manually chaining setUserContext before each one is:

• Error-prone (easy to forget)
• Repetitive code
• Hard to maintain

4. Additional info:

Environment: Self hosted Retool

Subscription: Business

DB: Postgres 17
Retool version: 3.284.0

What I'm looking for:

1. Is there a way to configure PostgreSQL resource in Retool to automatically execute a statement before EVERY query? (Like a connection-level hook or interceptor)

2. Does Retool have query middleware or pre-query execution hooks that run automatically before database operations?

3. Are there resource-level configuration options that could inject user context automatically (like connection string parameters)?

4. How do other teams handle audit logging with user context in Retool + PostgreSQL? What's the recommended pattern?

UPDATE

Current Issues:

• Connection pooling makes session variables unreliable, “SET LOCAL app.current_user = 'John Smith';” this cant be considered as a solution

• No consistent way to ensure user context is set before every database operation

[Marcus_Santos]

Hi @appbuildernyc , I did face this in the past and I solve it using the approach in this post:

• Audit Logs on Retool Applications

I hope this helps you!

[Jack_T]

Hi @appbuildernyc,

Interesting use case and request.

To answer your questions, Retool does not have a way to configure a pre-request at the resource level or the middleware/query level.

Let me know if @Marcus_Santos's comment/thread is able to help you as it seems he was dealing with a similar issue.

To have the functionality you are requesting, you would likely need to set up your own self hosted middleware/proxy server to modify the request traffic after it leaves Retool on its way to your DB. This would be if the current Audit Log functionality described by @Marcus_Santos or our Audit Log docs here.

[appbuildernyc]

@Marcus_Santos

Audit Logs on Retool Applications

Hey Marcus,
Thanks for your reply and sorry for being late.

Have you tested that approach in a production environment where actually multile users are actually working on the system simultaneously?

I have the same system already, its even implemented and I’m only relaying to that one for now, but authors are messed up sometimes.

I also pass “author” everywhere and having trigger function to check changes with pg function, but its not reliable at all…

[Jack_T]

Hi @appbuildernyc,

Hopefully @Marcus_Santos can get back to you with more details on his setup.

I was doing some research into this issue and it seems that the best architecture pattern revolves around minimal set up with Retool(besides passing in the current user) and having the rest of the database logging set up on the Postgres side of things.

Check out the steps below, could be useful inspiration. LLMs are really good at setting up SQL/Postgres functions!

1. Remove all direct table UPDATE/INSERT/DELETE permissions from Retool.
2. Create SECURITY DEFINER functions.
3. Pass the authenticated user as a function parameter.
4. Log it inside the function.
5. Only allow Retool to execute those functions.

Example:

CREATE OR REPLACE FUNCTION update_vehicle_status(
    p_vehicle_id int,
    p_new_status text,
    p_actor text
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $
BEGIN
    UPDATE vehicles
    SET status = p_new_status
    WHERE id = p_vehicle_id;

    INSERT INTO vehicle_audit (
        vehicle_id,
        action,
        actor,
        changed_at
    )
    VALUES (
        p_vehicle_id,
        'UPDATE_STATUS',
        p_actor,
        now()
    );
END;
$;

Then in Retool:

SELECT update_vehicle_status(
    {{ vehicleId }},
    {{ newStatus }},
    {{ current_user.fullName }}
);

Why this is superior:

• Single atomic operation
• No session variables
• No query chaining
• No reliance on connection state
• Fully controlled
• Easy to audit
• Easy to restrict

You revoke:

REVOKE UPDATE ON vehicles FROM retool_user;

Only allow:

GRANT EXECUTE ON FUNCTION update_vehicle_status TO retool_user;

[appbuildernyc]

Hi @Jack_T ,

Im doing the same, I can even share with you my current trigger function script.

Its the same approach but its not working in production actually when multiple people are working on updates.

In dev environment everything was perfect and it worked. Im trying to find a reason why its messing up authors but didnt have time as Im working on other features too…. i’ll keep you updated if I find something…

-- FUNCTION: public.log_vehicle_history()

-- DROP FUNCTION IF EXISTS public.log_vehicle_history();

CREATE OR REPLACE FUNCTION public.log_vehicle_history()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    old_hstore hstore;
    new_hstore hstore;
    changes_hstore hstore;
    performed_by TEXT;
    excluded_columns TEXT[] := ARRAY['created_at', 'updated_at', 'last_updated', 'latest_update_auto_date', 'author', 'images', 'damages', 'drivability_manual_override'];
    val TEXT;
    changed_column TEXT;
    old_val TEXT;
    new_val TEXT;
    column_type TEXT;
BEGIN
    -- Get the user who performed the action
    -- Use author field from the vehicles table
    IF TG_OP = 'INSERT' THEN
        performed_by := COALESCE(NEW.author, 'unknown');
    ELSIF TG_OP = 'UPDATE' THEN
        performed_by := COALESCE(NEW.author, 'unknown');
    ELSIF TG_OP = 'DELETE' THEN
        performed_by := COALESCE(OLD.author, 'unknown');
    END IF;

    -- HANDLE DELETE
    IF TG_OP = 'DELETE' THEN
        INSERT INTO history (vehicle_id, user_name, action_type, description, timestamp)
        VALUES (OLD.id::text, performed_by, 'VEHICLE_DELETED', 'Vehicle was removed from inventory.', now());
        RETURN OLD;
    END IF;

    -- HANDLE INSERT
    IF TG_OP = 'INSERT' THEN
        INSERT INTO history (vehicle_id, user_name, action_type, description, timestamp)
        VALUES (NEW.id::text, performed_by, 'VEHICLE_CREATED', 'New vehicle was added to inventory.', now());
        RETURN NEW;
    END IF;

    -- HANDLE UPDATE
    IF TG_OP = 'UPDATE' THEN
        old_hstore := hstore(OLD);
        new_hstore := hstore(NEW);
        
        -- Remove excluded columns from comparison
        FOREACH val IN ARRAY excluded_columns LOOP
            old_hstore := old_hstore - val;
            new_hstore := new_hstore - val;
        END LOOP;
        
        changes_hstore := new_hstore - old_hstore;
        
        IF changes_hstore IS NOT NULL AND array_length(akeys(changes_hstore), 1) > 0 THEN
            FOR changed_column, new_val IN SELECT * FROM each(changes_hstore) LOOP
                old_val := old_hstore -> changed_column;
                
                -- Get the column data type
                SELECT data_type INTO column_type
                FROM information_schema.columns
                WHERE table_schema = 'public'
                  AND table_name = TG_TABLE_NAME
                  AND column_name = changed_column;
                
                -- Convert boolean values to readable text
                IF column_type = 'boolean' THEN
                    old_val := CASE old_val 
                        WHEN 't' THEN 'true'
                        WHEN 'f' THEN 'false'
                        ELSE old_val
                    END;
                    new_val := CASE new_val 
                        WHEN 't' THEN 'true'
                        WHEN 'f' THEN 'false'
                        ELSE new_val
                    END;
                END IF;
                
                INSERT INTO history (vehicle_id, user_name, action_type, field_changed, old_value, new_value, description, timestamp)
                VALUES (NEW.id::text, performed_by, 'VEHICLE_UPDATED', changed_column, old_val, new_val, changed_column || ' was updated.', now());
            END LOOP;
        END IF;
        
        RETURN NEW;
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$BODY$;

ALTER FUNCTION public.log_vehicle_history()
    OWNER TO appsmith;

[appbuildernyc]

@Jack_T

GRANT EXECUTE ON FUNCTION update_vehicle_status TO retool_user;

Oh actually your main concern was to restrict direct updates and only work through “Security definer” function.

I didnt try that one actually to make updates through the functions.