Re: Help with configuring pgAudit

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(at)joeconway(dot)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
>
>