search

ossc-db / pgaudit

PostgreSQL Audit Extension
Other
2 stars 4 forks source link

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

Open harada-toshi opened 7 years ago

harada-toshi commented 7 years ago

Overview

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]
MasahikoSawada commented 7 years ago

Yeah, actually object_name and object_type are used only when pgaudit processes LOG_READ and LOG_WRITE class statements. Because there is problem about this; in current design, log_select_dml identifies the target relation name. So when processing DDL or MISC command pgaudit cannot identify the relation name. In order to do that, we need to use the parsed tree and get the relation name out.

harada-toshi commented 7 years ago

I see.

I think that this behavior should be explained in Readme.md.

harada-toshi commented 7 years ago