From: | David Steele <david(at)thelabyrinth(dot)net> |
---|---|
To: | John Scalia <jayknowsunix(at)gmail(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Fwd: Can I bother you for some more assistance? |
Date: | 2017-01-09 14:45:45 |
Message-ID: | adc23ff5-9799-22ea-e98c-9202916fd864@thelabyrinth.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)thelabyrinth(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2017-01-09 14:55:00 | Re: Using Views as Tables |
Previous Message | Scott Mead | 2017-01-09 14:14:41 | Re: Issue with pgAdmin4 UI |