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
---------------
allNote 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
---------------
allNote 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
2010/4/19 Đỗ Ngọc Trí Cường <seminoob@gmail.com>
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,
Setup your log_line_prefix and log_statement setting properly in
postgresql.conf so that you get the user info and other appropriate details.
After that you can tail the DB server log file and grep for that specific
user to get what sort of SQL is been executed.
--
Shoaib Mir
http://shoaibmir.wordpress.com/