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@xxxxxxxxxxxxx
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin