files/setup_postgres.sh.tpl

sed -i 's/host    all             all             127.0.0.1\/32            md5/host    all             all             127.0.0.1\/32            trust/' /etc/postgresql/12/main/pg_hba.conf
              

---

instances.tf

variable "do_token" {}

# Configure the DigitalOcean Provider
provider "digitalocean" {
  token = var.do_token
}

resource "digitalocean_droplet" "databases" {
  image  = "ubuntu-20-04-x64"
  name   = "database-${count.index}"
  region = "ams3"
  size   = "1gb"
  count  = 1

  private_networking = true
  monitoring         = true

  ssh_keys = ["e8:22:d9:3e:e8:95:73:b3:25:4b:85:d4:92:b7:6b:19"]

  connection {
    user        = "root"
    type        = "ssh"
    private_key = file("/home/omar/.ssh/id_rsa")
    timeout     = "2m"
    host        = self.ipv4_address
  }

  # https://www.terraform.io/docs/configuration/functions/templatefile.html
  provisioner "file" {
    content     = templatefile("files/setup_postgres.sh.tpl", {})
    destination = "/root/setup_postgres.sh"
  }

  provisioner "remote-exec" {
    inline = [
      "export PATH=$PATH:/usr/bin",
      "wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -",
      "echo \"deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main\" |sudo tee  /etc/apt/sources.list.d/pgdg.list",
      "sudo apt-get update",
      "sudo apt-get -y install nginx postgresql-12 postgresql-client-12",
      "echo 'y'|ufw enable",
      "sudo ufw allow 'Nginx HTTP'",
      "sudo ufw allow 'Nginx HTTPS'",
      "sudo ufw allow ssh",
      "sudo -u postgres psql -c \"alter user postgres with password 'test1234'\"",
      "sudo -u postgres psql -c \"CREATE DATABASE todo;\"",
      "sudo -u postgres psql -c \"CREATE USER app WITH ENCRYPTED PASSWORD 'test123';\"",
      "sudo -u postgres psql -c \"GRANT ALL PRIVILEGES ON DATABASE todo to app;\"",
      "chmod +x setup_postgres.sh",
      "bash setup_postgres.sh"
    ]
  }
}