mirror of
https://github.com/kevinveenbirkenbach/computer-playbook.git
synced 2026-11-06 10:59:59 +00:00
kevinveenbirkenbachandClaude Opus 4.7
1aa1b084d5
The schema-privileges block in svc-db-postgres/tasks/02_init.yml mixed
GRANT/ALTER DEFAULT PRIVILEGES SQL with Jinja interpolation in a 4-line
inline `query: |` scalar. Move the statements into
`roles/svc-db-postgres/templates/sql/grant_schema_privileges.sql.j2` and
load via `lookup('template', ...)` so the SQL stays reviewable as plain
SQL (Jinja stays only for identifier interpolation, since Postgres
prepared-statement bindings cannot substitute identifiers).
Also adds tests/lint/ansible/test_no_inline_multiline_sql.py that fails
when more than 3 SQL-leader lines (INSERT/UPDATE/SELECT/GRANT/ALTER/…)
appear in a single inline YAML scalar under roles/, tasks/, or
playbooks/. The error message points at the extraction-via-lookup
pattern so future blocks land in files/<name>.sql or
templates/sql/<name>.sql.j2 by default.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
13 lines
661 B
Django/Jinja
13 lines
661 B
Django/Jinja
-- Grant USAGE/CREATE on schema public to the per-app database role and
|
|
-- set ALTER DEFAULT PRIVILEGES so future tables created by other roles
|
|
-- still grant ALL to the app role.
|
|
--
|
|
-- Identifier substitution is done via Jinja at render time because
|
|
-- Postgres prepared-statement bindings ($1, %(name)s) only substitute
|
|
-- values, not identifiers. `database_username` is inventory-sourced,
|
|
-- so there is no untrusted-input path here.
|
|
GRANT USAGE ON SCHEMA public TO "{{ database_username }}";
|
|
GRANT CREATE ON SCHEMA public TO "{{ database_username }}";
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT ALL PRIVILEGES ON TABLES TO "{{ database_username }}";
|