audit table containing Select statements submitted

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

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/

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

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

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

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

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

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

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

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/

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/

"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

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:

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

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

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

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

"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

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

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...

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

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.

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:

Do we have any plans of introducing 'AUTONOMOUS TRANSACTION' like feature?

No.

regards, tom lane

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:

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

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.