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 

3 Answers

--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.

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.