Terraform: postgresql_database fails when assigning role ownership with Amazon RDS

[mattlqx]

Terraform Version

0.8.4

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_database
• postgresql_role

Terraform Configuration Files

resource "aws_db_instance" "app_db_master" {
  allocated_storage          = 10
  storage_type               = "standard"
  engine                     = "postgres"
  engine_version             = "9.6.1"
  name                       = "${lower("${var.prefix}_appdb")}"
  identifier                 = "${lower("${var.prefix}-${lower(var.env_type)}-appdb")}"
  username                   = "${var.admin_username}"
  password                   = "${var.admin_password}"
  ...
}
provider "postgresql" {
  alias    = "app_db_master"
  host     = "${aws_db_instance.app_db_master.address}"
  username = "${aws_db_instance.app_db_master.username}"
  password = "${aws_db_instance.app_db_master.password}"
  sslmode  = "require"
}

resource "postgresql_database" "ext" {
  provider          = "postgresql.app_db_master"
  name              = "${lower(var.env_type)}-ext"
  owner             = "${postgresql_role.role.name}"
  lc_collate        = "en_US.UTF-8"
  lc_ctype          = "en_US.UTF-8"
  connection_limit  = -1
  allow_connections = true
}

resource "postgresql_role" "role" {
  provider         = "postgresql.app_db_master"
  name             = "${var.db_username}"
  login            = true
  password         = "${var.db_password}"
  connection_limit = -1
}

Expected Behavior

RDS server, database and role should be able to be created without issue.

Actual Behavior

• postgresql_database.ext: Error creating database test-qa-int: pq: must be member of role "testuser"

Steps to Reproduce

Apply from a clean state.

Important Factoids

The admin user that RDS provides upon creating a server isn't a true superuser. So that admin user needs to be part of the role that it's going to grant to the new user.

Unfortunately, no Terraform resource presently exists to be able to add a user to a role so it doesn't appear Terraform can create an RDS database and then manage it with PostgreSQL resources.

References

• http://stackoverflow.com/questions/26684643/error-must-be-member-of-role-when-creating-schema-in-postgresql for non-TF context.

[sean-]

I'll dig into reproducing this later this week, probably for the next release (0.8.6, not 0.8.5). If I can figure out a way of making the UX nice I'll do that, or I'll clean up the docs. Thanks!

[pbthorste]

Hi - I have submitted a pull request for this issue, see: https://github.com/hashicorp/terraform/pull/11452

[codepushr]

Also see: http://stackoverflow.com/questions/26684643/error-must-be-member-of-role-when-creating-schema-in-postgresql

Having the same issue right now...

[Joosakur]

This used to work a couple months or so ago but i'm also now having that issue with 0.8.2

[shinzui]

Is there a terraform workaround?

[FlorinAndrei]

On Terraform 0.8.8 now and the bug still exists. The Postgresql provider in Terraform is basically broken if you use RDS. Please merge the PR.

[shorn]

Using TF 0.9.2 this still occurs.

My workaround:

provider "postgresql" {
  alias = "dev_rds_pg_provider"
  host = "${var.dev_pg_host}"
  port = "${var.dev_pg_port}"
  username = "${var.rds_user}"
  password = "${var.rds_password}"
  sslmode = "disable"
}

provider "postgresql" {
  alias = "db_int"
  host = "${var.dev_pg_host}"
  port = "${var.dev_pg_port}"
  username = "${postgresql_role.db_int_user.name}"
  password = "${postgresql_role.db_int_user.password}"
  sslmode = "disable"
}

resource "postgresql_database" "int_db" {
  provider = "postgresql.db_int"
  name = "int_db"
  owner = "${postgresql_role.db_int_user.name}"

}


resource "postgresql_role" "awsgui_db_int_user" {
  provider = "postgresql.dev_rds_pg_provider"
  login = true
  name = "db_int_user"
  password = "db_int_password"
  create_database = true
}

First time you run it, db creation will fail, saying user doesn't have create permission.
Run it again, and TF will then update the "create_database" property to true, then successfully create the database.

You're then left with a user who has an unnecessary "create database" privilege, so you probably want to change it to false and run "apply" one more time.

So... not the cleanest of workarounds, but hey - better than scripting up a bunch of PG stuff on my own.

[mattlqx]

@grubernaut Can somebody take a look at pull https://github.com/hashicorp/terraform/pull/11452 to get this closed out?

[grubernaut]

Closed via #11452

[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.