Postgresql – Help with configuring pgAudit

postgresql

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

Best Answer

Before you can use pgAudit, you need to add it on the postgresql.conf file. You need to uncomment the line (or add pgaudit if you already have another libraries) to the shared_preload_libraries, like this:

shared_preload_libraries='pgaudit'

After this you must start the server and add the pgaudit extension:

CREATE EXTENSION pgaudit

From then on you can configure what is going to be logged either directly on the postgresql.conf file or by set commands. pgAudit can register by session or by oject created, that depends on you. Only after all of that your set pgaudit.log = 'ddl' will register what you want.

Related Question