GCP Cloud SQL Postgres 13 如何撤销用户对指定数据库的访问权限

问题根因 #

1. 你之前执行的权限撤销语句目标数据库是gitlabhq_production，不是需要限制的main_db/test_db，完全没有作用在目标对象上。
2. PostgreSQL默认会给PUBLIC角色（所有用户默认都属于该角色）授予所有新建数据库的CONNECT、TEMP权限，同时默认public schema也会给PUBLIC角色开放USAGE、CREATE权限，这就是用户跨库访问的核心原因。
3. 你只做了数据库级别的权限撤销，没有处理schema、库内现有对象、未来新建对象的权限配置。

操作步骤 #

第一步：配置main_db权限隔离

用main_user连接到main_db，依次执行以下语句：

-- 撤销PUBLIC角色的数据库连接权限，切断所有非显式授权用户的连接入口
REVOKE CONNECT ON DATABASE main_db FROM PUBLIC;
-- 仅给main_user开放main_db的连接权限
GRANT CONNECT ON DATABASE main_db TO main_user;
-- 撤销PUBLIC角色对public schema的所有权限
REVOKE ALL ON SCHEMA public FROM PUBLIC;
-- 给main_user开放public schema的全量权限
GRANT ALL ON SCHEMA public TO main_user;
-- 给main_user开放schema下所有现有表、序列、函数的权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO main_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO main_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO main_user;
-- 配置默认权限，后续新建的对象自动给main_user授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO main_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO main_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO main_user;

第二步：配置test_db权限隔离

切换连接到test_db，依次执行以下语句：

-- 撤销PUBLIC角色的数据库连接权限
REVOKE CONNECT ON DATABASE test_db FROM PUBLIC;
-- 仅给test_user开放test_db的连接权限
GRANT CONNECT ON DATABASE test_db TO test_user;
-- 撤销PUBLIC角色对public schema的所有权限
REVOKE ALL ON SCHEMA public FROM PUBLIC;
-- 给test_user开放public schema的全量权限
GRANT ALL ON SCHEMA public TO test_user;
-- 给test_user开放schema下所有现有对象的权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO test_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO test_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO test_user;
-- 配置未来新建对象的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO test_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO test_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO test_user;

可选优化

由于main_user属于cloudsqlsuperuser角色组，默认携带Create role、Create DB权限，如果不需要该权限可以执行以下语句回收：

ALTER ROLE main_user NOCREATEROLE NOCREATEDB;

效果验证 #

配置完成后：

• 运行psql -U test_user -d main_db -h 127.0.0.1 -W会直接返回permission denied for database main_db错误
• main_user连接test_db也会触发连接权限报错，即使手动开放连接权限，也没有test_table的操作权限

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