问题

我正在尝试在PostgreSQL中创建一个允许以特定格式创建数据库的用户。我有以下命令来创建用户并授予权限：

```sql
CREATE ROLE someuser PASSWORD 'SomePassW0rd' NOSUPERUSER CREATEDB INHERIT LOGIN;
GRANT ALL PRIVILEGES ON DATABASE 'hello_%'.* TO someuser;

想法是允许用户创建像"hello_world"、"hello_something"等带有"hello_"前缀的数据库。但不幸的是，它引发了以下错误：

ERROR: pq: syntax error at or near 'hello_%'

有谁知道正确的语法是什么，或者是否可能实现这个行为？

<details>
<summary>英文:</summary>

I&#39;m trying to create a user in PostgreSQL that is allowed to create databases with a specific format. I&#39;ve the following commands to create the user and grant the permissions:

```sql
CREATE ROLE someuser PASSWORD &#39;SomePassW0rd&#39; NOSUPERUSER CREATEDB INHERIT LOGIN;
GRANT ALL PRIVILEGES ON DATABASE &#39;hello_%&#39;.* TO someuser;

The idea is that the user is allowed to create databases like "hello_world", "hello_something" and so on. So all must have the "hello_" prefix. But unfortunately it throws the following error:

ERROR: pq: syntax error at or near "'hello_%'"

Does anyone know what's the correct syntax for this behvaior is or if this is even possible?

答案1

得分: 0

根据PostgreSQL的GRANT手册，我认为直接从SQL中无法实现你想要的操作。

我建议创建一个程序，用户可以使用它来分析正则表达式模式，并在模式匹配时为该数据库授予所有权限。

例如在Python中：

import re

name = input("给你的数据库取一个名字")

if re.match("hello_[a-z_A-Z0-9]+", name):
    # 在Python中执行创建数据库的SQL查询
    # 在Python中执行授予所有权限的SQL

更多信息请参考：PostgreSQL GRANT 手册。

import re

name = input("Name your DB")

if re.match("hello_[a-z_A-Z0-9]+", name):
    # Execute create DB SQL query from python
    # Execute grant all permissions SQL from python