support IN DATABASE on assume_role in postgres_role

[ggermis]

The assume_role parameter for the postgresql_role resource currently does not support setting a database to allow for assuming a role based on which database the user connects to.

We do:

resource "postgresql_role" "this" {
  name = "app-1"
  login = true
  password = "..."
  roles = [ "db-1-owner", "db-2-reader" ]
  assume_role = "db-1-owner"
}

Then postgresql_roles translates that into the following ALTER ROLE statement:

		sql := fmt.Sprintf(
			"ALTER ROLE %s SET ROLE TO %s", pq.QuoteIdentifier(roleName), pq.QuoteIdentifier(assumeRole),
		)

Our use case however is that we have 2 databases each with an owner (who is the owner of the db) and reader role (which can only perform select on tables in that db)

• db-1 with roles db-1-owner and db-1-reader
• db-2 with roles db-2-owner and db-2-reader

Now we have an application which uses its own login (ie. app-1) which should be the owner of db-1, but should also be able to read in db-2. Therefore app-1 is granted the roles db-1-owner and db-2-reader

Since we need the newly create tables to have owner db-1-owner when the user app-1 logs into the db-1 database and starts creating tables, we set the assume_role parameter to db-1-owner

However, we want that same user to assume the role db-2-reader when connecting to the db-2 database. The connection we use is through the dblink extension, but I think it's irrelevant wheter you do it through dblink or a second connection pool or any other way...

This is currently not supported by the postgresql_role resource. It would require SQL statements equivalent to

ALTER ROLE "app-1" IN DATABASE "db-1" SET ROLE TO "db-1-owner";
ALTER ROLE "app-1" IN DATABASE "db-2" SET ROLE TO "db-2-reader";

After this is set, I can log in to db-1 with the app-1 user and see it set the current_user to db-1-owner, whereas when I login to db-2 it sets the current_user to db-2-reader

postgres=> \c db-1
db-1=> select current_user, session_user;
       current_user       |           session_user
--------------------------+-----------------------------------
 db-1-owner | app-1
(1 row)
db-1=> \c db-2
db-2=> select current_user, session_user;
       current_user       |           session_user
--------------------------+-----------------------------------
 db-2-reader | app-1

The database-level role configuration then looks like

postgres=> \drds
                                           List of settings
                 Role                  |      Database      |                Settings
-------------------------+--------------------+-----------------------------------------
app-1     | db-1 | role=db-1-owner
app-1     | db-2 | role=db-2-reader
...
(5 rows)

Is this mapping of roles based on database something that could be added to the postgresql_role resource?

[Bafff]

Hey, @ggermis !

Can you please review #635

I hope that helps