Thread: Help with configuring pgAudit

Hello,

I'm using PostgreSQL 10.5 on Linux (RHEL).  I recently installed pgAudit and was trying to configure it to capture DLL statements.

1) The first thing I tried was to edit the postgresql.conf file directly.  I didn't see any commented out default entries to edit, so near where I have the entries:
log_destination = 'csvlog'

log_collector = on

I entered my own entry of:

pgaudit.log = 'ddl'

Then I restarted the database.  After doing this I created and dropped a table, but no entry of that in the log file postgresql-2019-11-19-141901.csv.

2) So then tried to create the entry using the ALTER command:

ALTER SYSTEM SET pgaudit.log TO 'ddl';

SELECT pg_reload_conf();

After doing this, I noticed a second file was created (postgresql.auto.conf).  That file had the entry pgaudit.log = 'ddl'.  From what I read, this file get read after the main postgresql.conf file.  

However after creating and dropping a table, still no entry in the log file.  I did notice though when I run the command: show pgaudit.log;  It came back with 'Role' (and not ddl). 

3) So the final thing I tried was to enter the command: set pgaudit.log = 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'.  This time I tried to drop a table again.  After the table was dropped , my psql client displayed:
NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLE....blah,blah....<not logged>.  This looks like a pgaudit entry but was on my screen and not in the log file.  At this point I restarted the database again and now when I run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl).  

Can someone please help me see what it is i'm doing incorrectly?  

Thanks in advance,

Dave Hughes

Hello Dave,

What I can see is you missed to include pgAudit extension in shared_preload_libraries parameter (shared_preload_libraries='pgaudit'). 

Thanks

Rajni

On Wed, Nov 20, 2019 at 7:39 AM Dave Hughes <dhughes20@gmail.com> wrote:

Hello,

I'm using PostgreSQL 10.5 on Linux (RHEL).  I recently installed pgAudit and was trying to configure it to capture DLL statements.

1) The first thing I tried was to edit the postgresql.conf file directly.  I didn't see any commented out default entries to edit, so near where I have the entries:
log_destination = 'csvlog'

log_collector = on

I entered my own entry of:

pgaudit.log = 'ddl'

Then I restarted the database.  After doing this I created and dropped a table, but no entry of that in the log file postgresql-2019-11-19-141901.csv.

2) So then tried to create the entry using the ALTER command:

ALTER SYSTEM SET pgaudit.log TO 'ddl';

SELECT pg_reload_conf();

After doing this, I noticed a second file was created (postgresql.auto.conf).  That file had the entry pgaudit.log = 'ddl'.  From what I read, this file get read after the main postgresql.conf file.  

However after creating and dropping a table, still no entry in the log file.  I did notice though when I run the command: show pgaudit.log;  It came back with 'Role' (and not ddl). 

3) So the final thing I tried was to enter the command: set pgaudit.log = 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'.  This time I tried to drop a table again.  After the table was dropped , my psql client displayed:
NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLE....blah,blah....<not logged>.  This looks like a pgaudit entry but was on my screen and not in the log file.  At this point I restarted the database again and now when I run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl).  

Can someone please help me see what it is i'm doing incorrectly?  

Thanks in advance,

Dave Hughes

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

On 11/21/19 1:27 PM, Dave Hughes wrote:
> 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!

Ah, makes sense now.

For future reference, you can inspect the pgaudit (and other) active
settings using the pg_settings system view, e.g.:

select name, setting, source
from pg_settings where name like 'pgaudit.%';
            name            |     setting     |       source
----------------------------+-----------------+--------------------
 pgaudit.log                | ddl, role, read | configuration file
 pgaudit.log_catalog        | on              | configuration file
 pgaudit.log_client         | off             | default
 pgaudit.log_level          | log             | configuration file
 pgaudit.log_parameter      | on              | configuration file
 pgaudit.log_relation       | off             | configuration file
 pgaudit.log_statement_once | off             | configuration file
 pgaudit.role               |                 | default
(8 rows)

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development