Re: Fwd: Can I bother you for some more assistance?

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(at)pgmasters(dot)net