PostgreSQL 日志审计__审计是指记录用户的登陆退出以及登陆后在数据库里的行为操作。 PostgreSQL的日志（pg_log）功能十分丰富，接下来首先会介绍它的标准日志审计功能，之后还会详细介绍 PostgreSQL审计扩展(PgAudit) ，编译安装以及使用。_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL 日志审计

原作者：杨杰　　创作时间：2017-12-12 22:41:44+08　　

doudou586 发布于2017-12-15 22:41:44　　

评论: 1　　

顶: 1933　

踩: 2261　

# PostgreSQL 日志审计

#### 作者：杨杰
#### 发布：2017-12-12
#### 欢迎大家踊跃投稿，投稿信箱： press@postgres.cn

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

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

- none，即不记录
- ddl(记录create,drop和alter)
- mod(记录ddl+insert,delete,update和truncate)
- all(mod+select)

## 实例SQL:
```
highgo=# set log_statement = 'all';SEThighgo=# show log_statement;
 log_statement 
---------------
 all
(1 row)

highgo=# create table account (
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 主页](https://www.pgaudit.org/)， [PGAudit README 中文](https://my.oschina.net/yonj1e/blog/1506597)

### PostgreSQL版本兼容性
pgAudit开发支持PostgreSQL 9.5或更高版本。
pgAudit版本与postgresql主要版本有关，如下：

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

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

下载地址：[Download](https://github.com/pgaudit/pgaudit)

### 编译与安装
```
# 获取源码tar zxvf postgresql-9.5.7.tar.gz
tar zxvf pgaudit-1.0.6.tar.gz -C postgresql-9.5.7/contrib/# 编译安装PGcd postgresql-9.5.7
./configure
make install -s # 重命名，否则后面回归测试报错cd contrib
mv pgaudit-1.0.6/ pgauditcd pgaudit # 回归测试make -s check# 编译安装PGAuditmake 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';SEThighgo=# show pgaudit.log;
           pgaudit.log            ----------------------------------
 read, write, ddl, function, role
(1 row)

highgo=# 
highgo=# create table account (
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 $$declarebeginexecute '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 8STATEMENT:  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 startedERROR:  42710: role "yonj1e" already existsSTATEMENT:  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 existsSTATEMENT:  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]$

```

![PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

评论:1　　

顶: 1933　

踩: 2261　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-22 09:44:35+08

5个字的游戏名字:https://www.nanss.com/mingcheng/3868.html 晒老公的红包短句说说:https://www.nanss.com/wenan/3748.html 一条烟多少包:https://www.nanss.com/shenghuo/3626.html 年会策划方案:https://www.nanss.com/gongzuo/2738.html 关于樱花的网名:https://www.nanss.com/mingcheng/3746.html 十二五是哪几年:https://www.nanss.com/shenghuo/3630.html 六年级毕业哭到爆的句子:https://www.nanss.com/xuexi/3995.html 兄弟文案:https://www.nanss.com/wenan/3521.html 怎么说分手:https://www.nanss.com/wenti/3648.html 男生qq网名:https://www.nanss.com/mingcheng/3945.html 女流氓句子撩人:https://www.nanss.com/wenan/3454.html 很走心的干净短句:https://www.nanss.com/yulu/3489.html 形容女人气质优雅漂亮的句子:https://www.nanss.com/yulu/2919.html 骆驼祥子读后感400字:https://www.nanss.com/yuedu/3447.html 写事的作文800字:https://www.nanss.com/xuexi/3693.html 议论文:https://www.nanss.com/xuexi/3105.html 80后经典语录:https://www.nanss.com/yulu/3957.html 看透人心不说破的句子:https://www.nanss.com/yulu/3475.html 走心的生活感悟句子:https://www.nanss.com/yulu/3723.html 作文素材高考版:https://www.nanss.com/xuexi/2604.html 感谢母亲的作文:https://www.nanss.com/xuexi/3442.html 春天作文:https://www.nanss.com/xuexi/2849.html 给高三的儿子简短鼓励的话:https://www.nanss.com/xuexi/2521.html 毕业设计总结:https://www.nanss.com/xuexi/3068.html 季度思想汇报:https://www.nanss.com/xuexi/2963.html 八个字的唯美句子:https://www.nanss.com/yulu/3639.html 感谢词:https://www.nanss.com/shenghuo/3928.html 家族名称:https://www.nanss.com/mingcheng/3740.html 写给朋友的一封信:https://www.nanss.com/xuexi/3280.html 长城优秀作文500字:https://www.nanss.com/xuexi/2811.html