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

# 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)

**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/<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:

   ```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)
- [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)