Thread: audit table containing Select statements submitted

audit table containing Select statements submitted

From
"Hogan, James F. Jr."
Date:
I am trying desperately to find a way to create an audit table to
log...who SELECTED what... as I am dealing with HR data and Health Care
Patient Data

Any help would be greatly appreciated and put to good use in the Austin
Texas Hospitals.

Thanks

Jim



-----Original Message-----
From: Hogan, James F. Jr.
Sent: Thursday, May 04, 2006 12:46 PM
To: 'pgsql-sql@postgresql.org'; 'pgsql-general@postgresql.org'
Subject: audit table containing Select statements submitted

No response from the pgsql-admin list so I though I would try cross
posting here:
pgsql-sql@postgresql.org
pgsql-general@postgresql.org



I just know I am not the first to try and do this

Jim

*********************
Can anyone point me in a direction that may help me populate in real
time a table that holds?

Current_user
Timestamp
"The Select Statement Submitted by the User"

I need to be able to determine who viewed what and when they viewed it.

I have considered the fact that the result from SELECT yesterday may be
different than the result set returned by the SAME SELECT statement
today, but when used in conjunction with the INSERT, UPDATE, DELETE
audit logging I have already created, the answers to who viewed, what
and when would be readily available.

I have been searching all morning and...

The only thing I find on logging of Select statements is that the
information can be held in the Log Files...if Logging is enabled.

As I am only interested in the statements presented against certain
tables...

Turning on logging gives me more than I need or care to look through.

I could write a script to parses the Log Files into a Database Table but
would prefer to avoid enabling the file logging of statements if
possible.

Thanks for any reference or help you may be able to provide.

Jim


Re: audit table containing Select statements submitted

From
Josh Berkus
Date:
Jim,

> I am trying desperately to find a way to create an audit table to
> log...who SELECTED what... as I am dealing with HR data and Health Care
> Patient Data

Well, the issue with doing this by trigger or RULE is that unlike 
updates and deletes, SELECTS do *not* guarentee single execution.  For 
example, if the table is on the loop end of a nested loop, it could be 
fired hundreds or thousands of times.  This is the reason why we 
recommend against trying to build a trigger/RULE for SELECT auditing.

There are workarounds though.

One possibility, which I have used, is to not allow the application 
access to the base tables but instead force it to use Set Returning 
Functions.  For example, instead of:

SELECT * FROM users NATURAL JOIN permissions WHERE name = 'Joe';

you would do: SELECT * FROM view_users_perms(user,'Joe');

The SRF then can easily log the select statement.  This also provides 
you with the additional security of knowing that a user who hacks the 
database connection cannot launch ad-hoc queries which the application 
would not allow.  I've used this approach lots for web applications for 
that reason.

Secondly, you can use the log.  We've discussed on this list making it 
possible to log in CSV, XML or other database-digestable format. 
Unfortuantely, there doesn't appear to be much momentum behind that; I 
don't know that anyone is writing any code presently.  Sponsorship?

In the immediate time, you can (others have done this) have the log 
stream to a parser which digests the log and writes out different files 
(database-loadable) depending on the logged activity recorded.  I don't 
know of any OSS code which does this but you can probably get advice on 
the lists fromm people who have done it custom.

Good luck!

--Josh






Re: audit table containing Select statements submitted

From
"Joshua D. Drake"
Date:
Hogan, James F. Jr. wrote:
> I am trying desperately to find a way to create an audit table to
> log...who SELECTED what... as I am dealing with HR data and Health Care
> Patient Data
> 
> Any help would be greatly appreciated and put to good use in the Austin
> Texas Hospitals.

Use statement logging and make sure each person is logging in with their 
own role.

If you can't do that, then don't allow anyone to select anything but set 
returning functions, then you can log the application user with that.

Sincerely,

Joshua D. Drake

