postgresql_role destroy error "role cannot be dropped because some objects depend on it"

[jpo-tu]

Given a postgresql_role and a resource dependent on that role, an error is thrown when removing the dependency and destroying the role, as Terraform attempts to perform the destroy before the update. This only occurs if the database of the provider block is different than the database containing the resource.

Terraform Version

$ terraform -v
Terraform v1.8.1
on linux_amd64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.22.0

Affected Resource(s)

• postgresql_role

Terraform Configuration Files

terraform {
  required_version = ">= 1.0"
  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "~> 1"
    }
  }
}

provider "postgresql" {
  host      = "host.docker.internal"
  port      = 54321
  username  = "postgres"
  password  = ""
  database  = "postgres"
  superuser = false
  sslmode   = "disable"
}

resource "postgresql_database" "db" {
  name = "delete_before_update"
}

resource "postgresql_role" "publication_owner" {
  name = "${postgresql_database.db.name}_publication_owner"
}

resource "postgresql_publication" "pub" {
  database = postgresql_database.db.name
  name = "my_publication"
  owner = postgresql_role.publication_owner.name
}

What that terraform does:
a) creates a database
2) creates a role
d) makes the role owner of something (in this example, a publication). This creates a dependency between the something and the role

Steps to Reproduce

1. start a local postgresql instance listening on port 54321:

   docker run -it --rm -e POSTGRES_HOST_AUTH_METHOD=trust -p 54321:5432 postgres:15-alpine

2. terraform init && terraform apply the above terraform
3. remove the role from the code and update the owner (this removes the dependency on the role):

   # destroy the role by (say) commenting out the terraform
   #resource "postgresql_role" "publication_owner" {
   #  name = "${postgresql_database.db.name}_publication_owner"
   #}
   
   # update the owner to no longer reference the role
   resource "postgresql_publication" "pub" {
     database = postgresql_database.db.name
     name = "my_publication"
     owner = "postgres" # HERE
   }

4. terraform apply

Expected Behavior

owner should be updated and role destroyed

Actual Behavior

Error: could not delete role delete_before_update_publication_owner: pq: role "delete_before_update_publication_owner" cannot be dropped because some objects depend on it

Important Factoids

• issue originally discovered on AWS RDS Aurora Postgresql 15, and reproduced locally
• if the provider specifies the same database as the thing dependent on the role, the error does not occur. That is, the role is destroyed without issue and the publication owner updated.

[k3yss]

@jpo-tu Did you find a solution for this?

[soupdiver]

Yea, having the same issue here or at least something that looks similar.
Have a few schemas with users and terraform seems stuck with some kind of cyclic dependency issue... nothin can be removed because always some object depends on it