PGAud说明：
在PostgreSQL中，提供了开源的审计插件pgaudit，但是其功能并不完善，只提供了基本的审计功能，总体上审计插件的设计思路有点类似于日志库的设计。首先是要获取审计日志信息，需要先通过配置设置审计哪些内容，比如审计DDL语句，还是DML语句，或者都进行审计。当然，这里审计规则可以设置的非常灵活，商业数据库的审计功能会更加的完善。然后还需要设置审计日志输出哪些内容，比如，语句类型，执行结果是否成功，执行影响的行数等等
审计官方网站文档： https://github.com/pgaudit/pgaudit/blob/master/README.md
PGAudit参数说明：
--安装好后，数据库查看审计相关参数
select name,setting from pg_settings where name ~ 'pgaudit';
name | setting
----------------------------+----------------
pgaudit.log | WRITE,ROLE,DDL
pgaudit.log_catalog | off
pgaudit.log_client | on
pgaudit.log_level | log
pgaudit.log_parameter | on
pgaudit.log_relation | off
pgaudit.log_rows | off
pgaudit.log_statement | on
pgaudit.log_statement_once | off
pgaudit.role | pgaudit
(10 rows)
#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, SET.
MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.
ALL: Include all of the above.
#pgaudit.log_catalog
指定所有对象都在pg_catalog中时，是否记录到审计日志。如果禁用此设置，将减少psql和PgAdmin等工具在大量查询的时候的干扰。
#pgaudit.log_client
指定日志消息对客户端(如psql)是否可见。该设置通常应该保持禁用状态，但可能对调试或其他目的有用。默认off，注意，pgaudit.log_level仅在pgaudit.log_client打开时才启用。
#pgaudit.log_level
指定用于审计的日志级别(DEBUG1 .. DEBUG5,INFO,NOTICE,WARNING,ERROR,LOG,FATAL,PANIC)，但注意不允许ERROR、FATAL和PANIC,注意，pgaudit.log_level只在pgaudit.log_client打开时启用;否则将使用默认值,默认为log
#pgaudit.log_parameter
指定审计日志记录包括语句一起传递的参数。当参数出现时，它们将以CSV格式包含在语句文本之后，默认off
#pgaudit.log_relation
指定会话审计日志是否应该为SELECT或DML语句中引用的每个关系对象(TABLE、VIEW等)创建单独的日志条目。这是在不使用对象审计日志记录的情况下进行详尽日志记录的一种有用的快捷方式。
#pgaudit.log_rows
用于控制是否记录对特定表的行级审计事件。
当pgaudit.log_rows设置为all时，会记录所有的行级审计信息，包括SELECT、INSERT、UPDATE和DELETE操作。
当pgaudit.log_rows设置为none时，不记录任何行级审计信息。
#pgaudit.log_statement_once
当设置为ON时，它会导致对特定语句的审计信息只被记录一次，即使该语句被多次执行。这有助于减少日志文件的大小，特别是在高并发的环境中，默认设置为关闭。
#pgaudit.role
用于指定哪些数据库角色的行为将被审计，指定记录具有相关对象权限的role，没有默认值。
--1. 去官方下载插件、解压插件
https://github.com/pgaudit/pgaudit/releases
--2. 安装插件
[postgres@D2-HDDBREC202 pgaudit-1.6.2]$ cd /pg14/sw/pgaudit-1.6.2
make install USE_PGXS=1
或者指定PG_CONFIG
make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-13/bin/pg_config
[postgres@D2-HDDBREC202 pgaudit-1.6.2]$ make install USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pg14/soft/include/postgresql/server -I/pg14/soft/include/postgresql/internal -D_GNU_SOURCE -c -o pgaudit.o pgaudit.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pgaudit.so pgaudit.o -L/pg14/soft/lib -Wl,--as-needed -Wl,-rpath,'/pg14/soft/lib',--enable-new-dtags
/bin/mkdir -p '/pg14/soft/lib/postgresql'
/bin/mkdir -p '/pg14/soft/share/postgresql/extension'
/bin/mkdir -p '/pg14/soft/share/postgresql/extension'
/bin/install -c -m 755 pgaudit.so '/pg14/soft/lib/postgresql/pgaudit.so'
/bin/install -c -m 644 .//pgaudit.control '/pg14/soft/share/postgresql/extension/'
/bin/install -c -m 644 .//pgaudit--1.6.2.sql .//pgaudit--1.6--1.6.1.sql .//pgaudit--1.6.1--1.6.2.sql '/pg14/soft/share/postgresql/extension/'
2.1 use_pgxs释义参考链接：http://postgres.cn/docs/9.6/extend-pgxs.html
--3.激活pgaudit插件
配置$PGDATA目录下的postgresql.conf中的参数,将pgaudit添加进去
[postgres@D2-HDDBREC202 data]$ cat postgresql.conf | grep -i shared_preload_libraries
shared_preload_libraries = 'passwordcheck,pgaudit' # (change requires restart)
3.1 shared_preload_libraries说明：
shared_preload_libraries 是 PostgreSQL 中的配置参数，指定一个或者多个要在服务器启动时预载入的共享库。它包含一个由逗号分隔的库名列表，其中每个名称都会按LOAD命令的方式解析。
项之间的空格会被忽略，如果需要在库名中包含空格或者逗号，请把库名放在双引号内。这个参数只能在服务器启动时设置。如果指定的库没有找到，服务器将无法启动。
可以使用 shared_preload_libraries 加载的一些流行扩展包括：
pg_stat_statements：一个模块，提供有关服务器执行的SQL语句的统计信息，例如执行时间和频率。
pg_repack：一个对表进行在线重组的工具，可以帮助回收磁盘空间和提高性能。
pg_cron：一个作业调度程序，允许用户安排数据库任务以指定的时间间隔运行。
pgcrypto：一个在 PostgreSQL 中提供加密功能的模块，例如散列和加密。
当您将扩展添加到 shared_preload_libraries 列表时，扩展的代码将在 PostgreSQL 服务器启动时加载到内存中，并且其功能将在服务器的整个生命周期内可用。 这对于提供系统范围的功能或通过缓存常用数据来优化性能很有用。
--3.2 查看数据库已加载的参数 shared_preload_libraries ;
appdb=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------
passwordcheck,pgaudit
(1 row)
---3.2.1 shared_preload_libraries释义参考链接：http://postgres.cn/docs/11/runtime-config-client.html
http://postgres.cn/docs/11/bgworker.html
--4.重启pg服务器
[postgres@D2-HDDBREC202 data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2024-10-15 16:18:05.156 CST [4258] LOG: pgaudit extension initialized
2024-10-15 16:18:05.188 CST [4258] LOG: redirecting log output to logging collector process
2024-10-15 16:18:05.188 CST [4258] HINT: Future log output will appear in directory "/pglog".
done
server started
--5. 创建插件扩展
appdb=# create extension pgaudit;
CREATE EXTENSION
--5.1 验证查看插件，通过pg_extension字典表或者\dx快捷方式查看
appdb=# select * from pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+----------+----------+--------------+----------------+------------+-----------+--------------
13879 | plpgsql | 10 | 11 | f | 1.0 | |
16521 | sslinfo | 10 | 16405 | t | 1.2 | |
16532 | pgcrypto | 10 | 16405 | t | 1.3 | |
16576 | pgaudit | 10 | 16405 | t | 1.6.2 | |
(4 rows)
或者
appdb=# \dx
List of installed extensions
Name | Version | Schema | Description
----------+---------+------------+------------------------------------
pgaudit | 1.6.2 | public | provides auditing functionality
pgcrypto | 1.3 | appuser | cryptographic functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
sslinfo | 1.2 | appuser | information about SSL certificates
(4 rows)
--5.2 查看插件都有哪些函数可以使用
select oid,proname,pronamespace,probin from pg_proc where probin like '%pgaudit%'
oid | proname | pronamespace | probin
-------+-------------------------+--------------+-----------------
16577 | pgaudit_ddl_command_end | 16405 | $libdir/pgaudit
16579 | pgaudit_sql_drop | 16405 | $libdir/pgaudit
(2 rows)
--6. 配置审计日志的相关参数：
appdb=# alter system set pgaudit.log='WRITE,ROLE,DDL';
ALTER SYSTEM
appdb=# ALTER SYSTEM SET pgaudit.log_catalog=OFF;
ALTER SYSTEM
appdb=# ALTER SYSTEM SET pgaudit.log_parameter=ON;
ALTER SYSTEM
appdb=# alter system set pgaudit.log_client=on;
ALTER SYSTEM
--6.1 配置完，需重新加载参数配置
select pg_reload_conf();
--6.2 验证查看配置
appdb=# select name,setting from pg_settings where name ~ 'pgaudit';
name | setting
----------------------------+----------------
pgaudit.log | WRITE,ROLE,DDL
pgaudit.log_catalog | off
pgaudit.log_client | on
pgaudit.log_level | log
pgaudit.log_parameter | on
pgaudit.log_relation | off
pgaudit.log_rows | off
pgaudit.log_statement | on
pgaudit.log_statement_once | off
pgaudit.role | pgaudit
(10 rows)
--7. 查看是否审计，PG审计日志中
[postgres@D2-HDDBREC202 pglog]$ tail -300f postgresql-2024-10-16_150943.csv
2024-10-16 15:09:43.953 CST,,,14070,,670f66b7.36f6,1,,2024-10-16 15:09:43 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,"","postmaster",,0
2024-10-16 15:09:43.953 CST,,,14070,,670f66b7.36f6,2,,2024-10-16 15:09:43 CST,,0,LOG,00000,"starting PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44.0.3), 64-bit",,,,,,,,,"","postmaster",,0
2024-10-16 15:09:43.953 CST,,,14070,,670f66b7.36f6,3,,2024-10-16 15:09:43 CST,,0,LOG,00000,"listening on IPv4 address ""0.0.0.0"", port 5666",,,,,,,,,"","postmaster",,0
2024-10-16 15:09:43.961 CST,,,14070,,670f66b7.36f6,4,,2024-10-16 15:09:43 CST,,0,LOG,00000,"listening on Unix socket ""/tmp/.s.PGSQL.5666""",,,,,,,,,"","postmaster",,0
2024-10-16 15:09:43.976 CST,,,14072,,670f66b7.36f8,1,,2024-10-16 15:09:43 CST,,0,LOG,00000,"database system was shut down at 2024-10-16 15:09:43 CST",,,,,,,,,"","startup",,0
2024-10-16 15:09:43.983 CST,,,14070,,670f66b7.36f6,5,,2024-10-16 15:09:43 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,"","postmaster",,0
2024-10-16 15:10:40.997 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,1,"idle",2024-10-16 15:10:32 CST,4/2,0,LOG,00000,"statement: select * from appuser.app;",,,,,,,,,"psql","clientbackend",,0
2024-10-16 15:10:40.999 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,2,"SELECT",2024-10-16 15:10:32 CST,4/2,0,LOG,00000,"AUDIT: OBJECT,1,1,READ,SELECT,TABLE,appuser.app,select * from appuser.app;,<none>",,,,,,,,,"psql","client backend",,0
2024-10-16 15:10:40.999 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,3,"SELECT",2024-10-16 15:10:32 CST,4/0,0,LOG,00000,"duration: 1.429 ms",,,,,,,,,"psql","client backend",,0
示例：
启用pgaudit插件，要求如下：1)在数据库appdb中创建审计用户pgaudit
2)实现对 appdb数据库下的appuser.app表的select和delete操作进行审计。
答：
1.创建用户、授权、更改pgaudit的参数
create user pgaudit;
grant usage on schema appuser to pgaudit;
grant select ,delete on appuser.app to pgaudit;
alter system set pgaudit.role = 'pgaudit';
select pg_reload_conf();
2.pgaudit对appuser.app表 执行查询和删除，日志查看是否记录：
\c appdb appuser
select * from appuser.app ;
delete from appuser.app where id =2 ;
3. 查看日志，验证审计是否奏效：
[postgres@D2-HDDBREC202 pglog]$ tail -300f postgresql-2024-10-16_150943.csv
2024-10-16 15:10:40.997 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,1,"idle",2024-10-16 15:10:32 CST,4/2,0,LOG,00000,"statement: select * from appuser.app;",,,,,,,,,"psql","clientbackend",,0
2024-10-16 15:10:40.999 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,2,"SELECT",2024-10-16 15:10:32 CST,4/2,0,LOG,00000,"AUDIT: OBJECT,1,1,READ,SELECT,TABLE,appuser.app,select * from appuser.app;,<none>",,,,,,,,,"psql","client backend",,0
2024-10-16 15:10:40.999 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,3,"SELECT",2024-10-16 15:10:32 CST,4/0,0,LOG,00000,"duration: 1.429 ms",,,,,,,,,"psql","client backend",,0
2024-10-16 15:35:56.107 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,4,"idle",2024-10-16 15:10:32 CST,4/3,0,LOG,00000,"statement: delete from appuser.app where id=2;",,,,,,,,,"psql","client backend",,0
2024-10-16 15:35:56.108 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,5,"DELETE",2024-10-16 15:10:32 CST,4/3,0,LOG,00000,"AUDIT: SESSION,2,1,WRITE,DELETE,,,delete from appuser.app where id=2;,<none>",,,,,,,,,"psql","client backend",,0
2024-10-16 15:35:56.108 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,6,"DELETE",2024-10-16 15:10:32 CST,4/3,0,LOG,00000,"AUDIT: OBJECT,2,1,WRITE,DELETE,TABLE,appuser.app,delete from appuser.app where id=2;,<none>",,,,,,,,,"psql","client backend",,0
2024-10-16 15:35:56.111 CST,"pgaudit","appdb",14196,"[local]",670f66e8.3774,7,"DELETE",2024-10-16 15:10:32 CST,4/0,0,LOG,00000,"duration: 4.141 ms",,,,,,,,,"psql","client backend",,0
参考： https://github.com/pgaudit/pgaudit/blob/master/README.md
原文链接：https://blog.csdn.net/s_lisheng/article/details/139765268
