ossc-db / pgaudit Goto Github PK

View Code? Open in Web Editor NEW

This project forked from pgaudit/pgaudit

2.0 2.0 4.0 468 KB

PostgreSQL Audit Extension

License: Other

Makefile 0.36% Perl 46.44% PLpgSQL 10.28% Ruby 0.63% C 39.81% Shell 0.35% Lex 2.14%

pgaudit's People

Contributors

Stargazers

Watchers

Forkers

ooyamams harada-toshi higuchi-daisuke tameguro

pgaudit's Issues

Please add "user" filter to pgaudit (refactored branch).

Hi.

I am trying pgaudit (refactored branch).

In the setting of pgaudit (refactored), there is no user filtering function, but we think that it is better to add it assuming the following case.

For example, it is considered that there is a case where only the operation of the privileged user (eg "postgres") is audited and the application user's operation is not audited.

In pgaudit (advanced branch), filtering by "user" was possible.
Even "refactored branch", please implement filtering of "user".

Deletion of subordinate constraints and indexes is not output to the audit log when the table is deleted.(refactoed branch)

Hi.

I am trying pgaudit (refactored branch) and PostgreSQL 9.6.1.

Sumary

Constraints and indexes are set in the table.
When dropping the table, the "advanced branch" also reports that the constraints and indexes subordinate to the table have been deleted. (* 1)

Is this the "refactored branch" specification?

(* 1) When an event trigger is set.

advanced branch audit log

