postgresql_role doesn't update passwords in Amazon RDS

[jeffreymlewis]

Terraform Version

Terraform v0.12.9

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role

Terraform Configuration Files

  name                = var.role_name
  password            = data.aws_kms_secrets.secrets.plaintext["role_password"]
  login               = true
  connection_limit    = var.role_connection_limit
  skip_reassign_owned = true  # needed for AWS RDS
}

Expected Behavior

I expect terraform to change the postgresql user's password when it's changed inadvertently by a postgres admin.

Actual Behavior

Nothing.

Steps to Reproduce

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

1. terraform apply
2. change password in postgres using the /password command
3. terraform apply
4. Observe terraform output: Apply complete! Resources: 0 added, 0 changed, 0 destroyed.

References

This seems to be a recurrence of https://github.com/terraform-providers/terraform-provider-postgresql/issues/16

[cyrilgdn]

Hi @jlewis42lines,

Thanks for opening this issue.

If you update the password in Terraform, it normally updates correctly the role password. Let me know if it's not the case.

If you change the password manually in database, Terraform will indeed not update it (on RDS). We have no way to read the password value in RDS as user postgres is not a real superuser and does not have the permissions to read pg_shadow.

The only way would be to update the password at every terraform apply, but this means that terraform plan/apply will always have changes to do (if I run terraform apply twice, I expect the second one to tell me that Infrastructure is up-to-date).

Maybe we can add a settings in the provider to allow to choose one of the two behaviors?

What do you think?

[jeffreymlewis]

Hi @cyrilgdn

Thanks for the response! Indeed you are correct. Terraform changes the password if updated in terraform, but will not update the password when changed by a postgres admin.

I suppose we might talk to Amazon about granting the RDS postgres user permission to read pg_shadow. Failing that, I suppose a boolean to force password changes on every apply would be helpful.

[mltsy]

Another potential option... which may or may not be practical/feasible:

The provider could (when superuser = false) actually try logging in as each specified user to ensure the password hasn't changed. If the response indicates authentication failure, then it could update the user's password.

I don't know if that's any better than updating every user, but if the implementation for updating every user ends up being problematic, maybe this would work?