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?

• user1822: What programming environment are you using? Java connection pools usually have the ability to call some method when a connection is retrieved from the pool. That would be a good place to put the SET ROLE statement. There is nothing built-in to Postgres for this (no "logon triggers")
• pat: @a_horse_with_no_name Using Ruby in the web app, and bash scripts on the servers. I'll see if Rails/ActiveRecord has some scope for this, but appreciate the knowledge that there's nothing built-in to Postgres (even if that's not the answer I was hoping for!)
• laktak: I landed here while looking on how to do this with psql: unix.stackexchange.com/a/756961/46158
• Eric Hanson: Remember that RESET ROLE also exists, so if your app ever allows arbitrary SQL or has any SQL injection vulnerabilities, using this pattern would allow anyone to RESET ROLE; SET ROLE and become someone else. It dramatically increases the impact of SQL injection.

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

• pat: Thanks for that confirmation, which echoes what @a_horse_with_no_name has noted in their comment. My workaround for Rails is making use of the variables option in the appropriate environments in config/database.yml: ROLE: parent_role. It does the job well enough (I'll just need to be mindful of this when accessing the database in any non-Rails manner). api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/…

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.

• mustaccio: That doesn't seem to address the "automatically" part, does it