PGAudit：Postgres 审计

PGAudit 扩展了 Postgres 内置的日志功能。它可以用来有选择地跟踪你数据库中的活动。

这可以帮你：

🌐 This helps you with:

• 合规：满足法规的审计要求
• 安全：检测可疑的数据库活动
• 故障排除：识别并修复数据库问题

启用扩展 #

🌐 Enable the extension

配置扩展 #

🌐 Configure the extension

PGAudit 可以配置不同的精确度等级。

🌐 PGAudit can be configured with different levels of precision.

PGAudit 日志记录精度：

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

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

🌐 Although Session, User, and Global modes differ in their precision, they're all considered variants of Session Mode and are configured with the same input categories.

会话模式类别 #

🌐 Session mode categories

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

🌐 These modes can monitor predefined categories of database operations:

下面是一个如何将 PGAudit 分配来监控特定类别的有限示例。

🌐 Below is a limited example of how to assign PGAudit to monitor specific categories.

1
-- log all CREATE, ALTER, and DROP events
2
... pgaudit.log = 'ddl';
3
4
-- log all CREATE, ALTER, DROP, and SELECT events
5
... pgaudit.log = 'read, ddl';
6
7
-- log nothing
8
... pgaudit.log = 'none';

会话记录 #

🌐 Session logging

当你在会话环境中连接时，比如使用 psql 连接，你可以配置 PGAudit 来记录会话中发起的事件。

🌐 When you are connecting in a session environment, such as a psql connection, you can configure PGAudit to record events initiated within the session.

在一个会话中，默认情况下，PGAudit 什么都不会记录：

🌐 Inside a session, by default, PGAudit will log nothing:

1
-- returns 'none'
2
show pgaudit.log;

在会话中，你可以用 set 对 pgaudit.log 变量进行操作来记录事件：

🌐 In the session, you can set the pgaudit.log variable to record events:

1
-- log CREATE, ALTER, and DROP events
2
set pgaudit.log = 'ddl';
3
4
-- log all CREATE, ALTER, DROP, and SELECT events
5
set pgaudit.log = 'read, ddl';
6
7
-- log nothing
8
set pgaudit.log = 'none';

用户登录 #

🌐 User logging

有些情况下，你可能想要监控数据库用户的操作。例如，假设你把数据库连接到了 Zapier 并为它创建了一个自定义角色来使用：

🌐 There are some cases where you may want to monitor a database user's actions. For instance, say you connected your database to Zapier and created a custom role for it to use:

1
create user "zapier" with password '<new password>';

你可能想记录所有由 zapier 发起的操作，这可以用下面的命令完成：

🌐 You may want to log all actions initiated by zapier, which can be done with the following command:

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

要删除设置，运行以下代码：

🌐 To remove the settings, execute the following code:

1
-- disables role's log
2
alter role "zapier" set pgaudit.log to 'none';
3
4
-- check to make sure the changes are finalized:
5
select
6
  rolname,
7
  rolconfig
8
from pg_roles
9
where rolname = 'zapier';
10
-- should return a rolconfig path with "pgaudit.log=none" present

全球伐木 #

🌐 Global logging

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

🌐 The below SQL configures PGAudit to record all events associated with the postgres role. Since it has extensive privileges, this effectively monitors all database activity.

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

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

🌐 To check if the postgres role is auditing, execute the following command:

1
select
2
  rolname,
3
  rolconfig
4
from pg_roles
5
where rolname = 'postgres';
6
-- should return a rolconfig path with "pgaudit.log=all" present

要删除设置，运行以下代码：

🌐 To remove the settings, execute the following code:

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

对象记录 #

🌐 Object logging

要微调 PGAudit 会记录的对象事件，你必须创建一个权限有限的自定义数据库角色：

🌐 To fine-tune what object events PGAudit will record, you must create a custom database role with limited permissions:

1
create role "some_audit_role" noinherit;

