On Wed, Feb 1, 2012 at 4:27 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Feb 1, 2012 at 3:29 PM, Christian Ramseyer <rc@networkz.ch> wrote:
>> 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')".
>
> I think if you build the query as a string and EXECUTE it it will
> work. But I'm not guaranteeing it.
Note that you might have to build both queries and EXECUTE them to make it work.