文档章节

PostgreSQL 日志审计

yonj1e
 yonj1e
发布于 2017/08/11 15:44
字数 3996
阅读 3.4K
收藏 9

精选30+云产品,助力企业轻松上云!>>>

 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

设置

设置只能由超级用户进行修改。允许正常用户更改它们的设置将使审计日志的点被破坏。

可以在全局范围内指定设置(in postgresql.conf or using ALTER SYSTEM ... SET),在数据库级别(using ALTER DATABASE ... SET),或者在角色级别(using ALTER ROLE ... SET).请注意,设置不是通过正常角色继承的,而SET ROLE将不会更改用户的pgAudit设置。这是对角色系统的限制,而不是pgAudit所固有的。

修改配置文件:

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。

参数

pgaudit.log

可能的值是:

  • 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.

可以使用逗号分隔的列表来提供多个类,并且类可以通过使用一种来减少类来减少。

默认值为none。

pgaudit.log_catalog
指定在语句中的所有关系都在pg_catalog中的情况下,应该启用会话日志记录。禁用此设置将减少日志中的噪音,例如psql和PgAdmin等工具,这些工具对目录进行了大量查询。
默认值on 。

pgaudit.log_level
指定用于日志条目的日志级别 (see Message Severity Levels for valid levels) ,但注意ERROR、FATAL和PANIC。此设置用于进行回归测试,也可能对最终用户用于测试或其他目的使用。
默认值为log。

pgaudit.log_parameter
指定审核日志记录应该包含与语句传递的参数。当参数出现时,在语句文本之后,将以CSV格式包含。
缺省值off。

pgaudit.log_relation
指定会话审核日志记录是否应该为SELECT或DML语句中引用的每个关系(表、视图等)创建单独的日志项。这是在不使用对象审核日志记录的情况下进行穷举日志记录的一种有用的捷径。
缺省值已关闭。

pgaudit.log_statement_once
指定日志记录是否包含语句、文本和参数,其中包含statement/substatement组合的第一个日志条目,或与每个条目一起。禁用此设置将导致更少的日志记录,但可能会使确定生成日志项的语句变得更困难,尽管statement/substatement对和进程id应该足以标识使用上一个条目记录的语句文本。
缺省值off。

pgaudit.role
指定要用于对象审核日志记录的主角色。可以通过将多个审计角色授予主角色来定义多个审计角色。这就允许多个组负责审计日志记录的不同方面。
没有默认值。

会话审计日志记录

会话审计日志记录提供了后端用户执行的所有语句的详细日志。

配置

使用pgaudit.log设置启用会话日志记录。
为所有DML和DDL启用会话日志记录,并记录DML语句中的所有关系:

set pgaudit.log = 'write, ddl';

set pgaudit.log_relation = on;

为除MISC之外的所有命令启用会话日志记录,并将审核日志信息作为NOTICE提交:

set pgaudit.log = 'all, -misc';

set pgaudit.log_level = notice;

实例

在本例中,尝试了pgaudit.log的各个参数。

SQL:

highgo=# show pgaudit.log;
 pgaudit.log 
-------------
 none
(1 row)

highgo=# set pgaudit.log = 'read, write, ddl, function, role';
SET
highgo=# show pgaudit.log;
           pgaudit.log            
----------------------------------
 read, write, ddl, function, role
(1 row)