没有其他 Postgres 用户可以通过这个角色登录或使用它。它只是用来安全地定义 PGAudit 会记录什么。

🌐 No other Postgres user can assume or login via this role. It solely exists to securely define what PGAudit will record.

一旦角色创建好，你可以通过将其分配给 pgaudit.role 变量来指示 PGAudit 进行日志记录：

🌐 Once the role is created, you can direct PGAudit to log by assigning it to the pgaudit.role variable:

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

然后你可以分配一个角色，只监控已批准的对象事件，比如包含特定表的 select 语句：

🌐 You can then assign the role to monitor only approved object events, such as select statements that include a specific table:

1
grant select on random_table to "some_audit_role";

有了这个权限，PGAudit 会记录所有引用 random_table 的 select 语句，不管是谁或什么触发了这个事件。所有可分配的权限可以在 Postgres 文档 中查看。

🌐 With this privilege granted, PGAudit will record all select statements that reference the random_table, regardless of who or what initiated the event. All assignable privileges can be viewed in the Postgres documentation.

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

🌐 If you would no longer like to use object logging, you will need to unassign the pgaudit.role variable:

1
-- change pgaudit.role to no longer reference some_audit_role
2
alter role "postgres" set pgaudit.role to '';
3
4
-- view if pgaudit.role changed with the following command:
5
select
6
  rolname,
7
  rolconfig
8
from pg_roles
9
where rolname = 'postgres';
10
-- should return a rolconfig path with "pgaudit.role="

解读审计日志 #

🌐 Interpreting Audit Logs

PGAudit 的设计是将日志以 CSV 文件的形式存储，包含以下标题：

🌐 PGAudit was designed for storing logs as CSV files with the following headers:

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

🌐 A log made from the following create statement:

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

在 仪表板的 Postgres 日志 中生成以下日志：

🌐 Generates the following log in the Dashboard's Postgres Logs:

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

查找和筛选审核日志 #

🌐 Finding and filtering audit logs

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

🌐 Logs generated by PGAudit can be found in Postgres Logs. To find a specific log, you can use the log explorer. Below is a basic example to extract logs referencing CREATE TABLE events

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

实际例子 #

🌐 Practical examples

监控 API 事件 #

🌐 Monitoring API events

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

🌐 To monitor all writes initiated by the PostgREST API roles:

1
alter role "authenticator" set pgaudit.log to 'write';
2
3
-- the above is the practical equivalent to:
4
-- alter role "anon" set pgaudit.log TO 'write';
5
-- alter role "authenticated" set pgaudit.log TO 'write';
6
-- alter role "service_role" set pgaudit.log TO 'write';

监控 auth.users#

🌐 Monitoring the auth.users table

在最糟糕的情况下，如果特权角色的密码被泄露，你可以使用 PGAudit 来监控 auth.users 表是否被针对。需要说明的是，API 请求已经在 API Edge Network 中被监控，这更多是为了更清楚地了解数据库层发生的情况。

🌐 In the worst case scenario, where a privileged roles' password is exposed, you can use PGAudit to monitor if the auth.users table was targeted. It should be stated that API requests are already monitored in the API Edge Network and this is more about providing greater clarity about what is happening at the database level.

记录 auth.user 应该在对象模式下进行，并且需要自定义角色：

🌐 Logging auth.user should be done in Object Mode and requires a custom role:

1
-- create logging role
2
create role "auth_auditor" noinherit;
3
4
-- give role permission to observe relevant table events
5
grant select on auth.users to "auth_auditor";
6
grant delete on auth.users to "auth_auditor";
7
8
-- assign auth_auditor to pgaudit.role
9
alter role "postgres" set pgaudit.role to 'auth_auditor';

使用上面的代码，任何涉及读取或删除 auth.users 表的查询都会被记录下来。

🌐 With the above code, any query involving reading or deleting from the auth.users table will be logged.

最佳实践 #

🌐 Best practices

关闭多余的日志记录 #

🌐 Disabling excess logging

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

