How can I set a username for a Postgresql audit trigger?

Question

I am using a trigger in PostgreSQL 8.2 to audit changes to a table:

CREATE OR REPLACE FUNCTION update_issue_history() RETURNS trigger as $trig$
BEGIN
        INSERT INTO issue_history (username, issueid)
               VALUES ('fixed-username', OLD.issueid);
        RETURN NULL;
END;
$trig$ LANGUAGE plpgsql;

CREATE TRIGGER update_issue_history_trigger
AFTER UPDATE ON issue
      FOR EACH ROW EXECUTE PROCEDURE update_issue_history();

What I want to do is have some way to provide the value of fixed-username at the time that I execute the update. Is this possible? If so, how do I accomplish it?

possible duplicate of [Passing user id to PostgreSQL triggers](http://stackoverflow.com/questions/13172524/passing-user-id-to-postgresql-triggers) — A.H., Mar 10 '13 at 08:24
The answer [over here][1] sums it up quite well. [1]: http://stackoverflow.com/a/13172964/947357 — A.H., Mar 10 '13 at 08:25
8.2? Please read http://www.postgresql.org/support/versioning/ and start planning your upgrade. — Craig Ringer, Mar 10 '13 at 12:46
It's probably the case that a four year old question about an old version may not require admonitions about upgrades. As it happens, I don't even work there anymore :) — Chris R, Mar 11 '13 at 15:34
I just updated my answer below but I concur the other is better and would work on any version, and this should be closed as duplicate. — Chris Travers, Mar 12 '13 at 01:08
@ChrisR, I know this is an old question and you have moved on to other things, but I have done my best to try to provide an answer that may be of help if you run into anything similar. I would appreciate feedback as to whether my recently updated answer is helpful in that regard or what I could do to improve it. — Chris Travers, Mar 25 '13 at 04:23

score 1 · Answer 1 · answered Jul 14 '09 at 16:58

Try something like this:

CREATE OR REPLACE FUNCTION update_issue_history() 
RETURNS trigger as $trig$
DECLARE
      arg_username varchar;
BEGIN
      arg_username := TG_ARGV[0];
      INSERT INTO issue_history (username, issueid)
             VALUES (arg_username, OLD.issueid);
      RETURN NULL;
END;
$trig$ LANGUAGE plpgsql;

CREATE TRIGGER update_issue_history_trigger
AFTER UPDATE ON issue
      FOR EACH ROW EXECUTE PROCEDURE update_issue_history('my username value');

Chris Travers · Answer 2 · 2013-03-20T05:55:08.947

I don't see a way to do this other than to create temp tables and use EXECUTE in your trigger. This will have performance consequences though. A better option might be to tie into another table somewhere and log in who logs in/out by session id and back-end PID, and reference that?

Note you don't have any other way of getting the information into the update statement. Keep in mind that the trigger can only see what is available in the API or in the database. If you want a trigger to work transparently, you can't expect to pass information to it at runtime that it would not have access to otherwise.

The basic question you have to ask is "How does the db know what to put there?" Once you decide on a method there the answer should be straight-forward but there are no free lunches.

Update

In the past when I have had to do something like this when the login to the db is with an application role, the way I have done it is to create a temporary table and then access that table from the stored procedures. Currently stored procedures can handle temporary tables in this way but in the past we had to use EXECUTE.

There are two huge limitations with this approach. The first is that it creates a lot of tables and this leads eventually to the possibility of oid wraparound.

These days I much prefer to have the logins to the db being user logins. This makes this far easier to manage and you can just access via the value SESSION_USER (a newbie mistake is to use CURRENT_USER which shows you the current security context rather than the login of the user.

Neither of these approaches work well with connection pooling. In the first case you can't do connection pooling because your temporary tables will get misinterpreted or clobbered. In the second, you can't do it because the login roles are different.

How can I set a username for a Postgresql audit trigger?

2 Answers2