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 <mail@xxxxxxxxxxxxx> 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
