Passing user id to PostgreSQL triggers

I'm using PostgreSQL 9.1. My database is structured so that there is actual tables that my application uses. For every table there is history table that stores only change history. History tables contain same fields that actual tables plus fields form some extra information eg. edit time. History tables are only handled by triggers.

I have 2 kind of triggers:

1. Before INSERT trigger to add some extra information to tables when they are created (eg. create_time).

2. Before UPDATE trigger and before DELETE triggers to copy old values from actual table to history table.

Problem is that I'd like to use triggers to store also the id of user who made those changes. And by id I mean id from php application, not PostgreSQL user id.

Is there any reasonable way to do that?

With INSERT and UPDATE it could be possible to just add extra field for id to actual tables and pass user id to SQL as part of SQL query. As far as I know this doesn't work with DELETE.

All triggers are structured as follows:

CREATE OR REPLACE FUNCTION before_delete_customer() RETURNS trigger AS $BODY$
BEGIN
    INSERT INTO _customer (
        edited_by,
        edit_time,
        field1,
        field2,
        ...,
        fieldN
    ) VALUES (
        -1, // <- This should be user id.
        NOW(),
        OLD.field1,
        OLD.field2,
        ...,
        OLD.fieldN
    );
    RETURN OLD;
END; $BODY$
LANGUAGE plpgsql

Options include:

• When you open a connection, CREATE TEMPORARY TABLE current_app_user(username text); INSERT INTO current_app_user(username) VALUES ('the_user');. Then in your trigger, SELECT username FROM current_app_user to get the current username, possibly as a subquery.

• In postgresql.conf create an entry for a custom GUC like my_app.username = 'unknown';. Whenever you create a connection run SET my_app.username = 'the_user';. Then in triggers, use the current_setting('my_app.username') function to obtain the value. Effectively, you're abusing the GUC machinery to provide session variables. Read the documentation appropriate to your server version, as custom GUCs changed in 9.2.

• Adjust your application so that it has database roles for every application user. SET ROLE to that user before doing work. This not only lets you use the built-in current_user variable-like function to SELECT current_user;, it also allows you to enforce security in the database. See this question. You could log in directly as the user instead of using SET ROLE, but that tends to make connection pooling hard.

In both all three cases you're connection pooling you must be careful to DISCARD ALL; when you return a connection to the pool. (Though it is not documented as doing so, DISCARD ALL does a RESET ROLE).

Common setup for demos:

CREATE TABLE tg_demo(blah text);
INSERT INTO tg_demo(blah) VALUES ('spam'),('eggs');

-- Placeholder; will be replaced by demo functions
CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
SELECT 'unknown';
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION tg_demo_trigger() RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'Current user is: %',get_app_user();
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_demo_tg
AFTER INSERT OR UPDATE OR DELETE ON tg_demo 
FOR EACH ROW EXECUTE PROCEDURE tg_demo_trigger();

Using a GUC:

• In the CUSTOMIZED OPTIONS section of postgresql.conf, add a line like myapp.username = 'unknown_user'. On PostgreSQL versions older than 9.2 you also have to set custom_variable_classes = 'myapp'.
• Restart PostgreSQL. You will now be able to SHOW myapp.username and get the value unknown_user.

Now you can use SET myapp.username = 'the_user'; when you establish a connection, or alternately SET LOCAL myapp.username = 'the_user'; after BEGINning a transaction if you want it to be transaction-local, which is convenient for pooled connections.

The get_app_user function definition:

CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
    SELECT current_setting('myapp.username');
$$ LANGUAGE sql;

Demo using SET LOCAL for transaction-local current username:

regress=> BEGIN;
BEGIN
regress=> SET LOCAL myapp.username = 'test_user';
SET
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: test_user
INSERT 0 1
regress=> COMMIT;
COMMIT
regress=> SHOW myapp.username;
 myapp.username 
----------------
 unknown_user
(1 row)

