Обсуждение: Not able to set pgaudit.log with pgaudit 1.3.2 in PostgreSQL 11.9
/usr/pgsql-11/lib
postgres>ls -ltr *pgaudit*
-rwxr-xr-x. 1 root root 33088 Oct 6 10:48 pgaudit.so
postgres>
/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>
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)
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)
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
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
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 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
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.
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.