如何为GCP Cloud SQL PostgreSQL新用户授予SELECT权限

1 先修复GRANT时提示database not found的问题 #

• 执行所有授权操作前必须先连接到目标业务数据库，PostgreSQL不支持在默认postgres系统库下跨库授权其他数据库的权限
• 若数据库名包含大写字母、特殊符号，授权时需要给库名加双引号匹配大小写，示例：GRANT CONNECT ON DATABASE "OrderDB" TO test_user;

2 完整授权流程 #

请使用GCP Cloud SQL默认的postgres高权限账号，连接到目标业务库后依次执行以下命令：

• 授予数据库连接权限

GRANT CONNECT ON DATABASE 替换为你的数据库名 TO 替换为新建的用户名;

• 授予schema使用权限，默认业务表都存在public schema，使用自定义schema时替换为对应名称即可

GRANT USAGE ON SCHEMA public TO 替换为新建的用户名;

• 授予schema下所有现有表的SELECT权限

GRANT SELECT ON ALL TABLES IN SCHEMA public TO 替换为新建的用户名;

• 配置默认权限，保证后续新建的表自动对该用户开放SELECT权限，无需重复授权

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 替换为新建的用户名;

3 注意事项 #

• GRANT ALL PRIVILEGES ON DATABASE仅授予数据库级的连接、临时表创建等权限，不会自动覆盖schema和表的访问权限，这是PostgreSQL的标准权限规则，不属于GCP的特殊限制
• GCP Cloud SQL for PostgreSQL的所有用户默认无superuser权限，无需执行需要superuser权限的授权命令
• 如果使用多个自定义schema，需要对每个用到的schema单独执行上述schema级、表级授权命令
• 授权完成后无需重启实例，新建用户重新登录即可生效

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