Re: Help with configuring pgAudit

Joe Conway <[email protected]> Thu, 21 Nov 2019 10:15:50 -0500
Newsgroups	gmane.comp.db.postgresql.general
Message-ID	<[email protected]>
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
signature.asc (application/pgp-signature, 833 B)