如何实现PostgreSQL用户仅能创建单个专属数据库的权限配置

1. 创建基础用户 #

先创建无数据库创建权限的用户，初始状态下他们没有任何所属对象：

CREATE USER user1 WITH LOGIN PASSWORD 'your_secure_password';
CREATE USER user2 WITH LOGIN PASSWORD 'your_secure_password';
-- 批量创建可通过脚本循环实现

2. 建立用户创建数据库的状态跟踪表 #

用一张表记录哪些用户已经使用过创建数据库的权限：

CREATE TABLE user_db_creation (
    username text PRIMARY KEY,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

3. 编写事件触发器函数 #

这个函数会在用户创建数据库时触发，完成状态记录和权限回收：

CREATE OR REPLACE FUNCTION restrict_db_creation()
RETURNS event_trigger AS $$
BEGIN
    -- 将当前创建数据库的用户记录到跟踪表
    INSERT INTO user_db_creation (username) VALUES (current_user)
    ON CONFLICT (username) DO NOTHING;
    
    -- 立即撤销该用户的数据库创建权限
    EXECUTE format('REVOKE CREATE DATABASE FROM %I', current_user);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

注：SECURITY DEFINER 让函数以超级用户权限执行，确保普通用户无法绕过权限回收操作。

4. 绑定事件触发器 #

将函数关联到数据库创建的DDL事件上：

CREATE EVENT TRIGGER track_db_creation
ON ddl_command_end
WHEN TAG IN ('CREATE DATABASE')
EXECUTE FUNCTION restrict_db_creation();

5. 临时授予用户创建数据库权限 #

当需要允许用户创建第一个数据库时，临时赋予权限：

GRANT CREATE DATABASE TO user1;

用户登录后创建数据库（建议使用自身用户名命名，如CREATE DATABASE user1;），一旦创建完成，权限会被自动回收——即便之后删除该数据库，也无法再创建新库。

6. 确保用户对自身对象的全权操作 #

用户创建的数据库默认所有者为自身，因此对该库内的所有对象（表、视图、函数等）天然拥有ALL PRIVILEGES权限，无需额外配置。

可选：强制用户以自身用户名命名数据库 #

如果需要限制用户只能创建与自身用户名同名的数据库，可替换触发器函数为：

CREATE OR REPLACE FUNCTION enforce_db_name_and_restrict()
RETURNS event_trigger AS $$
DECLARE
    target_db_name text;
BEGIN
    -- 获取要创建的数据库名称
    SELECT objid::regclass::text INTO target_db_name FROM pg_event_trigger_ddl_commands();
    
    -- 校验数据库名与用户名是否一致
    IF target_db_name != current_user THEN
        RAISE EXCEPTION '仅允许创建与用户名同名的数据库：%', current_user;
    END IF;
    
    -- 记录用户状态并回收权限
    INSERT INTO user_db_creation (username) VALUES (current_user)
    ON CONFLICT (username) DO NOTHING;
    
    EXECUTE format('REVOKE CREATE DATABASE FROM %I', current_user);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

然后更新事件触发器：

DROP EVENT TRIGGER IF EXISTS track_db_creation;
CREATE EVENT TRIGGER enforce_db_creation_rules
ON ddl_command_end
WHEN TAG IN ('CREATE DATABASE')
EXECUTE FUNCTION enforce_db_name_and_restrict();

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