highgo=# 
highgo=# create table account
highgo-# (
highgo(#     id int,
highgo(#     name text,
highgo(#     password text,
highgo(#     description text
highgo(# );
CREATE TABLE
highgo=# insert into account (id, name, password, description)  values (1, 'yonj1e', 'yonj1e', 'yonj1e.github.io');
INSERT 0 1
highgo=# insert into account (id, name, password, description)  values (2, 'yonj1e', 'yonj1e', 'yonj1e.github.io');
INSERT 0 1
highgo=# update account set description = 'my blob' where id = 2;
UPDATE 1
highgo=# delete * from account where id = 1;
ERROR:  42601: syntax error at or near "*"
LINE 1: delete * from account where id = 1;
               ^
highgo=# delete from account where id = 1;
DELETE 1
highgo=# select * from account ;
 id |  name  | password | description 
----+--------+----------+-------------
  2 | yonj1e | yonj1e   | my blob
(1 row)

highgo=# do language plpgsql $$
declare
begin
execute 'create table account_'||'a (id int)';
end;     
$$;
DO
highgo=# truncate account;
TRUNCATE TABLE
highgo=# vacuum account;
VACUUM
highgo=# grant select (passwore) on public.account to highgo;
ERROR:  42703: column "passwore" of relation "account" does not exist
highgo=# grant select (password) on public.account to highgo;
GRANT
highgo=# 

Log Output:

 

[highgo@localhost pg_log]$ cat postgresql-2017-08-12_140004.log 
LOG:  00000: database system was shut down at 2017-08-12 14:00:03 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: AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,"create table account
	(
	    id int,
	    name text,
	    password text,
	    description text
	);",<not logged>
LOG:  00000: AUDIT: SESSION,2,1,WRITE,INSERT,,,"insert into account (id, name, password, description)  values (1, 'yonj1e', 'yonj1e', 'yonj1e.github.io');",<not logged>
LOG:  00000: AUDIT: SESSION,3,1,WRITE,INSERT,,,"insert into account (id, name, password, description)  values (2, 'yonj1e', 'yonj1e', 'yonj1e.github.io');",<not logged>
LOG:  00000: AUDIT: SESSION,4,1,READ,SELECT,,,"SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'f', 'v', 'P') AND substring(pg_catalog.quote_ident(c.relname),1,2)='ac' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
	UNION
	SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,2)='ac' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
	UNION
	SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'f', 'v', 'P') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,2)='ac' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
	LIMIT 1000",<not logged>
LOG:  00000: AUDIT: SESSION,5,1,READ,SELECT,,,"SELECT pg_catalog.quote_ident(attname)   FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c  WHERE c.oid = a.attrelid    AND a.attnum > 0    AND NOT a.attisdropped    AND substring(pg_catalog.quote_ident(attname),1,7)='descrip'    AND (pg_catalog.quote_ident(relname)='account'         OR '""' || relname || '""'='account')    AND pg_catalog.pg_table_is_visible(c.oid)
	LIMIT 1000",<not logged>
LOG:  00000: AUDIT: SESSION,6,1,WRITE,UPDATE,,,update account set description = 'my blob' where id = 2;,<not logged>
LOG:  00000: AUDIT: SESSION,7,1,READ,SELECT,,,"SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P') AND substring(pg_catalog.quote_ident(c.relname),1,2)='ac' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
	UNION
	SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,2)='ac' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
	UNION
	SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,2)='ac' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
	LIMIT 1000",<not logged>
