使用PostgreSQL pgaudit进行审计日志收集
大家好！这次我们来简单总结一下关于PostgreSQL的审计。
首先，审计是什么。
这个功能的作用是记录数据库的运行情况，这样在发生问题时能够更容易地进行应对。
关于PostgreSQL的审计
PostgreSQL的审计日志包括以下内容。
通过pgaudit获取
セッション監査ロギング
pgAudit パラメータ pgaudit.log に設定したクラス(READ / WRITE / FUNCTION / ROLE / DDL / MISC / MISC_SET/ALL)の中から取得範囲を指定できます。
オブジェクト監査ロギング
pgAudit のパラメータである pgaudit.role に設定したロールに対して設定することで、そのロールの操作がすべて記録されます。すべて記録すると膨大な量の監査ログが取得されてしまいますが、特定のテーブル等に対する操作だけを監査することも可能です。
通过 log_statement 参数进行获取
可以通过设置log_statement参数来控制在postgresql.conf中的日志输出，从而实现获取审计日志的目的。然而，由于获取的日志内容不足，因此也存在难以进行审计的缺点。
准备好
我们将假设PostgreSQL已经安装好。
关于安装方法请参考这里。
搜索 pgaudit 包
[root@postgresql yum.repos.d]# yum search pgaudit
サブスクリプション管理リポジトリーを更新しています。
コンシューマー識別子を読み込めません
このシステムは、エンタイトルメントサーバーに登録されていません。subscription-manager で登録できます。
メタデータの期限切れの最終確認: 2:39:34 時間前の 2022年11月06日 19時24分10秒 に実施しました。
==================================================== 名前 & 概要 一致: pgaudit =====================================================
pgauditlogtofile_11-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_12-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_13-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_14-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_15-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
======================================================== 名前 一致: pgaudit ========================================================
pgaudit12_10.x86_64 : PostgreSQL Audit Extension
pgaudit13_11.x86_64 : PostgreSQL Audit Extension
pgaudit14_12.x86_64 : PostgreSQL Audit Extension
pgaudit15_13.x86_64 : PostgreSQL Audit Extension
pgaudit16_14.x86_64 : PostgreSQL Audit Extension
pgaudit17_15.x86_64 : PostgreSQL Audit Extension
pgaudit_analyze.x86_64 : PostgreSQL Audit Analyzer
pgaudit_analyze.noarch : PostgreSQL Audit Analyzer
pgauditlogtofile-10.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile-11.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile-12.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile-13.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_10.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_11.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_12.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_13.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_14.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_15.x86_64 : PostgreSQL Audit Log To File Extension
安装 pgaudit 包。
[root@postgresql yum.repos.d]# yum install pgaudit17_15.x86_64
サブスクリプション管理リポジトリーを更新しています。
コンシューマー識別子を読み込めません
このシステムは、エンタイトルメントサーバーに登録されていません。subscription-manager で登録できます。
メタデータの期限切れの最終確認: 2:39:42 時間前の 2022年11月06日 19時24分10秒 に実施しました。
依存関係が解決しました。
====================================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
====================================================================================================================================
インストール:
pgaudit17_15 x86_64 1.7.0-1.rhel8 pgdg15 56 k
トランザクションの概要
====================================================================================================================================
インストール 1 パッケージ
ダウンロードサイズの合計: 56 k
インストール後のサイズ: 98 k
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
pgaudit17_15-1.7.0-1.rhel8.x86_64.rpm 35 kB/s | 56 kB 00:01
------------------------------------------------------------------------------------------------------------------------------------
合計 35 kB/s | 56 kB 00:01
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
準備 : 1/1
インストール中 : pgaudit17_15-1.7.0-1.rhel8.x86_64 1/1
scriptletの実行中: pgaudit17_15-1.7.0-1.rhel8.x86_64 1/1
検証 : pgaudit17_15-1.7.0-1.rhel8.x86_64 1/1
インストール済みの製品が更新されています。
インストール済み:
pgaudit17_15-1.7.0-1.rhel8.x86_64
完了しました!
在postgresql.conf中添加shared_preload_libraries=’pgaudit’的配置。
[postgres@postgresql ~]$ vi /var/lib/pgsql/15/data/postgresql.conf
[postgres@postgresql ~]$ cat /var/lib/pgsql/15/data/postgresql.conf | grep shared_preload
shared_preload_libraries = 'pgaudit' # (change requires restart)
4. 重新启动 PostgreSQL 服务
[root@postgresql pgsql]# systemctl restart postgresql-15.service
[root@postgresql pgsql]#
5. 将pgauidit注册为扩展。
[postgres@postgresql ~]$ psql -U postgres
psql (15.0)
"help"でヘルプを表示します。
postgres=# CREATE EXTENSION pgaudit;
CREATE EXTENSION
postgres=# \dx
インストール済みの拡張一覧
名前 | バージョン | スキーマ | 説明
---------+------------+------------+---------------------------------
pgaudit | 1.7 | public | provides auditing functionality
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 行)
会话审计日志记录
对于审计对象进行 alter database set pgaudit.log=’All’; 的操作
将 ALL 类别设置为 db1。
postgres=# create database db1;
CREATE DATABASE
postgres=# alter database db1 set pgaudit.log='ALL';
ALTER DATABASE
2. 确认审计日志的输出位置
虽然没有特别指定输出位置，但审计日志位于以下目录中。
[postgres@postgresql log]$ cd /var/lib/pgsql/15/data/log
[postgres@postgresql log]$ ls
postgresql-Mon.log postgresql-Sun.log
3. 创建测试数据
postgres=# \c db1
データベース"db1"にユーザー"postgres"として接続しました。
db1=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
db1=# insert into ta_1 values(1,'tanaka');
INSERT 0 1
4. 确认审计日志
对于db1的操作内容已经被审计。
[postgres@postgresql log]$ cat postgresql-Mon.log
2022-11-07 00:54:54.395 EST [4452] LOG: AUDIT: SESSION,5,1,DDL,CREATE TABLE,,,"create table ta_1(id integer,name varchar(10));",<not logged>
2022-11-07 00:55:13.718 EST [4452] STATEMENT: insert into ta_1(1,'tanaka');
2022-11-07 00:56:04.641 EST [4616] LOG: AUDIT: SESSION,1,1,WRITE,INSERT,,,"insert into ta_1 values(1,'tanaka');",<not logged>
对象审核日志
创建审计角色
postgres=# CREATE ROLE auditrole;
CREATE ROLE
2. 更改设置
[postgres@postgresql ~]$ vi /var/lib/pgsql/15/data/postgresql.conf
pgaudit.role = 'auditrole'
3. 重新启动PostgreSQL
[postgres@postgresql ~]$ pg_ctl stop
サーバーは停止しました
[postgres@postgresql ~]$ pg_ctl start
サーバー起動完了
4. 制作测试数据
postgres=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
postgres=# insert into ta_1 values(1,'tanaka');
INSERT 0 1
postgres=# select * from ta_1;
id | name
----+--------
1 | tanaka
(1 行)
将监视权限分配给 auditrole 对于 name 列。
postgres=# grant select(name) on ta_1 to auditrole;
GRANT
6. 表格查阅
postgres=# select * from ta_1;
id | name
----+--------
1 | tanaka
(1 行)
postgres=# select id,name from ta_1;
id | name
----+--------
1 | tanaka
(1 行)
postgres=# select id from ta_1;
id
----
1
(1 行)
postgres=# select name from ta_1;
name
--------
tanaka
(1 行)
7. 检查审计日志
只需一種選擇：查看生成的審計日誌，可以發現僅監視涉及名稱列的SELECT語句。並未生成選擇id的SELECT語句。
[postgres@postgresql log]$ ll
合計 4
-rw-------. 1 postgres postgres 703 11月 9 01:51 postgresql-Wed.log
[postgres@postgresql log]$ tail -f postgresql-Wed.log
2022-11-09 01:51:10.499 EST [2716] LOG: starting PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
2022-11-09 01:51:10.499 EST [2716] LOG: listening on IPv6 address "::1", port 5432
2022-11-09 01:51:10.499 EST [2716] LOG: listening on IPv4 address "127.0.0.1", port 5432
2022-11-09 01:51:10.500 EST [2716] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-09 01:51:10.502 EST [2716] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-09 01:51:10.504 EST [2720] LOG: database system was shut down at 2022-11-09 01:50:53 EST
2022-11-09 01:51:10.507 EST [2716] LOG: database system is ready to accept connections
2022-11-09 01:52:20.996 EST [2727] LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.ta_1,select * from ta_1;,<not logged>
2022-11-09 01:52:26.582 EST [2727] LOG: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.ta_1,"select id,name from ta_1;",<not logged>
2022-11-09 01:52:36.394 EST [2727] LOG: AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.ta_1,select name from ta_1;,<not logged>
这就是全部！
