DigitalOcean PostgreSQL创建只读用户遇权限异常问题排查

1. 只读用户能创建表的原因及解决 #

从你提供的\dn+输出可以看到，public schema的权限设置中，PUBLIC角色（所有用户默认继承该角色）拥有UC（USAGE + CREATE）权限，这就是只读用户能在public schema建表的核心原因。

你之前执行REVOKE CREATE ON SCHEMA public FROM PUBLIC;失败，是因为操作的用户（USER_A）不是超级用户。DigitalOcean托管的PostgreSQL中，只有postgres或带有Superuser属性的角色（比如你输出中的USER_B）才能执行这个撤销操作。

解决步骤：

• 使用超级用户（如postgres）登录到目标数据库（比如production）
• 执行：

  REVOKE CREATE ON SCHEMA public FROM PUBLIC;
  

• 对每个需要限制的数据库（staging等）重复上述操作

另外，你的初始SQL中用的是mydatabase，但实际目标数据库是production，要确保权限操作是针对实际使用的数据库执行的。

2. 无法查看其他用户表的原因及解决 #

出现这个问题通常有几个原因：

• 权限范围不全：你的production_ro角色只被授予了指定schema的SELECT权限，如果其他用户的表在public或其他schema中，需要给readonly角色添加对应schema的权限：

  -- 以public schema为例
  GRANT USAGE ON SCHEMA public TO production_ro;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO production_ro;
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO production_ro;
  

• 默认权限仅对后续创建的表生效：ALTER DEFAULT PRIVILEGES只会影响执行该命令后创建的表，之前已存在的表需要手动补授权限：

  GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO production_ro;
  

• 表所有者的默认权限未配置：如果其他用户创建表，需要该用户设置默认权限给readonly角色，否则新表不会自动对readonly开放权限。比如表所有者是app_user1，需要执行：

  ALTER DEFAULT PRIVILEGES FOR ROLE app_user1 IN SCHEMA myschema GRANT SELECT ON TABLES TO production_ro;
  

3. 用户可随意切换数据库的原因及解决 #

这是因为PUBLIC角色默认拥有所有数据库的CONNECT权限，即使你给特定角色授予了指定数据库的权限，用户依然能通过PUBLIC角色的权限切换到其他数据库。

解决步骤：

• 使用超级用户登录到每个目标数据库（如production）
• 撤销PUBLIC角色的CONNECT权限：

  REVOKE CONNECT ON DATABASE production FROM PUBLIC;
  

• 只给对应角色授予CONNECT权限：

  GRANT CONNECT ON DATABASE production TO production_ro;
  GRANT CONNECT ON DATABASE production TO production_rw;
  

• 对staging等其他数据库重复上述操作

额外注意事项 #

• 每个数据库（production、staging）都需要独立配置对应的ro/rw角色，不要用通用的readonly/readwrite角色，避免权限混淆
• 操作权限时，确保在目标数据库内部执行对应的GRANT/REVOKE命令，否则设置不会生效
• 定期检查角色权限，使用\dp查看表权限，\dn+查看schema权限，\l+查看数据库权限

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