Início / dba / Perguntas / 215549
Accepted
pat
Asked: 2018-08-22 21:55:33 +0800 CST

Invoque automaticamente `SET ROLE` ao conectar-se ao PostgreSQL

  • 772

A versão curta primeiro:

É possível ter uma nova conexão de banco de dados PostgreSQL invocada automaticamente SET ROLEcom uma função específica, seja por configurações na função de conexão (usando ALTER ROLE), ou uma opção no final de um URI de conexão?

Mais longo, com contexto:

Estou configurando um aplicativo da Web para usar credenciais de banco de dados rotativas (portanto, há uma variedade de funções em jogo). No entanto, essas credenciais também são usadas para modificações no banco de dados (via migrações do Rails), e isso significa que as tabelas se tornam propriedade de uma função que não deveria existir a longo prazo.

Eu posso modificar as credenciais rotativas para que elas herdem de uma função pai (que não tem a capacidade de fazer login) e, em seguida, SET ROLEtodas as modificações do banco de dados são de propriedade do pai, em vez da função filho de curto prazo. Isso resolve o problema de propriedade, mas exige que todas as conexões sejam invocadas SET ROLE parent- não é realmente viável.

Portanto, quero uma maneira de garantir que cada conexão filha sempre opere dentro do contexto da função pai. Isso é possível?

postgresql role

2 respostas

  1. Conforme respondido por @phemmer aqui, você pode usar seto comando assim:

    ALTER ROLE child_role SET ROLE parent_role;

    Dessa forma, child_rolealtere automaticamente para parent_roleno login.

    Desde que child_rolepertença a parent_role.

    Editar após comentários:

    Criação do objeto:

    [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

    Criamos my_schemaantes das configurações:

    [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)

    Como vemos, o proprietário é child_user.

    Agora modificamos a configuração do usuário.

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

    E criamos o my_schema2esquema:

    [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_schema2é de propriedade automática sem comando parent_childde tipo explícito .SET ROLE

    Nota: A documentação especifica que ocorre somente no login.

    SET ROLE não processa variáveis ​​de sessão conforme especificado pelas configurações ALTER ROLE da função; isso só acontece durante o login.

    • 3
  2. Best Answer

    Isso não é algo que o PostgreSQL possa fazer sozinho

    Você deseja fazer isso em seu pool de conexões

    Logo após obter uma conexão do pool, ligueSET ROLE

    Logo após liberar uma conexão com o pool, chameRESET ROLE

    Não é um cara do Ruby, então não posso ajudá-lo muito com isso, mas aqui está como você faria isso em 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

relate perguntas