无法限制PostgreSQL用户访问权限的技术求助

我正尝试自动化创建PostgreSQL数据库与用户。目前创建完成后，所有用户似乎都拥有管理员权限，可执行任意操作、查看所有内容，包括其他数据库。我执行的SQL语句如下：

CREATE DATABASE MY_DB WITH ENCODING 'UTF8';
CREATE USER MY_DB_ADMIN WITH ENCRYPTED PASSWORD 'SUPER_SECRET_PASSWORD';
GRANT ALL PRIVILEGES ON DATABASE MY_DB TO MY_DB_ADMIN;

我接触PostgreSQL时间不长，不确定这是PostgreSQL的特性还是通用SQL的问题。

更新1
这里的“任意操作”指的是我能在其他数据库中执行查询、创建表等操作。我尝试了以下语句：

REVOKE ALL ON DATABASE MY_DB FROM MY_DB_ADMIN;
GRANT CREATE, CONNECT, TEMPORARY ON DATABASE MY_DB TO MY_DB_ADMIN;

但问题仍未解决，用户DB_ONE_ADMIN仍能在DB_TWO中创建表。

问题原因 #

PostgreSQL默认会给所有普通用户授予PUBLIC角色的权限，而PUBLIC在默认情况下对所有数据库拥有CONNECT权限，并且对数据库的public schema拥有CREATE和USAGE权限——这就是用户能跨库操作的核心原因。你之前的权限调整只针对目标数据库，没处理全局的PUBLIC权限以及其他数据库的权限。

解决步骤 #

1. 撤销全局PUBLIC的不必要权限
   先撤销所有用户通过PUBLIC获得的跨库创建权限：

   REVOKE CREATE ON SCHEMA public FROM PUBLIC;
   

   如果需要禁止用户随意连接其他数据库，还可以撤销全局的CONNECT权限（之后再给特定用户授予目标数据库的连接权限）：

   REVOKE CONNECT ON DATABASE DB_TWO FROM PUBLIC;
   

2. 为目标用户设置最小必要权限
   针对你的场景，只给MY_DB_ADMIN授予MY_DB数据库的必要权限：

   -- 授予连接和临时表权限
   GRANT CONNECT, TEMPORARY ON DATABASE MY_DB TO MY_DB_ADMIN;
   -- 授予MY_DB中public schema的创建和使用权限
   GRANT CREATE, USAGE ON SCHEMA public TO MY_DB_ADMIN;
   

   如果你需要用户能操作数据库里的现有表，还要补充表权限：

   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO MY_DB_ADMIN;
   -- 自动给未来新建的表赋予权限
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO MY_DB_ADMIN;
   

3. 清理其他数据库的用户权限
   对于已经存在的其他数据库（比如DB_TWO），直接撤销该用户的相关权限：

   REVOKE ALL ON DATABASE DB_TWO FROM MY_DB_ADMIN;
   REVOKE ALL ON SCHEMA public FROM MY_DB_ADMIN;
   

注意事项 #

• 永远遵循最小权限原则，只给用户完成工作所需的最少权限。
• PostgreSQL的权限体系是分层的（数据库→Schema→表→列），调整权限时要确保每一层都符合预期。

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