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

Tue, 24 Jun 2003 13:17:39 -0700 (PDT)
Andrew Gould <andrewgould@yahoo.com>
capturing and storing query statement with rules
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


Tue, 24 Jun 2003 13:53:20 -0700
Joe Conway <mail@joeconway.com>
Re: capturing and storing query statement with rules
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@mascari.com



Tue, 24 Jun 2003 17:05:25 -0400
Mike Mascari <mascarm@mascari.com>
Re: capturing and storing query statement with rules
(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



Tue, 24 Jun 2003 14:07:23 -0700
Joe Conway <mail@joeconway.com>
Re: [GENERAL] capturing and storing query statement with rules


--On Tuesday, June 24, 2003 14:07:23 -0700 Joe Conway <mail@joeconway.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@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749




Tue, 24 Jun 2003 16:13:31 -0500
Larry Rosenman <ler@lerctr.org>
Re: [GENERAL] capturing and storing query statement with
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


24 Jun 2003 14:27:29 -0700
Mike Benoit <mikeb@netnation.com>
Re: capturing and storing query statement with rules
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/ -->.

Tue, 24 Jun 2003 22:40:28 +0000
Guillaume LELARGE <gleu@wanadoo.fr>
Re: capturing and storing query statement with rules
Joe Conway <mail@joeconway.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

Wed, 25 Jun 2003 10:40:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] capturing and storing query statement with rules 
Tom Lane wrote:
> Joe Conway <mail@joeconway.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


Wed, 25 Jun 2003 07:58:21 -0700
Joe Conway <mail@joeconway.com>
Re: [GENERAL] capturing and storing query statement with

Added to TODO:

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


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

Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail@joeconway.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 25 Jun 2003 17:40:47 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] capturing and storing query statement with
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@gmx.net


Fri, 27 Jun 2003 14:37:11 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] capturing and storing query statement with
Peter Eisentraut <peter_e@gmx.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

Fri, 27 Jun 2003 11:05:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] capturing and storing query statement with 