Issues configuring PostgreSQL Schema via Terraform Null Resource

Question

BLUF: I need to be able to add SCHEMAs to PostgreSQL 10.4 RDS DB instance via terraform in a fully automated way.

I am trying to do a fully automated install of Private Terraform Enterprise (PTFE). The deployment guide calls for a PostgreSQL v9.4 or newer. This deployment is on AWS, so I have chosen to deploy psql via RDS. RDS currently only has version 10.5 at this time.

I have no experience with psql and limited experience with bash. I attempted to use terraform's "Null Resource" and run a local provisioner to execute a few commands. See Below:

resource "null_resource" "db_setup" {
  depends_on = ["aws_db_instance.pes", "aws_security_group.db_main", "random_pet.db-pwd"]

    provisioner "local-exec" {
      command = <<-EOF
       ssh -i '~/example.pem' ubuntu@${var.ec2_instance}
       sudo apt-get -y install postgresql 
       psql --host=${aws_db_instance.pes.address} --port=5432 --username=example_username --dbname=example_name -c 'CREATE SCHEMA rails;' -c 'CREATE SCHEMA vault;' -c 'CREATE SCHEMA registry;' 
       EOF

      environment {
        PGPASSWORD = "${random_pet.db-pwd.id}"
      }
    }
}

When I run this in my "terraform apply" the DB and all other resources correctly build. When the terraform goes to execute the null resource "db-setup" it looks like it's not passing the password when prompted and also getting a "Pseudo-terminal will not be allocated because stdin is not a terminal."

module.pes.null_resource.db_setup: Creating...
module.pes.null_resource.db_setup: Provisioning with 'local-exec'...
module.pes.null_resource.db_setup (local-exec): Executing: ["/bin/sh" "-c" "ssh -i '~/example.pem' ubuntu@<redacted>\nsudo apt-get -y install postgresql \npsql --host=<redacted> --port=5432 --username=example_username --dbname=example_name -c 'CREATE SCHEMA rails;' -c 'CREATE SCHEMA vault;' -c 'CREATE SCHEMA registry;' \n"]
module.pes.null_resource.db_setup (local-exec): Pseudo-terminal will not be allocated because stdin is not a terminal.
module.pes.null_resource.db_setup (local-exec): Welcome to Ubuntu 18.04.1 LTS (GNU/Linux 4.15.0-1032-aws x86_64)

module.pes.null_resource.db_setup (local-exec):  * Documentation:  https://help.ubuntu.com
module.pes.null_resource.db_setup (local-exec):  * Management:     https://landscape.canonical.com
module.pes.null_resource.db_setup (local-exec):  * Support:        https://ubuntu.com/advantage
......

Password:module.pes.null_resource.db_setup: Still creating... (10s elapsed)

As you can see above, it shows the Password prompt you would normally see when you run the command. The creation will eventually timeout. I have tested the command string manually via the instance that I am running the command on and it is successful. I variables that are getting interpolated are correct, and the password generation works. I am thinking that there is something wrong with the "local-exec" not passing the PGPASSWORD.

OS: Ubuntu 18.04 PostreSQL: 10.4

I am open to any ideas to get this automated via terraform and working. Thanks in advance.

Rafael G. Busato Rafael G. Busato · Accepted Answer · 2019-07-29T19:11:28

The best way to "get this automated via terraform and working" is by leveraging the PostgreSQL provider.

You will need to specify the provider configuration like so (using 0.12 syntax):

provider "postgresql" {
  host             = var.pg_server.fqdn
  database         = var.pg_db.name
  username         = "${var.pg_admin}@${var.pg_server.name}"
  password         = var.pg_password
  sslmode          = "require"
  connect_timeout  = 15
  expected_version = var.pg_version
}

NOTE: the above requires an actual database to be provisioned as mentioned here. In addition to the 2 suggested workarounds mentioned in the link, if you use a terraform wrapper like terragrunt, you can easily get around this limitation by splitting the schema creation in a separate module and configuring a module dependency.

To actually create the schema required by TFE you do:

resource "postgresql_schema" "rails" {
  name = "rails"
}

resource "postgresql_schema" "vault" {
  name = "vault"
}

resource "postgresql_schema" "registry" {
  name = "registry"
}

I just had to do the same in Azure and I used this combination successfully.

Hope that helps.

Issues configuring PostgreSQL Schema via Terraform Null Resource

1 Answers