Switch role after connecting to database

Question

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  gt   du test           List of roles  Role name   Attributes     Member of     ----------- ------------ ----------------  test                      connect only   test  gt   du test2           List of roles  Role name   Attributes     Member of     ----------- ------------ ----------------  test2                     connect only   test  gt  set role test2  ERROR   permission denied to set role  test2  test  gt   q

User · Answer

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

User · Answer

Take a look at  SET ROLE  and  SET SESSION AUTHORIZATION

User · Answer

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

[postgresql] Switch role after connecting to database

Examples related to postgresql