使用pgaudit进行PostgreSQL的审计！试试DB用户级的会话审计！

1 年 ago

科, 雅

2 minutes

首先

这篇文章是”NTTテクノクロス Advent Calendar 2023″的第十天的文章。

你好！我是NTT技术创新的中村。

本文是回应对于使用pgaudit在PostgreSQL数据库进行审计而感到困惑的需求，提供具体的使用指南。同时也会涉及使用pgaudit时需要注意的要点。

数据库审计是什么？

数据库审计是指对数据库进行操作来检查是否存在非法访问。
具体来说，它是为了检测数据库是否存在非法访问而进行的操作。

②問題の検出および対策、もしくは予防措置を行います。

进行审核时，检查本身可以通过自动化或人工完成。然而，为了进行审计，需要收集和存储“哪个数据库用户”、“何时”、“针对什么”、“执行了什么操作”的信息。

pgaudit是一个支持收集和存储信息的扩展功能。不仅可以在本地环境中使用，还可以在云端环境，例如Amazon Aurora PostgreSQL、Amazon RDS for PostgreSQL和Azure Database for PostgreSQL中使用。

使用pgaudit进行会话审计的总结

那么首先，我们来总结一下pgaudit的会话审计。
会话审计是指记录连接到数据库后进行的“操作内容”。操作内容主要是指SQL语句，但也可以包括SET子句和PostgreSQL的命令集。

セッション監査はDBユーザ単位に設定できる
pgauditのインストール直後はデフォルトで監査はoff（postgresql.conf のパラメータ pgaudit.log = none）になっていますので、ALTER ROLE文を使ってDBユーザ単位に監査の設定を行います。

セッション監査では監査対象のコマンドを設定できる
以下「監査対象のコマンド」に記載の通り、SELECTやDDLなど、監査の対象としたいコマンドを指定できます。ただし、コマンドごとではなく、いくつかのまとまりの単位で指定する必要があります。

セッション監査の結果はPostgreSQLのサーバログに出力される
セッション監査のログはPostgreSQLのサーバログに他のログと一緒に出力されます。ログには「AUDIT」という文字列が含まれていますので、ユーザがログを分類することは比較的容易です。

审核对象的命令

您可以使用以下格式指定要审核的命令。

監査種別記録する内容READSELECT、（テーブルからデータを吸い出す）COPYWRITEINSERT、DELETE、UPDATE、TRUNCATE、（テーブルにデータを入れる）COPYFUNCTION関数呼び出し、DOブロックROLEGRANT、REVOKE、CREATE ROLE、DROP ROLE、ALTER ROLEDDLROLE関係を除く全DDLMISCVACUUM、SET、CHECKPOINT、FETCH、DISCARDMISC_SETその他のSETALL全操作NONE記録しない（デフォルト）

pgaudit 的官方文档如下所示。

pgaudit的会话审计配置策略

从这里开始正式进行，由于会话审核可以按照数据库用户进行设置，所以应根据用户的用途来选择不同的审核类型。

ユーザ種別（用途）監査種別考え方スーパーユーザALL全操作を記録管理者ユーザROLE、DDL、MISCVACUUMなどメンテナンス系も記録アプリケーションユーザROLE、DDL通常あまり実施しない操作を記録参照専用ユーザNONE記録しない

要注意的是“明确要监察的命令”，只针对所需的记录类型。即使是应用程序用户，也可能经常执行DDL命令，因此需要相应地进行调整。
指定的监察类型“大致”比较宽泛，因此往往会产生比想象中更多的日志。

比如，对于只能进行参考的用户，可以通过设置对象权限，使得DML和DDL的执行会导致错误，从而可以省略DML和DDL的审计工作。（如果数据中包含个人信息，还可能需要禁止对应信息的查阅权限，或将SELECT也纳入审计范围，这样的情况也可能存在）

因此，审计设置与用户设计密切相关。

以下是一个具体的设置示例。

