CODEWITHSUNDEEP
sqlpostgresqlloggingselectrules
David Anniwell
David Anniwell

Reputation: 63

Logging SELECT statements in PostgreSQL 8.4

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

Upvotes: 6

Views: 3752

Answers (2)

h0tw1r3
h0tw1r3

Reputation: 6818

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.

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181270

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.

Upvotes: 3

Related Questions