Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cQbMy-0000ny-4Y for pgsql-admin@arkaria.postgresql.org; Mon, 09 Jan 2017 14:56:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cQbMx-0000QU-No for pgsql-admin@arkaria.postgresql.org; Mon, 09 Jan 2017 14:56:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cQbMw-0000Oh-Rc for pgsql-admin@postgresql.org; Mon, 09 Jan 2017 14:56:18 +0000 Received: from mail.thelabyrinth.net ([45.56.70.56]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cQbMt-0002H7-VU for pgsql-admin@postgresql.org; Mon, 09 Jan 2017 14:56:18 +0000 Received: from Davids-MacBook.local (unknown [98.204.177.190]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id 878EA556D0; Mon, 9 Jan 2017 14:56:13 +0000 (UTC) Subject: Re: Fwd: Can I bother you for some more assistance? To: John Scalia , "pgsql-admin@postgresql.org" References:

From: David Steele Message-ID: <0cd1a798-7a31-de5d-a3fc-3e67fea99dcc@pgmasters.net> Date: Mon, 9 Jan 2017 09:56:11 -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: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin 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. > \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 '' for > both of these as opposed to showing that the UPDATE command should > have been 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