Обсуждение: 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
2010/4/19 Đỗ Ngọc Trí Cường <seminoob@gmail.com>
--
Shoaib Mir
http://shoaibmir.wordpress.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/