From: David Steele <david@pgmasters.net>
To: John Scalia <jayknowsunix@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Fwd: Can I bother you for some more assistance?
Date: Mon, 9 Jan 2017 09:56:11 -0500
Message-ID: <0cd1a798-7a31-de5d-a3fc-3e67fea99dcc@pgmasters.net> (raw)
In-Reply-To: <CABzCKRAnPg6vJSd-6FmO0LgxtfCd9nJoTGstPUKFToD+-XkF+Q@mail.gmail.com>
References: <CABzCKRDGEmx5-=WZL4hqQGXdoFGWTxYnZKDG6AJdeawGd90dBQ@mail.gmail.com>
<CABzCKRAnPg6vJSd-6FmO0LgxtfCd9nJoTGstPUKFToD+-XkF+Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.
> \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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: david@pgmasters.net, jayknowsunix@gmail.com
Subject: Re: Fwd: Can I bother you for some more assistance?
In-Reply-To: <0cd1a798-7a31-de5d-a3fc-3e67fea99dcc@pgmasters.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox