pg-数据库审计

古城微笑少年丶 2023-02-11 07:20 12阅读 0赞

## 审计的意义 ##

审计的一个目的就是记录用户在数据库中都做了什么？在出现误操作或者业务数据非常规修改的时候可以有迹可查。

## 审计的范围 ##

*  用户的登录情况
 *  DDL操作
 *  DML操作

## 审计操作的要求 ##

*  能够通过参数配置或者权限回收来关闭
 *  对核心表不能产生压力
 *  能够方便的检索审计记录
 *  能够自定义审计粒度和范围

## 审计的工具 ##

### log中记录操作 ###

通过在不同的级别设置 log\_statement=‘all’，来将所有的操作都记录在log中，达到审计的目的。

设置 log\_statement=‘all’

alter database testdb set log_statement='all';

在数据库执行语句,查看log内容：

user01@testdb:5432 > select now();
                  now              
    -------------------------------
     2016-08-22 15:59:32.006481+08
    (1 row)
    2016-08-22 15:59:32.006 CST,"user01","testdb",17245,"[local]",57bab0d8.435d,1,"idle",2016-08-22 15:59:20 CST,2/722,0,LOG,00000,"statement: select now();",,,,,,,,,"psql"
    user01@testdb:5432 > insert into test_vac values (1);
    INSERT 0 1
    2016-08-22 16:00:31.049 CST,"user01","testdb",17245,"[local]",57bab0d8.435d,13,"idle",2016-08-22 15:59:20 CST,2/734,0,LOG,00000,"statement: insert into test_vac values (1);",,,,,,,,,"psql"
    user01@testdb:5432 > delete from test_vac where id=1;
    DELETE 2
    2016-08-22 16:01:07.550 CST,"user01","testdb",17245,"[local]",57bab0d8.435d,14,"idle",2016-08-22 15:59:20 CST,2/735,0,LOG,00000,"statement: delete from test_vac where id=1;",,,,,,,,,"psql"
    user01@testdb:5432 > grant select on test to public;
    GRANT
    2016-08-22 16:01:40.582 CST,"user01","testdb",17245,"[local]",57bab0d8.435d,15,"idle",2016-08-22 15:59:20 CST,2/736,0,LOG,00000,"statement: grant select on test to public;",,,,,,,,,"psql"
    user01@testdb:5432 > drop table test_audit;
    DROP TABLE
    2016-08-22 16:02:23.232 CST,"user01","testdb",17245,"[local]",57bab0d8.435d,18,"idle",2016-08-22 15:59:20 CST,2/741,0,LOG,00000,"statement: drop table test_audit;",,,,,,,,,"psql"

同样，可以在role级别和role on database级别设置log\_statement参数：

alter role user01 in database testdb set log_statement='all';
    alter role user01 set log_statement='all';

清除配置：

alter database testdb reset log_statement;
    alter role user01 set log_statement to default;
    alter role user01 in database testdb reset log_statement;

注意事项

*  superuser可以修改log\_statement配置项，所以一般审计普通用户。
 *  在审计语句打开之后，log的日志量会比较大，建议log\_directory参数最好不要跟数据文件的目录放在一起，且放在一个比较大的目录中，目录的权限需要给启动postgres的写权限。

限制

*  log\_statement的审计颗粒比较大，只到了用户和数据库的层面。
 *  session连接的是哪个数据库，相关的信息会写在对应数据库的log中，在单节点PG中可行。对于分布式架构来说，日志内容会分散在各个节点上，无法判断某个操作是在哪个节点上执行的。所以，该方法并不适用于PG的分布式架构。

### 表上触发器&&hstore ###

测试

利用触发器可以对表中的记录进行跟踪，跟踪信息包括新老记录，记录变更时间，session的连接信息等，  
使用hstore类型来存放新老记录的row信息。  
创建触发器测试表：

CREATE TABLE test_trigger (id int primary key, info text, crt_time timestamp(0));

创建hstore扩展：

CREATE EXTENSION hstore;

创建记录跟踪信息的记录表：

CREATE TABLE table_change_rec (
    id serial8 primary key,
    relid oid,
    table_schema text,
    table_name text,
    when_tg text,
    level text,
    op text,
    old_rec hstore,
    new_rec hstore,
    crt_time timestamp without time zone DEFAULT now(),
    username text,
    client_addr inet,
    client_port int
    );

创建触发器函数：

