在所有1000个PostgreSQL数据库和模式上创建只读用户。

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个单一的数据库（当前数据库）。如果我使用readonly用户登录并切换到其他数据库，除第一个数据库外，我看不到任何表格。

集群包含许多数据库，每个数据库包含许多模式。不同数据库中具有相同名称的模式是不相关的。授予权限仅适用于当前DB中的特定模式（授予权限时的当前DB）。 https://dev59.com/4mAf5IYBdhLWcg3wMwTX#24923877 由于这个限制，似乎使上面的授予代码片段更加复杂。我应该以某种方式迭代所有的数据库并运行片段吗？如何进行数据库转换？是否可以在简单的SQL中进行（按照Vault's API的要求）？有人做过这个吗？

注意：在MySQL中完成相同的操作只需要两行代码，使用通配符*.*，Postgres不支持该通配符，据我所知。

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';

- Jesse

Answer 1

这是一项有意的设计决策，即SQL语句不能影响与您连接的数据库之外的对象。

是的，您需要遍历集群中的所有数据库并在其中运行脚本。

请注意，您的脚本中存在一个错误：不应该授予只读用户对序列的USAGE权限，否则他们可以修改序列值。SELECT权限是允许的。

我的建议是创建一个read_only_group（使用NOLOGIN），并将所有这些权限授予该角色。然后，在需要只读用户时，创建一个用户并将其添加到该组中，以便它继承组权限。不要直接向用户授予任何权限，以便在不再需要时可以轻松地DROP它。

- Laurenz Albe

---

谢谢Laurenz！非常有帮助。我们使用Flyway并构建了一个Python包装器，迭代每个数据库并将我们的Flyway迁移应用于它们。我们创建了一个迁移，为当前连接的数据库上的两个模式提供只读和读写角色，并将该迁移应用于所有数据库。然后在Hashicorp Vault中，我们只需创建一个用户角色，并将只读或读写角色授予此临时用户。几小时后用户被删除。 - Jesse

Answer 2

如果您想使用仅限于Postgres脚本来授予用户只读访问权限，可以这样做：

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE nome_banco TEXT;
DECLARE template_conexao TEXT;
DECLARE string_conexao TEXT;
DECLARE nome_usuario TEXT;
BEGIN
    template_conexao = 'user=foo password=bar dbname=';
    nome_usuario = 'baz';

    FOR nome_banco IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        string_conexao = template_conexao || nome_banco;

        perform dblink_exec(string_conexao, 'GRANT CONNECT ON DATABASE "' || nome_banco || '" TO ' || nome_usuario);
        perform dblink_exec(string_conexao, 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO ' || nome_usuario);
    END LOOP;

END
$$

--DROP EXTENSION IF EXISTS dblink;

它连接到每个数据库并执行脚本；它可以轻松适应其他需要在所有数据库上执行数据库本地命令的情况。

请记住，出于安全考虑，在执行上述脚本后，您应该删除由上述脚本创建的dblink扩展，当然，除非您已经将该扩展用于其他目的。

- Haroldo_OK