PostgreSQL 日志审计

 PostgreSQL 日志审计

摘要

审计是指记录用户的登陆退出以及登陆后在数据库里的行为操作。

Postgres 的日志(pg_log)功能十分丰富,接下来首先会介绍它的标准日志审计功能,之后还会详细介绍 postgresql审计扩展(PgAudit) ,编译安装以及使用。

设置

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参数值:

  1. none,即不记录
  2. ddl(记录create,drop和alter)
  3. mod(记录ddl+insert,delete,update和truncate)
  4. all(mod+select)

实例

SQL:

highgo=# set log_statement = 'all';
SET
highgo=# show log_statement;
 log_statement 
---------------
 all
(1 row)

highgo=# create table account
highgo-# (
highgo(#     id int,
highgo(#     name text,
highgo(#     password text,
highgo(#     description text
highgo(# );
CREATE TABLE
highgo=# 
highgo=# insert into account (id, name, password, description)
highgo-#              values (1, 'user1', 'HASH1', 'blah, blah');
INSERT 0 1
highgo=# 
highgo=# select *
highgo-#     from account;
 id | name  | password | description 
----+-------+----------+-------------
  1 | user1 | HASH1    | blah, blah
(1 row)

highgo=# do language plpgsql $$
declare
begin
for i in 1..5 loop
execute '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;

为什么是pgAudit?

使用 log_statement=all 提供基本语句日志记录。这是可接受的监测,但没有提供一般要求的审计所需的详细程度。标准日志设备显示了用户的要求,而pgAudit则关注数据库满足请求时所发生的情况。
例如,审计员可能希望验证在文档记录维护窗口中创建了一个特定表。

BEGIN

    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';

END $$;

标准日志将给您提供以下信息:

LOG:  statement: DO $$

BEGIN

    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';

END $$;

PGAudit对于相同的输入,它将在日志中生成该输出:

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$

BEGIN

    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';

END $$;"

AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

不仅记录了DO块,而且子结点2包含CREATE TABLE的全文,其语句类型,对象类型和全限定名称使搜索变得容易。

在记录SELECT和DML语句时,pgAudit可以被配置为语句中引用的每个关系记录一个单独的条目。不需要解析来查找与特定表相关联的所有语句。

PGAudit简介

postgresql审计扩展(或pgaudit)通过postgresql提供的标准日志工具提供详细的会话和/或对象审计日志。

完整的信息可参考:PGAudit 主页, PGAudit README 中文

PostgreSQL版本兼容性

pgAudit开发支持PostgreSQL 9.5或更高版本。
pgAudit版本与postgresql主要版本有关,如下:

  • pgAudit v1.1.X是用于支持postgresql 9.6的。
  • pgAudit v1.0.X是用于支持postgresql 9.5的。

下载地址:Download

编译与安装

# 获取源码

tar zxvf postgresql-9.5.7.tar.gz
tar zxvf pgaudit-1.0.6.tar.gz -C postgresql-9.5.7/contrib/

# 编译安装PG

cd postgresql-9.5.7
./configure
make install -s

# 重命名,否则后面回归测试报错

cd contrib
mv pgaudit-1.0.6/ pgaudit

cd pgaudit

# 回归测试

make -s check

# 编译安装PGAudit

make install

设置

修改配置文件

vim $PGDATA/postgres.conf

shared_preload_libraries = 'pgaudit'

create extension xxx;

[postgres@localhost bin]$ ./psql 
psql (9.5.7)
Type "help" for help.

postgres=# create extension pg_audit;
ERROR:  could not open extension control file "/opt/develop/db/1/share/postgresql/extension/pg_audit.control": No such file or directory
STATEMENT:  create extension pg_audit;
ERROR:  could not open extension control file "/opt/develop/db/1/share/postgresql/extension/pg_audit.control": No such file or directory
postgres=# create extension pgaudit;
CREATE EXTENSION
postgres=# 

注意:pgaudit 不是 pg_audit。

使用

赞 (0) 评论 分享 ()