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

New Issue

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

kert commented

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

Problem

The current code uses string interpolation to build SQL:

f"ALTER ROLE {role} PASSWORD '{pw}'"

While pw comes from HKDF derivation (hex or base64url, no special SQL chars), this is still a security anti-pattern. A future format change or bug could introduce injection.

Fix

When migrating to direct psycopg connection (#109), use parameterized queries:

# psycopg2 doesn't support parameterized DDL, but we can use:
from psycopg2 import sql
cur.execute(
    sql.SQL("ALTER ROLE {} PASSWORD %s").format(sql.Identifier(role)),
    (pw,)
)

Acceptance criteria

No string interpolation in SQL statements
Role names use sql.Identifier()
Passwords passed as parameters
Blocked by #109 (direct PostgreSQL connection)

## Problem The current code uses string interpolation to build SQL: ```python f"ALTER ROLE {role} PASSWORD '{pw}'" ``` While `pw` comes from HKDF derivation (hex or base64url, no special SQL chars), this is still a security anti-pattern. A future format change or bug could introduce injection. ## Fix When migrating to direct psycopg connection (#109), use parameterized queries: ```python # psycopg2 doesn't support parameterized DDL, but we can use: from psycopg2 import sql cur.execute( sql.SQL("ALTER ROLE {} PASSWORD %s").format(sql.Identifier(role)), (pw,) ) ``` ## Acceptance criteria - [ ] No string interpolation in SQL statements - [ ] Role names use `sql.Identifier()` - [ ] Passwords passed as parameters - [ ] Blocked by #109 (direct PostgreSQL connection)

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

kert added the quality label 2026-03-22 17:12:53 +00:00

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.