Re: Help with configuring pgAudit

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