Custom role inherits privileges that were not explicitly granted

你创建的角色可以在没有明确授权的情况下使用对象。例如，它可以读取 net 模式下的表，创建临时表，或者创建大对象。对这些对象的权限检查如 has_table_privilege 会返回 true。当你以 postgres 执行一些撤销操作时，它们会打印 WARNING: no privileges could be revoked，但实际上不会改变任何东西。

🌐 A role you create can use objects without an explicit grant. For example, it can read tables in the net schema, create temporary tables, or create large objects. Privilege checks such as has_table_privilege return true for these objects. When you run some revokes as postgres, they print WARNING: no privileges could be revoked and change nothing.

这个角色从 PUBLIC 获取这些权限，PUBLIC 代表数据库中的每个角色，包括你以后创建的角色。

🌐 The role receives these privileges from PUBLIC, which stands for every role in the database, including roles you create later.

你创建的用于限制某个服务或某个人的角色可能因此做得比你预期的更多。当启用 pg_net 时，任何可以连接到你数据库的角色都可以读取 pg_net 队列中的请求头，包括 API 密钥。它也可以自己队列 HTTP 请求。任何角色都可以通过创建临时表和大对象来使用磁盘空间。

🌐 A role you create to limit a service or a person can therefore do more than you intended. When pg_net is enabled, any role that can connect to your database can read the request headers that pg_net queues, including API keys. It can also queue HTTP requests of its own. Any role can use disk space by creating temporary tables and large objects.

找出你没授予的权限 #

🌐 Find privileges you didn't grant

要查找你没有授予的权限，可以列出角色可以访问的内容，然后与自己授予的权限进行对比。以下查询检查模式 usage 以及它们指定的表和序列权限。它们不会检查列级别的权限或函数上的 execute。要列出角色可以使用的模式，替换 app_svc 并在 SQL 编辑器 中运行以下内容：

🌐 To find privileges you didn't grant, list what the role can access and compare it with your own grants. The following queries check schema usage and the table and sequence privileges they name. They don't check column-level grants or execute on functions. To list the schemas a role can use, replace app_svc and run the following in the SQL Editor:

1
select nspname as schema
2
from pg_namespace
3
where
4
  has_schema_privilege('app_svc', oid, 'usage')
5
  and nspname not in ('pg_catalog', 'information_schema')
6
  and nspname not like 'pg\_%'
7
order by 1;

要列出该角色可以在这些模式中读取或更改的表、视图和序列，运行：

🌐 To list the tables, views, and sequences the role can read or change in those schemas, run:

1
select
2
  n.nspname as schema,
3
  c.relname as object,
4
  string_agg(p.privilege, ', ' order by p.privilege) as privileges
