Error updating user mapping options: pq: option "user" provided more than once

[nyxgear]

Terraform Version

Terraform v1.3.7
on linux_amd64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.18.0
+ provider registry.terraform.io/hashicorp/random v3.4.3

Affected Resource(s)

• postgresql_user_mapping

Terraform Configuration Files

terraform {
  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = ">= 1.18"
    }
    random = {
      source  = "hashicorp/random"
      version = ">= 3.4"
    }
  }
}

provider "postgresql" {
  # Configuration options
  alias           = "db1"
  host            = var.db1_db_host
  port            = var.db1_db_port 
  database        = var.db1_db_name
  username        = var.db1_db_administrator_login
  password        = var.db1_db_administrator_password
  sslmode         = "require"
  connect_timeout = 15
  superuser = false
}

resource "postgresql_extension" "db1_fdw" {
  provider       = postgresql.db1
  name           = "postgres_fdw"
  database       = var.db1_db_name
  create_cascade = true
}

##########################
#
# fdwDb1
#
resource "postgresql_role" "db2_fdw_db1" {
  provider = postgresql.db2
  name     = "fdw_db1"
  login    = true
  password = random_password.db2_passwords[3].result
}

resource "postgresql_grant" "db2_fdw_db1" {
  provider    = postgresql.db2
  database    = var.db2_db_name
  role        = postgresql_role.db2_fdw_db1.name
  schema      = "public"
  object_type = "table"
  objects     = ["data"]
  privileges  = ["SELECT"]
}
##########################

# CREATE SERVER IF NOT EXISTS db2_db_fdw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'host.example.com', port '5432', dbname 'db2_db_name');
resource "postgresql_server" "db2_db_fdw" {
  provider    = postgresql.db1
  server_name = "db2_db_fdw"
  fdw_name    = postgresql_extension.db1_fdw.name
  options = {
    host   = var.db2_db_host
    port   = var.db2_db_port
    dbname = var.db2_db_name
  }

  depends_on = [postgresql_extension.db1_fdw]
}

# GRANT USAGE ON FOREIGN SERVER db2_db_fdw TO serviceAccount;
resource "postgresql_grant" "serviceAccount_db2_db_fdw" {
  provider    = postgresql.db1
  database    = var.db1_db_name
  role        = postgresql_role.db1_serviceAccount.name
  schema      = "public"
  object_type = "foreign_server"
  objects     = [postgresql_server.db2_db_fdw.server_name]
  privileges  = ["USAGE"]
}

# CREATE USER MAPPING IF NOT EXISTS FOR role SERVER db2_db_fdw OPTIONS (user 'serviceAccountDB1', password '*****');
resource "postgresql_user_mapping" "serviceAccount_fdw_db1" {
  provider    = postgresql.db1
  server_name = postgresql_server.db2_db_fdw.server_name
  user_name   = postgresql_role.db1_serviceAccount.name
  options = {
    user     = postgresql_role.db2_fdw_db1.name
    password = postgresql_role.db2_fdw_db1.password
  }
}

Debug Output

After a first successful terraform apply:

$ terraform plan

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # module.module_name.postgresql_user_mapping.serviceAccount_fdw_db1 will be updated in-place
  ~ resource "postgresql_user_mapping" "serviceAccount_fdw_db1 " {
        id          = "db1_serviceAccount.db2_db_fdw"
      ~ options     = {
          + "password" = (sensitive value)
          + "user"     = "fdw_db1"
        }
        # (2 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Panic Output

bash-5.1$ tf apply
[...]

│ Error: Error updating user mapping options: pq: option "user" provided more than once
│
│   with module.databases_conf.postgresql_user_mapping.serviceAccount_fdw_db1,
│   on module/module_name/main.tf line 115, in resource "postgresql_user_mapping" "serviceAccount_fdw_db1":
│  115: resource "postgresql_user_mapping" "serviceAccount_fdw_db1" {
│
╵
Releasing state lock. This may take a few moments...

Expected Behavior

1. Write the code for the postgresql_user_mapping resource.
2. terraform plan (see stdout for creation of user_mapping)
3. terraform apply
4. User mapping is properly created.
5. terraform plan Output: nothing to do - no resource changed.

Actual Behavior

1. Write the code for the postgresql_user_mapping resource.
2. terraform plan (see stdout for creation of user_mapping)
3. terraform apply
4. User mapping is properly created.
5. (With no change in the tf code). terraform plan > output:

   ~ resource "postgresql_user_mapping" "serviceAccount_fdw_db1" {
       id          = "db1_serviceAccount.db2_db_fdw"
     ~ options     = {
         + "password" = (sensitive value)
         + "user"     = "fdw_db1"
       }
       # (2 unchanged attributes hidden)
   }

6. Doing the apply anyway, terraform apply
7. Error. "Error: Error updating user mapping options: pq: option "user" provided more than once".

Steps to Reproduce

See "actual behavior" above.

Important Factoids

I wouldn't say it is relevant but the 2 Postgres servers I'm configuring are Azure Postgres Flexible Servers. The version of both: 14.

References

N/A

Personal intuition on the problem

It seems like the options map on the postgresql_user_mapping resource doesn't retain the definition of "user" and "password" values across multiple runs of "plan" command. It might have to do with the "# (2 unchanged attributes hidden)" output but I'm not sure.

[cyrilgdn]

Hi,

I think it's linked to #274 , which should fix it.