Automatically invoke `SET ROLE` when connecting to PostgreSQL

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?

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.

StackExchange Database Administrators Q#215549, answer score: 7