Hello list
I'm trying to build a little trigger-based auditing for various web
applications. They have many users in the application layer, but they
all use the same Postgres DB and DB user.
So I need some kind of session storage to save this application level
username for usage in my triggers, which AFAIK doesn't exist in
Postgres. Googling suggested to use a temporary table to achieve
something similar.
Question 1: Is this really the right approach to implement this, or are
there other solutions, e.g. setting application_name to user@application
and using this in the triggers or similar workarounds?
On to question 2:
So now I was trying this:
create or replace function audit_init(text, text) returns void as $$
create temporary table application_session (
"user" text,
"application" text
) with ( oids = false);
insert into application_session
( "user", "application") values ($1, $2);
$$
language sql volatile;
Which unfortunately can't be created or executed, as it says:
ERROR: relation "application_session" does not exist
LINE 8: insert into application_session ("user", "application") ...
When I manually create the temporary table first, I can create the
function, but then when launching it in a new session that doesn't have
the table yet the error is the same.
If I split it up in two functions, one with the insert and one with the
create, it works fine. So apparently the objects in the DML must be
available at parse time of the function body. Is there an easy way
around this? Optimally, I'd just have my applications perform a single
call after connecting, e.g. "audit_init('USERNAME', 'Name of application')".
Thanks for your help.
Christian
PS: I'm aware that this solution falls flat on its face when the
applications are using persistent connections, pools etc, but this isn't
the case here. It's all straight and unshared Perl DBI->connect or PHP
pg_connect().
