Subject: Re: Fwd: Can I bother you for some more assistance?
To: John Scalia <jayknowsunix@gmail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
References: 
 <CABzCKRDGEmx5-=WZL4hqQGXdoFGWTxYnZKDG6AJdeawGd90dBQ@mail.gmail.com>
 <CABzCKRAnPg6vJSd-6FmO0LgxtfCd9nJoTGstPUKFToD+-XkF+Q@mail.gmail.com>
From: David Steele <david@thelabyrinth.net>
Message-ID: <adc23ff5-9799-22ea-e98c-9202916fd864@thelabyrinth.net>
Date: Mon, 9 Jan 2017 09:45:45 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:45.0)
 Gecko/20100101 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CABzCKRAnPg6vJSd-6FmO0LgxtfCd9nJoTGstPUKFToD+-XkF+Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin