Themelio compatibility reference. This page describes the retained Themelio implementation or its historical design. Check the parallel-operation guide before applying it to the current platform.

#DD-0016: Declarative Database List in the Ansible postgres Role — Implementation

Related ADR: ADR-0016: Declarative Database List in the Ansible postgres Role

Status: Draft

Date: 2026-06-04

Authors: Thanasis Katsadas

#Overview

This document specifies the refactoring of devops/ansible/roles/postgres/ to replace flat per-database Ansible variables with a postgres_databases list defined in inventory. The role will iterate over that list to validate passwords, create Kubernetes secrets, configure CNPG managed roles, and apply CNPG Database CRDs. Five database/secret-specific Jinja2 templates are replaced by two generic ones. Vault passwords are consolidated into a vault_postgres_passwords dict keyed by PostgreSQL username.

#Goals and Non-Goals

#Goals

• Replace all flat per-database variables with a single postgres_databases list entry per database.
• Consolidate five per-database Jinja2 templates into two generic ones.
• Replace seven per-database task blocks with three loops.
• Derive CNPG cluster managed roles dynamically from the list.
• Migrate vault password variables to a vault_postgres_passwords dict in all four environment vault files.
• Define postgres_databases in inventory/common/group_vars/all.yml so it is shared across all environments.

#Non-Goals

• Projecting database credentials into application namespaces — each service role remains responsible for that.
• Changing the CNPG cluster configuration, storage, or image settings.
• Supporting multiple CNPG clusters; the role manages one cluster named {{ cnpg_cluster_name }}.
• Migrating MLflow or Waldur per-tenant databases, which are managed by separate roles.

#Design

#1. postgres_databases List Schema

Each entry in the list describes one CNPG Database CRD and the Kubernetes Secret used to authenticate its owner. Required fields:

Multiple database entries may share the same user and secret_name (e.g., celery_results shares the waldur user). The role deduplicates by secret_name when creating secrets and by user when configuring CNPG managed roles.

#2. Inventory Changes

inventory/common/group_vars/all.yml — add the postgres_databases list:

postgres_databases:
  - resource_name: pg-gitlab
    db_name: gitlabhq
    user: gitlab
    secret_name: gitlab-db-secret
    extensions:
      - name: pg_trgm
        ensure: present
      - name: btree_gist
        ensure: present
      - name: amcheck
        ensure: present
  - resource_name: pg-waldur
    db_name: waldur
    user: waldur
    secret_name: waldur-db-secret
    extensions: []
  - resource_name: pg-waldur-celery-results
    db_name: celery_results
    user: waldur
    secret_name: waldur-db-secret
    extensions: []

Each environment's vault.yml (dev, staging, local, production) — replace the two flat password vars with a dict:

# Before
vault_gitlab_db_password: "..."
vault_waldur_db_password: "..."

# After
vault_postgres_passwords:
  gitlab: "..."
  waldur: "..."

The actual password values are unchanged. Only the key structure changes.

#3. defaults/main.yml Changes

Remove all flat per-database variables:

# Remove these:
gitlab_db_secret_name, gitlab_db_user, gitlab_db_name, gitlab_db_resource_name
gitlab_db_extensions
waldur_db_secret_name, waldur_db_user, waldur_db_name, waldur_db_resource_name
waldur_celery_results_db_name, waldur_celery_results_db_resource_name
waldur_db_extensions

Add an empty default so the role fails gracefully if the inventory omits the list:

postgres_databases: [] # noqa var-naming[no-role-prefix]

All cnpg_* vars and databases_namespace remain unchanged.

#4. tasks/main.yml Changes

Replace the two separate validation tasks, two secret creation tasks, and three database application tasks with three loops:

- name: Validate database passwords are provided
  ansible.builtin.assert:
    that:
      - vault_postgres_passwords is defined
      - item in vault_postgres_passwords
      - vault_postgres_passwords[item] | length > 0
    fail_msg: "Missing vault_postgres_passwords.{{ item }}"
  loop: "{{ postgres_databases | map(attribute='user') | unique | list }}"

- name: Create database secrets
  no_log: true
  kubernetes.core.k8s:
    state: present
    definition: "{{ lookup('template', 'db-secret.yml.j2') | from_yaml }}"
  vars:
    _db: "{{ item }}"
    _db_password: "{{ vault_postgres_passwords[item.user] }}"
  loop: "{{ postgres_databases | unique(attribute='secret_name') | list }}"

