How to set or change role upon login automatically in postgres?

Question

I have an existing postgres 11 database called host_db and we have an existing application called host_app that has been using this database for a long time. This service uses superuser host_app_user to connect to database and do all the transactions. Hence, all the database objects are owned by this database superuser.

Now, we want to create db_admin superuser role too in our database whose credentials will be maintained by Vault. But to not mix up database ownership, I was thinking that whenever db_admin logs in to the database, it assumes the role of host_app_user. That way whatever changes the logged admin does will all be done as host_app_user.

My question is: Is there a way I can automatically set the role of logged in user in postgres at the time of logging in?

Answer 1

I think the answer is no. But you could do this:

grant db_admin to host_app_user;

Then host_app_user will have all the permissions that db_admin has.

Answer 2

Use:

alter role db_admin set role host_app_user;

db_admin's role will be set to host_app_user on login.

Note though that db_admin must be a member of host_app_user.