如何在谷歌云中设置保管库和Postgres以获得正确的权限?

Question

问题

我正在尝试在Google Cloud中设置Hashicorp vault和Postgres。

我正在使用 liquibase 来管理架构，当它执行迁移时，它会从保管库中检索用户名/密码，然后在该用户下运行以执行迁移。

但是，默认 postgres 用户或任何其他尝试使用该表的用户不能并收到ERROR: permission denied for relation。

如果我理解正确，用户是在以postgres角色登录时创建的。这一切都在本地工作，但谷歌云的东西似乎以完全不同的方式设置。

下面是一些输出，用于查看数据库的设置：

du
List of roles
Role name             |                         Attributes                         |      Member of
----------------------------------+------------------------------------ 
------------------------+---------------------
cloudsqladmin                    | Superuser, Create role, Create DB, 
Replication, Bypass RLS | {}
cloudsqlagent                    | Create role, Create DB                                     
| {cloudsqlsuperuser}
cloudsqlreplica                  | Replication                                                
| {}
cloudsqlsuperuser                | Create role, Create DB                                     
| {}
postgres                         | Create role, Create DB                                     
| {cloudsqlsuperuser}
test                             | Create role, Create DB                                     
| {cloudsqlsuperuser}
v-token-power-watc-p079t4r13s85w | Password valid until              
| {cloudsqlsuperuser}

dt
List of relations
Schema |         Name          | Type  |              Owner
--------+-----------------------+-------+------------------------------ 
public | databasechangelog     | table | v-token-power-watc-p079t4r13s85w
public | databasechangeloglock | table | v-token-power-watc-p079t4r13s85w
public | test                  | table | v-token-power-watc-p079t4r13s85w

文件库创建 SQL 如下所示：

CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "{{name}}"; 
GRANT cloudsqlsuperuser to "{{name}}";"

我尝试过以多种方式扮演这些角色，但似乎我创建的所有新角色总是被放入自己的沙盒中，不能与任何其他角色一起扮演。在这一点上，我不知道下一步该怎么做，似乎无论我能通过grant选项，都无济于事。

当前问题

是所有者需要是 cloudsqlsuperuser 组而不是临时拥有者吗？

有没有办法将其设为默认值，而不必确保我的所有表都是由正确的所有者创建的？

使用本地 docker 实例进行复制

用于启动 Postgres 数据库的 Docker 命令

docker run --rm --name database -v $(pwd)/setup.sql:/docker-entrypoint-initdb.d/setup.sql -e POSTGRES_USER=temp -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=mydb postgres:9.6

设置内容.sql

ALTER ROLE postgres RENAME TO cloudsqladmin;
CREATE ROLE cloudsqlsuperuser WITH CREATEDB CREATEROLE;
ALTER DATABASE mydb OWNER TO cloudsqlsuperuser;
CREATE ROLE "postgres" WITH LOGIN CREATEDB CREATEROLE IN ROLE cloudsqlsuperuser;

在模拟保管库用户创建的帖子下创建用户

docker exec -ti database psql -U postgres -d mydb -c "CREATE ROLE testuser WITH LOGIN IN ROLE cloudsqlsuperuser"

从测试用户创建测试表

docker exec -ti database psql -U testuser -d mydb -c "CREATE TABLE test (col1 text)"

尝试选择显示错误的表

docker exec -ti database psql -U postgres -d mydb -c "SELECT * FROM test"

Answer 1

我认为这里的问题与在该临时用户下创建的新表有关 - 您的其他用户将没有访问它的权限。有几种方法可以尝试解决此问题。

第一种是在创建每个表后为其授予权限。您必须根据要授予访问权限的角色相应地调整此设置，但如果您只希望所有用户(当前和将来(都具有完全访问权限(，则可以使用此处所述的GRANT ALL PRIVILEGES ON <table> TO public;。

更好的解决方案可能是仅使用单个用户创建所有表。您仍然可以让保险柜根据需要为其生成新密码，并自动撤消对密码的访问权限，即使用户名相同也是如此。为此，您必须手动创建用户并首先为其授予所需的权限 - 假设您将其命名为migrator：

CREATE ROLE migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "migrator"; 

然后更改保险柜配置，以便在您请求凭据时添加登录权限，并在撤销凭据时将其移除：

vault write <role path here, e.g. database/roles/migrator> 
db_name=<db name here> 
creation_statements="ALTER ROLE migrator LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';" 
revocation_statements="ALTER ROLE migrator NOLOGIN;"

这样做的好处是，您可以使用ALTER DEFAULT PRIVILEGES(此处的文档(来避免为创建的每个新表授予权限。例如，如果您知道所有用户都应该有权访问所有内容：

ALTER DEFAULT PRIVILEGES FOR ROLE migrator GRANT ALL PRIVILEGES ON TABLES TO PUBLIC

我不确定如果您删除作为表所有者的用户，postgres 会如何反应，但如果出现问题，那么此解决方案也可以解决这个问题。

Answer 2

简短摘要

您必须在表/序列/函数创建过程之后运行REASSIGN OWNED BY "$VAULT_USERNAME" TO "cloudsqlsuperuser"以重新分配回公共角色。

更长的答案

Postgres 9.6CREATE TABLE默认情况下将在用户角色下创建表。由于 vault 用户被设计为临时凭证，因此我们还需要确保在临时角色下创建的对象重新分配回某种通用角色。在我在Google Cloud中概述的特定情况下，我们将重新分配回cloudsqlsuperuser。

我发现有效的一个命令是REASSIGN OWNED BY current_user to "cloudsqlsuperuser"命令，然后该命令将获取该临时用户拥有的所有对象并分配回该公共角色。

奖励：液基配置

我正在使用 liquibase 进行迁移，这在我的更改日志中，用于在每次运行迁移时修复权限：

<databaseChangeLog>
<changeSet author="elindblom" id="fix-permissions" context="" logicalFilePath="fix-permissions" runAlways="true" runOrder="last">
<sqlFile path="baselines/sql/fix-permissions.sql" splitStatements="false" relativeToChangelogFile="true" stripComments="false"/>
</changeSet>
... rest of your change logs after ...
</databaseChangeLog>

该 SQL 如下所示：

REASSIGN OWNED BY current_user TO "cloudsqlsuperuser";

changeSet在一切完成后运行，并且每次都运行。非常重要的部分是runAlways和runOrder元素。