Re: Help with configuring pgAudit

[Dave Hughes <dhughes20@xxxxxxxxx>]

Hey,

Thanks for reaching out.  It looks like I have that parameter set as well.  Here is a list of settings I have turned on in postgresql.conf since I installed pgAudit:

shared_preload_libraries = 'pgaudit'

log_destination = 'csvlog'

logging_collector = on

log_directory = '/work/PostgreSQL/10/data'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_connections = on

log_disconnections = on

log_line_prefix = '<%m:%r:%u@%d:[%p]:>'

pgaudit.log = 'ddl'

Is there some other setting I may be missing?

Thanks!

On Tue, Nov 19, 2019 at 4:27 PM Rajni Baliyan <saan654@xxxxxxxxx> wrote:

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@xxxxxxxxx> 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