Creating uuid-ossp on AWS RDS Postgres 9.6.6 silently fails

[inversion]

This succeeds when ran from psql with the same user.

Terraform Version

Terraform v0.11.7

• provider.postgresql v0.1.1

Affected Resource(s)

• postgresql_extension

Terraform Configuration Files

provider "postgresql" {
  version = "~> 0.1"

  host    = "${element( split( ":", module.rds_db_instance.this_db_instance_endpoint ), 0 )}"
  port    = "${module.rds_db_instance.this_db_instance_port}"
  sslmode = "require"

  username = "${local.rds_master_username}"
  password = "${var.rds_master_password}"
}

resource "postgresql_role" "db" {
  name     = "${local.rds_username}"
  login    = true
  password = "${var.rds_password}"
}

resource "postgresql_database" "db" {
  name       = "${local.rds_database}"
  owner      = "${local.rds_username}"
  encoding   = "UTF8"
  lc_collate = "en_US.UTF-8"
  lc_ctype   = "en_US.UTF-8"
  depends_on = ["postgresql_role.db"]
}

resource "postgresql_extension" "uuid_ossp" {
  name = "uuid-ossp"
}

Debug Output

module.eu-west-1-services.postgresql_extension.uuid_ossp: Creating...
2018/06/01 15:51:18 [TRACE] root.eu-west-1-services: eval: *terraform.EvalApply
2018/06/01 15:51:18 [DEBUG] apply: postgresql_extension.uuid_ossp: executing Apply
  name:    "" => "uuid-ossp"
  schema:  "" => "<computed>"
  version: "" => "<computed>"
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalWriteState
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalApplyProvisioners
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalIf
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalWriteState
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalWriteDiff
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalApplyPost
2018/06/01 15:51:19 [TRACE] root.eu-west-1-services: eval: *terraform.EvalUpdateStateHook
module.eu-west-1-services.postgresql_extension.uuid_ossp: Creation complete after 0s (ID: uuid-ossp)
2018/06/01 15:51:19 [TRACE] [walkApply] Exiting eval tree: module.eu-west-1-services.postgresql_extension.uuid_ossp
2018/06/01 15:51:19 [TRACE] dag/walk: walking "module.eu-west-1-services.provider.postgresql (close)"

Expected Behavior

The apply should have failed because the extension was not created.

Actual Behavior

The apply succeeded silently but the extension was not created.

Steps to Reproduce

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

1. Create a new AWS RDS Postgres deployment.
2. terraform apply to (attempt to) create the extension.

[sean-]

Howdy. I wasn't able to reproduce this.

provider "postgresql" {
  version = "~> 0.1"
  host    = "127.0.0.1"
  port    = "5432"
  username = "postgres"
}

resource "postgresql_extension" "uuid_ossp" {
  name = "uuid-ossp"
}

 % terraform apply -auto-approve                                                                                                                           
postgresql_extension.uuid_ossp: Creating...                                                                                                                                
  name:    "" => "uuid-ossp"                                                                                                                                               
  schema:  "" => "<computed>"                                                                                                                                              
  version: "" => "<computed>"                                                                                                                                              
postgresql_extension.uuid_ossp: Creation complete after 0s (ID: uuid-ossp)                                                                                                 
                                                                                                                                                                           
Apply complete! Resources: 1 added, 0 changed, 0 destroyed.          

postgres@[local]:5432/postgres# \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description
-----------+---------+------------+-------------------------------------------------
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.1     | public     | generate universally unique identifiers (UUIDs)
(2 rows)

Can you verify via \dx that the extension isn't already loaded, but in a different schema that is outside of your search_path?

[oliverseal]

Ours fails loudly with an "extension already exists". Also I can't seem to create extension on a database other than "postgres".

[carlpett]

@sean- We're seeing this too (on Azure, if that matters). Checking with \dx, the extension is indeed created, but we can't use it on other databases than the default postgres one. The current resource doesn't seem to be able to set the database context the extension is installed in, though?

[carlpett]

Ah, we need to set the database on the provider. If I do so, it works. @inversion does this help your case?

[inversion]

We didn't have database set on the provider - interesting. For bootstrapping in one run I guess we'd need to use two provider instances and depends_on? I've noticed the provider seems to connect immediately though, so the provider with database set would probably fail to initialise? I'll have to try this and work out a minimal example.

[CowChris]

+1 with regards to the schema attribute on postgresql_extension being ignored.

[jleeh]

+1 in regards to silently failing. In my case, it was failing because in the provider I had superuser = false, but it was shown as successful. When I marked the user as a superuser in the provider, the extensions were created fine on the box.

[dixneuf19]

Hi,

I had the same issue with Postgres on AWS RDS, with a silent fail. As of today I cannot use this resource :

• the parameter superuser = true on the postgres provider is required for it to functions properly (otherwise there is a silent error)
• You cannot user this param on AWS RDS since you cannot directly use the superuser. You have an superuser like user..

I had to use a local-exec as shown below, but I would be interested in better solutions.

resource "null_resource" "postgres_extension_uuid_ossp" {

  provisioner "local-exec" {
    
    command = "PGPASSWORD=$PASSWORD psql --host=$HOST --port=$PORT --username=$USERNAME --dbname=$DATABASE -c 'CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"'"
    on_failure = fail

    environment = {
      PASSWORD = ... # from remote state
      HOST = # from remote state
      PORT = # from remote state
      USERNAME = # from remote state
      DATABASE = var.db_postgres_name
    }
  }
}