> For AI agents: the complete documentation index is available at /llms.txt, the full documentation bundle is available at /llms-full.txt. > **Themelio compatibility reference.** This page describes the retained Themelio implementation or its historical design. Check the [parallel-operation guide](/compatibility/index.md) 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](/adr/0016-declarative-database-list-in-postgres-role.md) **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: | Field | Type | Description | | --------------- | ------ | ----------------------------------------------------------------------------- | | `resource_name` | string | Name of the CNPG `Database` Kubernetes resource (e.g. `pg-gitlab`) | | `db_name` | string | Actual PostgreSQL database name (e.g. `gitlabhq`) | | `user` | string | PostgreSQL role that owns the database (e.g. `gitlab`) | | `secret_name` | string | Name of the Kubernetes `Secret` holding credentials (e.g. `gitlab-db-secret`) | | `extensions` | list | List of `{name, ensure}` dicts for PostgreSQL extensions; may be empty | 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: ```yaml 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: ```yaml # 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: ```yaml # 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: ```yaml 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: ```yaml - 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`:** ```jinja2 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`:** ```jinja2 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: ```jinja2 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//hosts.yml playbooks/postgres.yml`. No changes to the role itself are needed. ## Testing Strategy 1. Run static checks against the changed role files: ```bash 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: ```bash ansible-playbook -i inventory/local/hosts.yml playbooks/postgres.yml --check ``` 3. Apply against the local Minikube cluster: ```bash ansible-playbook -i inventory/local/hosts.yml playbooks/postgres.yml ``` 4. Verify Kubernetes state: ```bash # 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](/adr/0016-declarative-database-list-in-postgres-role.md) - [CloudNativePG Database CRD](https://cloudnative-pg.io/documentation/current/database_object/) - [CloudNativePG Managed Roles](https://cloudnative-pg.io/documentation/current/postgresql_conf/#managed-roles) - [Ansible `unique` filter](https://docs.ansible.com/ansible/latest/collections/ansible/builtin/unique_filter.html)
Page source Source snapshot · Revision `f928834c8`.