postgresql - 在所有 1000 个 postgres 数据库和模式上创建只读用户

Question

使用 Vault我正在尝试在 Postgres 11.8 实例中创建一个按需临时只读用户。

我会:

• 1000 多个数据库(每个客户一个，也称为租户)
• 每个数据库都有一个public 和reporting 模式。

所以我试图找到一种方法来授予这个只读用户对每个数据库以及两个模式中所有表的访问权限。

虽然我想到了以下片段:

-- Create a new user
CREATE ROLE "my-readonly-user" WITH LOGIN PASSWORD 'test123';

-- Grant access to the two schema's we have
GRANT USAGE ON SCHEMA public TO "my-readonly-user";
GRANT USAGE ON SCHEMA reporting TO "my-readonly-user";

-- Grant access to all tables in our two schema's
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "my-readonly-user";
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO "my-readonly-user";

-- Grant access to sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO "my-readonly-user";
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA reporting TO "my-readonly-user";

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "my-readonly-user";
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO "my-readonly-user";

它将仅适用于 1 个数据库(当前数据库)。如果我使用只读用户登录并切换到某些数据库，除了第一个数据库，我看不到任何表。

A cluster holds many databases, which hold many schemas. Schemas (evenwith the same name) in different DBs are unrelated. Grantingprivileges for a schema only applies to this particular schema in thecurrent DB (the current DB at the time of granting).https://stackoverflow.com/a/24923877/1409047

由于该限制，它似乎使上面的授权代码段更加复杂。我应该以某种方式迭代所有数据库并运行代码段吗？我将如何进行数据库切换？甚至可以在纯 SQL 中使用(根据 Vault's API 的要求)？以前有人这样做过吗？

注意:在 MySQL 中做同样的事情只需要 2 行代码，使用通配符 *.*，这在 Postgres afaik 中是不支持的:

CREATE USER '{{name}}'@'10.0.0.0/255.0.0.0' IDENTIFIED BY '{{password}}';
GRANT SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO '{{name}}'@'10.0.0.0/255.0.0.0';

Answer 1

SQL 语句不能影响与您所连接的数据库不同的数据库中的对象，这是一项深思熟虑的设计决定。

是的，您必须遍历集群中的所有数据库并在那里运行脚本。

请注意，您的脚本中存在一个错误:您不应授予只读用户 USAGE 序列，否则他们可以修改序列值。 SELECT 没问题。

我要做的是创建一个 read_only_group(使用 NOLOGIN)并向该角色授予所有这些权限。然后，当有只读用户的请求时，创建一个用户并将其添加到该组中，使其继承该组的权限。不要向用户本身授予任何东西，以便您可以在不再需要它时轻松地DROP它。