pg审计

最新推荐文章于 2024-04-24 17:59:56 发布

明珠天南

最新推荐文章于 2024-04-24 17:59:56 发布

阅读量276

点赞数

分类专栏：数据库文章标签： pg 审计

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

pg-数据库审计

转自 https://my.oschina.net/yafeishi/blog/742313

审计的意义

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

审计的范围

用户的登录情况
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

查询跟踪记录表的信息：

user01@testdb:5432 > select id,table_schema,table_name,op,(each(old_rec)).* from table_change_rec;
 id | table_schema |  table_name  |   op   |   key    |        value        
----+--------------+--------------+--------+----------+---------------------
  9 | user01       | test_trigger | UPDATE | id       | 1
  9 | user01       | test_trigger | UPDATE | info     | test
  9 | user01       | test_trigger | UPDATE | crt_time | 2016-08-22 17:03:20
 10 | user01       | test_trigger | DELETE | id       | 1
 10 | user01       | test_trigger | DELETE | info     | test trigger
 10 | user01       | test_trigger | DELETE | crt_time | 2016-08-22 17:03:20
(6 rows)

限制

触发器虽然审计粒度比较小，但是性能消耗很大，对于频繁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

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

postgres@postgres:5432 # \c testdb user01
You are now connected to database "testdb" as user "user01".
user01@testdb:5432 > 
user01@testdb:5432 > 
user01@testdb:5432 > create table test_drop ( id serial primary key);
CREATE TABLE
user01@testdb:5432 > drop table test_drop;
DROP TABLE
user01@testdb:5432 > select * from public.tbl_ddl_drop_log;
ERROR:  permission denied for relation tbl_ddl_drop_log
user01@testdb:5432 > \c testdb postgres
You are now connected to database "testdb" as user "postgres".
postgres@testdb:5432 # select * from public.tbl_ddl_drop_log;
 login_role | db_name | client_ip |  ddl_type  | schema_name | object_type |   object_name    |     object_identity     |           drop_time           
------------+---------+-----------+------------+-------------+-------------+------------------+-------------------------+-------------------------------
 user01     | testdb  |           | DROP TABLE | user01      | table       | test_drop        | user01.test_drop        | 2016-08-23 10:26:04.35931+08
 user01     | testdb  |           | DROP TABLE | user01      | index       | test_drop_pkey   | user01.test_drop_pkey   | 2016-08-23 10:26:04.35931+08
 user01     | testdb  |           | DROP TABLE | user01      | sequence    | test_drop_id_seq | user01.test_drop_id_seq | 2016-08-23 10:26:04.35931+08
(3 rows)

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
基于PG9.5版本开发，9.5以下版本使用下面的项目：
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和表中均可以。
需要根据不同的审计要求来选择不同的工具，并且在审计的粒度和数据库的性能之间权衡。

学习链接

明珠天南

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
pg审计

pg-数据库审计转自https://my.oschina.net/yafeishi/blog/742313审计的意义审计的范围审计操作的要求审计的工具 log中记录操作设置 log_statement=‘all’ 注意事项限制表上触发器&&hstore 测试限制 event trigger 介绍 ...
复制链接

扫一扫