让我们来看一些具体的设置示例。
在安装RPM之后，需要重新启动PostgreSQL，并将shared_preload_libraries=’pgaudit’添加到postgresql.conf中，然后在要使用的数据库上执行CREATE EXTENSION pgaudit;即可开始使用pgaudit。

postgresql.conf 中的 pgaudit 参数全部默认值！

我会先列出所有的设置参数及其默认值。
我们可以先使用默认设置，然后查看输出日志，再进行调整。

パラメータ名デフォルト値備考pgaudit.lognone監査対象は全体で設定せず、DBユーザ単位で設定するので「none」pgaudit.log_catalogonpg_xxxなどのカタログも監査の対象とするpgaudit.log_clientoffデバッグ用なのでoffpgaudit.log_levellogpgaudit.log_client=on の時のみ有効になるpgaudit.log_parameteroffSQLに渡されたパラメータは出力しない。する場合onにするpgaudit.log_parameter_max_size0パラメータ情報の出力サイズ制限はしないので0pgaudit.log_relationoffテーブルごとに監査ログを分けて出力しないpgaudit.log_rowsoffSQLによって取得または更新された行を出力しないpgaudit.log_statementon実行されたSQL文を出力するpgaudit.log_statement_onceoff一つのSQL文で複数の監査ログが出力される場合でも、SQL文は毎回出力するpgaudit.role-オブジェクト監査用

另外，pgaudit的参数在安装后不会自动添加到postgresql.conf文件中。为了使其更易于理解，建议在postgresql.conf文件中以带有注释的形式添加默认值的补充。

修改数据库角色以进行每个DB用户的审计设置。

下面是为每个不同的DB用户设置审计配置的例子。
所有操作都是使用postgres用户执行的。

※验证操作在以下的本地环境中进行。

種別バージョンOSRHEL9.1PostgreSQL14.5pgaudit1.6.2

スーパーユーザ「postgres」の場合

postgres=# ALTER ROLE postgres SET pgaudit.log='ALL';
ALTER ROLE

管理者ユーザ「adminuser」の場合

postgres=# ALTER ROLE adminuser SET pgaudit.log='DDL,ROLE,MISC';
ALTER ROLE

アプリケーションユーザ「appuser」の場合

postgres=# ALTER ROLE appuser SET pgaudit.log='DDL,ROLE';
ALTER ROLE

読み取り専用ユーザ「readuser」の場合

postgres=# ALTER ROLE readuser SET pgaudit.log='NONE';
ALTER ROLE

您可以按照以下方式进行设置确认。

postgres=# SELECT rolname,rolconfig FROM pg_roles;
 rolname  |           rolconfig
----------+-------------------------------
 postgres | {pgaudit.log=ALL}
 adminser | {"pgaudit.log=DDL,ROLE,MISC"}
 appuser  | {"pgaudit.log=DDL,ROLE"}
 readuser | {pgaudit.log=NONE}
(4 rows)

日志输出示例

「READ」の監査対象としてAUDITログが出力されます。

postgres=# select * from pg_class limit 1;

AUDIT: SESSION,2,1,READ,SELECT,,,select * from pg_class limit 1;,<not logged>

「MISC」の監査対象としてAUDITログが出力されます。

postgres=> vacuum t_test;

AUDIT: SESSION,3,1,MISC,VACUUM,,,vacuum t_test;,<not logged>

「DDL」の監査対象としてAUDITログが出力されます。

postgres=> create table t_test2 (col1 int);

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.t_test2,create table t_test2 (col1 int);,<not logged>

总结

我解释了如何按DB用户对pgaudit会话进行审计。由于审计设置与DB用户设计密切相关，所以要注意是否进行审计，如果要进行审计，则需要整理DB用户的使用情况。

这篇文章告一段落。

请务必登录NTT Techno Cross的网站，我们也有关于PostgreSQL技术的文章，请务必阅读。

请继续享受NTT技术交叉的Advent Calendar 2023活动。