If you use SET instead of SET LOCAL the setting won't get reverted at commit/rollback time, so it's persistent across the session. It is still reset by DISCARD ALL:

regress=> SET myapp.username = 'test';
SET
regress=> SHOW myapp.username;
 myapp.username 
----------------
 test
(1 row)

regress=> DISCARD ALL;
DISCARD ALL
regress=> SHOW myapp.username;
 myapp.username 
----------------
 unknown_user
(1 row)

Also, note that you can't use SET or SET LOCAL with server-side bind parameters. If you want to use bind parameters ("prepared statements"), consider using the function form set_config(...). See system adminstration functions

Using a temporary table

This approach requires the use of a trigger (or helper function called by a trigger, preferably) that tries to read a value from a temporary table every session should have. If the temporary table cannot be found, a default value is supplied. This is likely to be somewhat slow. Test carefully.

The get_app_user() definition:

CREATE OR REPLACE FUNCTION get_app_user() RETURNS text AS $$
DECLARE
    cur_user text;
BEGIN
    BEGIN
        cur_user := (SELECT username FROM current_app_user);
    EXCEPTION WHEN undefined_table THEN
        cur_user := 'unknown_user';
    END;
    RETURN cur_user;
END;
$$ LANGUAGE plpgsql VOLATILE;

Demo:

regress=> CREATE TEMPORARY TABLE current_app_user(username text);
CREATE TABLE
regress=> INSERT INTO current_app_user(username) VALUES ('testuser');
INSERT 0 1
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: testuser
INSERT 0 1
regress=> DISCARD ALL;
DISCARD ALL
regress=> INSERT INTO tg_demo(blah) VALUES ('42');
NOTICE:  Current user is: unknown_user
INSERT 0 1

Secure session variables

There's also a proposal to add "secure session variables" to PostgreSQL. These are a bit like package variables. As of PostgreSQL 12 the feature has not been included, but keep an eye out and speak up on the hackers list if this is something you need.

Advanced: your own extension with shared memory area

For advanced uses you can even have your own C extension register a shared memory area and communicate between backends using C function calls that read/write values in a DSA segment. See the PostgreSQL programming examples for details. You'll need C knowledge, time, and patience.

set has a variant set session not mentioned here. It's most likely what application developers usually really want instead of plain set or set local.

set session trolol.userr = 'Lol';

My test trigger setup was a bit simpler, but the idea is the same as Craig Ringer's option 2.

create table lol (
    pk varchar(3) not null primary key,
    createuser varchar(20) not null);


CREATE OR REPLACE function update_created() returns trigger as $$ 
     begin new.createuser := current_setting('trolol.userr'); return new; end; $$ language plpgsql;

 
create trigger lol_update before update on lol for each row execute procedure update_created();
create trigger lol_insert before insert on lol for each row execute procedure update_created();

I find this quite acceptable at this point. No DDL statements and the insert/update will not succeed if the session variable is accidentally not set for some reason.

Using DISCARD ALL may not be a good idea as it discards everything. For example SqlKorma does not like this at all. Instead you could just reset the variable using

SET software.theuser TO DEFAULT

There was a fourth option I briefly considered. In the standard set of variables there is "application_name" which could be used. This solution has some limitations, but also some clear advantages depending on the context.

For more information on this fourth option refer to these:

setting application_name through JDBC

postgre documentation on application_name

Another option is to have a last_updated_user_id in the table being audited. This value can be set by the PHP/Webapp easily and will be available in the NEW.last_updated_user_id to be added to the audit table

I think by the time of writing this answer, there is no satisfying solution. I ended up using set_config on a nodejs middleware.

app.use((req, res, next)=>{
	db.query("SELECT set_config('myapp._user_id', $1, false)", [req.session.user.id]);
	next();
});

After debugging, I found that it is set globally to all users. (Race condition can easily occur) And you can't use SET LOCAL, because you need to BEGIN a transaction when request starts, and COMMIT/ROLLBACK when request finishes.