5
from pg_class c
6
join pg_namespace n on n.oid = c.relnamespace
7
cross join unnest(
8
  array['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'USAGE']
9
) as p(privilege)
10
where c.relkind in ('r', 'p', 'v', 'm', 'f', 'S')
11
  and n.nspname not in ('pg_catalog', 'information_schema')
12
  and has_schema_privilege('app_svc', n.oid, 'usage')
13
  and case
14
    when c.relkind = 'S' then
15
      case
16
        when p.privilege in ('USAGE', 'SELECT', 'UPDATE')
17
          then has_sequence_privilege('app_svc', c.oid, p.privilege)
18
        else false
19
      end
20
    else
21
      case
22
        when p.privilege = 'USAGE' then false
23
        else has_table_privilege('app_svc', c.oid, p.privilege)
24
      end
25
  end
26
group by 1, 2
27
order by 1, 2;

这些结果是该角色的有效权限。你没有直接授予的访问权限可能来自 PUBLIC、它所加入的角色，或者是因为拥有该对象。要查看谁对某个对象拥有权限，可以检查它的访问控制列表：

🌐 The results are the role's effective privileges. Access you didn't grant directly can come from PUBLIC, from a role it's a member of, or from owning the object. To see who holds privileges on an object, check its access control list:

1
select relacl from pg_class where oid = 'net.http_request_queue'::regclass;

以 = 开头的条目，例如 =arwdDxtm/supabase_admin，是对 PUBLIC 的补助。

🌐 An entry that starts with =, such as =arwdDxtm/supabase_admin, is a grant to PUBLIC.

每个角色从公共获取的内容 #

🌐 What every role receives from PUBLIC

一个角色除了拥有自身的权限外，还拥有授予 PUBLIC 的所有权限。noinherit 属性不会影响授予 PUBLIC 的权限，因为 PUBLIC 并不是角色成员。

🌐 A role holds everything granted to PUBLIC in addition to its own grants. The noinherit attribute doesn't affect privileges granted to PUBLIC, because PUBLIC isn't a role membership.

在 Supabase 项目中，一个你创建的角色在启用 pg_net 扩展时，可以使用 public 模式、系统目录以及 net 模式。其他 Supabase 模式，比如 auth、storage、extensions、vault 和 cron，不会授予 usage 给 PUBLIC。

🌐 On a Supabase project, a role you create can use the public schema, the system catalogs, and the net schema when the pg_net extension is enabled. Other Supabase schemas, such as auth, storage, extensions, vault, and cron, don't grant usage to PUBLIC.

下表列出了 PUBLIC 拥有什么，以及 postgres 角色是否可以撤销它。

🌐 The following table lists what PUBLIC holds and whether the postgres role can revoke it.

postgres 角色拥有 postgres 数据库，并通过它拥有 public 模式，因此它可以撤销这些对象上的 PUBLIC 授权。Supabase 角色拥有其他对象，所以对它们执行撤销会显示 no privileges could be revoked 警告。关于 pg_net 授权，请参见 撤销对 pg_net 对象的访问无效。

🌐 The postgres role owns the postgres database, and through it the public schema, so it can revoke the PUBLIC grants on those. Supabase roles own the other objects, so a revoke on them prints the no privileges could be revoked warning. For the pg_net grants, see Revoking access to pg_net objects has no effect.

你启用的扩展可能会给 PUBLIC 提供更多权限。启用扩展后，再次检查角色。

🌐 Extensions you enable can grant more privileges to PUBLIC. After you enable an extension, check the role again.

撤销你拥有的对象上的公共权限 #

🌐 Revoke PUBLIC privileges on objects you own

你可以撤销在 postgres 数据库、public 模式以及你创建的函数上的 PUBLIC 授权。

🌐 You can revoke the PUBLIC grants on the public schema, on the postgres database, and on functions you create.

你创建的函数 #

🌐 Functions you create

要阻止 PUBLIC 运行 postgres 在更改后创建的函数，可以在不指定模式的情况下更新默认权限：

🌐 To stop PUBLIC from running functions that postgres creates after the change, update the default privileges without naming a schema:

1
alter default privileges for role postgres
2
  revoke execute on functions from public;

一个带有 in schema 的默认权限集不能移除函数的 PUBLIC 默认权限，因为 Postgres 会把模式的默认权限加到全局权限上。

🌐 A default privilege set with in schema can't remove the PUBLIC default for functions, because Postgres adds schema defaults to the global ones.

Supabase 的默认设置也会授予 execute 给 anon、authenticated 和 service_role，作用于 postgres 在 public 创建的函数。这些角色将保留它们的访问权限。如果你修改了这些默认设置，或者以其他角色创建函数，需要将 execute 授予需要它的 API 角色。

🌐 The Supabase defaults also grant execute to anon, authenticated, and service_role on functions that postgres creates in public. Those roles keep their access. If you changed those defaults, or create functions as another role, grant execute to the API roles that need it.

要查看函数的默认权限，请运行：

🌐 To check the default privileges for functions, run:

1
select defaclrole::regrole as owner, defaclnamespace::regnamespace as schema, defaclacl
2
from pg_default_acl
3
where defaclobjtype = 'f';

其他模式中的函数不会获得那些权限。如果行级安全策略调用了 public 之外的辅助函数，就把 execute 授权给该策略适用的角色：

🌐 Functions in other schemas don't get those grants. If a Row Level Security policy calls a helper function outside public, grant execute on it to the roles the policy applies to:

1
grant execute on function private.is_admin() to authenticated;

要从已经存在的函数中撤销 PUBLIC 对 execute 的权限，运行：

🌐 To revoke execute from PUBLIC on a function that already exists, run:

1
revoke execute on function public.my_function() from public;

公共模式 #

🌐 The public schema

要阻止你创建的角色在没有授权的情况下使用 public 模式，请执行：

🌐 To stop roles you create from using the public schema without a grant, run:

1
revoke usage on schema public from public;

postgres、anon、authenticated 和 service_role 角色保留各自的 usage 授权，因此数据 API 仍然能正常工作。其他角色将失去对该模式的访问权限，除非他们有自己的授权，所以需要给每个需要的角色授予 usage。在将更改应用到生产环境之前，先在 预览分支 上测试一下。

🌐 The postgres, anon, authenticated, and service_role roles keep their own usage grants, so the Data API keeps working. Other roles lose access to the schema unless they have their own grant, so grant usage to each role you create that needs it. Test the change on a preview branch before you apply it to production.

临时表 #

🌐 Temporary tables

要只允许你授予 temporary 的角色使用临时表，执行:

🌐 To allow temporary tables only for roles that you grant temporary to, run:

1
revoke temporary on database postgres from public;

把 temporary 授权回每个需要它的角色。如果你的数据 API 功能创建临时表，也要把它授权给 authenticated。在预览分支上也测试一下这个更改。

🌐 Grant temporary back to each role that needs it. If your Data API functions create temporary tables, also grant it to authenticated. Test this change on a preview branch too.

确认更改 #

🌐 Confirm the change

要确认角色失去了你撤销的权限，替换 app_svc 和 public.my_function() 后运行：

🌐 To confirm that the role lost the privileges you revoked, replace app_svc and public.my_function() and run:

1
select
2
  has_schema_privilege('app_svc', 'public', 'usage') as can_use_public_schema,
3
  has_database_privilege('app_svc', 'postgres', 'temporary') as can_create_temp_tables,
4
  has_function_privilege('app_svc', 'public.my_function()', 'execute') as can_run_my_function;

每一列都会返回 false，除非该角色仍然通过它自己授予的权限或它所属的角色获得该权限。

🌐 Each column returns false unless the role still receives the privilege from its own grant or from a role it's a member of.

只授予服务所需的权限 #

🌐 Grant only what a service needs

给每个服务分配自己的角色，并且只授权它使用的模式和表。下面的示例创建了一个可以在 app 模式中读取和添加订单的角色：

🌐 Give each service its own role, and grant only the schemas and tables it uses. The following example creates a role that can read and add orders in an app schema:

1
create role app_svc login password '<strong-password>';
2
grant usage on schema app to app_svc;
3
grant select, insert on app.orders to app_svc;
4
grant usage on sequence app.orders_id_seq to app_svc;

你创建的角色在它不拥有的表上会受到行级安全的限制，除非你给它 bypassrls 属性。表的所有者会绕过它的策略，除非你运行 alter table <table_name> force row level security。

不要授予该角色在 postgres 或其他 Supabase 角色的成员资格，因为成员角色会继承其所有权限。有关角色的更多信息，请参见 Postgres 角色。

🌐 Don't grant the role membership in postgres or in other Supabase roles, because a member role inherits all of their privileges. For more information about roles, see Postgres Roles.

不会限制某个角色的设置 #

🌐 Settings that don't restrict a role

有些设置看起来像限制，但一个会话可以更改它们。使用权限来限制角色。

🌐 Some settings look like restrictions, but a session can change them. Use grants to restrict a role.

• default_transaction_read_only：一个会话可以先运行 set default_transaction_read_only = off 然后再写入。只用它来防止意外写入。
• search_path：一个会话可以设置自己的搜索路径，或者使用带模式的名字。