Unable to update password for admin DB user on oci_psql_db_system resource

[irmiller22]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform Version and Provider Version

 imiller@IanMiller  ~/D/s/infra   master  tf version
Terraform v1.8.1
on darwin_arm64

provider [registry.terraform.io/oracle/oci](http://registry.terraform.io/oracle/oci) v5.38.0

Affected Resource(s)

affected_resources = oci_psql_db_system

Terraform Configuration Files

resource "oci_psql_db_system" "this" {
  compartment_id = oci_identity_compartment.this.id
  display_name   = "${local.longname}-psql-${each.key}"
  shape          = lookup(each.value, "shape", local.default_psql_db_shape)
  db_version     = lookup(each.value, "version", 14)
  config_id      = oci_psql_configuration.this.id
  instance_count = var.environment == "prod" ? 2 : 1

  network_details {
    subnet_id = oci_core_subnet.private.id
  }

  storage_details {
    is_regionally_durable = true
    system_type           = "OCI_OPTIMIZED_STORAGE"
  }

  credentials {
    # Allowed pattern ^[A-Za-z]+[A-Za-z0-9_]+$.
    username = lookup(each.value, "username", "dba")
    password_details {
      password_type = "PLAIN_TEXT"
      password      = <redacted>
    }
  }

Debug Output

N/A

Panic Output

N/A

Expected Behavior

• Update the password value for a DB user on a oci_psql_db_system resource
• Once applied, should be able to log in with DB user using new password

Actual Behavior

• Update the password value for a DB user on a oci_psql_db_system resource
• Terraform applied the new value for the password, and saved the new value in the Terraform state
• Unable to log in with DB user using new password, but actually able to log in with the old password
• Had to force password update via oci psql db-system reset-master-user-password-plain-text-password-details --db-system-id <db_system_id> --password-details-password <redacted> command

Steps to Reproduce

1. Update the password value for a DB user on a oci_psql_db_system resource
2. terraform apply
3. Confirm that you cannot log into the DB with the DB user using the new password
4. Confirm that you can log into the DB with the DB user using the old password
5. Update the DB user password to the new value via oci psql db-system reset-master-user-password-plain-text-password-details --db-system-id <db_system_id> --password-details-password <redacted> command
6. Confirm that you can log into the DB with the DB user using the new password

[tf-oci-pub]

Thank you for reporting the issue. We observed the affected resources are not provided in the description or it's incorrect. We request you to add it in issue description as mentioned in below format.
Example: affected_resources = oci_core_instance , oci_core_instances

If it's not related to any particular resource then mention affected resource as terraform.
Example: affected_resources = terraform

As this works through automation, request you to follow exact syntax.