🌐 PGAudit, if not configured mindfully, can log all database events, including background tasks. This can generate an undesirably large amount of logs in a few hours.

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

🌐 The first step to solve this problem is to identify which database users PGAudit is observing:

1
-- find all users monitored by pgaudit
2
select
3
  rolname,
4
  rolconfig
5
from pg_roles
6
where
7
  exists (
8
    select
9
      1
10
    from UNNEST(rolconfig) as c
11
    where c like '%pgaudit.role%' or c like '%pgaudit.log%'
12
  );

为了防止 PGAudit 监控那些有问题的角色，你需要把它们的 pgaudit.log 值改成 none，pgaudit.role 值改成 empty quotes ''

🌐 To prevent PGAudit from monitoring the problematic roles, you'll want to change their pgaudit.log values to none and pgaudit.role values to empty quotes ''

1
-- Use to disable object level logging
2
  alter role "<role name>" set pgaudit.role to '';
3
4
  -- Use to disable global and user level logging
5
  alter role "<role name>" set pgaudit.log to 'none';

常见问题 #

🌐 FAQ

使用 PGAudit 调试数据库函数 #

🌐 Using PGAudit to debug database functions

从技术上讲是的，但这不是最好的方法。最好看看我们的函数调试指南。

🌐 Technically yes, but it is not the best approach. It is better to check out our function debugging guide instead.

正在下载数据库日志 #

🌐 Downloading database logs

在 日志仪表板 上，你可以将日志下载为 CSV 文件。

🌐 In the Logs Dashboard you can download logs as CSVs.

记录观察到的表行 #

🌐 Logging observed table rows

默认情况下，PGAudit 会记录查询，但不会记录返回的行。你可以用 pgaudit.log_rows 变量来修改这个行为：

🌐 By default, PGAudit records queries, but not the returned rows. You can modify this behavior with the pgaudit.log_rows variable:

1
--enable
2
alter role "postgres" set pgaudit.log_rows to 'on';
3
4
-- disable
5
alter role "postgres" set pgaudit.log_rows to 'off';

除非你_绝对_确定这对你的使用场景是必要的，否则你不应该这样做。这可能会把原本不该保存的敏感信息暴露到日志里。此外，如果过度使用，还可能明显降低数据库性能。

🌐 You should not do this unless you are absolutely certain it is necessary for your use case. It can expose sensitive values to your logs that ideally should not be preserved. Furthermore, if done in excess, it can noticeably reduce database performance.

记录函数参数 #

🌐 Logging function parameters

我们目前不支持配置 pgaudit.log_parameter，因为如果你正在使用 pgsodium 或 Vault，它可能会在加密列中记录秘密信息。

🌐 We don't currently support configuring pgaudit.log_parameter because it may log secrets in encrypted columns if you are using pgsodium orVault.

如果你希望取消这个限制，可以根据你的使用案例为这个功能请求投票。

🌐 You can upvote this feature request with your use-case if you'd like this restriction lifted.

PGAudit 支持全系统配置吗？ #

🌐 Does PGAudit support system wide configurations?

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

🌐 PGAudit allows settings to be applied to 3 different database scopes:

Supabase 对文件系统和数据库变量的完全权限有限制，这意味着 PGAudit 的修改只能在角色级别进行。将 PGAudit 分配给 postgres 角色几乎可以让它完全看到数据库，因此在角色级别进行调整是比在数据库或系统级别配置更实际的选择。

🌐 Supabase limits full privileges for file system and database variables, meaning PGAudit modifications can only occur at the role level. Assigning PGAudit to the postgres role grants it nearly complete visibility into the database, making role-level adjustments a practical alternative to configuring at the database or system level.

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

🌐 PGAudit's official documentation focuses on system and database level configs, but its docs officially supports role level configs, too.

资源 #

🌐 Resources

• 官方 PGAudit 文档
• 数据库功能日志
• Supabase 日志
• 自托管日志