create-readonly-db-role

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Create a Read-Only DB Role for Agents

为Agent创建只读数据库角色

A pattern used at DeepAPI. A SELECT-only role kills catastrophic writes at the permission level. Residual risks (data leaks, heavy queries) are handled by a denylist and timeouts. Agents stop being blind on prod; the human stops being the SQL bottleneck.

The SQL, timeouts, grants, denylist, RLS setting, role name, and connection steps below are customizable examples for your own system. Adapt them. They are not a copy of any live production setup.

这是DeepAPI采用的一种模式。仅支持SELECT操作的角色从权限层面杜绝了灾难性写入操作。剩余风险（数据泄露、查询负载过高）则通过黑名单和超时机制来处理。Agent无需再对生产环境一无所知；人工也不再是SQL操作的瓶颈。

以下的SQL语句、超时设置、权限授予、黑名单、RLS配置、角色名称及连接步骤均为可自定义的示例，供你适配自身系统使用。请按需调整，它们并非任何实际生产环境的配置副本。

The pattern — 3 layers

模式——三层防护

Hard wall — grants. The role gets SELECT and nothing else. Writes are impossible, not just discouraged.
Denylist, not allowlist. Grant SELECT on ALL current + future tables in
```
public
```
(via default privileges), then revoke tables that hold secrets or PII. Never grant the
```
auth
```
schema. Future tables are auto-readable by design; new sensitive tables need a manual revoke.
Soft guardrails. Example:
```
default_transaction_read_only = on
```
plus a short
```
statement_timeout
```
. Tune both for your workload.

RLS trap: if tables have Row Level Security and no policy mentions the new role, every SELECT returns 0 rows. One common fix is

alter role ... bypassrls

— this only skips row filtering. The SELECT-only grants and denylist still apply. Use it only if it fits your security model.

硬隔离——权限授予：该角色仅拥有SELECT权限，无其他任何权限。写入操作是完全禁止的，而非仅仅不建议。
黑名单而非白名单：授予该角色对
```
public
```
schema下所有现有及未来表的SELECT权限（通过默认权限设置），然后撤销对存储机密或个人身份信息（PII）的表的权限。切勿授予
```
auth
```
schema的权限。默认情况下，未来创建的表会自动对该角色可见；新增的敏感表则需要手动撤销权限。
软防护：示例：设置
```
default_transaction_read_only = on
```
并配置较短的
```
statement_timeout
```
。请根据你的工作负载调整这两项设置。

RLS陷阱：如果表启用了行级安全（Row Level Security，RLS）且没有任何策略提及新角色，那么所有SELECT查询都会返回0行数据。一种常见的解决方法是执行

alter role ... bypassrls

——这仅会跳过行过滤，仅SELECT权限和黑名单规则仍然有效。仅当符合你的安全模型时才使用此方法。

Workflow

工作流程

State-check.
```
select rolname from pg_roles where rolname = 'agent_reader';
```
— if it exists, you are updating, not creating. Replace
```
agent_reader
```
with the role name you choose.
Pick the denylist with the human. Ask which tables hold secrets or PII that agents must never see (credentials, webhook payloads, identity tables).
Write the SQL to a repo file first (e.g.
```
docs/database/create-agent-reader-role.sql
```
) with comments: what / why / how to apply / how to verify / how to revert. Never hand SQL only in chat.
The human applies it — agents never run DDL on prod. Supabase: paste the whole file into the SQL editor, then DELETE the query from editor history (it contains the password). Store the password in a password manager.
Wire the connection string through a protected secret manager or local environment configuration. Never commit it. For a Supabase session pooler, the username is typically
```
<role>.<project-ref>
```
on port 5432. Install
```
psql
```
from your package manager (Homebrew
```
libpq
```
on macOS) if it is missing.
Verify with the loop below.
Write a project-local usage skill so future agents know the key tables, query patterns, and hard rules (read-only forever, never paste PII into commits/docs).

状态检查：执行
```
select rolname from pg_roles where rolname = 'agent_reader';
```
——如果该角色已存在，则你需要进行更新而非创建。将
```
agent_reader
```
替换为你选择的角色名称。
与用户确认黑名单：询问用户哪些表存储了Agent绝对不能访问的机密或PII（如凭证、Webhook负载、身份表）。
先将SQL写入仓库文件（例如
```
docs/database/create-agent-reader-role.sql
```
）并添加注释：内容/用途/应用方式/验证方式/回滚方式。切勿仅在聊天中提供SQL语句。
由人工执行SQL——Agent永远不能在生产环境中执行DDL语句。在Supabase中：将整个文件内容粘贴到SQL编辑器，然后删除编辑器历史中的查询语句（其中包含密码）。将密码存储在密码管理器中。
通过受保护的密钥管理器或本地环境配置传递连接字符串。切勿提交到代码仓库。对于Supabase会话池，用户名通常为
```
<role>.<project-ref>
```
，端口为5432。如果缺少
```
psql
```
，请从包管理器安装（macOS可通过Homebrew安装
```
libpq
```
）。
通过以下循环进行验证。
编写项目本地的使用技能，让后续Agent了解关键表、查询模式及硬性规则（永远只读，切勿将PII粘贴到提交记录/文档中）。

