Not able to set pgaudit.log with pgaudit 1.3.2 in PostgreSQL 11.9

On 11/7/20 10:42 AM, Dhinakaran R wrote:

Hello,

I had installed PostgreSQL 11.9 and pgaudit 1.3.2.  updated
shared_preload_libraries, restarted PostgreSQL, created an extension for
pgaudit and set parameters for pgaudit in postgresql.conf.  But
surprisingly pgaudit.log is not getting picked up and neither able to
set it manually.

postgres>pwd
/usr/pgsql-11/lib
postgres>ls -ltr *pgaudit*
-rwxr-xr-x. 1 root root 33088 Oct  6 10:48 pgaudit.so
postgres>

postgres>pwd
/usr/pgsql-11/share/extension
postgres>ls -ltr *pgaudit*
-rw-r--r--. 1 root root 145 Oct  6 10:48 pgaudit.control
-rw-r--r--. 1 root root 615 Oct  6 10:48 pgaudit--1.3.2.sql
-rw-r--r--. 1 root root 175 Oct  6 10:48 pgaudit--1.3--1.3.1.sql
-rw-r--r--. 1 root root 177 Oct  6 10:48 pgaudit--1.3.1--1.3.2.sql
postgres>

postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# select * from pg_Extension;
 extname | extowner | extnamespace | extrelocatable | extversion |
extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql |       10 |           11 | f              | 1.0        |
      |
 pgaudit |       10 |         2200 | t              | 1.3.2      |
      |
(2 rows)

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

But I have below parameters in postgresql.conf.. not sure why its not
picking up pgaudit.log value.

Have you looked at the Postgres log to see if there are relevant error
messages?

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%m %u %d [%p]: '
shared_preload_libraries = 'pgaudit' # (change requires restart)
pgaudit.log = 'write, role, ddl, misc_set'
pgaudit.log_catalog = on
pgaudit.log_client = on
pgaudit.log_parameter = on

Thanks,
Dhiraam.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian

There were no error messages from startup, even with debug level 1 I got
only below.

2020-11-07 18:57:07.335 UTC [2290044]: LOG: database system was shut
down at 2020-11-07 18:57:03 UTC
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: checkpoint record is at
0/1A3FE00
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: redo record is at
0/1A3FE00; shutdown true
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: next transaction ID:
0:616; next OID: 24676
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: next MultiXactId: 1; next
MultiXactOffset: 0
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: oldest unfrozen
transaction ID: 561, in database 1
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: oldest MultiXactId: 1, in
database 1
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: commit timestamp Xid
oldest/newest: 0/0
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: transaction ID wrap limit
is 2147484208, limited by database with OID 1
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: MultiXactId wrap limit is
2147483648, limited by database with OID 1
2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: starting up replication
slots
2020-11-07 18:57:07.336 UTC [2290044]: DEBUG: MultiXactId wrap limit is
2147483648, limited by database with OID 1
2020-11-07 18:57:07.336 UTC [2290044]: DEBUG: MultiXact member stop
limit is now 4294914944 based on MultiXact 1
2020-11-07 18:57:07.340 UTC [2290042]: DEBUG: starting background worker
process "logical replication launcher"
2020-11-07 18:57:07.340 UTC [2290042]: LOG: database system is ready to
accept connections
2020-11-07 18:57:07.342 UTC [2290048]: DEBUG: autovacuum launcher started
2020-11-07 18:57:07.343 UTC [2290050]: DEBUG: logical replication
launcher started

On Sun, 8 Nov 2020 at 00:25, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Dhinakaran R <dhiraam.postgresql@gmail.com> wrote on 2020/11/07 02:03:22
PM:

I had installed PostgreSQL 11.9 and pgaudit 1.3.2.  updated
shared_preload_libraries, restarted PostgreSQL, created an extension

for

pgaudit and set parameters for pgaudit in postgresql.conf.  But
surprisingly pgaudit.log is not getting picked up and neither able to
set it manually.

pgaudit.log = 'write, role, ddl, misc_set'

misc_set was added in pgAudit 1.4, if you remove that it will likely work.

pgAudit should probably log when it has an invalid parameter.

Brad.

Hi Brad

Thank you for pointing this out, my bad I was looking at the wrong version
readme file. It's working after removing misc_set.

On Sun, 8 Nov 2020 at 01:38, Brad Nicholson <bradn@ca.ibm.com> wrote: