Syntax error creating roles/users on Redshift

[kinghuang]

Terraform Version

# terraform --version
Terraform v0.12.26

Affected Resource(s)

• postgresql_role

Terraform Configuration Files

resource "postgresql_role" "user" {
  for_each = local.database_users
  
  name        = each.key
  replication = lookup(each.value, "replication", false)
  login       = true
  password    = lookup(each.value, "password", null)
  valid_until = lookup(each.value, "valid_until", null)
}

Debug Output

postgresql_role.user["spendanalytics"]: Creating...
2020/06/11 15:44:24 [DEBUG] postgresql_role.user["spendanalytics"]: apply errored, but we're indicating that via the Error pointer rather than returning it: error creating role spendanalytics: pq: syntax error at or near "ROLE"
2020/06/11 15:44:24 [ERROR] <root>: eval: *terraform.EvalApplyPost, err: error creating role spendanalytics: pq: syntax error at or near "ROLE"
2020/06/11 15:44:24 [ERROR] <root>: eval: *terraform.EvalSequence, err: error creating role spendanalytics: pq: syntax error at or near "ROLE"

Error: error creating role spendanalytics: pq: syntax error at or near "ROLE"

  on roles.tf line 9, in resource "postgresql_role" "user":
   9: resource "postgresql_role" "user" {

Panic Output

N/A

Expected Behavior

A new role is created.

Actual Behavior

Error: error creating role spendanalytics: pq: syntax error at or near "ROLE"

Steps to Reproduce

1. Configure the postgresql provider to connect to a Redshift cluster.
2. Also in the provider config, set expected_version = "8.0". Redshift is closest to PostgreSQL 8.
3. Use the postgresql_role resource to create a new role.

Important Factoids

Redshift is sort of like PostgreSQL 8. Issue #3 and PR #12 suggest that at some point in the past, changes were made to enable this provider to create roles in Redshift. But, I can't seem to figure out how to get it to work.

References

• Unable to create another postgres role within AWS Redshift database. #3
• Cleanup PostgreSQL provider to be multi-version friendly... #12
• Fix schema support for RedShift #13

[bendrucker]

Redshift doesn't support CREATE ROLE, only CREATE USER:

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_USER.html

Whereas CREATE USER in Postgres is an alias for CREATE ROLE WITH LOGIN:

https://www.postgresql.org/docs/current/sql-createuser.html

Options here include making the query dependent on the Postgres version or perhaps allowing a user specified attribute to override the object (e.g., type = "role" (default) or type = "user").

We are looking at this at @TakeScoop and will probably be able to contribute some PRs to help add further Redshift compatibility.