0.2.0 release causes permissions issues

[sodul]

We just noticed that our terraform rules break on Linux (plugin upgraded to 0.2.0), but not on macOS (still on 0.1.3):

Terraform Version

terraform version

Terraform v0.11.11

• provider.archive v1.1.0
• provider.aws v1.59.0
• provider.postgresql v0.2.0

Affected Resource(s)

postgresql_role

Terraform Configuration Files

resource "postgresql_role" "db_role" {
  provider            = "postgresql.aurora_master"
  name                = "${var.db_username}"
  password            = "${var.db_password}"
  create_database     = true
  create_role         = false
  login               = true
  inherit             = false
  replication         = false
  connection_limit    = -1
  skip_reassign_owned = true
}

Debug Output

N/A

Panic Output

N/A

Expected Behavior

terraform plan should have completed without any issue.

Actual Behavior

* module.invsrv_db.postgresql_role.db_role: 1 error(s) occurred:

* module.invsrv_db.postgresql_role.db_role: postgresql_role.db_role: Error reading role: pq: permission denied for relation pg_shadow

Steps to Reproduce

terraform plan

Important Factoids

Pinning the provider to 0.1.3.0 fixed the problem. This is with Aurora Postgresql 9.6.8 in us-west-2.

References

N/A

[hypnoglow]

Upvote for this. This is probably because RDS does not have pg_shadow.

[cyrilgdn]

Hi, thanks for opening this issue.

Before 0.2.0 the role password was not correctly updated (see #54 ). Unfortunately fixing that caused this issue with RDS....

This is probably because RDS does not have pg_shadow

Actually there is but as "admin" user is not SUPERUSER on RDS, he don't have permission to read pg_shadow which is the way to know is password has changed or not.

I'll try to create a fix quickly (in next days). Also I don't see a perfect way to fix this... I think we can just disable password update for RDS (by adding a parameter in the provider).

Let me know if you see a better approach.

[emulanob]

+1 for this issue. I'm also experiencing this behavior.

[roccato]

+1 as well. This provider generally doesn't play nice with RDS(funny how removing actual SUPERUSER makes it hard to manage a database)

[sodul]

If pg_shadow is not available, which from a security standpoint seems like a good idea, a fallback solution could be to attempt to login with the given username and password. Yes, it is not ideal since each user might have login restrictions, and this would be slower, but it can be a way to detect a password change in a good chunk of cases.

[andrewwatson]

@sodul I'm having the exact same problem. how did you pin this provider to 0.1.3? I can't get it to accept my restrictions!

Also, it seems like it might be better if there was an option you could pass the provider to get it to ignore pg_shadow and skip password checking. I was used to it not being able to modify passwords! :)

[sodul]

@andrewwatson just set the provider to 0.1.3 it will allow all versions between 0.1.3.0 (included) and 0.2.0 (excluded). You might have to rerun terraform init to go backwards in plugin versions. In the worst case move the .terraform directory out of the way and retry.

[andrewwatson]

oh yeah, i tried all kinds of syntax for the version pinning but just saying "0.1.3.0" did the trick!

[andrewwatson]

why don't we just make it so it always tries to set the password and doesn't bother to see if it's already correct? what's the harm in that?

[flrnmrz]

This issue also affects Google Cloud SQL databases. We successfully pinned the release to 0.1.13 using the selector ~> 0.1.13. Previously, we were suffering from the problem that the postresql provider wouldn't keep the password up-to-date, so thumbs up from our side for attempting a fix here. @andrewwatson , I'm not sure if always setting the password is really a good option: this might cause a lot of noise in audit logs, so I'd go for attempting to log in to detect if the password has changed.

[andrewwatson]

I can see why that might trigger an alert. Maybe we can make it an option. Always update password or try to login first.

On Tue, Feb 26, 2019, 6:18 AM flrnmrz notifications@github.com wrote:

This issue also affects Google Cloud SQL databases. We successfully pinned the release to 0.1.13 using the selector ~> 0.1.13. Previously, we were suffering from the problem that the postresql provider wouldn't keep the password up-to-date, so thumbs up from our side for attempting a fix here. @andrewwatson https://github.com/andrewwatson , I'm not sure if always setting the password is really a good option: this might cause a lot of noise in audit logs, so I'd go for attempting to log in to detect if the password has changed.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/terraform-providers/terraform-provider-postgresql/issues/64#issuecomment-467400016, or mute the thread https://github.com/notifications/unsubscribe-auth/AALMep5oi0huVF0tE9RAPRoElH7ZhXs3ks5vRRfwgaJpZM4bGxW0 .

[cyrilgdn]

I created a PR (#66) to fix this problem.

It allows to specify in provider configuration that admin user is not a superuser (like in RDS and apparently GCP). in this case, the provider will not try to read the password while refreshing the state.

Password updates will still work as long as you are doing all modifications with Terraform (so if you don't update password manually in the database, otherwise you can taint the resource if needed)

Feel free to review the PR, so we can merge it (if approved :) ) and create a bugfix release.

[fernandomora]

+1 Same problem here

[cyrilgdn]

Hi all,

Version 0.2.1, has been released today .

You can add superuser = false in your provider configuration so it will not try to refresh password from your Postgres server.

example:

provider "postgresql" {
  host = "test-provider.xxxxxxxxxxx.eu-central-1.rds.amazonaws.com"
  port = "5432"

  username  = "postgres"
  password  = "xxx"
  superuser = false
}

See #66

[gomker]

I still see this issue with v0.2.1

provider "postgresql" {
    alias    = "rds"
    version  = "0.2.1"
    host     = "${module.rds.endpoint}"
    port     = 5432
    database = "postgres"
    username = "${var.rds_master_username}"
    password = "${var.rds_master_password}"
    sslmode  = "disable"
}

resource "postgresql_role" "main" {
  provider = "postgresql.rds"
  name     = "${var.role_name}"
  login    = true
  password = "${var.password}"
  superuser= false
}

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

[mlehner616]

@gomker It sounds like you'd need to put superuser = false in the provider block itself. I haven't tried myself though.

[cyrilgdn]

@gomker @mlehner616 Indeed, the superuser flag has to be set in the provider configuration, see in my example above.

[gomker]

Ah, I did not see that as an option on the provider - I will try that when I rebuild and report back