Обсуждение: [ADMIN] Fwd: Can I bother you for some more assistance?

Поиск
Список
Период
Сортировка

[ADMIN] Fwd: Can I bother you for some more assistance?

От
John Scalia
Дата:
Hi all,

This may not be the most appropriate forum for this question, but if anyone know of a better one, I'd be happy to change the destination, anyway I've been struggling most of this morning to get pgaudit working with object logging on a test database, but I can't seem to get it working properly. Apologies for the length of this, but here's the procedure I've been following:

psql -U postgres postgres
CREATE DATABASE phi;
GRANT ALL ON DATABASE phi TO postgres;
\c phi

-- 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.
\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';


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. In any event, I can't determine what step(s) I might be missing, and nothing is coming to mind immediately. Can anyone assist?

TIA,
Jay

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

От
David Steele
Дата:
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



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

От
David Steele
Дата:
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