Switch role after connecting to database

Is it possible to change the postgresql role a user is using when interacting with postgres after the initial connection?

The database(s) will be used in a web application and I'd like to employ database level rules on tables and schemas with connection pooling. From reading the postgresql documentation it appears I can switch roles if I originally connect as a user with the superuser role, but I would prefer to initially connect as a user with minimal permissions and switch as necessary. Having to specify the user's password when switching would be fine (in fact I'd prefer it).

What am I missing?

Update: I've tried both SET ROLE and SET SESSION AUTHORIZATION as suggested by @Milen however neither command seems to work if the user is not a superuser:

$ 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

222060

小开

--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 is now in the neil group, so webgui can call set role neil . However, webgui did not inherit neil's permissions.

Later, login as webgui:

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


set role neil;

webgui does not need superuser permission for this.

You want to set role at the beginning of a database session and reset it at the end of the session. In a web app, this corresponds to getting a connection from your database connection pool and releasing it, respectively. Here's an example using Tomcat's connection pool and 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);
}
}

Take a look at "SET ROLE" and "SET SESSION AUTHORIZATION".

If someone still needs it (like I do).

The specified role_name must be a role that the current session user is a member of. https://www.postgresql.org/docs/10/sql-set-role.html

We need to make the current session user a member of the role:

create role myrole;
set role myrole;
grant myrole to myuser;
set role myrole;

produces:

Role ROLE created.




Error starting at line : 4 in command -
set role myrole
Error report -
ERROR: permission denied to set role "myrole"


Grant succeeded.




Role SET succeeded.