postgres/example/postgres-manage.nomad.hcl

job "postgres-manage" {
  type = "batch"

  meta {
    # Force job to run each time
    run = "${uuidv4()}"
  }


  datacenters = ["dc1"]
  region      = "global"
  priority    = 80


  group "manage" {
    network {
      mode = "bridge"
    }

    ephemeral_disk {
      size = 101
    }

    service {
      name = "postgres-manage"
      connect {
        sidecar_service {
          proxy {
            upstreams {
              destination_name = "postgres"
              local_bind_port  = 5432
              # Work arround, see https://github.com/hashicorp/nomad/issues/18538
              destination_type = "service"
            }
          }
        }
        sidecar_task {

          logs {
            disabled = false
          }

          config {
            args = [
              "-c",
              "${NOMAD_SECRETS_DIR}/envoy_bootstrap.json",
              "-l",
              "${meta.connect.log_level}",
              "--concurrency",
              "${meta.connect.proxy_concurrency}",
              "--disable-hot-restart"
            ]
          }

          resources {
            cpu    = 50
            memory = 64
          }

        }
      }

    }

    # wait for required services tp be ready before starting the main task
    task "wait-for" {

      driver = "docker"
      user   = 1053

      config {
        image           = "danielberteaud/wait-for:24.5-1"
        readonly_rootfs = true
        pids_limit      = 20
      }

      lifecycle {
        hook = "prestart"
      }

      env {
        SERVICE_0 = "master.postgres.service.consul"
      }

      resources {
        cpu        = 10
        memory     = 10
        memory_max = 30
      }
    }



    task "postgres-manage" {
      driver = "docker"

      config {
        image           = "danielberteaud/ldap2pg:6.0-10"
        readonly_rootfs = true
        pids_limit      = 20
      }


      vault {
        policies     = ["postgres"]
        env          = false
        disable_file = true
        change_mode  = "noop"
      }


      env {
        LDAP2PG_CONFIG = "/secrets/ldap2pg.yml"
        LDAP2PG_MODE   = "dry"
      }



      # Use a template block instead of env {} so we can fetch values from vault
      template {
        data        = <<_EOT
LANG=fr_FR.utf8
TZ=Europe/Paris
WAIT_FOR_TARGETS=localhost:5432
_EOT
        destination = "secrets/.env"
        perms       = 400
        env         = true
      }


      template {
        data        = <<_EOF
PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGPASSWORD={{ with secret "kv/service/postgres" }}{{ .Data.data.pg_pwd | sprig_squote }}{{ end }}
_EOF
        destination = "secrets/pg-manage.env"
        uid         = 100000
        gid         = 100000
        perms       = 0400
        env         = true
      }

      template {
        data        = <<_EOF
postgres:
  managed_roles_query: |
    VALUES
      ('public'),
      ('managed_roles')

    UNION

    SELECT DISTINCT role.rolname
    FROM pg_roles AS role
    JOIN pg_auth_members AS ms ON ms.member = role.oid
    JOIN pg_roles AS parent
      ON parent.rolname = 'managed_roles' AND parent.oid = ms.roleid
    ORDER BY 1;
privileges:
  owner:
    - writer
    - __create_on_schemas__
    - __truncate_on_tables__
  reader:
    - user
    - __select_on_tables__
    - __select_on_sequences__
    - __usage_on_sequences__
  rewinder:
    - __connect__
    - __execute_on_functions__
  user:
    - __connect__
    - __usage_on_schema__
  writer:
    - reader
    - __temporary__
    - __insert_on_tables__
    - __update_on_tables__
    - __delete_on_tables__
    - __update_on_sequences__
    - __execute_on_functions__
    - __trigger_on_tables__
version: 6


rules:
  - roles:
      - comment: Parent role for all ldap2pg managed roles
        name: managed_roles
      - comment: Parent role for LDAP synced roles
        name: ldap_roles
        options: NOLOGIN
        parents:
          - managed_roles
      - comment: DB backup
        name: backup
        options: LOGIN REPLICATION
        parents:
          - pg_read_all_data
          - managed_roles
      - comment: Databases admins
        name: dba
        options: SUPERUSER NOLOGIN
        parents:
          - managed_roles
      - comment: Databases rewinder
        name: rewind
        options: LOGIN
        parents:
          - managed_roles
      - comment: Databases monitor
        name: monitor
        options: LOGIN
        parents:
          - managed_roles
          - pg_monitor
      - comment: Hashicorp Vault
        name: vault
        options: SUPERUSER LOGIN
        parents:
          - managed_roles
  - grant:
      databases: postgres
      privileges: reader
      role: vault
  - grant:
      privileges: user
      role: monitor
  - grant:
      databases: postgres
      privileges: rewinder
      role: rewind
  - grant:
      privileges: owner
      role: dba

_EOF
        destination = "secrets/ldap2pg.yml"
        uid         = 100000
        gid         = 100000
        perms       = 0400
      }


      resources {
        cpu    = 50
        memory = 32
      }

    }
  }
}