CREATE OR REPLACE FUNCTION dml_trace()
    RETURNS trigger
    LANGUAGE plpgsql
    AS $BODY$
    DECLARE
    v_new_rec hstore;
    v_old_rec hstore;
    v_username text := session_user;
    v_client_addr inet := inet_client_addr();
    v_client_port int := inet_client_port();
    BEGIN
    case TG_OP
    when 'DELETE' then 
      v_old_rec := hstore(OLD.*);
      insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, username, client_addr, client_port)
        values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_username, v_client_addr, v_client_port);
    when 'INSERT' then 
      v_new_rec := hstore(NEW.*);
      insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, new_rec, username, client_addr, client_port)
        values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_new_rec, v_username, v_client_addr, v_client_port);
    when 'UPDATE' then 
      v_old_rec := hstore(OLD.*);
      v_new_rec := hstore(NEW.*);
      insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, new_rec, username, client_addr, client_port)
        values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_new_rec, v_username, v_client_addr, v_client_port);
    else
      return null;
    end case;
      RETURN null;
    END;
    $BODY$ strict;

在表上创建增删改的触发器：

CREATE TRIGGER trigger_test AFTER DELETE or INSERT or UPDATE ON test_trigger FOR EACH ROW EXECUTE PROCEDURE dml_trace();

在表上执行增删改操作：

user01@testdb:5432 > insert into test_trigger values (1,'test',now());
    INSERT 0 1
    user01@testdb:5432 > update test_trigger set info='test trigger' where id=1;
    UPDATE 1
    user01@testdb:5432 > delete from test_trigger where id =1;
    DELETE 1

查询跟踪记录表的信息：

限制

*  触发器虽然审计粒度比较小，但是性能消耗很大，对于频繁DML的系统并不是很合适。

### event trigger ###

介绍

PG9.3版本引入了事件触发器，根据文档的描述，事件触发器是全局的来捕获DDL操作。

> event triggers are global to a particular database and are capable of capturing DDL events.

event trigger函数可以使用C、plpgsql等语言编写，但不能使用SQL语言函数。  
创建event trigger 的帮助：

