Thank you so much for all your help! I found out my issue on accident actually. I backed up all my user accounts into a SQL scripts and after reviewing it, I noticed there were some lines that said: ALTER ROLE postgres SET "pgauid.log" to 'Role'; ALTER ROLE postgres SET "pgaudit.log_level" to 'notice'; ALTER ROLE postgres SET "pgaudit.log_client" to 'on';
I think these commands were leftover from when I ran an integrity check on the pgaudit install (it crashed for other reasons) so it never cleaned up these settings. Once I reset those settings back, it's working perfectly now. Thanks again for helping me getting this thing setup and working! On Thu, Nov 21, 2019 at 10:15 AM Joe Conway <m...@joeconway.com> wrote: > 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 > >