- name: Apply CNPG Cluster
  kubernetes.core.k8s:
    state: present
    definition: "{{ lookup('template', 'cnpg-cluster.yml.j2') | from_yaml }}"

- name: Apply databases
  kubernetes.core.k8s:
    state: present
    definition: "{{ lookup('template', 'database.yml.j2') | from_yaml }}"
  vars:
    _db: "{{ item }}"
  loop: "{{ postgres_databases }}"

The Helm repository setup, CNPG operator install, and namespace creation tasks are unchanged.

#5. Template Changes

Remove the five per-database templates:

• gitlab-database.yml.j2
• waldur-database.yml.j2
• waldur-celery-results-database.yml.j2
• gitlab-db-secret.yml.j2
• waldur-db-secret.yml.j2

Add templates/db-secret.yml.j2:

apiVersion: v1
kind: Secret
metadata:
  name: "{{ _db.secret_name }}"
  namespace: "{{ databases_namespace }}"
type: kubernetes.io/basic-auth
stringData:
  username: "{{ _db.user }}"
  password: "{{ _db_password }}"

Add templates/database.yml.j2:

apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: "{{ _db.resource_name }}"
  namespace: "{{ databases_namespace }}"
spec:
  name: "{{ _db.db_name }}"
  owner: "{{ _db.user }}"
  cluster:
    name: "{{ cnpg_cluster_name }}"
{% if _db.extensions | default([]) | length > 0 %}
  extensions:
{% for ext in _db.extensions %}
    - name: "{{ ext.name }}"
      ensure: {{ ext.ensure }}
{% endfor %}
{% endif %}

Update templates/cnpg-cluster.yml.j2 — replace the hardcoded managed.roles block:

  managed:
    roles:
{% for db in postgres_databases | unique(attribute='user') %}
      - name: "{{ db.user }}"
        ensure: present
        login: true
        passwordSecret:
          name: "{{ db.secret_name }}"
{% endfor %}

#6. Adding a New Database (After Refactor)

To add a new database (e.g., for k8s-root-actor):

1. Add one entry to postgres_databases in inventory/common/group_vars/all.yml.
2. If it introduces a new PostgreSQL user, add the password to vault_postgres_passwords in each environment's vault.yml.
3. Run ansible-playbook -i inventory/<env>/hosts.yml playbooks/postgres.yml.

No changes to the role itself are needed.

#Testing Strategy

1. Run static checks against the changed role files:

   devops/ansible/scripts/ci/run_tests_local.sh --files roles/postgres/tasks/main.yml yamllint
   devops/ansible/scripts/ci/run_tests_local.sh --files roles/postgres/tasks/main.yml lint

2. Run with --check against the local inventory to validate template rendering and task structure:

   ansible-playbook -i inventory/local/hosts.yml playbooks/postgres.yml --check

3. Apply against the local Minikube cluster:

   ansible-playbook -i inventory/local/hosts.yml playbooks/postgres.yml

4. Verify Kubernetes state:

   # Three Database CRDs
   kubectl get database -n databases
   
   # Two secrets (not three — dedup by secret_name works)
   kubectl get secret -n databases
   
   # CNPG cluster shows both managed roles
   kubectl get cluster pg -n databases -o yaml | grep -A 10 'managed:'

5. Re-run the playbook to confirm idempotency (no changes reported on second run).

#Deployment & Rollout

1. Re-encrypt each environment's vault file to replace vault_gitlab_db_password and vault_waldur_db_password with vault_postgres_passwords.
2. Add postgres_databases to inventory/common/group_vars/all.yml.
3. Apply the role changes to defaults/main.yml, tasks/main.yml, templates.
4. Run ansible-playbook --check against local, then apply.
5. Apply to dev, staging, and production in sequence following the normal deployment process.

Rollback: the existing Kubernetes resources (Database CRDs, secrets, CNPG cluster) are not deleted by this change — the role only applies state: present. If rollback is needed, revert the role files and vault structure and re-run the playbook.

#References

• ADR-0016: Declarative Database List in the Ansible postgres Role
• CloudNativePG Database CRD
• CloudNativePG Managed Roles
• Ansible unique filter