Identifying Dashboard SQL Editor Activity by User

Last edited: 8/12/2026

当团队成员在仪表板 SQL 编辑器中运行 SQL 查询时，如果这些查询被记录在 Postgres 日志中，谁执行了哪个查询并不立即清楚。本指南将告诉你如何将查询追溯到执行它的具体团队成员。

🌐 When team members run SQL queries from the Dashboard SQL Editor, and if that query is logged in the Postgres Logs, it's not immediately clear who executed which query. This guide shows you how to track queries back to the specific team member who ran them.

了解仪表板查询执行#

🌐 Understanding Dashboard query execution

首先，理解仪表板查询是如何执行的会很有帮助。当有人从 SQL 编辑器运行查询时，它会通过数据库级别的 postgres 角色进行路由。Supabase 仪表板会自动在查询中添加元数据注释，具体是 -- user: [UUID]、-- source: dashboard 和 -- date。

🌐 First, it helps to understand how Dashboard queries are executed. When someone runs a query from the SQL editor, it's routed through the postgres role at the database level. The Supabase Dashboard automatically appends metadata comments to queries, specifically -- user: [UUID], -- source: dashboard, and -- date.

默认情况下，该角色的 log_statement 设置为 ddl，这意味着 Postgres 只记录模式级别的更改，例如 CREATE、ALTER 和 DROP。它不会记录修改数据的语句，例如 INSERT、UPDATE、DELETE 或 TRUNCATE。

🌐 By default, that role has log_statement set to ddl, which means Postgres logs only schema-level changes such as CREATE, ALTER, and DROP. It does not log data-modifying statements such as INSERT, UPDATE, DELETE or TRUNCATE.

所以，如果有人截断了一个表，而你依赖默认的日志记录，你是看不到的。

🌐 So, if someone truncates a table, and you're relying on the default logging, you won't see it.

启用数据修改日志#

🌐 Enabling data modification logging

要让这些操作可见，你可以提高 postgres 角色的日志级别：

🌐 To make those operations visible, you can increase the logging level for the postgres role:

1
ALTER ROLE postgres SET log_statement='mod';

注意： 这一步只有在你需要跟踪像 INSERT、UPDATE、DELETE 或 TRUNCATE 这样的数据修改语句时才必要。如果你只是想跟踪 DDL 语句（比如 CREATE、ALTER、DROP），默认的 log_statement='ddl' 设置已经足够了。

将它设置为 mod 会让 Postgres 记录所有修改数据的语句。一旦设置好了，可以尝试从仪表板运行类似 TRUNCATE 的操作。在日志中，你会看到类似这样的条目：

🌐 Setting it to mod tells Postgres to log all data-modifying statements. Once that's in place, try running something like a TRUNCATE from the Dashboard. In the logs, you'll see an entry similar to:

1
statement: TRUNCATE TABLE public.data;
2
-- source: dashboard
3
-- user: f8c2e1a9-3b4d-4f7e-8c9a-1d2e3f4a5b6c
4
-- date: 2026-04-02T11:41:22.158Z

注意日志中包含：

🌐 Notice that the log includes:

完整声明
时间戳
user 字段，即 Supabase 用户的 UUID
来源（仪表板）

那个 UUID 对应的是通过 Supabase 仪表板登录并在 SQL 编辑器中执行查询的团队成员。但此时，它只是一个 ID——还不是名字或邮箱。

🌐 That UUID corresponds to the team member who logged in via the Supabase Dashboard and executed queries in the SQL Editor. But at this point, it's only an ID - not yet a name or email.

将 UUID 映射到团队成员#

🌐 Mapping UUIDs to team members

要映射 UUID，你需要查询管理 API。流程是这样的：

🌐 To map the UUID, you'll need to query the Management API. The process looks like this:

1. 创建个人访问令牌 (PAT)

从你的账户设置生成一个令牌。

🌐 Generate a token from your account settings.

2. 调用组织成员端点

1
curl -X GET "https://api.supabase.com/v1/organizations/your-org-slug/members" \
2
  -H "Authorization: Bearer YOUR_PERSONAL_ACCESS_TOKEN"

