ossc-db / pgaudit Goto Github PK
View Code? Open in Web Editor NEWThis project forked from pgaudit/pgaudit
PostgreSQL Audit Extension
License: Other
This project forked from pgaudit/pgaudit
PostgreSQL Audit Extension
License: Other
[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'
$ 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.
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?
[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'
$ 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
[output]
logger = 'serverlog'
# SQL(1)
[rule]
class = 'READ'
timestamp = 'invalid-format'
$ 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
TMS timestamp = -1777955776
is implicitly set.[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'
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
[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'
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
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?
[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'
$ 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
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.
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]
[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'
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;
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]
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.
[option]
log_superuser = on
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.
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]
LOG: log_level = 15
.[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'
$ 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
$ psql postgres -U postgres -c "SHOW log_min_messages"
log_min_messages
------------------
warning
(1 row)
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]
While testing I noticed that pgaudit emits a UNKNOWN type log when reading view. This behavior seems same as what advanced
branch doing.
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;
refactored
branchNOTICE: 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.
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>
To be consistent with other sections like output
, rule
, I think that options
rule should be changed to option
.
Thought?
Hi.
I am trying pgaudit (refactored branch) and PostgreSQL 9.6.1.
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.
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.
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.
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".
[output]
logger = 'serverlog'
# CONNECT(1)
[rule]
class = 'CONNECT'
database = 'postgres'
psql -U postgres postgres -c "SELECT 1"
psql -U postgres test -c "SELECT 1"
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]
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
(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 = 'テストスキーマ.テストテーブル'
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
[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
}
$ 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
[output]
logger = 'serverlog'
# SQL
#
[rule]
class = 'READ,WRITE,DDL,MISC'
object_type = 'TABLE,INDEX'
-- 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_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]
log_line_prefix = 'd=%d,p=%p,v=%v,x=%x '
[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'
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;
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]
Hi.
I am trying pgaudit (refactored branch).
Audit log of MISC class command is not displayed correctly in PostgreSQL development version.
[option]
log_level = 'NOTICE'
log_catalog = on
log_for_test = on
[rule]
format = 'AUDIT: %class,%command_tag,%object_type,%object_name,%command_text'
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;
The following SQL command is MISC class in 9.6, but it is READ class in 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,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
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
$ 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
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
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
pgaudit setting file minor problem of open error message.
There is no parenthesis to the left of the path name in the error message.
$ 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.
$ 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.
[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'
$ 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
$
$ 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)
[output]
logger = 'serverlog'
# SQL(1)
[rule]
command_tag = 'SELECT'
class = 'READ,WRITE,DDL,MISC'
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_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]
There are several problems in the description of Session Audit Logging / Configuration section in README.md of refactored branch.
In accordance with the implementation, it requires a modification of README.md.
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.
How is it?
In the value '%' can be used for backward match or forward match.
[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%'
-- 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: 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]
Need to update copyright in each file.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.