数据库

PGAudit: PostgreSQL审计功能

PGAudit 扩展了 PostgreSQL 的内置日志记录功能，可用于选择性追踪数据库内的活动。

这有助于您实现：

  • 合规性：满足监管要求的审计需求
  • 安全性：检测可疑的数据库活动
  • 故障排查：识别并解决数据库问题

启用扩展

  1. 进入仪表盘的数据库页面
  2. 点击侧边栏中的扩展
  3. 搜索 pgaudit 并启用该扩展

配置扩展

PGAudit 可以配置不同精度的日志记录级别。

PGAudit 日志记录精度：

  • 会话级：记录单个连接内的活动，例如 psql 连接
  • 用户级：记录特定数据库用户的活动（如 anon 或 postgres）
  • 全局级：记录整个数据库范围内的活动
  • 对象级：记录与特定数据库对象相关的事件（例如 auth.users 表）

虽然会话级、用户级和全局级在精度上有所不同，但它们都被视为会话模式的变体，并使用相同的输入类别进行配置。

会话模式类别

这些模式可以监控预定义的数据库操作类别：

类别记录内容描述
read数据检索(SELECT, COPY)跟踪被访问的数据。
write数据修改(INSERT, DELETE, UPDATE, TRUNCATE, COPY)跟踪对数据库的更改。
function函数、存储过程和DO/END块执行跟踪例程/函数执行情况。
role用户管理操作(对用户和权限的CREATE, DROP, ALTER)跟踪用户权限和访问权限的变更。
ddl模式变更(CREATE, DROP, ALTER语句)监控数据库结构(表、索引等)的修改。
misc不常见命令(FETCH, CHECKPOINT)捕获不常见操作以便进行深度分析。
all上述所有类别全面日志记录，用于完整的审计追踪。

以下是一个如何配置PGAudit监控特定类别的简单示例：

1
2
3
4
5
6
7
8
-- 记录所有CREATE、ALTER和DROP事件... pgaudit.log = 'ddl';-- 记录所有CREATE、ALTER、DROP和SELECT事件... pgaudit.log = 'read, ddl';-- 不记录任何事件... pgaudit.log = 'none';

会话日志记录

当您在会话环境中连接时，例如通过 psql 连接，可以配置 PGAudit 来记录该会话内发起的事件。

在会话中，默认情况下 PGAudit 不会记录任何日志：

1
2
-- 返回 'none'show pgaudit.log;

在会话中，您可以通过 set 命令设置 pgaudit.log 变量来记录事件：

1
2
3
4
5
6
7
8
-- 记录 CREATE、ALTER 和 DROP 事件set pgaudit.log = 'ddl';-- 记录所有 CREATE、ALTER、DROP 和 SELECT 事件set pgaudit.log = 'read, ddl';-- 不记录任何事件set pgaudit.log = 'none';

用户行为日志记录

在某些情况下，您可能需要监控特定数据库用户的操作。例如，假设您将数据库连接到 Zapier 并为其创建了一个自定义角色：

1
create user "zapier" with password '<新密码>';

您可能希望记录 zapier 发起的所有操作，可以通过以下命令实现：

1
alter role "zapier" set pgaudit.log to 'all';

要移除这些设置，执行以下代码：

1
2
3
4
5
6
7
8
9
10
-- 禁用角色的日志记录alter role "zapier" set pgaudit.log to 'none';-- 检查确认更改已生效：select  rolname,  rolconfigfrom pg_roleswhere rolname = 'zapier';-- 应该返回包含 "pgaudit.log=none" 的 rolconfig 路径

全局日志记录

以下SQL配置PGAudit记录与postgres角色相关的所有事件。由于该角色拥有广泛权限，这实际上监控了所有数据库活动。

1
alter role "postgres" set pgaudit.log to 'all';

要检查postgres角色是否启用了审计，执行以下命令：

1
2
3
4
5
6
select  rolname,  rolconfigfrom pg_roleswhere rolname = 'postgres';-- 应返回包含"pgaudit.log=all"的rolconfig路径

要移除该设置，执行以下代码：

1
alter role "postgres" set pgaudit.log to 'none';

对象日志记录

要精细调整PGAudit记录的对象事件，您需要创建一个具有受限权限的自定义数据库角色：

1
create role "some_audit_role" noinherit;

其他Postgres用户无法继承或通过此角色登录。该角色仅用于安全定义PGAudit将记录的内容。

创建角色后，您可以通过将其分配给pgaudit.role变量来指示PGAudit进行记录：

1
alter role "postgres" set pgaudit.role to 'some_audit_role';

然后您可以分配该角色仅监控特定的对象事件，例如包含特定表的select语句：

1
grant select on random_table to "some_audit_role";

授予此权限后，PGAudit将记录所有引用random_table的select语句，无论实际由_谁_或_什么_触发了该事件。所有可分配的权限都可以在Postgres文档中查看。

如果您不再需要使用对象日志记录，需要取消分配pgaudit.role变量：

1
2
3
4
5
6
7
8
9
10
-- 修改pgaudit.role不再引用some_audit_rolealter role "postgres" set pgaudit.role to '';-- 使用以下命令查看pgaudit.role是否已更改：select  rolname,  rolconfigfrom pg_roleswhere rolname = 'postgres';-- 应该返回一个包含"pgaudit.role="的rolconfig路径

解读审计日志

PGAudit 设计用于将日志存储为包含以下标题的 CSV 文件：

