PostgreSQL 之 日志审计（pg_log）

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗

审计是指记录用户的登陆退出以及登陆后在数据库里的行为操作。
Postgres 的日志（pg_log）功能十分丰富，接下来首先会介绍它的标准日志审计功能
 
设置
highgo=# show logging_collector;  --是否开启日志收集，默认off
 logging_collector -------------------
 on
(1 row)
 
highgo=# show log_destination;  --日志记录类型，默认是stderr，只记录错误输出
 log_destination -----------------
 stderr
(1 row)
 
highgo=# show log_directory;   --日志路径，默认是$PGDATA/pg_log
 log_directory ---------------
 pg_log
(1 row)
 
highgo=# show log_filename;  -日志名称，默认是postgresql-%Y-%m-%d_%H%M%S.log
          log_filename          --------------------------------
 postgresql-%Y-%m-%d_%H%M%S.log
(1 row)
 
highgo=# show log_connections;  --用户session登陆时是否写入日志，默认off
 log_connections -----------------
 off
(1 row)
 
highgo=# show log_disconnections;  --用户session退出时是否写入日志，默认off
 log_disconnections --------------------
 off
(1 row)
 
highgo=# show log_rotation_age;  --保留单个文件的最大时长,默认是1d,也有1h,1min,1s
 log_rotation_age ------------------
 1d
(1 row)
 
highgo=# show log_rotation_size;  --保留单个文件的最大尺寸，默认是10MB
 log_rotation_size -------------------
 10MB
(1 row)
 
highgo=# show log_statement;  --记录用户登陆数据库后的各种操作
 log_statement ---------------
 none
(1 row)
#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
#log_statement = 'none'                 # none, ddl, mod, all
 
log_statement参数值：
none，即不记录
ddl(记录create,drop和alter)
mod(记录ddl+insert,delete,update和truncate)
all(mod+select)
 
实例
SQL:
highgo=# set log_statement = 'all';
SET
highgo=# show log_statement;
 log_statement ---------------
 all
(1 row)
 
 highgo=#create table account                                              
 (                                                                 
     id int,                                                       
     name text,                                                    
     password text,                                                
     description text                                              
 );                                                    
                                                                   
 highgo=#insert into account (id, name, password, description) values (1, 'user1', 'HASH1', 'blah, blah');
                                                                   
 highgo=#select * from account;
 
 id | name  | password | description
----+-------+----------+-------------
  1 | user1 | HASH1    | blah, blah
(1 row)
 
highgo=# do language plpgsql $$declarebeginfor i in 1..5 loopexecute 'create table account_'||i||' (id int)';end loop;end;
$$;DO
highgo=# \d
              List of relations
     Schema     |   Name    | Type  | Owner  ----------------+-----------+-------+--------
 oracle_catalog | dual      | view  | highgo
 public         | account   | table | highgo
 public         | account_1 | table | highgo
 public         | account_2 | table | highgo
 public         | account_3 | table | highgo
 public         | account_4 | table | highgo
 public         | account_5 | table | highgo
(7 rows)
 
highgo=#
Log Output：
[highgo@localhost pg_log]$ cat postgresql-2017-08-12_111312.log
LOG:  00000: database system was shut down at 2017-08-12 11:13:11 PDT
LOG:  00000: MultiXact member wraparound protections are now enabled
LOG:  00000: database system is ready to accept connections
LOG:  00000: autovacuum launcher started
LOG:  00000: statement: show log_statement;
LOG:  00000: statement: create table account
(
    id int,
    name text,
    password text,
    description text
);
LOG:  00000: statement: insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');
LOG:  00000: statement: select *
    from account;
LOG:  00000: statement: do language plpgsql $$
declare
begin
for i in 1..5 loop
execute 'create table account_'||i||' (id int)';
end loop;
end;
$$;
LOG:  00000: statement: SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'P' THEN 'partitioned table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'P','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;