Re: Help with tracking!

Yeb Havinga wrote:
> Craig Ringer wrote:
>> Đỗ Ngọc Trí Cường wrote:
>>
>>> Dear all,
>>>
>>> I've a problem but I search all the help file and can't find the
>>> solution.
>>>
>>> I want to track all action of a specify role on all or one schema in
>>> database.
>>>
>>> Can you help me?
>>>
>>
>> You can use statement-level logging, though there are no facilities in
>> statement-level logging to restrict what is logged to only one role's
>> activity.
>>
>> You can use the usual audit triggers on database tables, which is what I
>> would recommend. Audit triggers in PostgreSQL cannot track reads
>> (SELECTs), only INSERT/UPDATE/DELETE and in 8.4 also TRUNCATE. They
>> cannot track ALTER/RENAME/DROP table, changes to sequences, etc. It is
>> trivial to write an audit trigger that only records anything when a user
>> is a member of a particular role.
>>
> Yes tracking SELECTs needs would have to go with a log file, since also
> a DO INSTEAD rule on SELECT has to be another SELECT command, and cannot
> e.g. be a INSERT followed by a SELECT.

OK, then a trigger-based audit setup is not going to work for you
because Pg doesn't support triggers on SELECT. I guess you're stuck with
statement logging and a filter on that log unless there's something else
I don't know of.

One other question, though: Does your audit just have to track the SQL
executed? Or the *data* accessed? The same SQL statement has different
results at different times. If you need to track what someone has
accessed, you're not likely to be able to do that with PostgreSQL
without some heavy use of stored procedures to wrap basically every
query. Ick.

If all you need is to log the SQL executed, then stick with log_statement.

--
Craig Ringer