User mapping: permission denied in GCP Cloud SQL

[cyn-rus]

Terraform Version

1.5.2

Affected Resource(s)

postgresql_user_mapping

Terraform Configuration Files

provider "postgresql" {
  host            = #host
  port            = #port
  database        = "warehouse"
  username        = "postgres"
  password        = #password
  superuser       = false
  connect_timeout = 15
  sslmode         = "disable"

  expected_version = "14.8"
}

resource "postgresql_server" "warehouse_server" {
  server_name  = "server"
  fdw_name     = "postgres_fdw"
  server_owner = "server"
  
  options = {
    host   = #host
    dbname = #db
    port   = #port
  }
}

resource "postgresql_grant_role" "warehouse_postgres_grant_server" {
  role       = "postgres"
  grant_role = "server"
}

resource "postgresql_grant_role" "warehouse_user_grant_server" {
  role       = "dashboard"
  grant_role = "server"
}

resource "postgresql_user_mapping" "warehouse_server_dashboard" {
  server_name = "server"
  user_name   = "dashboard"
  options = {
    user     = #user
    password = #password 
  }
}

Debug Output

Error: Error reading user mapping: pq: permission denied for view _pg_user_mappings

Panic Output

N/A

Expected Behavior

User mapping created successfully and no error occured

Actual Behavior

User mapping created successfully but an error occured, hence the terraform state will assume the resource has not been created

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. Create user mapping on postgres that is hosted in GCP using postgresql_user_mapping
2. terraform apply

Important Factoids

I've tried querying

SELECT umoptions FROM information_schema._pg_user_mappings WHERE authorization_identifier='dashboard' AND foreign_server_name='server'

and it produces the same error, permission denied for view _pg_user_mappings, I think it may be related to where it select from

References

#274

[ademariag]

Also experiencing the same issue.

[ademariag]

@cyn-rus FYI I sent a PR in an attempt to address the issue