> 
> Thanks
> 
> Jim
> 
> 
> 
> -----Original Message-----
> From: Hogan, James F. Jr. 
> Sent: Thursday, May 04, 2006 12:46 PM
> To: 'pgsql-sql@postgresql.org'; 'pgsql-general@postgresql.org'
> Subject: audit table containing Select statements submitted
> 
> No response from the pgsql-admin list so I though I would try cross
> posting here:
> pgsql-sql@postgresql.org
> pgsql-general@postgresql.org
> 
> 
> 
> I just know I am not the first to try and do this 
> 
> Jim
> 
> *********************
> Can anyone point me in a direction that may help me populate in real
> time a table that holds?
> 
> Current_user
> Timestamp
> "The Select Statement Submitted by the User"
> 
> I need to be able to determine who viewed what and when they viewed it.
> 
> I have considered the fact that the result from SELECT yesterday may be
> different than the result set returned by the SAME SELECT statement
> today, but when used in conjunction with the INSERT, UPDATE, DELETE
> audit logging I have already created, the answers to who viewed, what
> and when would be readily available. 
> 
> I have been searching all morning and...
> 
> The only thing I find on logging of Select statements is that the
> information can be held in the Log Files...if Logging is enabled.  
> 
> As I am only interested in the statements presented against certain
> tables...
> 
> Turning on logging gives me more than I need or care to look through. 
> 
> I could write a script to parses the Log Files into a Database Table but
> would prefer to avoid enabling the file logging of statements if
> possible.
> 
> Thanks for any reference or help you may be able to provide.
> 
> Jim
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 


-- 
           === The PostgreSQL Company: Command Prompt, Inc. ===     Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240    Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: audit table containing Select statements submitted

From
Andrew Dunstan
Date:
Josh Berkus wrote:
>
> Secondly, you can use the log.  We've discussed on this list making it 
> possible to log in CSV, XML or other database-digestable format. 
> Unfortuantely, there doesn't appear to be much momentum behind that; I 
> don't know that anyone is writing any code presently.  Sponsorship?
>

Well, let's think about it some first, before we line up $$ :-)

We really have 3 bits of the log: the prefix, the tag, and the message.

Turning the prefix into whatever is needed is in the hands of the user. 
We could provide a corresponding log_line_suffix to allow XML element 
completion if necessary. The tag could likewise easily be XMLized (or 
CSVized, or whatever). The real problem is the message, which is now 
from the logging code's point of view basically an opaque string. 
Changing that would be a massive undertaking, especially when you think 
of the effect on the translators. And first we would need to come up 
with a set of fields, or several sets of fields, that we wanted to use. 
The reason I haven't gone down this road, and just did log_line_prefix, 
is that it strikes me as too inflexible. I think postprocessing is 
probably a better way to go, and just leave the messages opaque from 
postgres' point of view. If someone has a better proposal, let's see an 
example of how all the various messages would be handled.

cheers

andrew


Re: audit table containing Select statements submitted

From
Josh Berkus
Date:
Andrew,

> The real problem is the message, which is now
> from the logging code's point of view basically an opaque string.
> Changing that would be a massive undertaking, especially when you think
> of the effect on the translators. 

Hmmm ... I don't see this as a problem.  Just stick the whole message into 
a single XML field.  This is one area where XML is easier that SQL; since 
it's a document format, it has no problem with a great big blob of text.  
"Unstructured Data" and all that nonsense.

