Thread: Audtiting, DDL and DML in same SQL Function
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().
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.
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.
On 1 February 2012 22:29, 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? > There's an example in the manual of another way to keep session-specific data: http://www.postgresql.org/docs/current/static/plperl-global.html You can do similar things in other procedural languages too, just not in PL/pgSQL. Regards, Dean
On 2/2/12 12:39 AM, Scott Marlowe wrote:
> 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:
>>> 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.
>
Thanks Scott, executing it actually does the trick. I'm now using this:
create or replace function audit_start(text, text) returns void as $$
declare
username alias for $1;
application alias for $2;
begin
execute 'drop table if exists audit_session ;
create temporary table audit_session (
username text, application text)';
execute 'insert into audit_session
(username, application)
values ($1, $2)'
using username, application;
end;
$$
language plpgsql;
Christian