Re: [GENERAL] capturing and storing query statement with

I would like to create a audit log of update and
delete queries against a table. I want the log on the
server side, since access will be done using various
clients via ODBC. I would like to capture the user,
current datetime and the query statement. My thought
was to accomplish this by creating a rule that will
insert the information into a log table.

How can I captures the query statement so that I can
place it in a insert query? Is there a variable in
the server that holds the query statement as a string?

Am I about to go in over my head (again)?

Thanks,

Andrew

Guillaume LELARGE wrote:
> Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit :
>>I would like to create a audit log of update and
>>delete queries against a table. I want the log on the
>>server side, since access will be done using various
>>clients via ODBC. I would like to capture the user,
>>current datetime and the query statement. My thought
>>was to accomplish this by creating a rule that will
>>insert the information into a log table.
>
> To be server side, you need to use a trigger and a plpgsql function. On this
> page you will find more informations on trigger related functions:
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html
> Perhaps you can compare OLD and NEW rows to know which rows will be updated.
> And you'll find at the end of the page how to get current user (current_user
> keyword) and current datetime (function now).
>
>>How can I captures the query statement so that I can
>>place it in a insert query? Is there a variable in
>>the server that holds the query statement as a string?

I think this shows how to do what you want:
http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php

HTH,

Joe

Joe Conway wrote:

> Guillaume LELARGE wrote:
>
>> Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit :
>>
>>> I would like to create a audit log of update and
>>> delete queries against a table. I want the log on the
>>> server side, since access will be done using various
>>> clients via ODBC. I would like to capture the user,
>>> current datetime and the query statement. My thought
>>> was to accomplish this by creating a rule that will
>>> insert the information into a log table.
>>
>> And you'll find at the end of the page how to get current user
>> (current_user keyword) and current datetime (function now).
>>
>>> How can I captures the query statement so that I can
>>> place it in a insert query? Is there a variable in
>>> the server that holds the query statement as a string?
>
> I think this shows how to do what you want:
> http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php

Maybe debug_query_string should be mapped into a variable like
CURRENT_USER? Perhaps something like CURRENT_QUERY?

Mike Mascari
mascarm(at)mascari(dot)com

(moving to HACKERS)

Mike Mascari wrote:
> Joe Conway wrote:
>>I think this shows how to do what you want:
>>http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php
>
> Maybe debug_query_string should be mapped into a variable like
> CURRENT_USER? Perhaps something like CURRENT_QUERY?
>

I was thinking something similar. This exact question has come up at
least three times in the last three months. I doubt we'd want a special
keyword like CURRENT_QUERY, but maybe current_query()?

Comments?

Joe

--On Tuesday, June 24, 2003 14:07:23 -0700 Joe Conway <mail(at)joeconway(dot)com>
wrote:

> (moving to HACKERS)
>
> Mike Mascari wrote:
>> Joe Conway wrote:
>>> I think this shows how to do what you want:
>>> http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php
>>
>> Maybe debug_query_string should be mapped into a variable like
>> CURRENT_USER? Perhaps something like CURRENT_QUERY?
>>
>
> I was thinking something similar. This exact question has come up at
> least three times in the last three months. I doubt we'd want a special
> keyword like CURRENT_QUERY, but maybe current_query()?
>
> Comments?
I was thinking the same thing. Currently I use the contrib/dblink supplied
function, but a firstclass, in the default install, version would be nice.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

http://gborg.postgresql.org/project/tablelog/download/download.php

or

http://gborg.postgresql.org/project/audittrail/download/download.php?branch=devel

I haven't used either, but table_log seems to be one that I may start
using in the near future.

On Tue, 2003-06-24 at 13:17, Andrew Gould wrote:
> I would like to create a audit log of update and
> delete queries against a table. I want the log on the
> server side, since access will be done using various
> clients via ODBC. I would like to capture the user,
> current datetime and the query statement. My thought
> was to accomplish this by creating a rule that will
> insert the information into a log table.
>
> How can I captures the query statement so that I can
> place it in a insert query? Is there a variable in
> the server that holds the query statement as a string?
>
> Am I about to go in over my head (again)?
>
> Thanks,
>
> Andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Best Regards,

Mike Benoit
NetNation Communications Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
---------------------------------------