Then whatever utility the user uses to *read* the XML can parse the message 
according to the user's desires.  It'll still be an improvement over the 
current format for log digestion, since it will become easy to separate 
the message from the prefix and tag (which currently it's not).

The only real issue I see is the possibility of XML codes embedded in the 
text, but that seems minor.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: audit table containing Select statements submitted

From
Andrew Dunstan
Date:
Josh Berkus wrote:
> Andrew,
>
>   
>> The real problem is the message, which is now
>> from the logging code's point of view basically an opaque string.
>> Changing that would be a massive undertaking, especially when you think
>> of the effect on the translators. 
>>     
>
> Hmmm ... I don't see this as a problem.  Just stick the whole message into 
> a single XML field.  This is one area where XML is easier that SQL; since 
> it's a document format, it has no problem with a great big blob of text.  
> "Unstructured Data" and all that nonsense.
>
> Then whatever utility the user uses to *read* the XML can parse the message 
> according to the user's desires.  It'll still be an improvement over the 
> current format for log digestion, since it will become easy to separate 
> the message from the prefix and tag (which currently it's not).
>
> The only real issue I see is the possibility of XML codes embedded in the 
> text, but that seems minor.
>
>   
well, we could either XML escape the message or put it in a CDATA block. 
The latter would be arguably more humanly readable.

Given that, I think we could get away with a single GUC var to govern 
this, log_format with possible values (to start with, at least) of 
'plain' and 'xml'.

The user could just set up log_line_prefix as an XML attribute string. 
So we'd have something like:
 <pglog level="LOG" user="andrew" dbname="blurfl">|<![CDATA[|  
statement: select count(*) from foo where a < b ; |]]></pglog>|


Now, what were you saying about sponsorship? ;-)

cheers

andrew


Re: audit table containing Select statements submitted

From
"Jim C. Nasby"
Date:
On Fri, May 12, 2006 at 02:43:56PM -0400, Andrew Dunstan wrote:
> Josh Berkus wrote:
> >Andrew,
> >
> >  
> >>The real problem is the message, which is now
> >>from the logging code's point of view basically an opaque string.
> >>Changing that would be a massive undertaking, especially when you think
> >>of the effect on the translators. 
> >>    
> >
> >Hmmm ... I don't see this as a problem.  Just stick the whole message into 
> >a single XML field.  This is one area where XML is easier that SQL; since 
> >it's a document format, it has no problem with a great big blob of text.  
> >"Unstructured Data" and all that nonsense.
> >
> >Then whatever utility the user uses to *read* the XML can parse the 
> >message according to the user's desires.  It'll still be an improvement 
> >over the current format for log digestion, since it will become easy to 
> >separate the message from the prefix and tag (which currently it's not).
> >
> >The only real issue I see is the possibility of XML codes embedded in the 
> >text, but that seems minor.
> >
> >  
> well, we could either XML escape the message or put it in a CDATA block. 
> The latter would be arguably more humanly readable.
> 
> Given that, I think we could get away with a single GUC var to govern 
> this, log_format with possible values (to start with, at least) of 
> 'plain' and 'xml'.

I'm wondering if there would be value in allowing for a second, seperate
log stream...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: audit table containing Select statements submitted

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Well, the issue with doing this by trigger or RULE is that unlike 
> updates and deletes, SELECTS do *not* guarentee single execution.  For 
> example, if the table is on the loop end of a nested loop, it could be 
> fired hundreds or thousands of times.  This is the reason why we 
> recommend against trying to build a trigger/RULE for SELECT auditing.

There's an even more significant reason why you can't rely on
within-the-database logging if you want to track SELECTs.  Imagine
you have a trigger or whatever that tries to log what I do.
I just
begin;select something-I-shouldn't-know;rollback;

I just covered all my tracks quite effectively, because the ROLLBACK
canceled any and all side effects of my transaction.  But (unlike
if I'd rolled back an update) I still know what I found out.

So, if you want to log accesses to info as opposed to updates, you
really have to use something outside the SQL universe.  I concur
with Josh's suggestion to rely on reading the postmaster log.
        regards, tom lane


Re: audit table containing Select statements submitted

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Hmmm ... I don't see this as a problem.  Just stick the whole message into 
> a single XML field.  This is one area where XML is easier that SQL; since 
> it's a document format, it has no problem with a great big blob of text.  
> "Unstructured Data" and all that nonsense.

> Then whatever utility the user uses to *read* the XML can parse the message 
> according to the user's desires.  It'll still be an improvement over the 
> current format for log digestion, since it will become easy to separate 
> the message from the prefix and tag (which currently it's not).

This argument strikes me as nonsense.  You've got a utility that's smart
enough to parse the very-free-format message bodies, but it's going to
be too confused by the log line prefix?

> The only real issue I see is the possibility of XML codes embedded in the 
> text, but that seems minor.

Hardly minor, as anyone who is so in love with XML that he'd want to use
it for this would likely also have lots of XML tags in his data.
        regards, tom lane


Re: audit table containing Select statements submitted

From
Josh Berkus
Date:
Tom,

> This argument strikes me as nonsense.  You've got a utility that's smart
> enough to parse the very-free-format message bodies, but it's going to
> be too confused by the log line prefix?

Having tinkered a little with PQA, yes, actually.  The issue is that the 
"message" text can easily be multi-line and contain a vast variety of 
special characters.  The issue is figuring out where the prefix, the tag 
and the message begin and end.  And our text log format makes that a PITA.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: audit table containing Select statements submitted

From
"Joshua D. Drake"
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> Hmmm ... I don't see this as a problem.  Just stick the whole message into 
>> a single XML field.  This is one area where XML is easier that SQL; since 
>> it's a document format, it has no problem with a great big blob of text.  
>> "Unstructured Data" and all that nonsense.
> 
>> Then whatever utility the user uses to *read* the XML can parse the message 
>> according to the user's desires.  It'll still be an improvement over the 
>> current format for log digestion, since it will become easy to separate 
>> the message from the prefix and tag (which currently it's not).
> 
> This argument strikes me as nonsense.  You've got a utility that's smart
> enough to parse the very-free-format message bodies, but it's going to
> be too confused by the log line prefix?

Not that Tom's dissent isn't enough, but I have to agree. It is very 
easy to set up a parser for the log and XML is just going to add noise.

Joshua D. Drake




-- 
           === The PostgreSQL Company: Command Prompt, Inc. ===     Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240    Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: audit table containing Select statements submitted

From
"Joshua D. Drake"
Date:
Josh Berkus wrote:
> Tom,
> 
>> This argument strikes me as nonsense.  You've got a utility that's smart
>> enough to parse the very-free-format message bodies, but it's going to
>> be too confused by the log line prefix?
> 
> Having tinkered a little with PQA, yes, actually.  The issue is that the 
> "message" text can easily be multi-line and contain a vast variety of 
> special characters.  The issue is figuring out where the prefix, the tag 
> and the message begin and end.  And our text log format makes that a PITA.
> 

Hmmm... well why don't we add log_line_suffix :)

Joshua D. Drake

-- 
           === The PostgreSQL Company: Command Prompt, Inc. ===     Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240    Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: audit table containing Select statements submitted

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Hmmm... well why don't we add log_line_suffix :)

Doesn't help; you'd also need logic in there to quote any XML tags
appearing in the message text.  At that point, adding a
"log_line_suffix" is a transparent pretense of generality --- what
you might as well do is just have a full-fledged "emit the log in XML"
switch.

(I concur with Andrew's comments that this is pretty silly, unless
someone wants to go to the further work of XML-ifying the message
contents to some reasonable extent.  If you are going to have to write a
parser to make sense of the message contents, it is really pretty lame
to claim that you can't cope with parsing the current log format as-is.)
        regards, tom lane


Re: audit table containing Select statements submitted

From
Marc Munro
Date:
You could do this using Veil, http://pgfoundry.org/projects/veil/, or
something like it.  A Veil access function,
http://veil.projects.postgresql.org/curdocs/overview-page.html, could be
used to record every row returned within a query to the user that
requested it.  Note that this operates at the level of fetches and not
the resultset, meaning that queries like:

select stuff from a where exists (select 1 from b where....);

would record a fetch against b.

The basic trick is to replace table_that_you_want_audited with a view
that does something like:

select * from table_that_you_want_audited
where  audit_this_fetch(row_identifier);

You will also need instead-of triggers for insert, update and delete of
the view.

__
Marc

On Fri, 2006-05-12 at 14:19 -0300, pgsql-hackers-owner@postgresql.org
wrote:
> From: Hogan, James F. Jr.
> Sent: Thursday, May 04, 2006 12:46 PM
> To: 'pgsql-sql@postgresql.org'; 'pgsql-general@postgresql.org'
> Subject: audit table containing Select statements submitted
>
> No response from the pgsql-admin list so I though I would try cross
> posting here:
> pgsql-sql@postgresql.org
> pgsql-general@postgresql.org
>
>
>
> I just know I am not the first to try and do this
>
> Jim
>
> *********************
> Can anyone point me in a direction that may help me populate in real
> time a table that holds?
>
> Current_user
> Timestamp
> "The Select Statement Submitted by the User"
>
> I need to be able to determine who viewed what and when they viewed
> it.
>
> I have considered the fact that the result from SELECT yesterday may
> be
> different than the result set returned by the SAME SELECT statement
> today, but when used in conjunction with the INSERT, UPDATE, DELETE
> audit logging I have already created, the answers to who viewed, what
> and when would be readily available.
>
> I have been searching all morning and...
>
> The only thing I find on logging of Select statements is that the
> information can be held in the Log Files...if Logging is enabled.
>
> As I am only interested in the statements presented against certain
> tables...
>
> Turning on logging gives me more than I need or care to look through.
>
> I could write a script to parses the Log Files into a Database Table
> but
> would prefer to avoid enabling the file logging of statements if
> possible.
>
> Thanks for any reference or help you may be able to provide.
>
> Jim
>

Re: audit table containing Select statements submitted

From
"Hogan, James F. Jr."
Date:
Thank you all for the effort you put into response.

The biggest thing I want to avoid isn't so much having to parse through
the log files but to avoid turning on such extensive logging altogether.

I am not sure what kind of additional load logging to this extent may
add.

Looks like I am not going to have much in the way of alternative.

Maybe some day.

Good news is that most access is via Web Interface and I capture most
activity that way.


Again thank you all.

Jim

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 12, 2006 8:03 PM
To: Joshua D. Drake
Cc: josh@agliodbs.com; Andrew Dunstan; Hogan, James F. Jr.;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] audit table containing Select statements
submitted

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Hmmm... well why don't we add log_line_suffix :)

Doesn't help; you'd also need logic in there to quote any XML tags
appearing in the message text.  At that point, adding a
"log_line_suffix" is a transparent pretense of generality --- what
you might as well do is just have a full-fledged "emit the log in XML"
switch.

(I concur with Andrew's comments that this is pretty silly, unless
someone wants to go to the further work of XML-ifying the message
contents to some reasonable extent.  If you are going to have to write a
parser to make sense of the message contents, it is really pretty lame
to claim that you can't cope with parsing the current log format as-is.)
        regards, tom lane


Re: audit table containing Select statements submitted

From
"Jim C. Nasby"
Date:
How do you hope to avoid this overhead when you're looking to track
information on every single SELECT statement? Or were you looking to
only log access to some specific tables?

On Mon, May 15, 2006 at 09:38:54AM -0500, Hogan, James F. Jr. wrote:
> Thank you all for the effort you put into response.
> 
> The biggest thing I want to avoid isn't so much having to parse through
> the log files but to avoid turning on such extensive logging altogether.
> 
> I am not sure what kind of additional load logging to this extent may
> add.
> 
> Looks like I am not going to have much in the way of alternative.
> 
> Maybe some day.
> 
> Good news is that most access is via Web Interface and I capture most
> activity that way.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: audit table containing Select statements submitted

From
"Hogan, James F. Jr."
Date:
Only specific tables.

Of the 150 plus existing there are only 8 or 10 that hold sensitive
data.

This will grow over time but will always be in the minority.





-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Monday, May 15, 2006 10:42 AM
To: Hogan, James F. Jr.
Cc: Tom Lane; Joshua D. Drake; josh@agliodbs.com; Andrew Dunstan;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] audit table containing Select statements
submitted

How do you hope to avoid this overhead when you're looking to track
information on every single SELECT statement? Or were you looking to
only log access to some specific tables?

On Mon, May 15, 2006 at 09:38:54AM -0500, Hogan, James F. Jr. wrote:
> Thank you all for the effort you put into response.
>
> The biggest thing I want to avoid isn't so much having to parse
through
> the log files but to avoid turning on such extensive logging
altogether.
>
> I am not sure what kind of additional load logging to this extent may
> add.
>
> Looks like I am not going to have much in the way of alternative.
>
> Maybe some day.
>
> Good news is that most access is via Web Interface and I capture most
> activity that way.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: audit table containing Select statements submitted

From
"Jim C. Nasby"
Date:
On Mon, May 15, 2006 at 10:55:43AM -0500, Hogan, James F. Jr. wrote:
> Only specific tables.
> 
> Of the 150 plus existing there are only 8 or 10 that hold sensitive
> data.

In that case I'd definately go with the suggestion of creating access
functions and logging to a table from within them. Just make sure to
mark the functions as volatile.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: audit table containing Select statements submitted

From
Douglas McNaught
Date:
"Jim C. Nasby" <jnasby@pervasive.com> writes:

> On Mon, May 15, 2006 at 10:55:43AM -0500, Hogan, James F. Jr. wrote:
>> Only specific tables.
>> 
>> Of the 150 plus existing there are only 8 or 10 that hold sensitive
>> data.
>
> In that case I'd definately go with the suggestion of creating access
> functions and logging to a table from within them. Just make sure to
> mark the functions as volatile.

But what if the user calls the access function, sees the data, then
issues a ROLLBACK?  The audit record would be rolled back as well (as
Tom pointed out earlier).

You could use dblink to log to  a separate audit database, I suppose.

-Doug


Re: audit table containing Select statements submitted

From
"Jim C. Nasby"
Date:
On Mon, May 15, 2006 at 12:37:34PM -0400, Douglas McNaught wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> 
> > On Mon, May 15, 2006 at 10:55:43AM -0500, Hogan, James F. Jr. wrote:
> >> Only specific tables.
> >> 
> >> Of the 150 plus existing there are only 8 or 10 that hold sensitive
> >> data.
> >
> > In that case I'd definately go with the suggestion of creating access
> > functions and logging to a table from within them. Just make sure to
> > mark the functions as volatile.
> 
> But what if the user calls the access function, sees the data, then
> issues a ROLLBACK?  The audit record would be rolled back as well (as
> Tom pointed out earlier).
> 
> You could use dblink to log to  a separate audit database, I suppose.

Ooops, forgot about that. Yeah, you'd have to use dblink. If it works
with pgpool performance might not be too horrid.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: audit table containing Select statements submitted

From
Christopher Kings-Lynne
Date:
> Having tinkered a little with PQA, yes, actually.  The issue is that the 
> "message" text can easily be multi-line and contain a vast variety of 
> special characters.  The issue is figuring out where the prefix, the tag 
> and the message begin and end.  And our text log format makes that a PITA.

Try pgfouine...



Re: audit table containing Select statements submitted

From
Josh Berkus
Date:
Doug,

> But what if the user calls the access function, sees the data, then
> issues a ROLLBACK?  The audit record would be rolled back as well (as
> Tom pointed out earlier).
>
> You could use dblink to log to  a separate audit database, I suppose.

Or just write to some other non-transational resource, like a text file.  That 
would require the use of an external "untrusted" PL, though (like PL/PerlU, 
PL/sh or PL/PythonU)

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: audit table containing Select statements submitted

From
"Gurjeet Singh"
Date:
    Do we have any plans of introducing 'AUTONOMOUS TRANSACTION'  like feature?
   Again, it might not be a part of the standard.... but it is very
helpful in situations like these!!! You can run a trigger with an
autonomous transaction attached to it, which guarantees that the work
done by trigger persists even though the calling transaction rolls
back (potentially a hacker trying to cover his tracks)!!!

(http://asktom.oracle.com/~tkyte/autonomous/index.html)

Gurjeet.

On 5/16/06, Josh Berkus <josh@agliodbs.com> wrote:
> Doug,
>
> > But what if the user calls the access function, sees the data, then
> > issues a ROLLBACK?  The audit record would be rolled back as well (as
> > Tom pointed out earlier).


Re: audit table containing Select statements submitted

From
Tom Lane
Date:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
>     Do we have any plans of introducing 'AUTONOMOUS TRANSACTION'  like feature?

No.
        regards, tom lane


Re: audit table containing Select statements submitted

From
Thomas Hallgren
Date:
Some users of PL/Java make use of a non-default connection from within a Trigger in order to 
do this. In essence, they load the client JDBC package into the backend to let the backend 
as such become a client. The second connection is then maintained for the lifetime of the 
first. Perhaps not the most efficient way of doing it but it works.

Regards,
Thomas Hallgren

Gurjeet Singh wrote:
>    Do we have any plans of introducing 'AUTONOMOUS TRANSACTION'  like 
> feature?
> 
>    Again, it might not be a part of the standard.... but it is very
> helpful in situations like these!!! You can run a trigger with an
> autonomous transaction attached to it, which guarantees that the work
> done by trigger persists even though the calling transaction rolls
> back (potentially a hacker trying to cover his tracks)!!!
> 
> (http://asktom.oracle.com/~tkyte/autonomous/index.html)
> 
> Gurjeet.
> 
> On 5/16/06, Josh Berkus <josh@agliodbs.com> wrote:
>> Doug,
>>
>> > But what if the user calls the access function, sees the data, then
>> > issues a ROLLBACK?  The audit record would be rolled back as well (as
>> > Tom pointed out earlier).
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 



Re: audit table containing Select statements submitted

From
Greg Stark
Date:
Thomas Hallgren <thomas@tada.se> writes:

> Some users of PL/Java make use of a non-default connection from within a
> Trigger in order to do this. In essence, they load the client JDBC package into
> the backend to let the backend as such become a client. The second connection
> is then maintained for the lifetime of the first. Perhaps not the most
> efficient way of doing it but it works.

And you can do the same thing with any of the PL languages that have database
drivers like Perl or Python. It might be a little less inefficient using one
of them -- and probably a lot less code.

You should be aware of the risk of deadlocks if you touch the same resources.
Because the database is unaware that your main transaction is waiting for this
other session to complete it won't be able to detect any deadlocks that depend
on this hidden dependency.

-- 
greg



Re: audit table containing Select statements submitted

From
"Gurjeet Singh"
Date:
    Just a small example of the fact that people need such
functionality... and will devise other ways, albeit inefficient and
dangerous, to implement the missing feature.
   The success of an RDBMS (or any other product for that matter)
depends on how well it strikes the balance between the standards
implementation, and what the users need.

Gurjeet.

On 17 May 2006 02:31:20 -0400, Greg Stark <gsstark@mit.edu> wrote:
> Thomas Hallgren <thomas@tada.se> writes:
>
> > Some users of PL/Java make use of a non-default connection from within a
> > Trigger in order to do this. In essence, they load the client JDBC package into
> > the backend to let the backend as such become a client. The second connection
> > is then maintained for the lifetime of the first. Perhaps not the most
> > efficient way of doing it but it works.
>
> And you can do the same thing with any of the PL languages that have database
> drivers like Perl or Python. It might be a little less inefficient using one
> of them -- and probably a lot less code.
>
> You should be aware of the risk of deadlocks if you touch the same resources.
> Because the database is unaware that your main transaction is waiting for this
> other session to complete it won't be able to detect any deadlocks that depend
> on this hidden dependency.
>
> --
> greg
>
>