postgresql - usuario - Cambiar el rol después de conectarse a la base de datos

tipos de usuarios en postgresql

Question

¿Es posible cambiar el rol de postgresql que usa un usuario cuando interactúa con postgres después de la conexión inicial?

La (s) base (s) de datos se usarán en una aplicación web y me gustaría emplear reglas de nivel de base de datos en tablas y esquemas con agrupación de conexiones. Después de leer la documentación de postgresql, parece que puedo cambiar las funciones si me conecto originalmente como usuario con el rol de superusuario, pero preferiría conectarme inicialmente como usuario con permisos mínimos y cambiar de ser necesario. Tener que especificar la contraseña del usuario cuando se cambia estaría bien (de hecho, lo preferiría).

¿Qué me estoy perdiendo?

Actualización : Intenté tanto SET ROLE como SET SESSION AUTHORIZATION como lo sugirió @Milen; sin embargo, ninguno de los dos comandos parece funcionar si el usuario no es un superusuario:

$ psql -U test
psql (8.4.4)
Type "help" for help.

test=> /du test
          List of roles
 Role name | Attributes |   Member of    
-----------+------------+----------------
 test      |            | {connect_only}

test=> /du test2
          List of roles
 Role name | Attributes |   Member of    
-----------+------------+----------------
 test2     |            | {connect_only}

test=> set role test2;
ERROR:  permission denied to set role "test2"
test=> /q

Answer 1

Eche un vistazo a "SET ROLE" y "SET SESSION AUTHORIZATION" .

Answer 2

--create a user that you want to use the database as:

create role neil;

--create the user for the web server to connect as:

create role webgui noinherit login password ''s3cr3t'';

--let webgui set role to neil:

grant neil to webgui; --this looks backwards but is correct.

webgui ahora está en el grupo webgui , por lo que webgui puede llamar a set role neil . Sin embargo, webgui no heredó los permisos de webgui .

Más tarde, inicie sesión como webgui:

psql -d some_database -U webgui
(enter s3cr3t as password)

set role neil;

webgui no necesita permiso de superuser para esto.

Desea set role al comienzo de una sesión de base de datos y restablecerlo al final de la sesión. En una aplicación web, esto corresponde a obtener una conexión de su grupo de conexiones de base de datos y liberarla, respectivamente. Aquí hay un ejemplo usando el grupo de conexiones de Tomcat y Spring Security:

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);
    }
}