主页 / dba / 问题 / 215549
Accepted
pat
Asked: 2018-08-22 21:55:33 +0800 CST

连接到 PostgreSQL 时自动调用 `SET ROLE`

  • 772

首先是简短版本：

SET ROLE是否可以使用特定角色自动调用新的 PostgreSQL 数据库连接，无论是通过连接角色的配置（使用ALTER ROLE），还是连接 URI 末尾的选项？

更长，有上下文：

我正在设置一个 Web 应用程序以使用轮换数据库凭据（因此，有多种角色在起作用）。但是，这些凭据也用于修改数据库（通过 Rails 迁移），这意味着表将由一个不会长期存在的角色拥有。

我可以修改轮换凭据，以便它们从父角色（它本身不能登录）继承，然后通过SET ROLE所有数据库修改归父角色所有，而不是短期子角色。这解决了所有权问题，但它需要调用每个连接SET ROLE parent——实际上并不可行。

因此，我需要一些方法来确保每个子连接始终在父角色的上下文中运行。这可能吗？

postgresql role

2 个回答

  1. 正如@phemmer在这里回答的那样，您可以使用set这样的命令：

    ALTER ROLE child_role SET ROLE parent_role;

    这样，child_role自动更改parent_role为登录时。

    前提是child_role属于parent_role。

    评论后编辑：

    对象创建：

    [postgres@server ~]$ psql postgres 
psql (13.1)
Type "help" for help.

postgres=# CREATE ROLE parent_role NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE ROLE
postgres=# CREATE ROLE child_role LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE IN ROLE parent_role;
CREATE ROLE
postgres=# CREATE DATABASE my_database OWNER parent_role;
CREATE DATABASE

    我们创建my_schema之前的设置：

    [postgres@server ~]$ psql my_database -U child_role -c 'CREATE SCHEMA my_schema'
CREATE SCHEMA
[postgres@server ~]$ psql my_database -c "\dn+ my_schema"
                     List of schemas
   Name    |   Owner    | Access privileges | Description 
-----------+------------+-------------------+-------------
 my_schema | child_role |                   | 
 (1 row)

    正如我们所见，所有者是child_user。

    现在我们修改用户设置。

    my_database=# ALTER ROLE child_role SET ROLE parent_role;
ALTER ROLE

    我们创建my_schema2架构：

    [postgres@server ~]$ psql my_database -U child_role -c 'CREATE SCHEMA my_schema2'
CREATE SCHEMA
[postgres@server ~]$ psql my_database -c "\dn+ my_schema*"
                             List of schemas
    Name    |    Owner    |  Access privileges   |      Description       
------------+-------------+----------------------+------------------------
 my_schema  | child_role  |                      | 
 my_schema2 | parent_role |                      | 
(3 rows)

    my_schema2parent_child无需显式键入SET ROLE命令即可自动拥有。

    注意： 文档指定它仅在登录时发生。

    SET ROLE 不处理角色的 ALTER ROLE 设置指定的会话变量；这仅在登录期间发生。

    • 3
  2. Best Answer

    这不是 PostgreSQL 可以自己做的事情

    你想在你的连接池中这样做

    从池中获得连接后，立即调用SET ROLE

    释放与池的连接后，立即调用RESET ROLE

    不是 Ruby 的人，所以在这方面帮不了你太多，但这是你在 Java 中的做法：

    public class SetRoleJdbcInterceptor extends JdbcInterceptor {

    @Override
    public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) {

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();

        if(authentication != null) {
            try {

                /* 
                  use OWASP's ESAPI to encode the username to avoid SQL Injection. Can't use parameters with SET ROLE. Need to write PG codec.

                  Or use a whitelist-map approach
                */
                String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName());

                Statement statement = pooledConnection.getConnection().createStatement();
                statement.execute("set role \"" + username + "\"");
                statement.close();
            } catch(SQLException exp){
                throw new RuntimeException(exp);
            }
        }
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

        if("close".equals(method.getName())){
            Statement statement = ((Connection)proxy).createStatement();
            statement.execute("reset role");
            statement.close();
        }

        return super.invoke(proxy, method, args);
    }
}
    • 1

相关问题