Terraform: Postgresql Provider role resource not operational for older versions of Postgresql

[awebneck]

Terraform Version

Terraform v0.8.4

Affected Resource(s)

• postgresql_role

Terraform Configuration Files

provider "postgresql" {
  host = "myhost"
  alias = "pg1"
  username = "${var.pg-user}"
  password = "${var.pg-password}"
}

resource "postgresql_role" "monitoring" {
  provider = "postgresql.pg1"
  name = "monitoring"
  login = true
  password = "monitoring"
}

Expected Behavior

running terraform plan should have retrieved the role created by this configuration in the older version of terraform without issue.

Actual Behavior

terraform plan errors out with the following message:

Error refreshing state: 1 error(s) occurred:

* postgresql_role.monitoring: Error reading role: pq: column "rolbypassrls" does not exist

Steps to Reproduce

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

1. Configure a role in terraform 0.7.x on Postgresql 9.3 per configuration above
2. Upgrade terraform
3. run terraform plan

Important Factoids

The column "rolbypassrls" about which the provider is complaining was not added to Postgresql until version 9.5, which means I am unable to use this provider / resource for any versions of Postgresql prior to that.

[sean-]

Drat, PostgreSQL 9.3 isn't EOLed yet. Alright, I'll add something to detect the version of PostgreSQL and catalog differences between versions.

[awebneck]

Cheers!

[yusuf-cochinwala]

FYI .. facing exact same issue when using PostgresSQL 9.4.7 via Amazon RDS.

[apparentlymart]

It seems like this might be related to (but probably not an exact duplicate of) #11875: seems like we have a general problem of dealing with changes to the Postgres language and protocol between versions. I wonder if there's a holistic solution to address all problems of this type, or if we'd need to just spot-patch each situation separately.

[sean-]

@apparentlymart This is a known phenomena when the system catalogs change and something external is poking the system catalogs. I've figured out how I want to solve this problem but haven't had a chance to spin out the necessary fix. It will probably take me one or two nights to knock out the fix once I start work on this, but this is going to keep coming up as PostgreSQL evolves its schema. When I open a PR I'll tie both of these issues to the fix.

[likwid]

@sean- any chance of outlining the general solution? Maybe someone else can take a stab at it? I have a quick fix in mind for the redshift issue, as that is my immediate need, but I could try to solve this issue too.

[sean-]

@likwid RedShift is based on PostgreSQL 8.0.2, which is... old. The thing that needs to happen with the PostgreSQL provider is detect the dialect of PostgreSQL that it's talking to, and then have a query writer that produces the correct SQL based on the capabilities of the target. With CockroachDB and a few others out there, this indirection layer is going to be necessary. The "fix" in this case is trivial and I'll push a patch for this one-off with a note so that I can come back and fix it in the future.

[kshcherban]

Issue still appears with latest terraform 0.9.11 and postgres 9.4.7.

[sean-]

@kshcherban See and comment on terraform-providers/terraform-provider-postgresql#15 instead.

[hashibot[bot]]

I'm going to lock this issue because it has been closed for _30 days_ ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.