Behavior for removing postgresql_role resources doesn't follow the documentation

[paul-civitas]

The documentation states:

When a postgresql_role resource is removed, the PostgreSQL ROLE will automatically run a REASSIGN OWNED and DROP OWNED to the CURRENT_USER (normally the connected user for the provider). If the specified PostgreSQL ROLE owns objects in multiple PostgreSQL databases in the same PostgreSQL Cluster, one PostgreSQL provider per database must be created and all but the final postgresql_role must specify a skip_drop_role.

However looking at the code:

terraform-provider-postgresql/postgresql/resource_postgresql_database.go

Line 504 in 450ce85

currentUser := db.client.config.getDatabaseUsername()

It gets the username via the config file, instead of the postgres CURRENT_USER function.

This causes issues in planetscale specifically, because there the username you login with is like:

postgres.xxxxxxxxx

The .xxxxxxxxx is used for planetscale's routing. The dot and the part after it are removed, then the user you end up with is:

postgres=> select CURRENT_USER;
 current_user
--------------
 postgres
(1 row)

This reflects correctly in CURRENT_USER. However because rather than CURRENT_USER being used like is described in the docs, it uses the username from the config.

currentUser := db.client.config.getDatabaseUsername()

	sql := fmt.Sprintf("REASSIGN OWNED BY %s TO %s", pq.QuoteIdentifier(currentOwner), pq.QuoteIdentifier(newOwner))
	if _, err := lockTxn.Exec(sql); err != nil {
		return fmt.Errorf("error reassigning objects owned by '%s': %w", currentOwner, err)
	}

This leads to this error when trying to remove a role.

│ Error: could not reassign owned by role rolename to postgres.xxxxxxxxx: pq: role "postgres.xxxxxxxxx" does not exist

[paul-civitas]

Workaround, you can use the

database_username option, which I missed.