ERROR:  42601: syntax error at or near "*" at character 8
STATEMENT:  delete * from account where id = 1;
LOG:  00000: AUDIT: SESSION,8,1,WRITE,DELETE,,,delete from account where id = 1;,<not logged>
LOG:  00000: AUDIT: SESSION,9,1,READ,SELECT,,,"SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P') AND substring(pg_catalog.quote_ident(c.relname),1,2)='ac' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
	UNION
	SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,2)='ac' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
	UNION
	SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'P') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,2)='ac' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,2) = substring('ac',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
	LIMIT 1000",<not logged>
LOG:  00000: AUDIT: SESSION,10,1,READ,SELECT,,,select * from account ;,<not logged>
LOG:  00000: AUDIT: SESSION,11,1,FUNCTION,DO,,,"do language plpgsql $$
	declare
	begin
	execute 'create table account_'||'a (id int)';
	end;
	$$;",<not logged>
LOG:  00000: AUDIT: SESSION,11,2,DDL,CREATE TABLE,,,create table account_a (id int),<not logged>

 

对象审核日志

只有SELECT、INSERT、UPDATE和DELETE命令才会被支持。在对象审核日志记录中不包含TRUNCATE。
pgaudit.log = 'read, write'的一个更细粒度的替换。因此,将它们结合在一起可能是不明智的,但一个可能的场景是使用会话日志记录每个语句,然后用对象日志来补充有关特定关系的更多细节。

配置
对象级别的审计日志记录是通过角色系统实现的。pgaudit.role设置定义了用于审核日志记录的角色。当审计角色具有执行命令的权限或继承来自另一个角色的权限时,将对关系(表、视图等)进行审核。这使您能够有效地拥有多个审计角色,尽管在任何上下文中都有一个主角色。

将pgaudit.role设置为auditor,并grant在ACCOUNT表中的SELECT和DELETE权限。现在将记录帐户表中的任何SELECT或DELETE语句:

set pgaudit.role = 'auditor';


grant select, delete

   on public.account

   to auditor;

实例
在本例中, 我要审计某个表的select操作,那么首先在数据库中创建一个审计角色,把这个表的select权限赋予给这个审计角色,那么任何人对这个表执行select都会被审计。

SQL:

highgo=# show pgaudit.log;
 pgaudit.log 
-------------
 none
(1 row)

highgo=# set pgaudit.log_relation = on;
SET
highgo=# set pgaudit.log_parameter = on;
SET
highgo=# create role yonj1e nologin;
ERROR:  42710: role "yonj1e" already exists
highgo=# set pgaudit.role = 'yonj1e';
SET
highgo=# show pgaudit.role ;
 pgaudit.role 
--------------
 yonj1e
(1 row)

highgo=# create table account
(
    id int,
    name text,
    password text,
    description text
);

CREATE TABLE
highgo=# 
highgo=# grant select on public.account to yonj1e;
GRANT
highgo=# insert into account (id, name, password, description)  values (1, 'yonj1e', 'yonj1e', 'yonj1e.github.io');
INSERT 0 1
highgo=# insert into account (id, name, password, description)  values (2, 'yonj1e', 'yonj1e', 'yonj1e.github.io');
INSERT 0 1
highgo=# select * from account where id = 2;
 id |  name  | password |   description    
----+--------+----------+------------------
  2 | yonj1e | yonj1e   | yonj1e.github.io
(1 row)

highgo=# delete from account ;
DELETE 2
highgo=# 

Log Output:

[highgo@localhost pg_log]$ cat postgresql-2017-08-12_143116.log 
LOG:  00000: database system was shut down at 2017-08-12 14:31:15 PDT
LOG:  00000: MultiXact member wraparound protections are now enabled
LOG:  00000: database system is ready to accept connections
LOG:  00000: autovacuum launcher started
ERROR:  42710: role "yonj1e" already exists
STATEMENT:  create role yonj1e nologin;
LOG:  00000: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select * from account where id = 2;,<none>
[highgo@localhost pg_log]$ 

通过对象审计,可以将PostgreSQL的审计功能提升一个档次。

格式

审计条目被写入标准日志设备,并以逗号分隔格式包含以下列。只有在删除每个日志条目的日志行前缀部分时,输出才是符合CSV格式的。

  • AUDIT_TYPE - SESSION or OBJECT.
  • STATEMENT_ID - Unique statement ID for this session. Each statement ID represents a backend call. Statement IDs are sequential even if some statements are not logged. There may be multiple entries for a statement ID when more than one relation is logged.
  • SUBSTATEMENT_ID - Sequential ID for each sub-statement within the main statement. For example, calling a function from a query. Sub-statement IDs are continuous even if some sub-statements are not logged. There may be multiple entries for a sub-statement ID when more than one relation is logged.
  • CLASS - e.g. READ, ROLE (see pgaudit.log).
  • COMMAND - e.g. ALTER TABLE, SELECT.
  • OBJECT_TYPE - TABLE, INDEX, VIEW, etc. Available for SELECT, DML and most DDL statements.
  • OBJECT_NAME - The fully-qualified object name (e.g. public.account). Available for SELECT, DML and most DDL statements.
  • STATEMENT - Statement executed on the backend.
  • PARAMETER - If pgaudit.log_parameter is set then this field will contain the statement parameters as quoted CSV.

使用log_line_prefix添加满足审计日志要求所需的任何其他字段。典型的日志行前缀可能为‘\%m\%u\%d:’,该前缀将为每个审计日志提供日期/时间、用户名和数据库名称。

highgo=# show log_line_prefix ;
 log_line_prefix 
-----------------
 %m%u%d:
(1 row)

highgo=# set pgaudit.log = 'all';
SET
highgo=# insert into account (id, name, password, description)  values (2, 'yonj1e', 'yonj1e', 'yonj1e.github.io');


[highgo@localhost pg_log]$ cat postgresql-2017-08-12_143116.log 
LOG:  00000: database system was shut down at 2017-08-12 14:31:15 PDT
LOG:  00000: MultiXact member wraparound protections are now enabled
LOG:  00000: database system is ready to accept connections
LOG:  00000: autovacuum launcher started
ERROR:  42710: role "yonj1e" already exists
STATEMENT:  create role yonj1e nologin;
LOG:  00000: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select * from account where id = 2;,<none>
[highgo@localhost pg_log]$ vim ../postgresql.conf 
[highgo@localhost pg_log]$ ll
total 8
-rw-------. 1 highgo highgo 752 Aug 12 14:45 postgresql-2017-08-12_143116.log
-rw-------. 1 highgo highgo 694 Aug 12 14:46 postgresql-2017-08-12_144532.log
[highgo@localhost pg_log]$ cat postgresql-2017-08-12_144532.log 
2017-08-12 14:45:32.858 PDT:LOG:  00000: database system was shut down at 2017-08-12 14:45:31 PDT
2017-08-12 14:45:32.865 PDT:LOG:  00000: MultiXact member wraparound protections are now enabled
2017-08-12 14:45:32.866 PDT:LOG:  00000: database system is ready to accept connections
2017-08-12 14:45:32.869 PDT:LOG:  00000: autovacuum launcher started
2017-08-12 14:46:11.846 PDThighgohighgo:LOG:  00000: AUDIT: SESSION,1,1,MISC,SET,,,set pgaudit.log = 'all';,<not logged>
2017-08-12 14:46:22.004 PDThighgohighgo:LOG:  00000: AUDIT: SESSION,2,1,WRITE,INSERT,,,"insert into account (id, name, password, description)  values (2, 'yonj1e', 'yonj1e', 'yonj1e.github.io');",<not logged>
[highgo@localhost pg_log]$ 

 

yonj1e
粉丝 17
博文 20
码字总数 31358
作品 0
济南
后端工程师
私信 提问
加载中
请先登录后再评论。
pg数据库安全配置

pg数据库安全配置 [TOC] 数据库安全配置是数据库管理的重要环节之一。安全配置主要包括密码、网络访问控制、审计等。下面会依次讲解在瀚高数据库中如何实现密码相关的安全性配置。 1 密码加密...

Chrisworld
05/12
14
0
Postgres的日志实用功能

不得不说,Postgres的日志(pglog,类似oracle的alter文件,非pgxlog)确实是很灵活,功能也很丰富的,下面是借用postgres的日志来实现一些管理功能,下面涉及的参数都在文件$PGDATA/postgresq...

kenyon_君羊
2013/03/29
1.2W
3
PGAudit README

pgAudit Open Source PostgreSQL Audit Logging PostgreSQL Auditing Extension -- PGAudit 注意:对README.md进行翻译,无校对,有错误,请指出,谢谢! 导言 postgresql审计扩展(PgAudit)...

yonj1e
2017/08/11
332
0
PostgreSQL 从文件时间戳获悉一些信息(如数据库创建时间)

标签 PostgreSQL , Linux , stat , file , atime , mtime , ctime 背景 PG的一些元数据没有时间字段,例如对象的创建时间、DDL的修改时间。 如果要获得这个时间信息,我们可以 1、通过事件触...

德哥
2018/05/06
0
0
【阿里云新品发布会】第30期:日志审计服务最新“神操作”,一键开启云上合规“保护伞”

点击订阅新品发布会! 新产品、新版本、新技术、新功能、价格调整,评论在下方,下期更新!关注更多内容,了解更多 云上合规“保护伞”,一键开启日志审计服务 2019年10月30日15时,日志审计...

云攻略小攻
2019/10/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

设计模式(6) 适配器模式

结构型模式 适配器模式 类适配器和对象适配器 结构型模式 结构型模式的重点在于如何通过灵活的体系组织不同的对象,并在此基础上完成更为复杂的类型(或者类型系统),而参与组合的各类型之间...

zhixin9001
30分钟前
15
0
《Kubernetes设计与实现》DaemonSet概述

TODO

恋恋美食
35分钟前
18
0
在打印预览模式下使用Chrome的Element Inspector? - Using Chrome's Element Inspector in Print Preview Mode?

问题: I am working on developing a website and need to work on the print view. 我正在开发一个网站,需要处理打印视图。 Typically when I have layout issues I use Chrome's Element......

javail
39分钟前
22
0
性能测试神器 wrk 使用教程

原文连接:https://blog.fengjx.com/wrk/ wrk 是一个类似 ab(apache bench)、jmeter 的压力测试工具,底层基于 epoll 和 kqueue 实现,能充分利用服务器 cpu 资源,降低测试工具本身性能开销...

ws-小铁匠
49分钟前
18
0
Pandas 秘籍·翻译完成

协议:CC BY-NC-SA 4.0 欢迎任何人参与和完善:一个人可以走的很快,但是一群人却可以走的更远。 在线阅读 ApacheCN 面试求职交流群 724187166 ApacheCN 学习资源 目录 Pandas 秘籍 零、前言...

布客飞龙
56分钟前
24
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部