Could not read role password from Postgres as connected user is not a SUPERUSER

[jleeh]

Terraform Version

v0.11.13
Postgres provider: v0.3.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role

Terraform Configuration Files

provider "postgresql" {
  host = "${data.terraform_remote_state.rds.endpoint}"
  port = "${data.terraform_remote_state.rds.port}"

  username  = "${var.rds_name}"
  password  = "${data.aws_secretsmanager_secret_version.db_pw.secret_string}"
  superuser = true
}

resource "postgresql_role" "linkstats" {
  name     = "linkstats-${var.name}"
  login    = true
  password = "${random_string.role_password.result}"

  skip_reassign_owned = true
}

Debug Output

2019/05/08 20:21:07 [DEBUG] ReferenceTransformer: "postgresql_role.linkstats" references: []
2019/05/08 20:21:07 [TRACE] Graph after step *terraform.ReferenceTransformer:
postgresql_role.linkstats - *terraform.NodeRefreshableManagedResourceInstance
2019/05/08 20:21:07 [TRACE] Graph after step *terraform.RootTransformer:
postgresql_role.linkstats - *terraform.NodeRefreshableManagedResourceInstance
2019/05/08 20:21:15 [ERROR] root: eval: *terraform.EvalRefresh, err: postgresql_role.linkstats: could not read role password from Postgres as connected user linkstats is not a SUPERUSER. You can set `superuser = false` in the provider configuration so it will not try to read the password from Postgres
2019/05/08 20:21:15 [ERROR] root: eval: *terraform.EvalSequence, err: postgresql_role.linkstats: could not read role password from Postgres as connected user linkstats is not a SUPERUSER. You can set `superuser = false` in the provider configuration so it will not try to read the password from Postgres
2019/05/08 20:21:15 [TRACE] [walkRefresh] Exiting eval tree: postgresql_role.linkstats

Expected Behavior

The connected user is a superuser so the role should be created fine.

Actual Behavior

Reported user isn't a superuser when it is. Prior to the role creation, the same user creates extensions without issue.

Steps to Reproduce

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

1. terraform apply

Important Factoids

• RDS Aurora Postgres 9.6.6

References

None

[cyrilgdn]

Hi @jleeh ,

could not read role password from Postgres as connected user linkstats is not a SUPERUSER. You can set superuser = false in the provider configuration so it will not try to read the password from Postgres

As the error is mentioning, you have to add superuser = false in the provider configuration (see https://www.terraform.io/docs/providers/postgresql/index.html#superuser)

Non-superuser role does not have the privileges to read password in Postgres.
Updating password will still work with superuser = false but it will not detect if you changed the password manually (i.e.: not with Terraform)

[jleeh]

Hey @cyrilgdn, that's the issue. The user is a superuser. The user I'm using is the one created with the RDS cluster, and just before this error, that same user created extensions, which requires superuser.

[cyrilgdn]

@jleeh Sorry, I read a bit too quickly your issue.

But roles can't be SUPERUSER on RDS, the postgres role created by RDS is just a member of rds_superuser which is close to a Postgres SUPERUSER but with some limitations. One limitation is that it can't read role passwords.

See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.Roles

[jleeh]

No worries. So I'm going through the provider source at the read role password function:
https://github.com/terraform-providers/terraform-provider-postgresql/blob/master/postgresql/resource_postgresql_role.go#L439

From looking at the logic, even if the password doesn't match on read then the password given as input is returned, no error is thrown if they don't match. If the configured user provider has superuser as false or with no login, then it just returns the password given again. It makes me think to why this even is done at all?

In this case can't the password be returned rather than throwing an error? Or remove the password reading all together since it doesn't really do anything practical.

Edit: If you agree, I'm happy to make the changes.

[jonasneves]

I'm having the same issue. I'm trying to delete some postgres users and terraform doesn't let me because it tries to read the user password. I don't even care about the user password since they all use RDS_IAM authentication.

This is still an issue, why was this closed?

[cyrilgdn]

@jonasneves Did you set the superuser settings to false in your provider configuration?

[jonasneves]

Yes, if I set it to false I get " permission denied to reassign objects".
I am able to delete the same user if I login via pgAdmin using the same credentials that the provider.postgresql in terraform is using though.

[minhajuddin]

For future readers, I was able to work around this issue by changing the state directly and manually removing the role from RDS.

terraform state rm postgresql_role.name_of_the_role

# psql  into RDS
DROP ROLE name_of_the_role;