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

[Dhinakaran R <dhiraam.postgresql@xxxxxxxxx>]

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@xxxxxxxxxxx> wrote:

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