PostgreSQL 审计 - pg_audit module
背景
以前写过一些PostgreSQL审计相关的文章，有些是通过系统自带的参数来配置审计功能的，还有是通过扩展模块来配置审计的，还有通过事件触发器，或者触发器来实现跟踪和审计的。如下：
1. PostgreSQL自带的日志审计，实现语句级别，数据库级别，用户级别的审计。
弊端是审计颗粒度太大。
http://blog.163.com/digoal@126/blog/static/16387704020132208241607/
通过触发器来审计表级别，行级别（带条件的），用户级别的数据库操作。
弊端是开销大。
http://blog.163.com/digoal@126/blog/static/16387704020132209854525/
通过事件触发器审计数据库或限制数据库的DDL操作。
弊端是目前只能审计DDL操作。
http://blog.163.com/digoal@126/blog/static/16387704020132131361949/
使用pg_log_userqueries模块审计用户级或数据库级的数据库操作。
弊端是颗粒度太大。
http://blog.163.com/digoal@126/blog/static/1638770402012019112218804/
使用hstore和触发器跟踪表级别的数据操作。
弊端是性能开销大。
http://blog.163.com/digoal@126/blog/static/163877040201252575529358/
使用hstore和触发器跟踪表级别的数据操作，并实现flashback query。
弊端是性能开销大，不适合频繁DML操作的表。
http://blog.163.com/digoal@126/blog/static/1638770402014728105442434/
修改数据库默认的命令级别，GetCommandLogLevel@src/backend/tcop/utility.c，定制日志输出。
弊端是需要修改源码。
http://blog.163.com/digoal@126/blog/static/163877040201421702248430/
这些审计功能可以满足大部分需求，需要非常细的审计，可以用触发器，需要颗粒度大的，性能损耗小的审计，用数据库自带的配置。但是我如果又要细的，又要性能损耗小的，目前还不支持。
包括今天我要说的这个9.5的插件，其实也是颗粒度较大的，审计没有细化到行级别，要做行级别的审计，还是需要用触发器。
今天要说的这个插件是通过钩子来实现审计的，
/*
* Install our hook functions after saving the existing pointers to preserve
* the chains.
*/
next_ExecutorStart_hook = ExecutorStart_hook;
ExecutorStart_hook = pg_audit_ExecutorStart_hook;
next_ExecutorCheckPerms_hook = ExecutorCheckPerms_hook;
ExecutorCheckPerms_hook = pg_audit_ExecutorCheckPerms_hook;
next_ProcessUtility_hook = ProcessUtility_hook;
ProcessUtility_hook = pg_audit_ProcessUtility_hook;
next_object_access_hook = object_access_hook;
object_access_hook = pg_audit_object_access_hook;
这几个钩子分别在这几处：
src/backend/executor/execMain.c
用来跟踪赋权和非DDL语句。
/* ----------------------------------------------------------------
* ExecutorStart
*
* This routine must be called at the beginning of any execution of any
* query plan
*
* Takes a QueryDesc previously created by CreateQueryDesc (which is separate
* only because some places use QueryDescs for utility commands). The tupDesc
* field of the QueryDesc is filled in to describe the tuples that will be
* returned, and the internal fields (estate and planstate) are set up.
*
* eflags contains flag bits as described in executor.h.
*
* NB: the CurrentMemoryContext when this is called will become the parent
* of the per-query context used for this Executor invocation.
*
* We provide a function hook variable that lets loadable plugins
* get control when ExecutorStart is called. Such a plugin would
* normally call standard_ExecutorStart().
*
* ----------------------------------------------------------------
*/
void
ExecutorStart(QueryDesc *queryDesc, int eflags)
{
if (ExecutorStart_hook)
(*ExecutorStart_hook) (queryDesc, eflags);
else
standard_ExecutorStart(queryDesc, eflags);
}
/*
* ExecCheckRTPerms
* Check access permissions for all relations listed in a range table.
*
* Returns true if permissions are adequate. Otherwise, throws an appropriate
* error if ereport_on_violation is true, or simply returns false otherwise.
*
* Note that this does NOT address row level security policies (aka: RLS). If
* rows will be returned to the user as a result of this permission check
* passing, then RLS also needs to be consulted (and check_enable_rls()).
*
* See rewrite/rowsecurity.c.
*/
bool
ExecCheckRTPerms(List *rangeTable, bool ereport_on_violation)
{
ListCell *l;
bool result = true;
foreach(l, rangeTable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);
result = ExecCheckRTEPerms(rte);
if (!result)
{
Assert(rte->rtekind == RTE_RELATION);
if (ereport_on_violation)
aclcheck_error(ACLCHECK_NO_PRIV, ACL_KIND_CLASS,
get_rel_name(rte->relid));
return false;
}
}
if (ExecutorCheckPerms_hook)
result = (*ExecutorCheckPerms_hook) (rangeTable,
ereport_on_violation);
return result;
}
src/backend/tcop/utility.c
用来跟踪DDL。
/*
* ProcessUtility
* general utility function invoker
*
* parsetree: the parse tree for the utility statement
* queryString: original source text of command
* context: identifies source of statement (toplevel client command,
* non-toplevel client command, subcommand of a larger utility command)
* params: parameters to use during execution
* dest: where to send results
* completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE
* in which to store a command completion status string.
*
* Notes: as of PG 8.4, caller MUST supply a queryString; it is not
* allowed anymore to pass NULL. (If you really don't have source text,
* you can pass a constant string, perhaps "(query not available)".)
*
* completionTag is only set nonempty if we want to return a nondefault status.
*
* completionTag may be NULL if caller doesn't want a status string.
*/
void
ProcessUtility(Node *parsetree,
const char *queryString,
ProcessUtilityContext context,
ParamListInfo params,
DestReceiver *dest,
char *completionTag)
{
Assert(queryString != NULL); /* required as of 8.4 */
/*
* We provide a function hook variable that lets loadable plugins get
* control when ProcessUtility is called. Such a plugin would normally
* call standard_ProcessUtility().
*/
if (ProcessUtility_hook)
(*ProcessUtility_hook) (parsetree, queryString,
context, params,
dest, completionTag);
else
standard_ProcessUtility(parsetree, queryString,
context, params,
dest, completionTag);
}
pg_audit支持的跟踪语句分类如下：
依旧没有将INSERT,UPDATE,DELETE,TRUNCATE分开。
但是实际上通过对象审计可以实现比这个更强大的审计，例如可以将颗粒度细化到对象的privilege级别，如select,update,insert,delete,truncate on table, execute on function, ….。
/* Bits within auditLogBitmap, defines the classes we understand */
#define LOG_DDL (1 << 0) /* CREATE/DROP/ALTER objects */
#define LOG_FUNCTION (1 << 1) /* Functions and DO blocks */
#define LOG_MISC (1 << 2) /* Statements not covered */
#define LOG_READ (1 << 3) /* SELECTs */
#define LOG_ROLE (1 << 4) /* GRANT/REVOKE, CREATE/ALTER/DROP ROLE */
#define LOG_WRITE (1 << 5) /* INSERT, UPDATE, DELETE, TRUNCATE */
#define LOG_NONE 0 /* nothing */
#define LOG_ALL (0xFFFFFFFF) /* All */
使用pg_audit模块的优势：
1. 可以记录嵌套级别的SQL，例如一个on line code, 或者函数调用，里面的SQL也可以被记录下来。
shared_preload_libraries = 'pg_audit'
pg_audit.log = 'function, ddl, write'
pg_audit.log_parameter = on
pg_audit.log_relation = on
记录DDL：
postgres=# create table tbl(id int);
CREATE TABLE
2015-05-15 15:11:17.463 CST,"postgres","postgres",5922,"[local]",55559bff.1722,8,"CREATE TABLE",2015-05-15 15:10:55 CST,2/5,1757,LOG,00000,"AUDIT: SESSION,2,1,DDL,CREATE TABLE,,,create table tbl(id int);,<none>",,,,,,"create table tbl(id int);",,"log_audit_event, pg_audit.c:686","psql"
记录FUNCTION，以及内部DDL。
postgres=# do language plpgsql $$
declare
begin
for i in 1..100 loop
execute 'create table test_'||i||' (id int)';
end loop;
end;
$$;
DO
2015-05-15 15:12:15.905 CST,"postgres","postgres",5922,"[local]",55559bff.1722,10,"DO",2015-05-15 15:10:55 CST,2/7,0,LOG,00000,"AUDIT: SESSION,3,1,FUNCTION,DO,,,""do language plpgsql $$
declare
begin
for i in 1..100 loop
execute 'create table test_'||i||' (id int)';
end loop;
end;
$$;"",<none>",,,,,,"do language plpgsql $$
declare
begin
for i in 1..100 loop
execute 'create table test_'||i||' (id int)';
end loop;
end;
$$;",,"log_audit_event, pg_audit.c:686","psql"
2015-05-15 15:12:15.907 CST,"postgres","postgres",5922,"[local]",55559bff.1722,11,"DO",2015-05-15 15:10:55 CST,2/7,1758,LOG,00000,"AUDIT: SESSION,3,2,DDL,CREATE TABLE,,,create table test_1 (id int),<none>",,,,,"SQL statement ""create table test_1 (id int)""
PL/pgSQL function inline_code_block line 5 at EXECUTE statement","do language plpgsql $$
declare
begin
for i in 1..100 loop
execute 'create table test_'||i||' (id int)';
end loop;
end;
$$;",,"log_audit_event, pg_audit.c:686","psql"
......
2015-05-15 15:12:15.949 CST,"postgres","postgres",5922,"[local]",55559bff.1722,110,"DO",2015-05-15 15:10:55 CST,2/7,1758,LOG,00000,"AUDIT: SESSION,3,101,DDL,CREATE TABLE,,,create table test_100 (id int),<none>",,,,,"SQL statement ""create table test_100 (id int)""
PL/pgSQL function inline_code_block line 5 at EXECUTE statement","do language plpgsql $$
declare
begin
for i in 1..100 loop
execute 'create table test_'||i||' (id int)';
end loop;
end;
$$;",,"log_audit_event, pg_audit.c:686","psql"
2. 支持对象审计，这种审计方法比较奇特，例如我要审计某个表的INSERT操作，那么首先在数据库中创建一个审计角色，把这个表的INSERT权限赋予给这个审计角色，那么任何人对这个表执行INSERT都会被审计。
/*
* GUC variable for pg_audit.role
*
* Administrators can choose which role to base OBJECT auditing off of.
* Object-level auditing uses the privileges which are granted to this role to
* determine if a statement should be logged.
*/
char *auditRole = NULL;
/*
* Object type, used for SELECT/DML statements and function calls.
*
* For relation objects, this is essentially relkind (though we do not have
* access to a function which will just return a string given a relkind;
* getRelationTypeDescription() comes close but is not public currently).
*
* We also handle functions, so it isn't quite as simple as just relkind.
*
* This should be kept consistent with what is returned from
* pg_event_trigger_ddl_commands(), as that's what we use for DDL.
*/
#define OBJECT_TYPE_TABLE "TABLE"
#define OBJECT_TYPE_INDEX "INDEX"
#define OBJECT_TYPE_SEQUENCE "SEQUENCE"
#define OBJECT_TYPE_TOASTVALUE "TOAST TABLE"
#define OBJECT_TYPE_VIEW "VIEW"
#define OBJECT_TYPE_MATVIEW "MATERIALIZED VIEW"
#define OBJECT_TYPE_COMPOSITE_TYPE "COMPOSITE TYPE"
#define OBJECT_TYPE_FOREIGN_TABLE "FOREIGN TABLE"
#define OBJECT_TYPE_FUNCTION "FUNCTION"
#define OBJECT_TYPE_UNKNOWN "UNKNOWN"
测试：
假设我这里需要使用的是审计角色digoal。
pg_audit.role = 'digoal'
创建一个审计角色，digoal，不需要登录权限。
postgres=# create role digoal nologin;
postgres=# create table t(id int);
CREATE TABLE
postgres=# delete from t;
DELETE 0
postgres=# grant select on t to digoal; -- 表示我要审计表t的select操作。
GRANT
postgres=# delete from t;
DELETE 0
postgres=# select * from t;
id
----
(0 rows)
查看日志，只有select操作被审计了，其他操作不会被审计。
2015-05-15 15:39:57.661 CST,"postgres","postgres",6572,"[local]",5555a299.19ac,4,"SELECT",2015-05-15 15:39:05 CST,2/7,0,LOG,00000,"AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.t,select * from t;,<none>",,,,,,"select * from t;",,"log_audit_event, pg_audit.c:686","psql"
通过对象审计，可以将PostgreSQL的审计功能提升一个档次。
参考
1. http://blog.163.com/digoal@126/blog/static/163877040201421702248430/
2. http://blog.163.com/digoal@126/blog/static/1638770402012019112218804/
3. http://blog.163.com/digoal@126/blog/static/163877040201252575529358/
4. http://blog.163.com/digoal@126/blog/static/16387704020132131361949/
5. http://blog.163.com/digoal@126/blog/static/16387704020132208241607/
6. http://blog.163.com/digoal@126/blog/static/16387704020132209854525/
7. http://blog.163.com/digoal@126/blog/static/1638770402014728105442434/