Logging SELECT statements in PostgreSQL 8.4

Question

Hi All

I've got a table which contains sensitive data and according to data protection policy we have to keep a record of every read/write of the data including a row identifier and the user who accessed the table. The writing is no issue using triggers but triggers aren't supported for SELECT statements.

What's the best method of doing this? I've looked at rules but I can't get them to INSERT into a table, and I've tried logging every query but this doesn't seem to log SELECT statements. Ideally for security I'd like to keep the log within a table on the database but logging to a file is fine too.

Thanks,
David

Answer 1

You could enable SENTENCE LOGGING in PostgreSQL:

In postgresql.conf:

log_statement = 'all'                   # none, ddl, mod, all

Then restart your database and all senteces will be logged.

Answer 2

You can take either take the log files and import them into the database using the cvslog format, or setup something like syslog-ng to put the messages back into postgres or some other database.