Database API 42501 errors

Postgres 42501 错误，客户常报告为 401 或 403 错误，意味着请求缺少足够的权限。可以在 日志浏览器 中通过运行以下命令查看：

1
select
2
  cast(postgres_logs.timestamp as datetime) as timestamp,
3
  event_message,
4
  parsed.error_severity,
5
  parsed.user_name,
6
  parsed.query,
7
  parsed.detail,
8
  parsed.hint
9
from
10
  postgres_logs
11
  cross join unnest(metadata) as metadata
12
  cross join unnest(metadata.parsed) as parsed
13
where
14
  regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
15
  and parsed.sql_state_code = '42501'
16
order by timestamp desc
17
limit 100;

它们通常是由以下某个因素引起的。

🌐 They tend to be caused by one of the following factors.

尝试访问被禁止的模式 #

🌐 Attempted to access a forbidden schema

API 角色无法访问某些模式，尤其是 auth 和 vault。这个限制也适用于依赖 vault 的外部数据封装器。虽然你可以通过使用 安全定义者函数 来绕过，但这些模式出于安全原因被故意限制。

🌐 API roles cannot access certain schemas, most notably auth and vault. This restriction extends to Foreign Data Wrappers relying on vault. While you can bypass it using a security definer function, these schemas are intentionally restricted for security reasons.

尝试访问自定义模式 #

🌐 Attempted to access a custom schema

如果你创建了自定义模式，你需要给数据库 API 查询它的权限。参考我们的使用自定义模式指南获取更多说明。

🌐 If you created a custom schema, you will have to give the Database API permission to query it. Follow our Using Custom Schemas guide for more directions.

缺少表级权限 #

🌐 Missing table-level privileges

如果你看到像 permission denied for table your_table 这样的错误，查询角色可能没有执行该操作所需的权限。

🌐 If you see an error like permission denied for table your_table, the querying role may not have the required privilege for the operation.

默认情况下，public 模式中的表会将 SELECT、INSERT、UPDATE 和 DELETE 权限授予 anon 和 authenticated 角色。不过，你可以在仪表板的 集成 > 数据 API 部分或者通过 SQL 改变这些权限。

🌐 By default, tables in the public schema are granted SELECT, INSERT, UPDATE, and DELETE to the anon and authenticated roles. However, you can change these privileges in the Integrations > Data API section of the Dashboard or via SQL.

要查看表上的当前权限：

🌐 To check the current privileges on a table:

1
select grantee, privilege_type
2
from information_schema.role_table_grants
3
where table_name = 'your_table';

授予某个角色特定权限：

🌐 To grant a specific privilege to a role:

1
grant select on table public.your_table to anon;

授予所有权限：

🌐 To grant all privileges:

1
grant select, insert, update, delete on table public.your_table to anon, authenticated;

已配置列级限制 #

🌐 Configured column-level restrictions

如果你在 Dashboard 中或通过 SQL 设置了基于列的访问权限，访问受限制的列时查询会报 42501 错误。这也包括使用 select *，因为它会扩展包含被禁止的列。

🌐 If you've set column-based access in the Dashboard or via SQL, queries will fail with a 42501 error when accessing restricted columns. This includes using select *, as it expands to include forbidden columns.

RLS： #

🌐 RLS:

如果匿名用户或已认证用户尝试在没有必要的行级安全权限的情况下进行 UPDATE 或 INSERT，Postgres 会返回 42501 错误。

🌐 If the anon or authenticated roles attempt to UPDATE or INSERT values without the necessary RLS permissions, Postgres will return a 42501 error.