postgresql_role creation fails for database with SCRAM-SHA-256 encryption UNLESS provider is built from source

[rodfsoares]

Hi there,

I'm getting an Error: pq: unknown authentication response: 10 when attempting to create a user role in a DB configured with SCRAM-SHA-256 password encryption. This issue does NOT happen for DBs configured with MD5.

I understand this should already be fixed thanks to #113 and #114 (included in v.1.5.0), but role creation only succeeds when I build the v.1.6.0 release from source and manually place that binary in the .terraform folder (replacing the one downloaded by terraform init).

Terraform Version

Terraform v0.12.28
+ provider.external v1.2.0
+ provider.postgresql v1.6.0

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role

Terraform Configuration Files

provider "postgresql" {
  connect_timeout  = 15
  database         = "stg_dummyusers"
  expected_version = "11.6"
  host             = data.terraform_remote_state.postgresql_dummyusers.outputs.route53.record.fqdn
  password         = "password"
  port             = data.terraform_remote_state.postgresql_dummyusers.outputs.instance_details.port
  sslmode          = "require"
  superuser        = false
  username         = "master"
}

resource "postgresql_role" "user" {
  encrypted_password  = true
  login               = true
  name                = "rod"
  password            = "1234"
  roles               = ["ro-stg-dummyusers]
  skip_reassign_owned = true
}

Debug Output

https://gist.github.com/rodsoaresTD/c27cff93c322bc81959393e808d281b2

Expected Behavior

When I use the binary built from v1.6.0's source:

terraform apply
data.terraform_remote_state.postgresql_dummyusers: Refreshing state...
module.gopass_secrets_postgresql.data.external.secrets["masteruser"]: Refreshing state...

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # postgresql_role.user will be created
  + resource "postgresql_role" "user" {
      + bypass_row_level_security = false
      + connection_limit          = -1
      + create_database           = false
      + create_role               = false
      + encrypted_password        = true
      + id                        = (known after apply)
      + inherit                   = true
      + login                     = true
      + name                      = "rod"
      + password                  = (sensitive value)
      + replication               = false
      + roles                     = [
          + "ro-stg-dummyusers",
        ]
      + skip_drop_role            = false
      + skip_reassign_owned       = true
      + superuser                 = false
      + valid_until               = "infinity"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

postgresql_role.user: Creating...
postgresql_role.user: Creation complete after 2s [id=rod]

Apply complete! Resources: 1 added, 0 changed, 0 destroyed.
Releasing state lock. This may take a few moments...

Actual Behavior

When I use the binary downloaded by terraform init:

terraform apply
data.terraform_remote_state.postgresql_dummyusers: Refreshing state...
module.gopass_secrets_postgresql.data.external.secrets["masteruser"]: Refreshing state...

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # postgresql_role.user will be created
  + resource "postgresql_role" "user" {
      + bypass_row_level_security = false
      + connection_limit          = -1
      + create_database           = false
      + create_role               = false
      + encrypted_password        = true
      + id                        = (known after apply)
      + inherit                   = true
      + login                     = true
      + name                      = "rod"
      + password                  = (sensitive value)
      + replication               = false
      + roles                     = [
          + "ro-stg-dummyusers",
        ]
      + skip_drop_role            = false
      + skip_reassign_owned       = true
      + superuser                 = false
      + valid_until               = "infinity"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

postgresql_role.user: Creating...

Error: pq: unknown authentication response: 10

  on users.tf line 8, in resource "postgresql_role" "user":
   8: resource "postgresql_role" "user" {


Releasing state lock. This may take a few moments...

Steps to Reproduce

1. terraform init
2. terraform apply - issue occurs
3. Manually download and extract source code from v.1.6.0:
   https://github.com/terraform-providers/terraform-provider-postgresql/releases/tag/v1.6.0
4. Build source downloaded in previous step: `go build -o terraform-provider-postgresql_v1.6.0_x4``
5. Overwrite the "terraform-provider-postgresql_v1.6.0_x4" binary that was downloaded by terraform init in step 1, by the one built in the previous step
6. terraform init (notice it does NOT re-download the v1.6.0 binary, the one built in step 4 is kept)
7. terraform apply should now create the user with no issues

Important Factoids

There is a pre-existing default role in the Postgres DB ("ro-stg-dummyusers") that we want to GRANT to the new user.

References

• We use expected_version = "11.6" because of Postgres Compatibility Issue with 11.6 #124
• We use skip_reassign_owned = true because of Can not destroy postgresql_role after create #36

[seuf]

I have the same issue. I have to build my provider locally before use it..
Maybe an issue in the release process ?

[cyrilgdn]

@rodsoaresTD @seuf

I was able to get a similar error but when the provider try to connect if the configured user in the provider (usually postgres) needs a SCRAM-SHA-256 authentication.

Upgrading the Postgres provider to 1.7.0 (which has been released today) solves this error. Could you try it and tell me if it solves it for you too?

Previous version of this provider were (mistakenly) built with an old Go version and we upgrade to a recent one in v1.7.0.

[seuf]

It works with terraform provider postgresql version 1.7.0 ! 👍

[cyrilgdn]

@seuf Thanks 👍 So I close this issue.

@rodsoaresTD Feel free to open it back if it still doesn't work for you.

[rodfsoares]

Hi @cyrilgdn,

Just wanted to confirm that upgrading to 1.7.0 solved it for me.

Thank you very much for solving the issue!

(thank you @seuf as well for adding to the discussion)