Обсуждение: [ADMIN] Fwd: Can I bother you for some more assistance?
Hi all,
This may not be the most appropriate forum for this question, but if anyone know of a better one, I'd be happy to change the destination, anyway I've been struggling most of this morning to get pgaudit working with object logging on a test database, but I can't seem to get it working properly. Apologies for the length of this, but here's the procedure I've been following:psql -U postgres postgres
CREATE DATABASE phi;-- Null out pgaudit.log or the extension will not load
SET pgaudit.log = '';
--- I have to exit the database at this point or the create schema database command will fail, not sure why. Maybe the audit.sql is doing something weird.
psql -U postgres phi
CREATE SCHEMA phi;SET search_path TO phi, pgaudit, public;
-- The INSERT command should not logged
-- The UPDATE should be logged
Hi John, On 1/6/17 10:55 AM, John Scalia wrote: > -- Null out pgaudit.log or the extension will not load > SET pgaudit.log = ''; > CREATE EXTENSION pgaudit; > SET pgaudit.log = 'function,ddl,rule,misc'; > \i .usr/pgaudit_analyze/sql/audit.sql > > --- I have to exit the database at this point or the create schema > database command will fail, not sure why. Maybe the audit.sql is doing > something weird. What error are you seeing? Create database must run in its own transaction so it will error if you try to run all these commands in a single transaction. > psql -U postgres phi > CREATE SCHEMA phi; > SET search_path TO phi, pgaudit, public; > GRANT ALL ON SCHEMA phi TO postgres; > CREATE TABLE patient (id INT, 'first_name' VARCHAR(20), 'last_name' > VARCHAR(30), SS_NUM CHAR(12), mpi VARCHAR(20)); > SET pgaudit.role = 'auditor'; > GRANT select, update, delete on patient TO auditor; > > -- The INSERT command should not logged > INSERT INTO patient VALUES (1, 'Test', 'Test', '000-00-0000', 'T29383'); > > -- The UPDATE should be logged > UPDATE patient SET mpi = 'T459093'; You have not granted the auditor role usage on the phi schema and this is likely why insert and update are not being logged. > If you'll note the last 4 commands, they're basically identical to the > pgaudit instructions, but the CSV logfile shows '<not logged>' for > both of these as opposed to showing that the UPDATE command should > have been logged. <not logged> refers to the parameters, not the statement. log_parameter is off by default. Regards, -- -David david@thelabyrinth.net
Hi John, On 1/6/17 10:55 AM, John Scalia wrote: > -- Null out pgaudit.log or the extension will not load > SET pgaudit.log = ''; > CREATE EXTENSION pgaudit; > SET pgaudit.log = 'function,ddl,rule,misc'; > \i .usr/pgaudit_analyze/sql/audit.sql > > --- I have to exit the database at this point or the create schema > database command will fail, not sure why. Maybe the audit.sql is doing > something weird. What error are you seeing? Create database must run in its own transaction so it will error if you try to run all these commands in a single transaction. > \q > psql -U postgres phi > CREATE SCHEMA phi; > SET search_path TO phi, pgaudit, public; > GRANT ALL ON SCHEMA phi TO postgres; > CREATE TABLE patient (id INT, 'first_name' VARCHAR(20), 'last_name' > VARCHAR(30), SS_NUM CHAR(12), mpi VARCHAR(20)); > SET pgaudit.role = 'auditor'; > GRANT select, update, delete on patient TO auditor; > > -- The INSERT command should not logged > INSERT INTO patient VALUES (1, 'Test', 'Test', '000-00-0000', 'T29383'); > > -- The UPDATE should be logged > UPDATE patient SET mpi = 'T459093'; You have not granted the auditor role usage on the phi schema and this is likely why insert and update are not being logged. > If you'll note the last 4 commands, they're basically identical to the > pgaudit instructions, but the CSV logfile shows '<not logged>' for > both of these as opposed to showing that the UPDATE command should > have been logged. <not logged> refers to the parameters, not the statement. log_parameter is off by default. Regards, -- -David david@pgmasters.net