Re: Audtiting, DDL and DML in same SQL Function - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Audtiting, DDL and DML in same SQL Function
Date
Msg-id CAOR=d=1HjDCLZ1orsyD3WXr+EhK=OVdC6wr2TAjnEQ2GRLRotg@mail.gmail.com
Whole thread Raw
In response to Re: Audtiting, DDL and DML in same SQL Function  (Scott Marlowe <scott.marlowe@gmail.com>)
Responses Re: Audtiting, DDL and DML in same SQL Function
List pgsql-general
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.

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Audtiting, DDL and DML in same SQL Function
Next
From: Chris Travers
Date:
Subject: Re: Issue with CREATE EXTENSION tablefuncKreiter