在PostgreSQL 8.4中记录SELECT语句(Logging SELECT statements in PostgreSQL 8.4)

在PostgreSQL 8.4中记录SELECT语句(Logging SELECT statements in PostgreSQL 8.4)

我有一个包含敏感数据的表，根据数据保护政策，我们必须记录每次数据的读/写，包括行标识符和访问表的用户。 使用触发器写入没有问题，但SELECT语句不支持触发器。

这样做的最佳方法是什么？ 我已经查看了规则，但是我无法让它们INSERT到表中，并且我已经尝试记录每个查询，但这似乎没有记录SELECT语句。 理想情况下，为了安全起见，我希望将日志保存在数据库的表中，但是记录到文件也很好。

谢谢， 大卫

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

最满意答案

您可以在PostgreSQL中启用SENTENCE LOGGING：

在postgresql.conf中：

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

然后重新启动数据库，将记录所有的senteces。

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.