LOG:  AUDIT: DDL,CREATE TABLE,TABLE,public.foo,CREATE TABLE foo (
LOG:  AUDIT: DDL,CREATE INDEX,INDEX,public.foo_id_key,CREATE TABLE foo (
LOG:  AUDIT: DDL,CREATE TABLE AS,TABLE,public.bar,CREATE TABLE bar AS SELECT * FROM foo;
LOG:  AUDIT: DDL,ALTER TABLE,TABLE,public.baz,ALTER TABLE foo RENAME TO baz;
LOG:  AUDIT: DDL,DROP TABLE,TABLE,public.bar,DROP TABLE bar;
LOG:  AUDIT: DDL,DROP TABLE,TABLE,public.baz,DROP TABLE baz;
LOG:  AUDIT: DDL,DROP TABLE,TABLE CONSTRAINT,foo_id_key on public.baz,DROP TABLE baz;
LOG:  AUDIT: DDL,DROP TABLE,INDEX,public.foo_id_key,DROP TABLE baz;

See "DROP TABLE baz" command.
Along with deleting baz, foo_id_key constraint and deletion of index have been reported.

refactored branch audit log.

LOG:  AUDIT: SESSION,1,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/3,2050,,,CREATE FUNCTION,,,"CREATE FUNCTION pgaudit_ddl_command_end()
LOG:  AUDIT: SESSION,2,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/4,2051,,,CREATE EVENT TRIGGER,,,"CREATE EVENT TRIGGER pgaudit_ddl_command_end
LOG:  AUDIT: SESSION,3,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/5,2052,,,CREATE FUNCTION,FUNCTION,public.pgaudit_sql_drop(),"CREATE FUNCTION pgaudit_sql_drop()
LOG:  AUDIT: SESSION,4,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/6,2053,,,CREATE EVENT TRIGGER,,,"CREATE EVENT TRIGGER pgaudit_sql_drop
LOG:  AUDIT: SESSION,1,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/8,2054,,,CREATE TABLE,TABLE,public.foo,"CREATE TABLE foo (
LOG:  AUDIT: SESSION,1,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/8,2054,,,CREATE INDEX,INDEX,public.foo_id_key,"CREATE TABLE foo (
LOG:  AUDIT: SESSION,2,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/9,2055,,,CREATE TABLE AS,TABLE,public.bar,CREATE TABLE bar AS SELECT * FROM foo;,<not logged>
LOG:  AUDIT: SESSION,3,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/10,2056,,,ALTER TABLE,TABLE,public.baz,ALTER TABLE foo RENAME TO baz;,<not logged>
LOG:  AUDIT: SESSION,4,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/11,2057,,,DROP TABLE,TABLE,public.bar,DROP TABLE bar;,<not logged>
LOG:  AUDIT: SESSION,5,1,DDL,2017-02-08 16:14:23 JST,regression,[local],pg_regress,2/12,2058,,,DROP TABLE,TABLE,public.baz,DROP TABLE baz;,<not logged>

However, in the "refactored branch", it is not output to the audit log that the constraints or indexes subordinate to the table have been deleted.

unnecessary "AUDIT:" prefix is logged when database started.

I want to log about "foo" database only. but it also log "database system was shut down" and "database system is ready to accept connections".

pgaudit.conf

[output]
logger= 'serverlog'

[rule]
database = 'foo'
class = 'READ'

postgresql.log

LOG:  AUDIT: SESSION,,,SYSTEM,database system was shut down at 2016-08-11 10:24:29 JST
LOG:  database system was shut down at 2016-08-11 10:24:29 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  AUDIT: SESSION,,,SYSTEM,database system is ready to accept connections
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Reading view emit a audit log as UNKNOWN

While testing I noticed that pgaudit emits a UNKNOWN type log when reading view. This behavior seems same as what advanced branch doing.

In advanced branch (with format 'HOGE : user = %user, class = %class,tag = %command_tag, type = %object_type, text = %command_text')

LOG:  HOGE : user = masahiko, class = READ MISC MISC, tag = SELECT UNKNOWN SELECT, type = VIEW VIEW TABLE, text = select * from hoge_view;

In refactored branch

NOTICE:  AUDIT: SESSION,1,1,READ,2017-02-13 20:06:52 JST,postgres,masahiko,[local],psql,2/30,0,,,SELECT,VIEW,public.hoge_view,select * from hoge_view;,<not logged>
NOTICE:  AUDIT: SESSION,1,1,MISC,2017-02-13 20:12:28 JST,postgres,masahiko,[local],psql,2/30,0,,,UNKNOWN,VIEW,public.hoge_view,select * from hoge_view;,<not logged>
NOTICE:  AUDIT: SESSION,1,1,READ,2017-02-13 20:12:28 JST,postgres,masahiko,[local],psql,2/30,0,,,SELECT,TABLE,public.hoge,select * from hoge_view;,<not logged>

This cause is that old and new column are added when rewriting query. So these SELECT, UNKNOWN and SELECT are corresponding to old column, new column and the table. I understand the cause of this but I'm not sure how we can fix it. Issued to not forget it.

(refactored branch) [bug] By config file description of mistakes, Segfault occurs during PostgreSQL start.

Overview

By config file description of mistakes, Segfault occurs during PostgreSQL start.
No message at startup is displayed after "server starting" message.
Examining the process, the postgres process is not running.
Postmaster.pid is present in the database cluster.
A core file exists in the database cluster.

Config file

[output]
        logger = 'serverlog'

# SQL(0)
[rule]
        class = 'READ'

# SQL(1)
        database = 'test'
        class = 'READ'
        object_name = 'public.foo'

# SQL(2)
        database = 'test'
        class = 'WRITE'
        object_name = 'public.foo'

# SQL(3)
        database = 'bad'
        class = 'WRITE'
        object_name = 'public.bar'

I forgot to write [rule]. Does this happen when there are multiple filters in the same rule?

Execute log.

$ pg_ctl -D ~/pgdata/9.6-pgaudit/ start
server starting
$ ps aux | grep post
root      1474  0.0  0.1  93212  2248 ?        Ss   09:53   0:00 /usr/libexec/postfix/master -w
postfix   1494  0.0  0.3  93384  3928 ?        S    09:53   0:00 qmgr -l -t unix -u
postfix  17152  0.0  0.3  93316  3904 ?        S    16:41   0:00 pickup -l -t unix -u
nuko     19725  0.0  0.0 112664   968 pts/0    R+   17:41   0:00 grep --color=auto post
$ ls -l ~/pgdata/9.6-pgaudit/core.19719
-rw-------. 1 nuko nuko 1073152  3月  7 17:41 /home/nuko/pgdata/9.6-pgaudit/core.19719
$ ls -l ~/pgdata/9.6-pgaudit/postmaster.pid
-rw-------. 1 nuko nuko 53  3月  7 17:41 /home/nuko/pgdata/9.6-pgaudit/postmaster.pid
$

Core file (execute gdb log)

$ gdb ~/pgsql/pgsql/bin/postgres -c ~/pgdata/9.6-pgaudit/core.19719
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-80.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /home/nuko/pgsql/pgsql-9.6.2/bin/postgres...(no debugging symbols found)...done.
[New LWP 19719]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `/home/nuko/pgsql/pgsql-9.6.2/bin/postgres -D /home/nuko/pgdata/9.6-pgaudit'.
Program terminated with signal 11, Segmentation fault.
#0  0x00000000007c620a in MemoryContextAlloc ()
Missing separate debuginfos, use: debuginfo-install glibc-2.17-105.el7.x86_64 libxml2-2.9.1-6.el7_2.2.x86_64 xz-libs-5.1.2-12alpha.el7.x86_64 zlib-1.2.7-15.el7.x86_64
(gdb) where
#0  0x00000000007c620a in MemoryContextAlloc ()
#1  0x00000000007c6ca1 in MemoryContextStrdup ()
#2  0x00007f543a238369 in processAuditConfigFile () from /home/nuko/pgsql/pgsql-9.6.2/lib/pgaudit.so
#3  0x00007f543a235ba8 in _PG_init () from /home/nuko/pgsql/pgsql-9.6.2/lib/pgaudit.so
#4  0x00000000007aa7a8 in internal_load_library ()
#5  0x00000000007ab00c in load_file ()
#6  0x00000000007b218b in load_libraries ()
#7  0x00000000007b33ce in process_shared_preload_libraries ()
#8  0x000000000065fc35 in PostmasterMain ()
#9  0x000000000046ced0 in main ()
(gdb)

(refactored branch) Even if an unnecessary token exists in the setting file, it does not cause an error.

Overview

Even if an unnecessary token exists in the setting file, it does not cause an error.
- In the following example, an unnecessary token "}" exists in the configuration file.
Readme.md does not describe handling when unnecessary tokens exist in each section.

config file sample

[output]
        logger = 'serverlog'
        level   = 'LOG'
}

[option]
        role = 'auditor'
}

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC' # error
}

PostgreSQL launch log.

$ pg_ctl -w -D ~/pgdata/9.6-pgaudit/ start
waiting for server to start....LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role = auditor
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 500
LOG:  pgaudit extension initialized
}}}LOG:  database system was shut down at 2017-03-01 11:28:49 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  connection received: host=[local]
LOG:  connection authorized: user=nuko database=postgres
FATAL:  role "nuko" does not exist
 done
server started

(refactored branch) pgaudit setting file minor problem of open error message.

pgaudit setting file minor problem of open error message.
There is no parenthesis to the left of the path name in the error message.

When pgaudit.config_file is not set in postgresql.conf

$ pg_ctl -w start -D ~/pgdata/9.6-pgaudit/
waiting for server to start....FATAL:  could not open file "")
LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

If you set a path that does not exist or is not authoritative in pgaudit.config_file

$ pg_ctl -w start -D ~/pgdata/9.6-pgaudit/
waiting for server to start....FATAL:  could not open file "/tmp/foobarbaz")
LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

(refactored branch) Even if you specify a command_tag filter, not filtered.

Overview

Even if you specify a command_tag filter, not filtered.
In Readme.md, command_tag is described as an item that can be designated as a filter.
Writing command_tag = '...' in the configuration file does not cause an error in the configuration file.
However, this filter is not working in the current version. Even if you specify it, all command tags are output.

Config file

[output]
        logger = 'serverlog'

# SQL(1)
[rule]
        command_tag = 'SELECT'
        class = 'READ,WRITE,DDL,MISC'

Executed SQL

CREATE TABLE IF NOT EXISTS foo(id int, data text);
BEGIN;
INSERT INTO foo VALUES (1, 'aaa');
TABLE foo;
UPDATE foo SET data = 'AAA' WHERE id = 1;
SELECT * FROM foo;
DELETE FROM foo;
TRUNCATE foo;
COMMIT;
DROP TABLE foo;

Log.

LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role =
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 356
LOG:      STR command_tag = select
LOG:  pgaudit extension initialized
LOG:  database system was shut down at 2017-03-07 15:00:19 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  AUDIT: SESSION,1,1,DDL,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/2,2358,,,CREATE TABLE,TABLE,public.foo,"CREATE TABLE IF NOT EXISTS foo(id int, data text);",<not logged>
LOG:  AUDIT: SESSION,2,1,MISC,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,0,,,BEGIN,,,BEGIN;,<not logged>
LOG:  AUDIT: SESSION,3,1,WRITE,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,0,,,INSERT,TABLE,public.foo,"INSERT INTO foo VALUES (1, 'aaa');",<not logged>
LOG:  AUDIT: SESSION,4,1,READ,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,2359,,,SELECT,TABLE,public.foo,TABLE foo;,<not logged>
LOG:  AUDIT: SESSION,5,1,WRITE,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,2359,,,UPDATE,TABLE,public.foo,UPDATE foo SET data = 'AAA' WHERE id = 1;,<not logged>
LOG:  AUDIT: SESSION,6,1,READ,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,2359,,,SELECT,TABLE,public.foo,SELECT * FROM foo;,<not logged>
LOG:  AUDIT: SESSION,7,1,WRITE,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,2359,,,DELETE,TABLE,public.foo,DELETE FROM foo;,<not logged>
LOG:  AUDIT: SESSION,8,1,WRITE,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,2359,,,TRUNCATE TABLE,,,TRUNCATE foo;,<not logged>
LOG:  AUDIT: SESSION,9,1,MISC,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/3,2359,,,COMMIT,,,COMMIT;,<not logged>
LOG:  AUDIT: SESSION,10,1,DDL,2017-03-07 15:00:24 JST,postgres,postgres,[local],psql,2/4,2360,,,DROP TABLE,TABLE,public.foo,DROP TABLE foo;,<not logged>
LOG:  disconnection: session time: 0:00:00.053 user=postgres database=postgres host=[local]

In this verification, we expected only the SELECT command to be output to the audit log.
SQL commands with command tags INSERT and DELETE are also output in the audit log.

(refactored branch) The filter by the database name has no effect on the audit log of the connection attempt.

Overview

The filter by the database name has no effect on the audit log of the connection attempt.
For the connection completion audit log, the filter based on the database name has an effect.
The disconnection audit log is valid as well.
The database name is not set in the audit log at the time of connection attempt.
When specifying a database name in the filter, should audit log of connection attempt should suppress output?

pgaudit config file

[output]
        logger = 'serverlog'

# CONNECT(1)
[rule]
        class = 'CONNECT'
        database = 'postgres'

Execute psql

psql -U postgres postgres -c "SELECT 1"
psql -U postgres test -c "SELECT 1"

Audit Log

LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 14:20:30 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 14:20:30 JST,postgres,postgres,[local],[unknown],2/1,0,00000,connection authorized: user=postgres database=postgres,,,,,
LOG:  connection authorized: user=postgres database=postgres
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 14:20:30 JST,postgres,postgres,[local],psql,,0,00000,disconnection: session time: 0:00:00.021 user=postgres database=postgres host=[local],,,,,
LOG:  disconnection: session time: 0:00:00.021 user=postgres database=postgres host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 14:20:30 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=test
LOG:  disconnection: session time: 0:00:00.008 user=postgres database=test host=[local]

The connection completion audit log for the "test" database is not output, but the connection trial audit log is output.

(refactored branche) When "log_parameter = on" is specified and SQL with multiple parameters is executed, parameters are concatenated.

Overview

When "log_parameter = on" is specified and SQL with multiple parameters is executed, parameters are concatenated.
In "advanced branch", a plurality of parameters were concatenated with spaces.
"refactored branch" is also, I think it is better to connect the spaces between the parameters.

config file sample

[output]
        logger = 'serverlog'
        level   = 'LOG'

[option]
        log_parameter = on

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_id,%command_parameter,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC'

Executed SQL

BEGIN;
PREPARE prep_dx AS SELECT * FROM pg_extension WHERE extname = $1 AND extversion = $2;
EXECUTE prep_dx ('plpgsql','1.0');
EXECUTE prep_dx ('hogehoge','2.0');
DEALLOCATE PREPARE prep_dx;
COMMIT;

Audit log

It will be concatenated like "plpgsql1.0" or "hogehoge 2.0".

LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  AUDIT: SESSION,1,1,MISC,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,BEGIN,,,BEGIN;,<none>
LOG:  AUDIT: SESSION,2,1,READ,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,PREPARE,,,PREPARE prep_dx AS SELECT * FROM pg_extension WHERE extname = $1 AND extversion = $2;,<none>
LOG:  AUDIT: SESSION,3,1,READ,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,SELECT,TABLE,pg_catalog.pg_extension,PREPARE prep_dx AS SELECT * FROM pg_extension WHERE extname = $1 AND extversion = $2;,,plpgsql1.0
LOG:  AUDIT: SESSION,3,2,MISC,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,EXECUTE,,,"EXECUTE prep_dx ('plpgsql','1.0');",<none>
LOG:  AUDIT: SESSION,4,1,READ,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,SELECT,TABLE,pg_catalog.pg_extension,PREPARE prep_dx AS SELECT * FROM pg_extension WHERE extname = $1 AND extversion = $2;,,hogehoge2.0
LOG:  AUDIT: SESSION,4,2,MISC,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,EXECUTE,,,"EXECUTE prep_dx ('hogehoge','2.0');",<none>
LOG:  AUDIT: SESSION,5,1,MISC,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,DEALLOCATE,,,DEALLOCATE PREPARE prep_dx;,<none>
LOG:  AUDIT: SESSION,6,1,MISC,2017-03-01 17:27:04 JST,postgres,postgres,[local],psql,2/3,0,,,COMMIT,,,COMMIT;,<none>
LOG:  disconnection: session time: 0:00:00.034 user=postgres database=postgres host=[local]

options -> option

To be consistent with other sections like output, rule, I think that options rule should be changed to option.
Thought?

(refactored branche) Question: Does the parameter name in the setting file allow only lowercase letters?

Overview

Does the parameter name in the setting file allow only lowercase letters?
In "advanced branch", parameter names allowed both uppercase and lowercase letters.
If you only accept lowercase letters in refactored version parameter names, you should note it in Readme.md.

Example: LOG_CATALOG

Config file sample

[output]
        logger = 'serverlog'
        level   = 'LOG'

[option]
        role = 'auditor'
        log_catalog = on
        LOG_CATALOG = off

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC'

PostgreSQL Launch log

$ pg_ctl -w -D ~/pgdata/9.6-pgaudit/ start
waiting for server to start....FATAL:  error invalid token "L" in "option section"
LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

(refactored branch) Even if you specify a object_type filter, not filtered.

Overview

Even if you specify a object_type filter, not filtered.
In Readme.md, object_type is described as an item that can be filter.

Config file

[output]
        logger = 'serverlog'

# SQL
#
[rule]
        class = 'READ,WRITE,DDL,MISC'
        object_type = 'TABLE,INDEX'

Executed SQL

-- create objects
CREATE TABLE foo (id int, data text);
CREATE INDEX foo_id_idx ON foo USING btree (id);
CREATE VIEW foo_v AS SELECT * FROM foo;
CREATE MATERIALIZED VIEW foo_mv AS SELECT * FROM foo;

INSERT INTO foo VALUES (1, 'aaa'),(2,'bbb');
REFRESH MATERIALIZED VIEW foo_mv;
REINDEX INDEX foo_id_idx;
REINDEX TABLE foo;
-- SELECT
TABLE foo;
TABLE foo_v;
TABLE foo_mv;

-- drp objects
DROP MATERIALIZED VIEW foo_mv;
DROP VIEW foo_v;
DROP INDEX foo_id_idx;
DROP TABLE foo;

Log.

LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role =
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 356
LOG:      BMP object_type = 3
LOG:  pgaudit extension initialized
LOG:  database system was shut down at 2017-03-07 15:35:51 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  AUDIT: SESSION,1,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/2,2374,,,CREATE TABLE,TABLE,public.foo,"CREATE TABLE foo (id int, data text);",<not logged>
LOG:  AUDIT: SESSION,2,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/3,2375,,,CREATE INDEX,INDEX,public.foo_id_idx,CREATE INDEX foo_id_idx ON foo USING btree (id);,<not logged>
LOG:  AUDIT: SESSION,3,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/4,2376,,,CREATE VIEW,VIEW,public.foo_v,CREATE VIEW foo_v AS SELECT * FROM foo;,<not logged>
LOG:  AUDIT: SESSION,4,1,READ,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/5,0,,,SELECT,TABLE,public.foo,CREATE MATERIALIZED VIEW foo_mv AS SELECT * FROM foo;,<not logged>
LOG:  AUDIT: SESSION,4,2,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/5,2377,,,CREATE MATERIALIZED VIEW,MATERIALIZED VIEW,public.foo_mv,CREATE MATERIALIZED VIEW foo_mv AS SELECT * FROM foo;,<not logged>
LOG:  AUDIT: SESSION,5,1,WRITE,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/6,0,,,INSERT,TABLE,public.foo,"INSERT INTO foo VALUES (1, 'aaa'),(2,'bbb');",<not logged>
LOG:  AUDIT: SESSION,6,1,MISC,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/7,2379,,,UNKNOWN,MATERIALIZED VIEW,public.foo_mv,REFRESH MATERIALIZED VIEW foo_mv;,<not logged>
LOG:  AUDIT: SESSION,6,1,MISC,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/7,2379,,,UNKNOWN,MATERIALIZED VIEW,public.foo_mv,REFRESH MATERIALIZED VIEW foo_mv;,<not logged>
LOG:  AUDIT: SESSION,6,1,READ,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/7,2379,,,SELECT,TABLE,public.foo,REFRESH MATERIALIZED VIEW foo_mv;,<not logged>
LOG:  AUDIT: SESSION,6,2,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/7,2379,,,REFRESH MATERIALIZED VIEW,MATERIALIZED VIEW,public.foo_mv,REFRESH MATERIALIZED VIEW foo_mv;,<not logged>
LOG:  AUDIT: SESSION,7,1,MISC,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/8,2380,,,REINDEX,,,REINDEX INDEX foo_id_idx;,<not logged>
LOG:  AUDIT: SESSION,8,1,MISC,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/9,2381,,,REINDEX,,,REINDEX TABLE foo;,<not logged>
LOG:  AUDIT: SESSION,9,1,READ,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/10,0,,,SELECT,TABLE,public.foo,TABLE foo;,<not logged>
LOG:  AUDIT: SESSION,10,1,MISC,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/11,0,,,UNKNOWN,VIEW,public.foo_v,TABLE foo_v;,<not logged>
LOG:  AUDIT: SESSION,10,1,READ,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/11,0,,,SELECT,TABLE,public.foo,TABLE foo_v;,<not logged>
LOG:  AUDIT: SESSION,11,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/13,2382,,,DROP MATERIALIZED VIEW,MATERIALIZED VIEW,public.foo_mv,DROP MATERIALIZED VIEW foo_mv;,<not logged>
LOG:  AUDIT: SESSION,12,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/14,2383,,,DROP VIEW,VIEW,public.foo_v,DROP VIEW foo_v;,<not logged>
LOG:  AUDIT: SESSION,13,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/15,2384,,,DROP INDEX,INDEX,public.foo_id_idx,DROP INDEX foo_id_idx;,<not logged>
LOG:  AUDIT: SESSION,14,1,DDL,2017-03-07 15:35:58 JST,postgres,postgres,[local],psql,2/16,2385,,,DROP TABLE,TABLE,public.foo,DROP TABLE foo;,<not logged>
LOG:  disconnection: session time: 0:00:00.096 user=postgres database=postgres host=[local]

Although object_type = 'TABLE, INDEX' is specified, other object_type (VIEW, MATERIALZED VIEW) is also output in the audit log.

(refactored branch) Proposal: Mode in which all superuser operations are output to the audit log.

Outline of the proposal

In the audit of PostgreSQL, it is important to logging the operation of the super user role (ex. "postgres").
For this reason, we propose adding a mode (log_superuser) to forcibly logging superuser operations in the option section of the configuration file.

Setting Example

[option]
  log_superuser = on

Parameter type is boolean.
The default value is on.

Behavior when log_superuser is on

All operations by roles with super user authority are subject to audit log output. Specifying the filter described in the rule section is invalidated.
For roles that are not superuser privileges, output the audit log according to the filter specification described in the rule section.

Behavior when log_superuser is off

Output the audit log according to the filter specification described in the rule section.

(refactored branch) Bug: An "SQLSTATE = 42809" error occurred when DROP TABLE was executed on a database incorporating an event trigger.

Summary

An "SQLSTATE = 42809" error occurred when DROP TABLE was executed on a database incorporating an event trigger.
DROP TABLE statement fails.
pgaudit (refactored branch) + occurred in PostgreSQL 9.6.2 environment.
This error does not occur in master branch.

Error log

$ psql -U postgres test
psql (9.6.2)
Type "help" for help.

test=# CREATE TABLE test (id int primary key, data text);
NOTICE:  AUDIT: SESSION,1,1,DDL,,test,[local],psql,,,,,CREATE TABLE,TABLE,public.test,"CREATE TABLE test (id int primary key, data text);",<not logged>
NOTICE:  AUDIT: SESSION,1,1,DDL,,test,[local],psql,,,,,CREATE INDEX,INDEX,public.test_pkey,"CREATE TABLE test (id int primary key, data text);",<not logged>
CREATE TABLE
test=# DROP TABLE test;
NOTICE:  AUDIT: SESSION,2,1,DDL,,test,[local],psql,,,,,DROP TABLE,TABLE,public.test,DROP TABLE test;,<not logged>
NOTICE:  AUDIT: SESSION,,,ERROR,,test,[local],psql,,,42809,invalid value "TABLE CONSTRAINT" for object_type,,,,,
ERROR:  invalid value "TABLE CONSTRAINT" for object_type

The location of the error occurrence is considered to be config.c line 183 (objecttype_to_bitmap function).
This error may be avoidable by adding a code to determine the object type "TABLE CONSTRAINT".

If an event trigger is not installed in the database, no error occurs.
(However, PK creation / deletion information will not be output as well)

$ psql -U postgres test
psql (9.6.2)
Type "help" for help.

test=# CREATE TABLE test (id int primary key, data text);
NOTICE:  AUDIT: SESSION,1,1,DDL,,test,[local],psql,,,,,CREATE TABLE,,,"CREATE TABLE test (id int primary key, data text);",<not logged>
CREATE TABLE
test=# DROP TABLE test;
NOTICE:  AUDIT: SESSION,2,1,DDL,,test,[local],psql,,,,,DROP TABLE,,,DROP TABLE test;,<not logged>
DROP TABLE

Reference: Behavior in master branch

In master branch, this error does not occur even if this event trigger is installed in the database.

$ psql -U postgres test
psql (9.6.2)
Type "help" for help.

test=# CREATE TABLE test (id int primary key, data text);
NOTICE:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test,"CREATE TABLE test (id int primary key, data text);",<not logged>
NOTICE:  AUDIT: SESSION,1,1,DDL,CREATE INDEX,INDEX,public.test_pkey,"CREATE TABLE test (id int primary key, data text);",<not logged>
CREATE TABLE
test=# DROP TABLE test;
NOTICE:  AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.test,DROP TABLE test;,<not logged>
NOTICE:  AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE CONSTRAINT,test_pkey on public.test,DROP TABLE test;,<not logged>
NOTICE:  AUDIT: SESSION,2,1,DDL,DROP TABLE,INDEX,public.test_pkey,DROP TABLE test;,<not logged>
DROP TABLE

unnecessary multiple log output.

pgaudit.conf

[output]
logger= 'serverlog'

[rule]
database = 'foo'
class = 'READ'

[rule]
database = 'baa'
class = 'READ'

then executed these commands.

$psql -d foo
foo=# select 1;

postgresql.log

LOG:  AUDIT: SESSION,1,1,READ,SELECT,,select 1;,<not logged>
LOG:  AUDIT: SESSION,1,1,READ,SELECT,,select 1;,<not logged>

I tried to add another one rule.
pgaudit.conf

[output]
logger= 'serverlog'

[rule]
database = 'foo'
class = 'READ'

[rule]
database = 'baa'
class = 'READ'

[rule]
database != 'yada'
class = 'READ'

$psql -d foo
foo=# select 1;

then triplet were logged.

LOG:  AUDIT: SESSION,1,1,READ,SELECT,,select 1;,<not logged>
LOG:  AUDIT: SESSION,1,1,READ,SELECT,,select 1;,<not logged>
LOG:  AUDIT: SESSION,1,1,READ,SELECT,,select 1;,<not logged>

(refactored branch) Pattern matching with '%' character is not supported.

Overview

Pattern matching with '%' character is not supported
In Readme.md '%' with the character, it has been described as a possible backward match or forward match.

In the value '%' can be used for backward match or forward match.

If pattern matching is not supported, it is necessary to modify Readme.md.

Config file

[output]
        logger = 'serverlog'

# SQL(1)
[rule]
        class = 'READ,WRITE'
        object_name = 'public.f%'

# SQL(2)
[rule]
        class = 'READ,WRITE'
        object_name = 'public.f%,b%,public.baz%'

# SQL(3)
[rule]
        class = 'READ,WRITE'
        object_name = 'ba%,public.baz%,public.fo%'

Executed SQL

-- create objects
CREATE TABLE IF NOT EXISTS foo(id int, data text);
CREATE TABLE IF NOT EXISTS bar(id int, data text);
BEGIN;
INSERT INTO foo VALUES (1, 'aaa');
INSERT INTO bar VALUES (1, 'xxx');
UPDATE foo SET data = 'AAA' WHERE id = 1;
UPDATE bar SET data = 'XXX' WHERE id = 1;
SELECT * FROM foo;
SELECT * FROM bar;
DELETE FROM foo;
DELETE FROM bar;
TRUNCATE foo;
TRUNCATE bar;
COMMIT;
DROP TABLE foo;
DROP TABLE bar;

Log.

LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  disconnection: session time: 0:00:00.060 user=postgres database=postgres host=[local]

Since no pattern match evaluation is performed, all audit logs are not output.

(refactored branche) Even if you specify WARNING as the level of the output section, the log level of the audit log is LOG instead of WARNING.

Overview

Even if you specify WARNING as the level of the output section, the log level of the audit log is LOG instead of WARNING.
Looking at the startup log, even when WARNING is specified, it is displayed as LOG: log_level = 15.
At present, is level setting unimplemented?

config file sample

[output]
        logger = 'serverlog'
        level   = 'WARNING'

[option]
        role = 'auditor'

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC'

PostgreSQL Launch Log

$ pg_ctl -w -D ~/pgdata/9.6-pgaudit/ start
waiting for server to start....LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role = auditor
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 500
LOG:  pgaudit extension initialized
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
 done
server started

Executed SQL

$ psql postgres -U postgres -c "SHOW log_min_messages"
 log_min_messages
------------------
 warning
(1 row)

Audit Log

LOG:  connection authorized: user=postgres database=postgres
LOG:  AUDIT: SESSION,1,1,MISC,2017-03-01 15:31:31 JST,postgres,postgres,[local],psql,2/17,0,,,SHOW,,,SHOW log_min_messages,<not logged>
LOG:  disconnection: session time: 0:00:00.012 user=postgres database=postgres host=[local]

(refactored branch) When log_connections or log_disconnections is turned off and reloaded, connection audit logs are no longer output.

Overview

When log_connections or log_disconnections is turned off and reloaded, connection audit logs are no longer output.
In the advanced branch, these parameters were forced to be on.
For this reason, even after reloading with the parameter set to off, the connection audit log was output.

We think that it is undesirable to be able to change the output of the audit log by reloading.

log

The log_connections and log_disconnections of postgresql.conf in the off, reload.

$ pg_ctl -w reload -D ~/pgdata/9.6-pgaudit/
server signaled
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "log_connections" changed to "off"
LOG:  parameter "log_disconnections" changed to "off"

Turn on log_connections and log_disconnections in postgresql.conf and reload.
The connection audit log is output again.

Even if you connect from the client (eg psql) in this state, the audit log is not output.

$ pg_ctl -w reload -D ~/pgdata/9.6-pgaudit/
server signaled
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "log_connections" changed to "on"
LOG:  parameter "log_disconnections" changed to "on"
LOG:  AUDIT: SESSION,,,CONNECT,2017-02-22 18:23:07 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-02-22 18:23:07 JST,postgres,postgres,[local],[unknown],2/24,0,00000,connection authorized: user=postgres database=postgres,,,,,
LOG:  connection authorized: user=postgres database=postgres
LOG:  AUDIT: SESSION,,,CONNECT,2017-02-22 18:23:11 JST,postgres,postgres,[local],psql,,0,00000,disconnection: session time: 0:00:03.129 user=postgres database=postgres host=[local],,,,,
LOG:  disconnection: session time: 0:00:03.129 user=postgres database=postgres host=[local]

(refactored branch) Setting file error does not occur even if the order of each section is changed.

Overview

Setting file error does not occur even if the order of each section is changed.
In Readme.md it is explained as follows.

The settings are divided into sections where you can set some parameters to control pgaudit. They are "output", "option" and "rule" sections, and should be arranged this order in the file.

However, in the current version, even if you write the option section before the output section, it will not result in an error.
Is it a bug on the program side or an error in the description in Readme.md?

config file sample

[option]
    role = 'auditor'

[output]
    logger = 'serverlog'

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_id,%command_text'
        class='READ,WRITE,DDL,ROLE,FUNCTION,MISC'

PostgreSQL launch log

$ more 04-6-02.log
$ pg_ctl -w start -D ~/pgdata/9.6-pgaudit/
waiting for server to start....LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role = auditor
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 500
LOG:  pgaudit extension initialized
}LOG:  database system was shut down at 2017-02-22 16:07:01 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  connection received: host=[local]
LOG:  connection authorized: user=nuko database=postgres
FATAL:  role "nuko" does not exist
 done
server started

(refactored branch) Defining multiple output sections does not cause an error.

Overview

Defining multiple output sections does not cause an error.
In Readme.md, it is described as follows.

At most one output-section can be written at the beginning of the file.

However, describing two output sections does not cause an error, and the server starts.
Is it a bug on the program side or an error in the description in Readme.md?

config file sample

[output]
        logger = 'serverlog'
        level = 'NOTICE'
[output]
        logger = 'serverlog'
        level = 'INFO'

# SQL
[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC'

PostgreSQL launch log

$ pg_ctl -w start -D ~/pgdata/9.6-pgaudit/
waiting for server to start....LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role =
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 500
LOG:  pgaudit extension initialized
}{}LOG:  database system was shut down at 2017-02-22 15:33:10 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  connection received: host=[local]
LOG:  connection authorized: user=nuko database=postgres
FATAL:  role "nuko" does not exist
 done
server started

(refactored branch) The audit log that is output when pg_basebackup is executed is "class = SYSTEM"

Overview

The audit log that is output when pg_basebackup is executed is "class = SYSTEM".
In Readme.md, in the case of pg_basebackup event it has been described as "class = BACKUP".
Document error or Program bug?

Audit Log

LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 11:35:54 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 11:35:54 JST,,postgres,[local],[unknown],2/1,0,00000,replication connection authorized: user=postgres,,,,,
LOG:  replication connection authorized: user=postgres
LOG:  received replication command: IDENTIFY_SYSTEM
LOG:  AUDIT: SESSION,,,SYSTEM,2017-03-07 11:35:54 JST,,postgres,[local],pg_basebackup,2/0,0,00000,received replication command: BASE_BACKUP LABEL 'pg_basebackup base backup'      ,,,,,
LOG:  received replication command: BASE_BACKUP LABEL 'pg_basebackup base backup'
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 11:35:58 JST,,postgres,[local],pg_basebackup,,0,00000,disconnection: session time: 0:00:04.813 user=postgres database= host=[local],,,,,
LOG:  disconnection: session time: 0:00:04.813 user=postgres database= host=[local]

The CLASS of the audit log containing the "received replication command: BASE_BACKUP LABEL 'pg_basebackup base backup'"message is SYSTEM instead of BACKUP.

(refactored branch) pid is not output to the audit log.

Overview

pid is not output to the audit log.
Instead, the transaction ID (not Virtual transaction ID) is output.
In the description of Readme.md, pid (corresponding to% p of log_line_ prefix) is output to the audit log, but the transaction ID (corresponding to% x of log_line_ prefix) is not output.

postgresql.conf

Set log_line_ prefix as follows. log_line_prefix = 'd=%d,p=%p,v=%v,x=%x '

pgaudit config file

[output]
        logger = 'serverlog'
        level   = 'LOG'

# CONNECT
[rule]
        format = '%d,%u,%p,%class,%connection_message'
        class = 'CONNECT'


# SQL
[rule]
        format = '%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,FUNCTION,MISC'

Execued SQL

CREATE TABLE foo (id int, data text);
CREATE FUNCTION foo_add(p1 int, p2 int) RETURNS integer AS $$
DECLARE
  ret int;
BEGIN
        ret := p1 + p2;
        return ret;
END;
$$ LANGUAGE plpgsql ;

BEGIN;
SELECT foo_add(10, 20);
INSERT INTO foo VALUES (1, 'aaa'),(2,'bbb');
TABLE foo;
UPDATE foo SET data = 'BBB' WHERE id = 2;
DELETE FROM foo WHERE id = 1;
SELECT * FROM foo;
TRUNCATE foo;
COMMIT;

DROP FUNCTION foo_add(p1 int, p2 int);
DROP TABLE foo;

Output audit log

d=[unknown],p=5520,v=,x=0 LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 10:56:11 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
d=[unknown],p=5520,v=,x=0 LOG:  connection received: host=[local]
d=postgres,p=5520,v=2/12,x=0 LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 10:56:11 JST,postgres,postgres,[local],[unknown],2/12,0,00000,connection authorized: user=postgres database=postgres,,,,,
d=postgres,p=5520,v=2/12,x=0 LOG:  connection authorized: user=postgres database=postgres
d=postgres,p=5520,v=2/13,x=2306 LOG:  AUDIT: SESSION,1,1,DDL,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/13,2306,,,CREATE TABLE,TABLE,public.foo,"CREATE TABLE foo (id int, data text);",<not logged>
d=postgres,p=5520,v=2/14,x=2307 LOG:  AUDIT: SESSION,2,1,DDL,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/14,2307,,,CREATE FUNCTION,FUNCTION,"public.foo_add(integer,integer)","CREATE FUNCTION foo_add(p1 int, p2 int) RETURNS integer AS $$
        DECLARE
          ret int;
        BEGIN
                ret := p1 + p2;
                return ret;
        END;
        $$ LANGUAGE plpgsql ;",<not logged>
d=postgres,p=5520,v=2/15,x=0 LOG:  AUDIT: SESSION,3,1,MISC,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,0,,,BEGIN,,,BEGIN;,<not logged>
d=postgres,p=5520,v=2/15,x=0 LOG:  AUDIT: SESSION,4,1,READ,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,0,,,SELECT,,,"SELECT foo_add(10, 20);",<not logged>
d=postgres,p=5520,v=2/15,x=0 LOG:  AUDIT: SESSION,4,2,FUNCTION,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,0,,,EXECUTE,FUNCTION,public.foo_add,"SELECT foo_add(10, 20);",<not logged>
d=postgres,p=5520,v=2/15,x=0 LOG:  AUDIT: SESSION,5,1,WRITE,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,0,,,INSERT,TABLE,public.foo,"INSERT INTO foo VALUES (1, 'aaa'),(2,'bbb');",<not logged>
d=postgres,p=5520,v=2/15,x=2308 LOG:  AUDIT: SESSION,6,1,READ,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,2308,,,SELECT,TABLE,public.foo,TABLE foo;,<not logged>
d=postgres,p=5520,v=2/15,x=2308 LOG:  AUDIT: SESSION,7,1,WRITE,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,2308,,,UPDATE,TABLE,public.foo,UPDATE foo SET data = 'BBB' WHERE id = 2;,<not logged>
d=postgres,p=5520,v=2/15,x=2308 LOG:  AUDIT: SESSION,8,1,WRITE,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,2308,,,DELETE,TABLE,public.foo,DELETE FROM foo WHERE id = 1;,<not logged>
d=postgres,p=5520,v=2/15,x=2308 LOG:  AUDIT: SESSION,9,1,READ,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,2308,,,SELECT,TABLE,public.foo,SELECT * FROM foo;,<not logged>
d=postgres,p=5520,v=2/15,x=2308 LOG:  AUDIT: SESSION,10,1,WRITE,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,2308,,,TRUNCATE TABLE,,,TRUNCATE foo;,<not logged>
d=postgres,p=5520,v=2/15,x=2308 LOG:  AUDIT: SESSION,11,1,MISC,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/15,2308,,,COMMIT,,,COMMIT;,<not logged>
d=postgres,p=5520,v=2/16,x=2309 LOG:  AUDIT: SESSION,12,1,DDL,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/16,2309,,,DROP FUNCTION,FUNCTION,"public.foo_add(integer,integer)","DROP FUNCTION foo_add(p1 int, p2 int);",<not logged>
d=postgres,p=5520,v=2/17,x=2310 LOG:  AUDIT: SESSION,13,1,DDL,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,2/17,2310,,,DROP TABLE,TABLE,public.foo,DROP TABLE foo;,<not logged>
d=postgres,p=5520,v=,x=0 LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 10:56:11 JST,postgres,postgres,[local],psql,,0,00000,disconnection: session time: 0:00:00.072 user=postgres database=postgres host=[local],,,,,
d=postgres,p=5520,v=,x=0 LOG:  disconnection: session time: 0:00:00.072 user=postgres database=postgres host=[local]

Related issues

SQLSTATE output and filtering

In the Advanced branch, the audit log of CLASS = ERROR is output unless it is "00" by judging the error class of SQLSTATE.

By adding the following functions, it is possible to output a specific error (eg permittion denied error) to the audit log.

Add output of SQLSTATE to format.
Make SQLSTATE judgeable with filter.

How is it?

Rewriting README.md

I felt that current README has some out of date information and that chapters seems to be broken. I've started to work on rewriting README.md here. Please give me feedback.

(refactored branch) Behavior when you specify an invalid timestamp literal

Overview

If an illegal timestamp literal is specified, it will not result in an error and the timestamp interval that can not hit (possibly) will be implied.
If an illegal timestamp literal is specified, either action is required.
- If an illegal timestamp literal is specified, abort the startup of the PostgreSQL server as an error. This is, to follow the behavior of the description error of other configuration files.
- Describe the current behavior in Readme.md.

Config file sample

[output]
        logger = 'serverlog'

# SQL(1)
[rule]
        class = 'READ'
        timestamp = 'invalid-format'

PostgreSQL launch log

$ pg_ctl -D ~/pgdata/9.6-pgaudit/ start
server starting
$ LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role =
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      TMS timestamp = -1777955776
LOG:      TMS timestamp = -1777955776
LOG:      BMP class = 64
LOG:  pgaudit extension initialized

If an incorrect timestamp literal is set, a value such as TMS timestamp = -1777955776 is implicitly set.

(refactored branch) Filters do not work correctly in the Japanese object name

Overview

Filters do not work correctly in the Japanese object name.

(Below, it is written in Japanese.)

概要

日本語オブジェクト名によるフィルタ機能が正しく動作しない。
たとえば、object_name = 'テストスキーマ.テストテーブル'といったフィルタをルールに記述して、そのテーブルにアクセスするSQL文を実行しても、監査ログに出力されない。

設定ファイル

[output]
        logger = 'serverlog'

[option]
        log_parameter = on

[rule]
        class = 'CONNECT'
        database = 'テストデータベース'

[rule]
        class = 'READ,WRITE,MISC'
        object_name = 'テストスキーマ.テストテーブル'

実行したSQL文

CREATE SCHEMA テストスキーマ;

CREATE TABLE テストスキーマ.テストテーブル ("識別子" integer, "データ" text);

PREPARE 準備文1(int, text) AS
  INSERT INTO テストスキーマ.テストテーブル VALUES ($1, $2);
EXECUTE 準備文1(1, 'あああ');

PREPARE 準備文2(int, text) AS
  SELECT * FROM テストスキーマ.テストテーブル WHERE "識別子" = $1 AND "データ" =  $2;
EXECUTE 準備文2(1, 'あああ');

PREPARE prep3(int, text) AS
  SELECT * FROM テストスキーマ.テストテーブル WHERE "識別子" = $1 AND "データ" =  $2;
EXECUTE prep3(1, 'あああ');

DROP TABLE テストスキーマ.テストテーブル;
DROP SCHEMA テストスキーマ;

SET ROLE フー;
SELECT 1;
RESET ROLE;

監査ログ

LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],[unknown],2/7,0,00000,connection authorized: user=postgres database=テストデータベース,,,,,
LOG:  connection authorized: user=postgres database=テストデータベース
LOG:  AUDIT: SESSION,1,1,MISC,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],psql,2/11,2492,,,EXECUTE,,,"EXECUTE 準備文1(1, 'あああ');",<none>
LOG:  AUDIT: SESSION,2,1,MISC,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],psql,2/13,0,,,EXECUTE,,,"EXECUTE 準備文2(1, 'あああ');",<none>
LOG:  AUDIT: SESSION,3,1,MISC,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],psql,2/15,0,,,EXECUTE,,,"EXECUTE prep3(1, 'あああ');",<none>
LOG:  AUDIT: SESSION,4,1,MISC,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],psql,2/18,0,,,SET,,,SET ROLE フー;,<none>
LOG:  AUDIT: SESSION,5,1,MISC,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],psql,2/20,0,,,RESET,,,RESET ROLE;,<none>
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,テストデータベース,postgres,[local],psql,,0,00000,disconnection: session time: 0:00:00.041 user=postgres database=テストデータベース host=[local],,,,,
LOG:  disconnection: session time: 0:00:00.041 user=postgres database=テストデータベース host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,テストデータベース,バー,[local],[unknown],2/21,0,00000,connection authorized: user=バー database=テストデータベース,,,,,
LOG:  connection authorized: user=バー database=テストデータベース
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,テストデータベース,バー,[local],psql,,0,00000,disconnection: session time: 0:00:00.004 user=バー database=テストデータベース host=[local],,,,,
LOG:  disconnection: session time: 0:00:00.004 user=バー database=テストデータベース host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:15 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  disconnection: session time: 0:00:00.134 user=postgres database=postgres host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:16 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  disconnection: session time: 0:00:00.007 user=postgres database=postgres host=[local]
LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:49:16 JST,,,[local],[unknown],,0,00000,connection received: host=[local],,,,,
LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
LOG:  disconnection: session time: 0:00:00.005 user=postgres database=postgres host=[local]

日本語データベース名によるフィルタリングは正しく動作している。
object_name = 'テストスキーマ.テストテーブル'のフィルタをはずすと、監査ログは出力される。

LOG:  AUDIT: SESSION,,,CONNECT,2017-03-07 20:58:33 JST,テストデータベース,postgres,[local],[unknown],2/7,0,00000,connection authorized: user=postgres database=テストデータベース,,,,,
LOG:  connection authorized: user=postgres database=テストデータベース
LOG:  AUDIT: SESSION,1,1,WRITE,2017-03-07 20:58:33 JST,テストデータベース,postgres,[local],psql,2/10,0,,,PREPARE,,,"PREPARE 準備文1(int, text) AS
          INSERT INTO テストスキーマ.テストテーブル VALUES ($1, $2);",<none>
LOG:  AUDIT: SESSION,2,1,WRITE,2017-03-07 20:58:33 JST,テストデータベース,postgres,[local],psql,2/11,0,,,INSERT,TABLE,"""テストスキーマ"".""テストテーブル""","PREPARE 準備文1(int, text) AS
          INSERT INTO テストスキーマ.テストテーブル VALUES ($1, $2);",1 あああ

このときに監査ログに出力されたobject_nameを見ると、"""テストスキーマ"".""テストテーブル"""のように展開されている。
しかし、設定ファイルのobject_nameに二重引用符を含めようとすると、設定ファイル誤りと判断されてしまう。
設定ファイル

[rule]
        class = 'READ,WRITE,MISC'
        object_name = '"テストスキーマ"."テストテーブル"'

起動時のエラーログ

FATAL:  invalid format parameter ""テストスキーマ"."テストテーブル"" of field "object_name" in rule section
LOG:  database system is shut down

(refactored branch) There is a problem with the explanation of Session Audit Logging / Configuration of Readme.md.

There are several problems in the description of Session Audit Logging / Configuration section in README.md of refactored branch.

In the explanation of README.md, it can be interpreted that it can be changed by format setting. However, in the current implementation the session audit log is a fixed form.
In the explanation of README.md, it can be interpreted that all audit logs are output unless the rule section is described. However, in the current implementation, no audit log is output.

In accordance with the implementation, it requires a modification of README.md.

Audit log of MISC class command is not displayed correctly in PostgreSQL development version. (refactored branch)

Hi.

I am trying pgaudit (refactored branch).
Audit log of MISC class command is not displayed correctly in PostgreSQL development version.

pgaudit configuration file.

[option]
log_level = 'NOTICE'
log_catalog = on
log_for_test = on

[rule]
        format = 'AUDIT: %class,%command_tag,%object_type,%object_name,%command_text'

Executed SQL commands.

CREATE TABLE foo (id int primary key, data text);
INSERt INTO foo VALUES (1, 'aaa'),(2, 'bbb'),(3,'ccc');

-- VACUUM, ANALYZE,CHECKPOINT
VACUUM foo;
ANALYZE foo;
CHECKPOINT;

-- FETCH
BEGIN;
DECLARE mycur CURSOR FOR SELECT * FROM foo;
FETCH FORWARD 2 FROM mycur;
FETCH PRIOR FROM mycur;
PREPARE myprep AS SELECT * FROM foo WHERE id = $1;
EXECUTE myprep(2);
END;

-- DISCARD
DISCARD ALL;

DROP TABLE foo;

Class of audit log output summary

The following SQL command is MISC class in 9.6, but it is READ class in devel.

VACUUM, ANALYZE, CHECKPOINT, BEGIN, FETCH, EXECUTE, COMMIT, DISCARD

Audit logs.

on 9.6.1

CREATE TABLE foo (id int primary key, data text);
psql:/tmp/sql_misc.sql:1: NOTICE:  AUDIT: SESSION,1,1,DDL,,testdb,[local],psql,,,,,CREATE TABLE,,,"CREATE TABLE foo (id int primary key, data text);",<not logged>
CREATE TABLE
INSERt INTO foo VALUES (1, 'aaa'),(2, 'bbb'),(3,'ccc');
psql:/tmp/sql_misc.sql:2: NOTICE:  AUDIT: SESSION,2,1,WRITE,,testdb,[local],psql,,,,,INSERT,TABLE,public.foo,"INSERt INTO foo VALUES (1, 'aaa'),(2, 'bbb'),(3,'ccc');",<not logged>
INSERT 0 3
VACUUM foo;
psql:/tmp/sql_misc.sql:5: NOTICE:  AUDIT: SESSION,3,1,MISC,,testdb,[local],psql,,,,,VACUUM,,,VACUUM foo;,<not logged>
VACUUM
ANALYZE foo;
psql:/tmp/sql_misc.sql:6: NOTICE:  AUDIT: SESSION,4,1,MISC,,testdb,[local],psql,,,,,ANALYZE,,,ANALYZE foo;,<not logged>
ANALYZE
CHECKPOINT;
psql:/tmp/sql_misc.sql:7: NOTICE:  AUDIT: SESSION,5,1,MISC,,testdb,[local],psql,,,,,CHECKPOINT,,,CHECKPOINT;,<not logged>
CHECKPOINT
BEGIN;
psql:/tmp/sql_misc.sql:10: NOTICE:  AUDIT: SESSION,6,1,MISC,,testdb,[local],psql,,,,,BEGIN,,,BEGIN;,<not logged>
BEGIN
DECLARE mycur CURSOR FOR SELECT * FROM foo;
psql:/tmp/sql_misc.sql:11: NOTICE:  AUDIT: SESSION,7,1,READ,,testdb,[local],psql,,,,,SELECT,TABLE,public.foo,DECLARE mycur CURSOR FOR SELECT * FROM foo;,<not logged>
psql:/tmp/sql_misc.sql:11: NOTICE:  AUDIT: SESSION,7,2,READ,,testdb,[local],psql,,,,,DECLARE CURSOR,,,DECLARE mycur CURSOR FOR SELECT * FROM foo;,<not logged>
DECLARE CURSOR
FETCH FORWARD 2 FROM mycur;
psql:/tmp/sql_misc.sql:12: NOTICE:  AUDIT: SESSION,8,1,MISC,,testdb,[local],psql,,,,,FETCH,,,FETCH FORWARD 2 FROM mycur;,<not logged>
 id | data
----+------
  1 | aaa
  2 | bbb
(2 rows)

FETCH PRIOR FROM mycur;
psql:/tmp/sql_misc.sql:13: NOTICE:  AUDIT: SESSION,9,1,MISC,,testdb,[local],psql,,,,,FETCH,,,FETCH PRIOR FROM mycur;,<not logged>
 id | data
----+------
  1 | aaa
(1 row)

PREPARE myprep AS SELECT * FROM foo WHERE id = $1;
psql:/tmp/sql_misc.sql:14: NOTICE:  AUDIT: SESSION,10,1,READ,,testdb,[local],psql,,,,,PREPARE,,,PREPARE myprep AS SELECT * FROM foo WHERE id = $1;,<not logged>
PREPARE
EXECUTE myprep(2);
psql:/tmp/sql_misc.sql:15: NOTICE:  AUDIT: SESSION,11,1,READ,,testdb,[local],psql,,,,,SELECT,TABLE,public.foo,PREPARE myprep AS SELECT * FROM foo WHERE id = $1;,<not logged>
psql:/tmp/sql_misc.sql:15: NOTICE:  AUDIT: SESSION,11,2,MISC,,testdb,[local],psql,,,,,EXECUTE,,,EXECUTE myprep(2);,<not logged>
 id | data
----+------
  2 | bbb
(1 row)

END;
psql:/tmp/sql_misc.sql:16: NOTICE:  AUDIT: SESSION,12,1,MISC,,testdb,[local],psql,,,,,COMMIT,,,END;,<not logged>
COMMIT
DISCARD ALL;
psql:/tmp/sql_misc.sql:19: NOTICE:  AUDIT: SESSION,13,1,MISC,,testdb,[local],psql,,,,,DISCARD ALL,,,DISCARD ALL;,<not logged>
DISCARD ALL
DROP TABLE foo;
psql:/tmp/sql_misc.sql:21: NOTICE:  AUDIT: SESSION,14,1,DDL,,testdb,[local],psql,,,,,DROP TABLE,,,DROP TABLE foo;,<not logged>
DROP TABLE

on devel

CREATE TABLE foo (id int primary key, data text);
psql:/tmp/sql_misc.sql:1: NOTICE:  AUDIT: SESSION,1,1,DDL,,testdb,[local],psql,,,,,CREATE TABLE,,,"CREATE TABLE foo (id int primary key, data text);",<not logged>
CREATE TABLE
INSERt INTO foo VALUES (1, 'aaa'),(2, 'bbb'),(3,'ccc');
psql:/tmp/sql_misc.sql:2: NOTICE:  AUDIT: SESSION,2,1,WRITE,,testdb,[local],psql,,,,,INSERT,TABLE,public.foo,"INSERt INTO foo VALUES (1, 'aaa'),(2, 'bbb'),(3,'ccc');",<not logged>
INSERT 0 3
VACUUM foo;
psql:/tmp/sql_misc.sql:5: NOTICE:  AUDIT: SESSION,3,1,READ,,testdb,[local],psql,,,,,VACUUM,,,VACUUM foo;,<not logged>
VACUUM
ANALYZE foo;
psql:/tmp/sql_misc.sql:6: NOTICE:  AUDIT: SESSION,4,1,READ,,testdb,[local],psql,,,,,ANALYZE,,,ANALYZE foo;,<not logged>
ANALYZE
CHECKPOINT;
psql:/tmp/sql_misc.sql:7: NOTICE:  AUDIT: SESSION,5,1,READ,,testdb,[local],psql,,,,,CHECKPOINT,,,CHECKPOINT;,<not logged>
CHECKPOINT
BEGIN;
psql:/tmp/sql_misc.sql:10: NOTICE:  AUDIT: SESSION,6,1,READ,,testdb,[local],psql,,,,,BEGIN,,,BEGIN;,<not logged>
BEGIN
DECLARE mycur CURSOR FOR SELECT * FROM foo;
psql:/tmp/sql_misc.sql:11: NOTICE:  AUDIT: SESSION,7,1,READ,,testdb,[local],psql,,,,,SELECT,TABLE,public.foo,DECLARE mycur CURSOR FOR SELECT * FROM foo;,<not logged>
psql:/tmp/sql_misc.sql:11: NOTICE:  AUDIT: SESSION,7,2,READ,,testdb,[local],psql,,,,,DECLARE CURSOR,,,DECLARE mycur CURSOR FOR SELECT * FROM foo;,<not logged>
DECLARE CURSOR
FETCH FORWARD 2 FROM mycur;
psql:/tmp/sql_misc.sql:12: NOTICE:  AUDIT: SESSION,8,1,READ,,testdb,[local],psql,,,,,FETCH,,,FETCH FORWARD 2 FROM mycur;,<not logged>
 id | data
----+------
  1 | aaa
  2 | bbb
(2 rows)

FETCH PRIOR FROM mycur;
psql:/tmp/sql_misc.sql:13: NOTICE:  AUDIT: SESSION,9,1,READ,,testdb,[local],psql,,,,,FETCH,,,FETCH PRIOR FROM mycur;,<not logged>
 id | data
----+------
  1 | aaa
(1 row)

PREPARE myprep AS SELECT * FROM foo WHERE id = $1;
psql:/tmp/sql_misc.sql:14: NOTICE:  AUDIT: SESSION,10,1,READ,,testdb,[local],psql,,,,,PREPARE,,,PREPARE myprep AS SELECT * FROM foo WHERE id = $1;,<not logged>
PREPARE
EXECUTE myprep(2);
psql:/tmp/sql_misc.sql:15: NOTICE:  AUDIT: SESSION,11,1,READ,,testdb,[local],psql,,,,,SELECT,TABLE,public.foo,PREPARE myprep AS SELECT * FROM foo WHERE id = $1;,<not logged>
psql:/tmp/sql_misc.sql:15: NOTICE:  AUDIT: SESSION,11,2,READ,,testdb,[local],psql,,,,,EXECUTE,,,EXECUTE myprep(2);,<not logged>
 id | data
----+------
  2 | bbb
(1 row)

END;
psql:/tmp/sql_misc.sql:16: NOTICE:  AUDIT: SESSION,12,1,READ,,testdb,[local],psql,,,,,COMMIT,,,END;,<not logged>
COMMIT
DISCARD ALL;
psql:/tmp/sql_misc.sql:19: NOTICE:  AUDIT: SESSION,13,1,READ,,testdb,[local],psql,,,,,DISCARD ALL,,,DISCARD ALL;,<not logged>
DISCARD ALL
DROP TABLE foo;
psql:/tmp/sql_misc.sql:21: NOTICE:  AUDIT: SESSION,14,1,DDL,,testdb,[local],psql,,,,,DROP TABLE,,,DROP TABLE foo;,<not logged>
DROP TABLE

(refactore branch) Update Copyright

Need to update copyright in each file.

(refactored branche) Parameters written in uppercase letters are ignored.

Over view

"Commit id = 7449e84" Fix (See. #35 ), Even if parameters are described in uppercase letters, it does not become a configuration file error.
However, when describing the parameter in uppercase letters, the parameter is ignored.

lowe case parameter

Config file

[output]
        logger = 'serverlog'
        level   = 'LOG'

[option]
        role = 'auditor'
        log_catalog = off

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC'

PostgreSQL Launch log.

log_catalog = 0, Correct.

LOG:  log_catalog = 0
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role = auditor
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 500
LOG:  pgaudit extension initialized

Upper case parameter

Config file

[output]
        logger = 'serverlog'
        level   = 'LOG'

[option]
        role = 'auditor'
        LOG_CATALOG = off

[rule]
        format = '%t,%d,%u,%p,%v,%statement_id,%sub_statement_id,%class,%command_tag,%object_type,%object_name,%command_text'
        class = 'READ,WRITE,DDL,ROLE,FUNCTION,MISC'

PostgreSQL Launch log.

log_catalog = 1, Incorrect.

LOG:  log_catalog = 1
LOG:  log_level_string = (null)
LOG:  log_level = 15
LOG:  log_parameter = 0
LOG:  log_statement_once = 0
LOG:  log_for_test = 0
LOG:  role = auditor
LOG:  logger = serverlog
LOG:  facility = (null)
LOG:  priority = (null)
LOG:  ident = (null)
LOG:  option = (null)
LOG:  pathlog = (null)
LOG:  Rule 0
LOG:      BMP class = 500
LOG:  pgaudit extension initialized