Command:     CREATE EVENT TRIGGER
    Description: define a new event trigger
    Syntax:
    CREATE EVENT TRIGGER name
      ON event
      [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
      EXECUTE PROCEDURE function_name()

触发事件中不包括对共享系统对象的操作，同时对事件自身的DDL不会触发事件触发器。  
目前支持三种event：  
ddl\_command\_start:在create,alter,drop,security label,comment,grant,revoke,select into 命令执行前被触发。注意没有truncate命令，truncate可以使用普通触发器来触发。  
ddl\_command\_end:在上述命令执行后，事务提交之前触发  
sql\_drop:在drop的ddl\_command\_end触发之前触发。  
table\_rewrite(9.5新增):在alter table，alter type命令执行前触发。  
如果event trigger函数执行失败，DDL事务将回滚。

测试：审计数据库的drop命令

要审计drop命令，需要创建sql\_drop事件触发器，记录数据库中drop命令的历史记录。  
创建drop命令记录表：

postgres@testdb:5432 # create table tbl_ddl_drop_log (
    testdb(#  login_role      text,
    testdb(#  db_name         character varying(64),
    testdb(#  client_ip       inet,
    testdb(#  ddl_type    character varying(32),
    testdb(#  schema_name     text,
    testdb(#  object_type     text,
    testdb(#  object_name     text,
    testdb(#  object_identity text,
    testdb(#  drop_time       timestamp with time zone
    testdb(# );
    CREATE TABLE

将tbl\_ddl\_drop\_log的insert权限赋给应用用户user01：

postgres@testdb:5432 # grant insert on tbl_ddl_drop_log to user01;
    GRANT

创建sql\_drop触发器函数：

postgres@testdb:5432 # CREATE OR REPLACE FUNCTION fun_log_drop_command() 
    testdb-# RETURNS event_trigger LANGUAGE plpgsql  AS $$
    testdb$# DECLARE
    testdb$#     obj record;
    testdb$# BEGIN
    testdb$#     FOR obj IN (SELECT * FROM pg_event_trigger_dropped_objects() t where t.object_type 
    testdb$#         in ('table','sequence','index','function','view'))
    testdb$#     LOOP
    testdb$#         insert into tbl_ddl_drop_log (
    testdb$#            login_role,
    testdb$#            db_name,      
    testdb$#            client_ip,
    testdb$#            ddl_type,
    testdb$#            schema_name,    
    testdb$#            object_type,   
    testdb$#            object_name,   
    testdb$#            object_identity,
    testdb$#            drop_time)
    testdb$#          values (
    testdb$#            current_user,
    testdb$#            current_database(),
    testdb$#            inet_client_addr(),
    testdb$#            tg_tag,
    testdb$#            obj.schema_name,
    testdb$#            obj.object_type,
    testdb$#            obj.object_name,
    testdb$#            obj.object_identity,
    testdb$#            now());                                 
    testdb$#     END LOOP;
    testdb$# END;         
    testdb$# $$;
    CREATE FUNCTION

事件触发器函数的返回类型必须为event\_trigger，用于事件触发器调用。  
函数pg\_event\_trigger\_dropped\_objects()返回被删除的数据库对象，只能在sql\_drop的事件触发器函数中调用：

postgres@testdb:5432 # select * from pg_event_trigger_dropped_objects();
    ERROR:  pg_event_trigger_dropped_objects() can only be called in a sql_drop event trigger function

创建触发器：

postgres@testdb:5432 # CREATE EVENT TRIGGER trg_log_drop_command ON sql_drop  EXECUTE PROCEDURE fun_log_drop_command();
    CREATE EVENT TRIGGER

在应用用户下创建测试对象并删除：

id列上的index 和sequence 都被drop table命令级联删除。  
应用用户只有insert 记录drop命令记录表的权限，保证了记录不被应用用户修改。  
事件触发器函数执行失败，DDL事务将回滚：

postgres@testdb:5432 # \c testdb user02
    You are now connected to database "testdb" as user "user02".
    user02@testdb:5432 > create sequence test_drop;
    CREATE SEQUENCE
    user02@testdb:5432 > drop sequence test_drop;
    ERROR:  permission denied for relation tbl_ddl_drop_log
    CONTEXT:  SQL statement "insert into tbl_ddl_drop_log (
               login_role,
               db_name,      
               client_ip,
               ddl_type,
               schema_name,    
               object_type,   
               object_name,   
               object_identity,
               drop_time)
             values (
               current_user,
               current_database(),
               inet_client_addr(),
               tg_tag,
               obj.schema_name,
               obj.object_type,
               obj.object_name,
               obj.object_identity,
               now())"
    PL/pgSQL function fun_log_drop_command() line 8 at SQL statement
    user02@testdb:5432 > 
    user02@testdb:5432 > \ds
               List of relations
     Schema |   Name    |   Type   | Owner  
    --------+-----------+----------+--------
     user02 | test_drop | sequence | user02
    (1 row)

限制

*  仅能记录DDL操作，且不包括truncate操作。

### pg\_audit ###

项目主页：  
[https://github.com/pgaudit/pgaudit][https_github.com_pgaudit_pgaudit]  
基于PG9.5版本开发，9.5以下版本使用下面的项目：  
[https://github.com/2ndQuadrant/pgaudit][https_github.com_2ndQuadrant_pgaudit]  
通过配置参数来审计不同类别的操作：

> Specifies which classes of statements will be logged by session audit logging. Possible values are:  
> READ: SELECT and COPY when the source is a relation or a query.  
> WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.  
> FUNCTION: Function calls and DO blocks.  
> ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.  
> DDL: All DDL that is not included in the ROLE class.  
> MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM.

pg\_audit测试

优势1：审计嵌套sql  
目前参数如下：

shared_preload_libraries = 'pg_audit'
    pg_audit.log = 'function, ddl, write'
    pg_audit.log_parameter = on
    pg_audit.log_relation = on

DDL：

postgres@testdb:5532 # create table test_1(id int);
    CREATE TABLE
    2016-08-23 16:04:26.690 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,3,"CREATE TABLE",2016-08-23 15:41:24 CST,3/48,2382,LOG,00000,"AUDIT: SESSION,3,1,DDL,CREATE TABLE,,,create table test_1(id int);,<not logged>",,,,,,,,,"psql"
    
    postgres@testdb:5532 # drop table test_1;
    DROP TABLE
    2016-08-23 16:06:18.442 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,5,"DROP TABLE",2016-08-23 15:41:24 CST,3/50,2383,LOG,00000,"AUDIT: SESSION,4,1,DDL,DROP TABLE,,,drop table test_1;,<not logged>",,,,,,,,,"psql"

记录函数以及内部的DDL：

postgres@testdb:5532 # do language plpgsql $$
    testdb$# declare
    testdb$# begin
    testdb$#   for i in 1..100 loop
    testdb$#     execute 'create table test_'||i||' (id int)'; 
    testdb$#   end loop;
    testdb$# end;
    testdb$# $$;
    DO
    
    2016-08-23 16:05:37.478 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,4,"DO",2016-08-23 15:41:24 CST,3/49,0,ERROR,42P07,"relation ""test_1"" already exists",,,,,"SQL statement ""create table test_1 (id int)""
    PL/pgSQL function inline_code_block line 5 at EXECUTE","do language plpgsql $$
    declare
    begin
      for i in 1..100 loop
        execute 'create table test_'||i||' (id int)'; 
      end loop;
    end;
    $$;",,,"psql"
    2016-08-23 16:07:15.155 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,6,"DO",2016-08-23 15:41:24 CST,3/51,2384,LOG,00000,"AUDIT: SESSION,5,1,DDL,CREATE TABLE,,,create table test_1 (id int),<not logged>",,,,,,,,,"psql"
    2016-08-23 16:07:15.156 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,7,"DO",2016-08-23 15:41:24 CST,3/51,2384,LOG,00000,"AUDIT: SESSION,5,2,DDL,CREATE TABLE,,,create table test_2 (id int),<not logged>",,,,,,,,,"psql"
    2016-08-23 16:07:15.156 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,8,"DO",2016-08-23 15:41:24 CST,3/51,2384,LOG,00000,"AUDIT: SESSION,5,3,DDL,CREATE TABLE,,,create table test_3 (id int),<not logged>",,,,,,,,,"psql"
    .......
    2016-08-23 16:07:15.190 CST,"postgres","testdb",19468,"[local]",57bbfe24.4c0c,105,"DO",2016-08-23 15:41:24 CST,3/51,2384,LOG,00000,"AUDIT: SESSION,5,100,DDL,CREATE TABLE,,,create table test_100 (id int),<not logged>",,,,,,,,,"psql"

优势2：对象审计  
可以只在log中记录对某个对象的特定操作。需要一个额外的role，把需要审计的对象权限赋给这个role即可，甚至可以审计relation中字段上的操作。  
测试过程：

postgres@testdb:5532 # create role audit nologin;
    CREATE ROLE
    
    postgres@testdb:5532 # grant select (password)
    testdb-#    on public.account
    testdb-#    to audit;
    GRANT
    postgres@testdb:5532 # select id, name
    testdb-#   from account;
     id | name  
    ----+-------
      1 | user1
    (1 row)
    
    postgres@testdb:5532 # select password
    testdb-#   from account;
     password 
    ----------
     HASH1
    (1 row)
    
    2016-08-23 16:25:40.714 CST,"postgres","testdb",13622,"[local]",57bc085d.3536,2,"SELECT",2016-08-23 16:25:01 CST,3/68,0,LOG,00000,"AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,""select password
      from account;"",<not logged>",,,,,,,,,"psql"

可以看到在决定审计password字段上select操作后，select id和name的时候，没有记录log，select password的操作在log中出现。

限制

*  跟log\_statement有同样的输出位置，所以在分布式系统中不适用。

### 总结 ###

*  对于分布式架构，审计的内容最好记录在表中，方便检索。
 *  对于单节点PG，审计内容存放在logfile和表中均可以。
 *  需要根据不同的审计要求来选择不同的工具，并且在审计的粒度和数据库的性能之间权衡。

## 学习链接 ##

*  [PostgreSQL 9.5 new feature - pg\_audit module][PostgreSQL 9.5 new feature - pg_audit module]
 *  [PostgreSQL Audit by Database or Role wide parameter][]
 *  [Database audit@Wikipedia][Database audit_Wikipedia]
 *  [Quick guide to Auditing a (postgreSQL) Database][Quick guide to Auditing a _postgreSQL_ Database]
 *  [Auditing Data Modifications in Postgres][]
 *  [Audit trigger 91plus][]
 *  [Total security in a PostgreSQL database][]
 *  [pgaudit@github][https_github.com_pgaudit_pgaudit]
 *  [Event\_Triggers][Event_Triggers]
 *  [AuditLoggingforPostgreSQL][]

[https_github.com_pgaudit_pgaudit]: https://github.com/pgaudit/pgaudit
[https_github.com_2ndQuadrant_pgaudit]: https://github.com/2ndQuadrant/pgaudit
[PostgreSQL 9.5 new feature - pg_audit module]: http://ju.outofmemory.cn/entry/144473
[PostgreSQL Audit by Database or Role wide parameter]: http://blog.163.com/digoal@126/blog/static/16387704020132208241607/
[Database audit_Wikipedia]: https://en.wikipedia.org/wiki/Database_audit
[Quick guide to Auditing a _postgreSQL_ Database]: https://doublebyteblog.wordpress.com/2014/02/11/quick-guide-to-auditing-a-postgresql-database/
[Auditing Data Modifications in Postgres]: https://www.garysieling.com/blog/auditing-data-changes-postgres
[Audit trigger 91plus]: https://wiki.postgresql.org/wiki/Audit_trigger_91plus
[Total security in a PostgreSQL database]: http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html
[Event_Triggers]: https://wiki.postgresql.org/wiki/Event_Triggers
[AuditLoggingforPostgreSQL]: https://www.pgcon.org/2016/schedule/attachments/427_AuditLoggingforPostgreSQL.pdf