SQL template

SQL模板

sql

-- Example only. Rename the role, timeout, and denylist for your system.

-- 1. role + soft guardrails
create role agent_reader with login password 'REPLACE_ME';
alter role agent_reader set default_transaction_read_only = on;
alter role agent_reader set statement_timeout = '10s';  -- example timeout; change as needed

-- 2. SELECT-only grants, denylist model
grant usage on schema public to agent_reader;
grant select on all tables in schema public to agent_reader;
alter default privileges for role postgres in schema public
  grant select on tables to agent_reader;   -- future tables auto-readable

-- 3. denylist: keep secrets and PII invisible (replace with your own tables)
revoke select on table public.secrets from agent_reader;
revoke select on table public.private_events from agent_reader;

-- 4. only if RLS is enabled, no policy covers this role, and bypass fits your model
alter role agent_reader bypassrls;

Revert:

drop owned by agent_reader; drop role agent_reader;

sql

-- Example only. Rename the role, timeout, and denylist for your system.

-- 1. role + soft guardrails
create role agent_reader with login password 'REPLACE_ME';
alter role agent_reader set default_transaction_read_only = on;
alter role agent_reader set statement_timeout = '10s';  -- example timeout; change as needed

-- 2. SELECT-only grants, denylist model
grant usage on schema public to agent_reader;
grant select on all tables in schema public to agent_reader;
alter default privileges for role postgres in schema public
  grant select on tables to agent_reader;   -- future tables auto-readable

-- 3. denylist: keep secrets and PII invisible (replace with your own tables)
revoke select on table public.secrets from agent_reader;
revoke select on table public.private_events from agent_reader;

-- 4. only if RLS is enabled, no policy covers this role, and bypass fits your model
alter role agent_reader bypassrls;

回滚：

drop owned by agent_reader; drop role agent_reader;

Verification loop (all must pass before declaring done)

验证循环（所有检查必须通过才能完成）

bash

undefined

bash

undefined

Load the connection URL from your secret manager or local environment configuration.

psql "<readonly-connection-url>" -X -c "select current_user;" # -> agent_reader psql "<readonly-connection-url>" -X -c "show statement_timeout;" # -> matches your chosen timeout psql "<readonly-connection-url>" -X -c "select count(*) from public.<big_table>;" # -> real number, NOT 0 psql "<readonly-connection-url>" -X -c "delete from public.<any_table> where false;"

-> ERROR: read-only transaction (soft guardrail)

psql "<readonly-connection-url>" -X -c "begin; set transaction read write; delete from public.<any_table> where false; rollback;"

-> ERROR: permission denied (the hard wall)

psql "<readonly-connection-url>" -X -c "select * from public.<denylisted> limit 1;" # -> ERROR: permission denied psql "<readonly-connection-url>" -X -c "select * from auth.<identity_table> limit 1;" # -> ERROR: permission denied


Writes must be blocked **twice over**: once by the read-only guardrail, and again by `permission denied` with the guardrail off. If any check fails, fix the grants and re-run ALL checks.


写入操作必须被**双重拦截**：一次是通过只读防护，另一次是在关闭防护后触发`permission denied`错误。如果任何检查失败，请修复权限设置并重新运行所有检查。

Failure modes

故障模式

Every table returns 0 rows → RLS is enabled and the role has no policy → consider
```
bypassrls
```
(step 4 of template) only if it fits your model.
A write succeeded during verification → grants are wrong. Stop, revoke everything, re-run the template.
Supabase auth failed → pooler username is usually
```
<role>.<project-ref>
```
, not the bare role name.
statement timeout
on legit queries → query too heavy; add filters/limits. Do not raise the timeout as a first resort.

所有表返回0行数据 → 启用了RLS且该角色无对应策略 → 仅当符合你的安全模型时，才考虑使用模板中的
```
bypassrls
```
（步骤4）。
验证过程中写入操作成功 → 权限设置错误。立即停止操作，撤销所有权限，重新运行模板。
Supabase认证失败 → 池化用户名通常为
```
<role>.<project-ref>
```
，而非单纯的角色名称。
合法查询触发
statement timeout
→ 查询负载过高；添加过滤条件/限制条数。切勿首先尝试提高超时时间。

Maintenance

维护

New sensitive table → add a
```
revoke select
```
next to the denylist block.
Rotate password:
```
alter role agent_reader with password '...'
```
then update the secret in your secret manager or local environment configuration.
Never let agents write through this role. Prod writes stay human-only.

新增敏感表 → 在黑名单代码块中添加
```
revoke select
```
语句。
轮换密码：执行
```
alter role agent_reader with password '...'
```
，然后更新密钥管理器或本地环境配置中的密钥。
永远不要让Agent通过该角色执行写入操作。生产环境的写入操作必须仅由人工完成。