Thread: Help with tracking!

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?

Thanks a lot and Best Regard,

Đỗ 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.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

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.

Something similar is mentioned in this thread:
http://archives.postgresql.org/pgsql-performance/2008-07/msg00144.php

regards,
Yeb Havinga

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

2010/4/18 Craig Ringer <craig@postnewspapers.com.au>:
> Đỗ 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.

Wouldn't this work:

alter user smarlowe set log_statement = 'all';

On Tue, 2010-04-20 at 06:03 -0600, Scott Marlowe wrote:
> Wouldn't this work:
>
> alter user smarlowe set log_statement = 'all';

IIRC it only works inside the given session (so it needs to be run each
time a query will be executed)
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

On Tue, Apr 20, 2010 at 6:11 AM, Devrim GÜNDÜZ <devrim@gunduz.org> wrote:
> On Tue, 2010-04-20 at 06:03 -0600, Scott Marlowe wrote:
>> Wouldn't this work:
>>
>> alter user smarlowe set log_statement = 'all';
>
> IIRC it only works inside the given session (so it needs to be run each
> time a query will be executed)

Not true:

psql
show log_statement;
 log_statement
---------------
 none
alter user smarlowe set log_statement = 'all';
show log_statement;
 log_statement
---------------
 all
\q
psql
show log_statement;
 log_statement
---------------
 all

On Tue, Apr 20, 2010 at 8:31 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Apr 20, 2010 at 6:11 AM, Devrim GÜNDÜZ <devrim@gunduz.org> wrote:
>> On Tue, 2010-04-20 at 06:03 -0600, Scott Marlowe wrote:
>>> Wouldn't this work:
>>>
>>> alter user smarlowe set log_statement = 'all';
>>
>> IIRC it only works inside the given session (so it needs to be run each
>> time a query will be executed)
>
> Not true:
>
> psql
> show log_statement;
>  log_statement
> ---------------
>  none
> alter user smarlowe set log_statement = 'all';
> show log_statement;
>  log_statement
> ---------------
>  all
> \q
> psql
> show log_statement;
>  log_statement
> ---------------
>  all

Note however that other sessions won't see the change.  Only
connections that come after the change will see it.

On 20/04/2010 10:33 PM, Scott Marlowe wrote:

>> psql
>> show log_statement;
>>   log_statement
>> ---------------
>>   all
>
> Note however that other sessions won't see the change.  Only
> connections that come after the change will see it.

Also, as the OP wants to use it for auditing, it's worth noting that
it's trivial for the audited user to simply disable log_statement in a
session with a SET command.

--
Craig Ringer

Craig Ringer wrote:
> On 20/04/2010 10:33 PM, Scott Marlowe wrote:
>
>>> psql
>>> show log_statement;
>>>   log_statement
>>> ---------------
>>>   all
>>
>> Note however that other sessions won't see the change.  Only
>> connections that come after the change will see it.
>
> Also, as the OP wants to use it for auditing, it's worth noting that
> it's trivial for the audited user to simply disable log_statement in a
> session with a SET command.

 From the docs for log_statement: "Only superusers can change this
setting. "

Cheers,
Steve

On 21/04/2010 8:10 AM, Steve Crawford wrote:

>  From the docs for log_statement: "Only superusers can change this
> setting. "

Argh. Thankyou.

--
Craig Ringer