3. 匹配 UUID

回应将包括类似这样的条目：

🌐 The response will include entries like:

1
{
2
  "user_id": "f8c2e1a9-3b4d-4f7e-8c9a-1d2e3f4a5b6c",
3
  "user_name": "john@supabase.io",
4
  "email": "john@supabase.io",
5
  "role_name": "Administrator"
6
}

现在你可以直接将 Postgres 日志中的 user_id 值与对应的团队成员匹配起来。

🌐 Now you can directly match user_id values from the Postgres logs to the corresponding team members.

查询特定操作的日志#

🌐 Querying logs for specific operations

导航到 Logs Explorer 并查询 postgres_logs。这里有一个示例查询，用于搜索数据修改操作并将用户 ID 映射到团队成员：

🌐 Navigate to the Logs Explorer and query postgres_logs. Here's an example query that searches for data-modifying operations and maps user IDs to team members:

1
SELECT
2
  DATETIME(postgres_logs.timestamp) AS time,
3
  parsed.session_id,
4
  postgres_logs.identifier,
5
  parsed.user_name AS db_role,
6
  CASE
7
    WHEN REGEXP_CONTAINS(postgres_logs.event_message, 'f8c2e1a9-3b4d-4f7e-8c9a-1d2e3f4a5b6c')
8
      THEN 'john@example.com'
9
    WHEN REGEXP_CONTAINS(postgres_logs.event_message, 'insert another-uuid-here')
10
      THEN 'jane@example.io'
11
    ELSE 'unknown'
12
  END AS detected_user,
13
  parsed.error_severity,
14
  postgres_logs.event_message
15
FROM postgres_logs
16
CROSS JOIN UNNEST(metadata) AS metadata
17
CROSS JOIN UNNEST(parsed) AS parsed
18
WHERE postgres_logs.timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
19
  AND (
20
    REGEXP_CONTAINS(postgres_logs.event_message, '(?i)DELETE|TRUNCATE|UPDATE|ALTER|DROP')
21
    OR REGEXP_CONTAINS(parsed.query, '(?i)DELETE|TRUNCATE|UPDATE|ALTER|DROP')
22
  )
23
ORDER BY postgres_logs.timestamp DESC
24
LIMIT 500;

这个查询：

🌐 This query:

搜索最近7天的日志
常见数据修改操作的过滤器（DELETE、TRUNCATE、UPDATE、ALTER、DROP）
使用 CASE 语句将已知的 UUID 映射到团队成员的邮箱
按时间戳排序返回结果（最新的在前）

示例输出：

数据库角色	检测到的用户	错误严重性	事件消息	标识符

你可以通过筛选特定命令来进一步优化你的搜索，比如 TRUNCATE 或 DELETE，其中 parsed.user_name = 'postgres'。

🌐 You can further refine your search by filtering for specific commands like TRUNCATE or DELETE where parsed.user_name = 'postgres'.

跟踪外部工具#

🌐 Tracking external tools

对于像 n8n 或其他连接到你数据库的外部工具，你可以通过在连接字符串后加上 ?application_name=example_app_name 来标识数据库更改的来源。这样可以确保在日志中清楚地标识来源，更容易区分仪表板操作和外部工具操作。

🌐 For external tools like n8n or other applications connecting to your database, you can identify the source of database changes by appending ?application_name=example_app_name to your connection string. This ensures the source is clearly identified in the logs, making it easier to distinguish between Dashboard operations and external tool operations.

额外的日志级别#

🌐 Additional logging levels

Postgres 支持这些 log_statement 值：

🌐 Postgres supports these log_statement values:

none：没有记录任何语句
ddl：日志数据定义语句（CREATE、ALTER、DROP）——这是 postgres 角色的默认设置
mod：日志数据修改语句加上所有DDL（包括 INSERT、UPDATE、DELETE、TRUNCATE）
all：记录所有语句（包括 SELECT 查询）

注意： 将设置为 all 可能会生成非常大的日志量。仅在必要时并且限时使用。在你的具体环境中测试 log_statement='mod' 的性能影响，因为影响取决于你的查询量和工作负载。