如何使PostgreSQL表归属指定Role而非Vault动态临时用户？

问题根源 #

动态凭证生成的临时角色是会话级的，迁移作业默认用当前登录的临时角色作为新表的所有者。当临时角色过期或更换后，新的临时角色因无权限访问旧临时角色所有的表，导致作业失败。

解决方案思路 #

创建永久的迁移角色，让所有迁移操作以该角色身份执行，确保表的所有权固定到这个永久角色。同时配置Vault动态角色，让生成的临时角色继承该迁移角色的权限，这样后续所有临时角色都能访问这些表。

具体操作步骤 #

1. 在PostgreSQL中创建永久迁移角色

先创建一个拥有足够权限的永久角色（例如db_migration_role），建议设置为NOINHERIT以提升安全性：

-- 创建迁移角色，禁止自动继承权限
CREATE ROLE db_migration_role WITH LOGIN NOINHERIT;

-- 授予该角色对public schema的全权限
GRANT ALL PRIVILEGES ON SCHEMA public TO db_migration_role;

-- 设置默认权限，确保该角色能访问后续创建的对象
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO db_migration_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO db_migration_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO db_migration_role;

2. 修正Vault数据库角色配置

移除原配置中无效的SET ROLE admin_user;（该语句仅在Vault创建角色的会话中生效，与迁移作业会话无关），改为授予临时角色访问永久迁移角色的权限：

vault write database/roles/user-db-migration \
    db_name="postgresql-user-database" \
    default_ttl="5h" \
    max_ttl="12h" \
    creation_statements=" \
        CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
        GRANT db_migration_role TO \"{{name}}\"; \
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO \"{{name}}\"; \
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO \"{{name}}\"; \
    " \
    revocation_statements=" \
        ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM \"{{name}}\"; \
        ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM \"{{name}}\"; \
        REVOKE db_migration_role FROM \"{{name}}\"; \
        DROP ROLE \"{{name}}\"; \
    "

3. 修改迁移作业脚本

在迁移脚本的开头添加SET ROLE语句，切换到永久迁移角色执行后续操作，确保新表的所有者为db_migration_role：

-- 切换到永久迁移角色
SET ROLE db_migration_role;

-- 后续迁移语句（例如CREATE TABLE等）

关键说明 #

• 之所以要显式执行SET ROLE，是因为我们给迁移角色设置了NOINHERIT，临时角色不会自动继承其权限，必须显式切换才能以迁移角色身份操作，避免临时角色拥有过高权限。
• 如果不需要严格的权限隔离，可将迁移角色改为INHERIT，此时迁移作业无需执行SET ROLE，但临时角色会自动拥有迁移角色的所有权限，需权衡安全性。

内容的提问来源于stack exchange，提问作者Waseem Nazir Mir