PostgreSQL热备服务器创建只读用户的解决方案咨询

这个问题我在运维PostgreSQL集群时碰到过好多次，热备库处于hot standby模式时确实没法执行任何写操作（包括创建用户、修改权限），核心解决方案很明确：所有用户创建和权限配置必须在主库完成，然后通过流复制自动同步到备库。具体步骤拆解如下：

1. 连接主库创建带密码的只读角色 #

先登录到你的PostgreSQL主库，用DB owner或者超级用户角色执行命令：

-- 创建带登录权限和密码的用户（如果不需要登录权限可以用CREATE ROLE）
CREATE USER readonly_user WITH LOGIN PASSWORD 'your_strong_password_here';

注意：记得把readonly_user改成你想要的用户名，your_strong_password_here替换成安全的强密码，别用示例里的弱密码。

2. 配置只读权限 #

接下来给这个用户配置只读所需的权限，分两种场景处理：

针对单个数据库的只读权限 #

假设目标数据库是your_target_db，先切换到该库：

\c your_target_db

然后依次执行以下权限配置：

-- 赋予连接数据库的权限
GRANT CONNECT ON DATABASE your_target_db TO readonly_user;

-- 赋予使用schema的权限（这里以public为例，其他自定义schema请替换）
GRANT USAGE ON SCHEMA public TO readonly_user;

-- 赋予现有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- 赋予现有序列的SELECT权限（部分查询会依赖序列，比如带serial字段的表）
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

-- 设置默认权限：未来创建的表自动赋予SELECT权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

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

全局只读权限（所有数据库） #

如果需要让该用户能访问所有数据库，可以执行：

-- 赋予所有数据库的连接权限（仅超级用户可执行）
GRANT CONNECT ON DATABASES TO readonly_user;

-- 对每个自定义schema，重复上述USAGE、SELECT和默认权限的配置

3. 确认主备同步完成 #

主库操作完后，等PostgreSQL的流复制把这些变更同步到备库。你可以在备库执行以下命令检查同步状态：

SELECT pg_is_in_recovery(), sync_state FROM pg_stat_replication;

当pg_is_in_recovery()返回true（确认是备库），且sync_state为sync或quorum时，说明同步正常，备库已经拿到了新用户和权限配置。

4. 测试备库连接 #

现在就可以用新创建的readonly_user连接热备库了，验证权限是否符合要求：

-- 正常查询应该能返回结果
SELECT * FROM public.your_table LIMIT 10;

-- 尝试写操作应该报错（符合只读预期）
INSERT INTO public.your_table VALUES ('test');

注意事项 #

• 如果备库存在同步延迟，得等延迟消除后再测试，否则可能看不到新创建的用户。
• 若数据库用了多个自定义schema，需要为每个schema重复配置USAGE、SELECT和默认权限。
• 绝对不要在备库执行任何写操作，包括修改用户密码，所有变更都必须在主库完成后同步到备库。

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