Postgres创建角色限制到特定数据库

Question

在Postgres中，我该如何创建一个用户，使其具有“CREATEROLE”特权，但仅适用于特定的DB/一组DB？

我尝试了以下方法：

CREATE ROLE user WITH LOGIN PASSWORD 'password' NOCREATEDB CREATEROLE;

这是否正确？+如何将CREATEROLE授权给多个数据库？

- salient

---

请注意CREATEROLE权限。对于CREATEROLE角色的特权，没有继承概念。这意味着即使一个角色没有某个特权，但允许创建其他角色，它仍然可以轻松地创建具有不同特权的另一个角色（除了创建具有超级用户特权的角色）。例如，如果角色“user”具有CREATEROLE特权但没有CREATEDB特权，它仍然可以创建具有CREATEDB特权的新角色。因此，请将具有CREATEROLE特权的角色视为几乎超级用户角色。 - Abelisto

Answer 1

看起来这并不是一个简单的方法。但是你可以使用带有 security definer选项的函数来实现所需的行为：

create or replace function fn_create_role(p_name text, p_password text, p_databases text[]) returns void
  language plpgsql
  security definer
as $$
begin
  execute format('create role %I with login password %L;', p_name, p_password);
  execute format('grant connect on database %s to %I', array_to_string(p_databases, ','), p_name);
  return;
end $$;

请使用超级用户创建此函数。

然后，您可以使用NOCREATEROLE选项创建角色，但授予该角色对此函数的EXECUTE特权，并使用它来创建其他角色。

注意：您需要从特定数据库中撤销public角色的connect选项，以默认禁止角色连接到它们，例如：

revoke connect on database db1, db2 from public;

测试它：

---

作为超级用户（在我的情况下是nd，他还拥有相同名称的模式）

postgres=# create database db1; create database db2;
CREATE DATABASE
CREATE DATABASE
postgres=# revoke connect on database db1, db2 from public;
REVOKE
postgres=# create role foo with login password 'bar' nocreatedb nocreaterole;
CREATE ROLE
postgres=# set role foo;
SET
postgres=> create role win with login password 'amp' nocreatedb nocreaterole;
ERROR:  permission denied to create role
postgres=> set role nd;
SET
postgres=# grant usage on schema nd to foo;
GRANT
postgres=# grant execute on function nd.fn_create_role(text, text, text[]) to foo;
GRANT
postgres=# set role foo;
SET
postgres=> select nd.fn_create_role('win', 'amp', '{db1}');
┌────────────────┐
│ fn_create_role │
╞════════════════╡
│                │
└────────────────┘
(1 row)

在终端中执行以下命令：

$ psql -h localhost -d db1 -U win
用户 win 的密码：
psql (9.6.3)
SSL 连接（协议：TLSv1.2，加密方式：ECDHE-RSA-AES256-GCM-SHA384，位数：256，压缩：关闭）
键入 "help" 获取帮助。
win@db1=> \q
$ psql -h localhost -d db2 -U win
用户 win 的密码：
psql: FATAL:  对数据库 "db2" 没有权限
DETAIL:  用户没有 CONNECT 权限。

- Abelisto

Answer 2

角色是由数据库群集创建的，根据doc，而不是由数据库创建。如果您授予用户此强大的权限，则他将能够创建可在群集中每个数据库中使用的角色。

由于需要向角色授予权限，因此您可以确保来自单个数据库的内容被分配给该角色。

- JGH