Syntax error creating roles/users on Redshift

[veljkot]

Terraform Version

$ terraform --version
Terraform v1.0.4
on linux_amd64

Affected Resource(s)

• postgresql_role

Terraform Configuration Files

resource "postgresql_role" "application_user_role" {
  provider        = postgresql.app_db_root
  name            = var.AppUser
  login           = true
  password        = var.AppPassword
  create_database = true
}

Debug Output

module.redshift_db.postgresql_role.application_user_role: Creating...
╷
│ Error: error creating role testrole: pq: syntax error at or near "ROLE"
│ 
│   with module.redshift_db.postgresql_role.application_user_role,

Expected Behavior

A new role is created.

Actual Behavior

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

Steps to Reproduce

1. Configure the postgresql provider to connect to a Redshift cluster.
2. Use the postgresql_role resource to create a new role.

Important Factoids

This is pretty much the same issue as the one created by @kinghuang . Since hashicorp/terraform-provider-postgresql is archived and issues are not moved here, I'm opening this new one. More details about possible solutions is in the old issue as explained by @bendrucker. See hashicorp/terraform-provider-postgresql#147 (comment)

References

• Syntax error creating roles/users on Redshift hashicorp/terraform-provider-postgresql#147

[bendrucker]

Having thought about this since then I don't think this provider should attempt to support Redshift. Redshift is substantially its own thing distinct from Postgres. It's closed source, with no option for testing outside of a real cluster in AWS, which comes with substantial hourly costs even a cluster only exists for the life of a single acceptance test run. It's reasonable for PG tools to make minor changes for cross-compatibility but once there's Redshift-specific cases in switch statements, which it seems like there'd be here, that's a large burden on the project.

The behaviors of RDS are limited and well known and ultimately it's still PG under the hood, just with some specific pre-configured roles.

https://github.com/cyrilgdn/terraform-provider-postgresql/blob/1c4c23fd3c8ad6dc5bfe39d9b55ca405ae846d30/tests/switch_rds.sh

Seems like some folks have worked on Redshift providers:

https://registry.terraform.io/search/providers?q=redshift

In particular, note things like redshift_schema in this provider, which is able to implement a bunch of Redshift-specific features (schemas linked to RDS databases or other AWS sources):

https://registry.terraform.io/providers/brainly/redshift/latest/docs/resources/schema#nested-schema-for-external_schema

I've not tried that provider yet but it's quite new/active and probably what I'd start with. If you're using the purely PG-compatible subset of Redshift, then you can also use the postgresql provider. But looking at the resources, there's quite a bit of Redshift-specific attributes, seems like a better idea to start with a Redshift-specific provider.

[veljkot]

It does make sense not to support Redshift, since there are separate providers that already do that. I tried brainly/redshift and it works like a charm for my use case. Thanks for your fast response, this issue can be closed.