Automatically invoke `SET ROLE` when connecting to PostgreSQL

Question

The short version first:

Is it possible to have a new PostgreSQL database connection automatically invoke SET ROLE with a specific role, whether by configurations on the connecting role (using ALTER ROLE), or an option on the end of a connection URI?

---

Longer, with context:

I'm setting up a web application to use rotating database credentials (thus, there are a variety of roles in play). However, these credentials are also used for modifications to the database (via Rails migrations), and that means tables become owned by a role that isn't meant to exist in the long-term.

I can modify the rotating credentials so they inherit from a parent role (which doesn't have the ability to log in itself), and then via SET ROLE all database modifications are owned by the parent, rather than the short-term child role. This resolves the ownership issue, but it requires every connection to invoke SET ROLE parent - not really feasible.

Hence, I want some way to ensure that every child connection will always operate within the context of the parent role. Is this possible?

Answer 1

This isn't something PostgreSQL can do on its own

You want to do this in your connection pooler

Right after you get a connection from the pool, call SET ROLE

Right after you release a connection to the pool, call RESET ROLE

Not a Ruby guy, so can't help you much with that, but here's how you'd do it in 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);
    }
}

Answer 2

As answered by @phemmer here you can use set command like this:

ALTER ROLE child_role SET ROLE parent_role;

This way, child_role change automatically to parent_role at login.

Provided that child_role belongs to parent_role.

Edit after comments:

Object creation:

[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

We create my_schema before settings:

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

As we see, the owner is child_user.

Now we modify the user setting.

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

And we create my_schema2 schema:

[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 is automatically owned to parent_child without explicitly type SET ROLE command.

Note: The documentation specifies that it occurs at login only.

SET ROLE does not process session variables as specified by the role's ALTER ROLE settings; this only happens during login.