Enabling PostGIS on AWS RDS PostgreSQL with Terraform

Question

Can't find a way to activate PostGIS extension using official aws_db_instance resource, is there are any other options to activate it with Terraform?

Answer 1

You should be able to do it using postgres provider. It may be tricky with connectivity though.

provider "postgresql" {
  host             = aws_db_instance.app_rds.address
  port             = aws_db_instance.app_rds.port
  database         = "postgres"
  username         = aws_db_instance.app_rds.username
  password         = var.db_admin_password
  sslmode          = "require"
  expected_version = aws_db_instance.app_rds.engine_version
  superuser        = false
}

# Installs postgres PostGIS extension
resource "postgresql_extension" "postgis" {
  name = "postgis"
}

Answer 2

The provider approach effectively just connects to the database instance with the provided credentials and then runs whatever commands you request, like creating the extension. If your RDS instance is on a private subnet and Terraform is being run from elsewhere (e.g. Terraform Cloud), then the provider won't be able to connect. Security best practice says that you shouldn't make your database directly accessible from the internet, so this approach won't work for most people.

Instead, you can install the extension in the setup code for the container which is interacting with the database. In my case, I was using Flask to access the RDS instance, so it already had the credentials. My db initialization now looks like this:

from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

def create_app(config):
  app = Flask(__name__)

  db = SQLAlchemy()
  db.init_app(app)
  db.engine.execute("CREATE EXTENSION IF NOT EXISTS postgis")

  migrate = Migrate()
  migrate.init_app(app, db)