Disclaimer: Opinions expressed here are my own and not
necessarily those of my employer

Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit :
> I would like to create a audit log of update and
> delete queries against a table. I want the log on the
> server side, since access will be done using various
> clients via ODBC. I would like to capture the user,
> current datetime and the query statement. My thought
> was to accomplish this by creating a rule that will
> insert the information into a log table.
>
To be server side, you need to use a trigger and a plpgsql function. On this
page you will find more informations on trigger related functions:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html
Perhaps you can compare OLD and NEW rows to know which rows will be updated.
And you'll find at the end of the page how to get current user (current_user
keyword) and current datetime (function now).

> How can I captures the query statement so that I can
> place it in a insert query? Is there a variable in
> the server that holds the query statement as a string?
>
I don't think... or wasn't able to find something like this one in the
documentation.

Regards.

--
Guillaume <!-- http://absfr.tuxfamily.org/ -->.

Joe Conway <mail(at)joeconway(dot)com> writes:
> I was thinking something similar. This exact question has come up at
> least three times in the last three months. I doubt we'd want a special
> keyword like CURRENT_QUERY, but maybe current_query()?

Not unless you want to promote a quick debugging hack, not expected or
required to work 100%, into a supported feature. I don't think
debug_query_string can be relied on to always reflect what the system
is doing, particularly not in the 3.0 protocol extended-query case.
And how about when you're executing queries inside a function --- is it
supposed to tell you about the most closely nested SQL query?

I don't say this is not worth doing --- but I do say you are opening a
larger can of worms than you probably think.

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>I was thinking something similar. This exact question has come up at
>>least three times in the last three months. I doubt we'd want a special
>>keyword like CURRENT_QUERY, but maybe current_query()?
>
> Not unless you want to promote a quick debugging hack, not expected or
> required to work 100%, into a supported feature. I don't think
> debug_query_string can be relied on to always reflect what the system
> is doing, particularly not in the 3.0 protocol extended-query case.
> And how about when you're executing queries inside a function --- is it
> supposed to tell you about the most closely nested SQL query?
>
> I don't say this is not worth doing --- but I do say you are opening a
> larger can of worms than you probably think.
>

Hmmm. Good points. This one may best wait for 7.5 at least. Does it make
sense to turn it into a TODO?

* promote debug_query_string into a documented, supported feature

Anyone who *does* use the function from dblink, please be sure to report
circumstances where dblink_current_query() returns something other than
what you would expect.

Thanks,

Joe

Added to TODO:

* Promote debug_query_string into a server-side function
current_query()

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail(at)joeconway(dot)com> writes:
> >
> >>I was thinking something similar. This exact question has come up at
> >>least three times in the last three months. I doubt we'd want a special
> >>keyword like CURRENT_QUERY, but maybe current_query()?
> >
> > Not unless you want to promote a quick debugging hack, not expected or
> > required to work 100%, into a supported feature. I don't think
> > debug_query_string can be relied on to always reflect what the system
> > is doing, particularly not in the 3.0 protocol extended-query case.
> > And how about when you're executing queries inside a function --- is it
> > supposed to tell you about the most closely nested SQL query?
> >
> > I don't say this is not worth doing --- but I do say you are opening a
> > larger can of worms than you probably think.
> >
>
> Hmmm. Good points. This one may best wait for 7.5 at least. Does it make
> sense to turn it into a TODO?
>
> * promote debug_query_string into a documented, supported feature
>
> Anyone who *does* use the function from dblink, please be sure to report
> circumstances where dblink_current_query() returns something other than
> what you would expect.
>
> Thanks,
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Joe Conway writes:

> I was thinking something similar. This exact question has come up at
> least three times in the last three months. I doubt we'd want a special
> keyword like CURRENT_QUERY, but maybe current_query()?

The current statement can be examined using the statistics views and
functions.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Joe Conway writes:
>> I was thinking something similar. This exact question has come up at
>> least three times in the last three months. I doubt we'd want a special
>> keyword like CURRENT_QUERY, but maybe current_query()?

> The current statement can be examined using the statistics views and
> functions.

That's not very reliable though --- unless the current query has been
running for quite a few milliseconds, there's no guarantee it will be
reflected in the statistics collector's output.

regards, tom lane