morphit/ops/ansible/roles/postgres/tasks/main.yml

# Role: postgres
#
# Install Postgres, bind to loopback only (§37.8), provision the
# morphit_indexer + morphit_relay databases and users.
#
# Uses the Ubuntu-shipped postgresql package (Ubuntu 24.04 ships
# PG 16 by default).  If you need PG 17 to match the repo's
# ops/postgres/init.sql, add the PGDG apt repository first.

---
- name: Install PostgreSQL + Python adapter
  ansible.builtin.apt:
    name:
      - postgresql
      - postgresql-contrib
      - python3-psycopg2
    state: present
    update_cache: true

# The version-dir expressions below read `ansible_facts.packages` to find the
# Ubuntu-shipped /etc/postgresql/<major>/main directory.  Normal fact-gathering
# does NOT populate `ansible_facts.packages` — only package_facts does — so
# WITHOUT this task every one of those expressions (including its own
# `'postgresql' in ansible_facts.packages` guard) raised
# "object of type 'dict' has no attribute 'packages'" and the install died the
# moment it reached postgres.  cp635.
- name: Gather installed-package facts (populates ansible_facts.packages)
  ansible.builtin.package_facts:
    manager: auto

- name: Ensure postgresql listens on loopback only
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgres_version_dir }}/main/postgresql.conf
    regexp: "^#?listen_addresses\\s*="
    line: "listen_addresses = '{{ postgres_listen_addresses }}'"
  vars:
    # Detect the Ubuntu-shipped version dir
    postgres_version_dir: "{{ ansible_facts.packages['postgresql'][0].version.split('+')[0].split('.')[0] if 'postgresql' in ansible_facts.packages else '16' }}"
  notify: Restart postgresql

- name: Ensure postgresql port is the configured one
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgres_version_dir }}/main/postgresql.conf
    regexp: "^#?port\\s*="
    line: "port = {{ postgres_port }}"
  vars:
    postgres_version_dir: "{{ ansible_facts.packages['postgresql'][0].version.split('+')[0].split('.')[0] if 'postgresql' in ansible_facts.packages else '16' }}"
  notify: Restart postgresql

- name: Remove the permissive host-all-all loopback rule (hardening)
  # We do NOT want a blanket "any user, any DB over 127.0.0.1" rule. But we MUST
  # still let the ONE user the indexer + relay use in (both connect via
  # localhost:PORT = loopback TCP, sharing the morphit_indexer DB/user) — the
  # scoped rule below does exactly that. cp668: previously this task removed the
  # blanket rule and added NOTHING, so the indexer's TCP login had no matching
  # pg_hba entry and died with 'no pg_hba.conf entry for host "127.0.0.1"' the
  # first time Postgres reloaded pg_hba.conf (i.e. the next restart/reboot).
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgres_version_dir }}/main/pg_hba.conf
    regexp: '^host\s+all\s+all\s+'
    state: absent
  vars:
    postgres_version_dir: "{{ ansible_facts.packages['postgresql'][0].version.split('+')[0].split('.')[0] if 'postgresql' in ansible_facts.packages else '16' }}"
  notify: Restart postgresql

- name: Allow ONLY the morphit_indexer user in over loopback TCP (indexer + relay)
  # scram-sha-256 matches the password encoding the postgresql_user task writes on
  # PG 14+ (Ubuntu 24 ships PG 16). Both IPv4 and IPv6 loopback are covered because
  # `localhost` in the DATABASE_URL can resolve to either 127.0.0.1 or ::1.
  ansible.builtin.lineinfile:
    path: /etc/postgresql/{{ postgres_version_dir }}/main/pg_hba.conf
    line: "{{ item }}"
    insertafter: EOF
    state: present
  loop:
    - "host    {{ postgres_indexer_db }}    {{ postgres_indexer_user }}    127.0.0.1/32    scram-sha-256"
    - "host    {{ postgres_indexer_db }}    {{ postgres_indexer_user }}    ::1/128    scram-sha-256"
  vars:
    postgres_version_dir: "{{ ansible_facts.packages['postgresql'][0].version.split('+')[0].split('.')[0] if 'postgresql' in ansible_facts.packages else '16' }}"
  notify: Restart postgresql

- name: Enable + start PostgreSQL
  ansible.builtin.systemd:
    name: postgresql
    enabled: true
    state: started

- name: Create morphit_indexer user
  community.postgresql.postgresql_user:
    name: "{{ postgres_indexer_user }}"
    password: "{{ postgres_indexer_password }}"
    role_attr_flags: NOSUPERUSER,NOCREATEROLE,NOCREATEDB
    state: present
  become: true
  become_user: postgres

# cp663 #5 — the relay + indexer SHARE the indexer DB (the relay's tables
# are part of the indexer's single unified migration set, applied only to
# the indexer DB).  A separate morphit_relay DB/user was created here but
# never got the schema, so the relay hit "relation does not exist".  It's
# no longer created; relay.env.j2 points the relay at the indexer DB.
# (cp664 deep-deep: the vestigial postgres_relay_* / vault_postgres_relay_password
# vars and the ops-cli relayDbPassword input have now been removed too.)

- name: Create morphit_indexer database
  community.postgresql.postgresql_db:
    name: "{{ postgres_indexer_db }}"
    owner: "{{ postgres_indexer_user }}"
    encoding: UTF8
    lc_collate: en_US.UTF-8
    lc_ctype: en_US.UTF-8
    template: template0
    state: present
  become: true
  become_user: postgres

- name: Apply repo's init.sql to indexer DB if present
  ansible.builtin.shell:
    cmd: psql -d {{ postgres_indexer_db }} -f {{ morphit_source_dir }}/ops/postgres/init.sql
    creates: "{{ morphit_source_dir }}/ops/postgres/.init.applied"
  become: true
  become_user: postgres
  failed_when: false
  # init.sql may already have been applied; the schema migrator
  # handles further evolution.  The `morphit` role runs migrations
  # after services start.