pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Fwd: Can I bother you for some more assistance? 3+ messages / 3 participants [nested] [flat]
* Fwd: Can I bother you for some more assistance? @ 2017-01-06 15:55 John Scalia <jayknowsunix@gmail.com> 2017-01-09 14:45 ` Re: Fwd: Can I bother you for some more assistance? David Steele <david@thelabyrinth.net> 2017-01-09 14:56 ` Re: Fwd: Can I bother you for some more assistance? David Steele <david@pgmasters.net> 0 siblings, 2 replies; 3+ messages in thread From: John Scalia @ 2017-01-06 15:55 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Fwd: Can I bother you for some more assistance? 2017-01-06 15:55 Fwd: Can I bother you for some more assistance? John Scalia <jayknowsunix@gmail.com> @ 2017-01-09 14:45 ` David Steele <david@thelabyrinth.net> 1 sibling, 0 replies; 3+ messages in thread From: David Steele @ 2017-01-09 14:45 UTC (permalink / raw) To: John Scalia <jayknowsunix@gmail.com>; 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@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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Fwd: Can I bother you for some more assistance? 2017-01-06 15:55 Fwd: Can I bother you for some more assistance? John Scalia <jayknowsunix@gmail.com> @ 2017-01-09 14:56 ` David Steele <david@pgmasters.net> 1 sibling, 0 replies; 3+ messages in thread From: David Steele @ 2017-01-09 14:56 UTC (permalink / raw) To: John Scalia <jayknowsunix@gmail.com>; 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. > \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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-01-09 14:56 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-01-06 15:55 Fwd: Can I bother you for some more assistance? John Scalia <jayknowsunix@gmail.com> 2017-01-09 14:45 ` David Steele <david@thelabyrinth.net> 2017-01-09 14:56 ` David Steele <david@pgmasters.net>
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