标题描述
AUDIT_TYPESESSION（会话）或 OBJECT（对象）
STATEMENT_ID该会话的唯一语句ID。即使某些语句未被记录，ID也会保持连续。
SUBSTATEMENT_ID主语句中每个子语句的顺序ID。即使某些子语句未被记录，ID也会保持连续。
CLASS..., READ（读取）, ROLE（角色） (参见 pgaudit.log)。
COMMAND..., ALTER TABLE（修改表）, SELECT（查询）。
OBJECT_TYPETABLE（表）, INDEX（索引）, VIEW（视图）等。适用于 SELECT、DML 和大多数 DDL 语句。
OBJECT_NAME完全限定的对象名称（例如 public.account）。适用于 SELECT、DML 和大多数 DDL。
STATEMENT在后端执行的语句。
PARAMETER如果设置了 pgaudit.log_parameter，此字段包含以引号包裹的 CSV 格式语句参数，或 <none>。否则显示 <not logged>（未记录）。

以下创建语句生成的日志：

1
2
3
4
5
create table account (  id int primary key,  name text,  description text);

在 控制台的 Postgres 日志 中会生成如下日志：

1
2
3
4
5
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(  id int,  name text,  description text); <not logged>

查找和筛选审计日志

PGAudit 生成的日志可以在 Postgres 日志 中找到。要查找特定日志，您可以使用日志浏览器。以下是一个提取引用 CREATE TABLE 事件日志的基础示例：

1
2
3
4
5
6
7
8
9
10
select  cast(t.timestamp as datetime) as timestamp,  event_messagefrom  postgres_logs as t  cross join unnest(metadata) as m  cross join unnest(m.parsed) as pwhere event_message like 'AUDIT%CREATE TABLE%'order by timestamp desclimit 100;

实际应用示例

监控 API 事件

要监控由 PostgREST API 角色发起的所有写入操作：

1
2
3
4
5
6
alter role "authenticator" set pgaudit.log to 'write';-- 上述命令实际等同于：-- alter role "anon" set pgaudit.log TO 'write';-- alter role "authenticated" set pgaudit.log TO 'write';-- alter role "service_role" set pgaudit.log TO 'write';

监控 auth.users 表

在最坏情况下，当特权角色的密码泄露时，您可以使用PGAudit来监控auth.users表是否成为攻击目标。需要说明的是，API请求已经在API边缘网络中被监控，这里的重点是为数据库层面发生的事件提供更清晰的可见性。

对auth.user的日志记录应采用对象模式，并需要创建一个自定义角色：

1
2
3
4
5
6
7
8
9
-- 创建日志记录角色create role "auth_auditor" noinherit;-- 授予角色观察相关表事件的权限grant select on auth.users to "auth_auditor";grant delete on auth.users to "auth_auditor";-- 将auth_auditor分配给pgaudit.rolealter role "postgres" set pgaudit.role to 'auth_auditor';

通过上述代码，任何涉及从auth.users表读取或删除的查询都将被记录。

最佳实践

禁用多余日志记录

如果未谨慎配置，PGAudit可能会记录所有数据库事件，包括后台任务。这可能在几小时内产生大量不必要的日志。

解决这个问题的第一步是确定PGAudit正在监控哪些数据库用户：

1
2
3
4
5
6
7
8
9
10
11
12
-- 查找所有被pgaudit监控的用户select  rolname,  rolconfigfrom pg_roleswhere  exists (    select      1    from UNNEST(rolconfig) as c    where c like '%pgaudit.role%' or c like '%pgaudit.log%'  );

要阻止PGAudit监控有问题的角色，您需要将它们的pgaudit.log值改为none，并将pgaudit.role值改为空引号''

1
2
3
4
5
-- 用于禁用对象级日志记录  alter role "<role name>" set pgaudit.role to '';  -- 用于禁用全局和用户级日志记录  alter role "<role name>" set pgaudit.log to 'none';

常见问题

使用PGAudit调试数据库函数

从技术上讲是可以的，但这并非最佳方法。建议查阅我们的函数调试指南来获取更好的解决方案。

下载数据库日志

您可以在日志仪表盘中将日志下载为CSV格式文件。

记录查询返回的行数据

默认情况下，PGAudit会记录查询语句但不会记录返回的行数据。您可以通过pgaudit.log_rows变量修改这一行为：

1
2
3
4
5
-- 启用alter role "postgres" set pgaudit.log_rows to 'on';-- 禁用alter role "postgres" set pgaudit.log_rows to 'off';

除非您_完全_确定这是必要的使用场景，否则不建议启用此功能。这可能会将敏感值暴露在日志中，而这些值本不应被保留。此外，如果过度使用，可能会显著降低数据库性能。

记录函数参数

我们目前不支持配置pgaudit.log_parameter，因为如果您使用pgsodium或Vault加密列，这可能会在日志中记录加密前的原始值。

如果您希望解除这一限制，可以在该功能请求中说明您的使用场景并点赞支持。

PGAudit 是否支持系统级配置？

PGAudit 允许将设置应用于3种不同的数据库范围：

范围描述配置文件/命令
系统级整个服务器ALTER SYSTEM 命令
数据库级特定数据库ALTER DATABASE 命令
角色级特定用户/角色ALTER ROLE 命令

Supabase 限制了文件系统和数据库变量的完全权限，这意味着 PGAudit 的修改只能在角色级别进行。将 PGAudit 分配给 postgres 角色可以使其获得几乎完整的数据库可见性，这使得角色级别的调整成为配置数据库或系统级设置的实际替代方案。

PGAudit 的官方文档主要关注系统和数据库级别的配置，但其文档也正式支持角色级别的配置。

相关资源