Logging SELECT statements in PostgreSQL 8.4

Question

I have a table that contains sensitive data, and in accordance with the data protection policy, we must keep a record of each read / write data, including the identifier of the row and the user who accessed the table. Recording does not cause problems using triggers, but triggers are not supported for SELECT .

What is the best way to do this? I looked at the rules, but I can’t get them in the INSERT in the table, and I tried to log every request, but this does not look like a log SELECT . Ideally, for security, I would like to keep the log in a table in the database, but file entry is also good.

Thanks,
David

Answer 1

You can enable SENTENCE LOGGING in PostgreSQL:

In postgresql.conf:

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

Then reload your database and all records will be recorded.

Answer 2

You can take the log files and import them into the database using cvslog format or configure something like syslog-ng to return messages to postgres or another database.