PostgreSQL 17.2.1中pg_dump --schema-only所需最小权限咨询

1. 创建专用只读角色 #

先创建带登录权限的只读角色，示例角色名为readonly_user：

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'your_secure_password';

2. 授予数据库连接权限 #

必须先给角色授予目标数据库的CONNECT权限，否则无法登录：

GRANT CONNECT ON DATABASE your_database TO readonly_user;

3. 单个Schema的只读权限配置 #

假设目标Schema为target_schema，需完成三步配置：

• 授予Schema的USAGE权限（允许访问Schema内的对象）
• 授予Schema内现有对象的只读权限
• 配置默认权限，确保未来新增对象自动获得只读权限

执行以下SQL：

-- 授予Schema的USAGE权限
GRANT USAGE ON SCHEMA target_schema TO readonly_user;

-- 授予现有表、视图的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA target_schema TO readonly_user;

-- 授予现有序列的USAGE权限（若表含自增字段，需此权限查看序列值）
GRANT USAGE ON ALL SEQUENCES IN SCHEMA target_schema TO readonly_user;

-- 配置默认权限：未来在该Schema创建的表、视图自动赋予SELECT权限
ALTER DEFAULT PRIVILEGES IN SCHEMA target_schema
GRANT SELECT ON TABLES TO readonly_user;

-- 配置默认权限：未来创建的序列自动赋予USAGE权限
ALTER DEFAULT PRIVILEGES IN SCHEMA target_schema
GRANT USAGE ON SEQUENCES TO readonly_user;

4. 所有Schema的只读权限配置 #

若需角色拥有数据库内所有现有及未来创建的Schema的只读权限，执行以下操作：

-- 授予所有现有Schema的USAGE权限
GRANT USAGE ON ALL SCHEMAS IN DATABASE your_database TO readonly_user;

-- 授予所有现有表、视图的SELECT权限
GRANT SELECT ON ALL TABLES IN ALL SCHEMAS IN DATABASE your_database TO readonly_user;

-- 授予所有现有序列的USAGE权限
GRANT USAGE ON ALL SEQUENCES IN ALL SCHEMAS IN DATABASE your_database TO readonly_user;

-- 设置全局默认权限：未来创建的Schema自动赋予USAGE权限
ALTER DEFAULT PRIVILEGES
GRANT USAGE ON SCHEMAS TO readonly_user;

-- 设置全局默认权限：未来创建的表、视图自动赋予SELECT权限
ALTER DEFAULT PRIVILEGES
GRANT SELECT ON TABLES TO readonly_user;

-- 设置全局默认权限：未来创建的序列自动赋予USAGE权限
ALTER DEFAULT PRIVILEGES
GRANT USAGE ON SEQUENCES TO readonly_user;

注意：全局默认权限仅对执行ALTER DEFAULT PRIVILEGES的用户创建的对象生效。若有其他用户创建Schema/对象，需单独给这些用户配置对应默认权限，示例：

ALTER DEFAULT PRIVILEGES FOR ROLE other_user
GRANT USAGE ON SCHEMAS TO readonly_user;

5. 默认权限的作用 #

默认权限是PostgreSQL的自动授权机制，避免每次新增对象后手动配置权限。若未配置默认权限，角色仅能访问权限配置时已存在的对象，后续新增的对象会无访问权限。

6. pg_dump --schema-only 所需权限 #

执行pg_dump --schema-only需以下权限：

• 目标数据库的CONNECT权限（已授予）
• 需导出的所有Schema的USAGE权限
• Schema内所有对象（表、视图、序列、函数等）的SELECT/USAGE权限（前面的配置已覆盖）
• pg_catalogSchema的USAGE权限（默认已有，若缺失需手动授予）：

GRANT USAGE ON SCHEMA pg_catalog TO readonly_user;

关于锁定：pg_dump --schema-only仅读取对象元数据，只会在读取瞬间加共享锁，不会长期锁定数据库，不影响正常业务操作。

权限验证 #

切换到只读角色，执行以下命令验证权限是否生效：

SET ROLE readonly_user;
-- 查询目标Schema的表
SELECT * FROM target_schema.your_table LIMIT 1;
-- 查看可访问的Schema列表
SELECT schema_name FROM information_schema.schemata;

内容的提问来源于stack exchange，提问作者Lethargos