On 11/20/19 5:54 PM, Dave Hughes wrote: > Thanks for the tips Joe! After fighting with this all day, I realized > while I was testing this, I was logging into the database as the > "postgres" user. For some reason those actions were not being logged. > But once I logged in as another superuser account I have, I saw all my > ddl statements being audited in the log file. So it was working after > all, but just not for the "postgres" user. Do you happen to know if > that behavior is how pgaudit is supposed to work? You'd think even the > "postgres" user activity would be logged as well? I'm not sure what you are doing wrong, but the "postgres" user actions should get logged just like everything else. For grins I followed the aforementioned supplement pdf sections 2.2 (pgaudit) except I used cvslog instead of stderr as a destination, and 2.3 (logging), started up postgres, logged in as postgres, created a table, and then tailed the postgres log: ---------------- tail -n 33 $PGDATA/postgresql.conf shared_preload_libraries = 'pgaudit' # Enable catalog logging - default is 'on' pgaudit.log_catalog='on' # Specify the verbosity of log information (INFO, NOTICE, LOG, WARNING, DEBUG) pgaudit.log_level='log' # Log the parameters being passed pgaudit.log_parameter='on' # Log each relation (TABLE, VIEW, etc.) mentioned in a SELECT or DML statement pgaudit.log_relation='off' # For every statement and substatement, log the statement and parameters every time pgaudit.log_statement_once='off' # Define the master role to use for object logging # pgaudit.role='' # Choose the statements to log: # READ - SELECT, COPY # WRITE - INSERT, UPDATE, DELETE, TRUNCATE, COPY # FUNCTION - Function Calls and DO Blocks # ROLE - GRANT, REVOKE, CREATE/ALTER/DROP ROLE # DDL - All DDL not included in ROLE # MISC - DISCARD, FETCH, CHECKPOINT, VACUUM pgaudit.log='ddl, role, read' log_line_prefix = '%m %u %d: ' log_destination = 'csvlog' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_file_mode = 0600 log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 ---------------- (restart postgres) ---------------- psql test psql (11.1) Type "help" for help. test=# CREATE TABLE pgatest(id int); CREATE TABLE test=# \q ---------------- tail -n 1 $PGDATA/pg_log/postgresql-Thu.csv 2019-11-21 10:07:39.320 EST,"postgres","test",14809,"[local]",5dd6a829.39d9,1,"CREATE TABLE",2019-11-21 10:07:21 EST,3/8,394984,LOG,00000,"AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE pgatest(id int);,<none>",,,,,,,,,"psql" Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Attachment:
signature.asc
Description: OpenPGP digital signature
