[P16] Replace docker exec psql with direct PostgreSQL connection #109

New Issue

kert · 2026-03-22T17:12:52Z

kert commented

2026-03-22 17:12:52 +00:00

Problem

provision_postgres() uses docker exec postgres psql to rotate passwords. This requires:

Docker socket mounted in the CI step container
The postgres container must be running and named exactly postgres
The CI step must be on a network that can reach the Docker daemon

This is fragile — if the postgres container restarts during deploy, or the Docker socket path changes, provision fails.

Fix

Use a direct PostgreSQL connection via psycopg2 or asyncpg instead of docker exec. The CI step container can connect to postgres via the CI network.

import psycopg2
conn = psycopg2.connect(host='postgres', port=5432, user='postgres', password=superuser_pw)
conn.autocommit = True
cur = conn.cursor()
cur.execute(f"ALTER ROLE {role} PASSWORD %s", (new_pw,))

This removes the Docker socket dependency for PostgreSQL provisioning.

Acceptance criteria

provision_postgres() connects directly to PostgreSQL, no docker exec
Parameterized queries (no SQL injection via password strings)
Works from CI containers on the ci/storage network
Existing tests updated
No Docker socket needed for provision (only for compose up)

## Problem `provision_postgres()` uses `docker exec postgres psql` to rotate passwords. This requires: - Docker socket mounted in the CI step container - The postgres container must be running and named exactly `postgres` - The CI step must be on a network that can reach the Docker daemon This is fragile — if the postgres container restarts during deploy, or the Docker socket path changes, provision fails. ## Fix Use a direct PostgreSQL connection via `psycopg2` or `asyncpg` instead of `docker exec`. The CI step container can connect to postgres via the CI network. ```python import psycopg2 conn = psycopg2.connect(host='postgres', port=5432, user='postgres', password=superuser_pw) conn.autocommit = True cur = conn.cursor() cur.execute(f"ALTER ROLE {role} PASSWORD %s", (new_pw,)) ``` This removes the Docker socket dependency for PostgreSQL provisioning. ## Acceptance criteria - [ ] `provision_postgres()` connects directly to PostgreSQL, no `docker exec` - [ ] Parameterized queries (no SQL injection via password strings) - [ ] Works from CI containers on the ci/storage network - [ ] Existing tests updated - [ ] No Docker socket needed for provision (only for compose up)

kert added this to the P16: Provision Hardening — resilient credential rotation milestone 2026-03-22 17:12:52 +00:00

kert added the ci label 2026-03-22 17:12:52 +00:00

kert referenced this issue

2026-03-22 17:12:53 +00:00

[P16] Fix SQL injection in provision_postgres password rotation #113

kert referenced this issue from a commit

2026-03-22 17:32:24 +00:00

harden provision: API-only Gitea, safe SQL, retry, Woodpecker sync

kert closed this issue

2026-03-22 17:32:24 +00:00

Sign in to join this conversation.