Logging SELECT statements in PostgreSQL 8.4

Question

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

Pablo Santa Cruz · Accepted Answer · 2010-04-16 15:36:05Z

3

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.

answered Apr 16, 2010 at 15:36

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

h0tw1r3 · Accepted Answer · 2010-04-18 16:51:24Z

1

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.

answered Apr 18, 2010 at 16:51

h0tw1r3

6,9281 gold badge32 silver badges35 bronze badges

Collectives™ on Stack Overflow

Logging SELECT statements in PostgreSQL 8.4

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related