PostgreSQL中如何创建仅能创建指定前缀数据库的用户？

你的需求是可行的，但原SQL命令存在语法错误，且PostgreSQL无法直接通过GRANT语句限制新数据库的命名前缀，需要通过事件触发器实现该限制。

原命令的错误点 #

GRANT ALL PRIVILEGES ON DATABASE 'hello_%'.* TO someuser; 存在两处问题：

1. PostgreSQL的GRANT ON DATABASE语法不支持通配符%，只能指定具体的数据库名称
2. 'hello_%'.*的写法不符合PostgreSQL语法规范，没有这种通配数据库的用法

正确实现步骤 #

1. 创建目标用户（原命令这部分是正确的）

CREATE ROLE someuser PASSWORD 'SomePassW0rd' NOSUPERUSER CREATEDB INHERIT LOGIN;

2. 创建检查数据库前缀的函数

这个函数会在创建数据库时验证名称是否符合hello_前缀要求，不符合则抛出错误：

CREATE OR REPLACE FUNCTION check_db_prefix()
RETURNS event_trigger AS $$
BEGIN
  IF TG_EVENT = 'CREATE DATABASE' THEN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
      -- 仅限制someuser用户的数据库命名
      IF current_user = 'someuser' AND obj.objectname !~ '^hello_' THEN
        RAISE EXCEPTION '数据库名称必须以"hello_"开头';
      END IF;
    END LOOP;
  END IF;
END;
$$ LANGUAGE plpgsql;

3. 创建事件触发器绑定函数

触发器会在所有数据库创建操作完成前触发上述检查函数：

CREATE EVENT TRIGGER enforce_db_prefix
ON ddl_command_end
WHEN TAG IN ('CREATE DATABASE')
EXECUTE FUNCTION check_db_prefix();

效果验证 #

• 使用someuser登录后，执行CREATE DATABASE hello_world;会成功
• 执行CREATE DATABASE test_db;会抛出错误：ERROR: 数据库名称必须以"hello_"开头

如果需要取消这个限制，直接删除触发器即可：

DROP EVENT TRIGGER enforce_db_prefix;

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