Something went wrong. Try again.
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224#!/usr/bin/env python3"""Regenerate the inline CVE / known-bug VALUES blocks in pgFirstAid's three SQL files.
Source of truth: data/cves.json - curated CVE entries (per-major fix-version map) data/known_bugs.json - curated non-CVE release-note bugs (fixed_in_minor)
Behaviour: python tools/generate_cve_sql.py # write fresh rows into all SQL files python tools/generate_cve_sql.py --check # dry run: exit 1 if any SQL file is stale
The generator inserts VALUES rows between sentinel comments in each SQL file:
-- GENERATED cves BEGIN (regenerate via tools/generate_cve_sql.py; do not edit) ('CVE-...', ...), ... -- GENERATED cves END
-- GENERATED bugs BEGIN ('PG...-...-..', ...), ... -- GENERATED bugs END
If a sentinel pair is missing, the script fails with a clear error."""
from __future__ import annotations
import argparseimport jsonimport sysfrom pathlib import Path
REPO_ROOT = Path(__file__).resolve().parent.parentDATA_DIR = REPO_ROOT / "data"
CVES_JSON = DATA_DIR / "cves.json"BUGS_JSON = DATA_DIR / "known_bugs.json"
# Files in which the inline VALUES blocks live. Order is irrelevant; we process# each independently.SQL_FILES = [ REPO_ROOT / "pgFirstAid.sql", REPO_ROOT / "view_pgFirstAid.sql", REPO_ROOT / "view_pgFirstAid_managed.sql",]
# Sentinel comments. Keep these strings stable: they are how the generator# locates where to write. Format: "-- GENERATED <kind> BEGIN" / "... END".SENTINEL_BEGIN_TEMPLATE = "-- GENERATED {kind} BEGIN (do not edit; regenerate via tools/generate_cve_sql.py)"SENTINEL_END_TEMPLATE = "-- GENERATED {kind} END"
# Indentation for the VALUES rows. Each row lines up with the surrounding# CTE block (8 spaces inside `values (`...).ROW_INDENT = " " # 8 spaces
def _version_num(major: str, minor: int) -> int: """Convert (major, minor) -> PostgreSQL server_version_num.
server_version_num format = MJMmm: e.g. 15.4 -> 150004, 17.11 -> 170011. """ return int(major) * 10000 + minor
def _sql_quote(s: str) -> str: """PostgreSQL-safe string literal: wrap in single quotes, double internal apostrophes.
Python's repr() emits backslash escapes (e.g. 'foo\\'s'), which PostgreSQL's standard_conforming_strings interprets as a multi-line string-start, not an embedded quote. Doubling the apostrophe ('foo''s') is the only form that works in plain single-quoted string literals under any PG setting. """ return "'" + s.replace("'", "''") + "'"
def _render_cve_row(cve_id: str, cvss: float, summary: str, major: str, minor: int, doc_link: str) -> str: """Render one VALUES row for a CVE entry expanded to a single major version.""" affected_min = _version_num(major, 0) fixed_in = _version_num(major, minor) return ( f"{ROW_INDENT}({_sql_quote(cve_id)}, {cvss}, " f"{_sql_quote(summary)}, {affected_min}, {fixed_in}, {_sql_quote(doc_link)})," )
def _render_bug_row(issue_id: str, summary: str, major: str, minor: int, doc_link: str) -> str: """Render one VALUES row for a known-bug entry expanded to a single major version.""" affected_min = _version_num(major, 0) fixed_in = _version_num(major, minor) return ( f"{ROW_INDENT}({_sql_quote(issue_id)}, {_sql_quote(summary)}, " f"{affected_min}, {fixed_in}, {_sql_quote(doc_link)})," )
def render_cve_rows(cves_doc: dict) -> list[str]: """Expand all CVE entries to per-major VALUES rows.
PostgreSQL VALUES rules: - Two or more rows: separated by commas, last row has NO trailing comma. - One row only: no comma at all. We render every row with a trailing comma, then strip the last one. """ out = [] for cve in cves_doc["cves"]: cve_id = cve["cve_id"] cvss = cve["cvss"] summary = cve["summary"] doc_link = cve["doc_link"] # Sort majors numerically so output is reproducible. for major in sorted(cve["fixed_in"], key=int): minor = cve["fixed_in"][major] out.append(_render_cve_row(cve_id, cvss, summary, major, minor, doc_link)) if out: # Strip the trailing comma from the last row: PostgreSQL rejects # the form `(...),` on the final VALUES entry. out[-1] = out[-1].rstrip(",") return out
def render_bug_rows(bugs_doc: dict) -> list[str]: """Expand all known-bug entries to per-major VALUES rows.
The bug rows keep the bug's major prefix encoded in the issue_id, so each bug only expands to its own single major (unlike CVEs which can affect many). """ out = [] for bug in bugs_doc["bugs"]: # Parse the leading major from the issue_id, e.g. "PG15-..." -> "15". # The major must be a non-empty decimal, otherwise the row can't map # to a server_version_num range and _version_num would either explode # or silently emit a non-matching row. prefix, sep, rest = bug["issue_id"].partition("-") if not prefix.startswith("PG") or not sep or not rest: raise ValueError( f"issue_id {bug['issue_id']!r} is malformed; expected 'PG<MAJOR>-<rest>'" ) major_str = prefix[2:] if not major_str.isdigit() or int(major_str) <= 0: raise ValueError( f"issue_id {bug['issue_id']!r} has non-positive or non-decimal major" ) out.append(_render_bug_row( bug["issue_id"], bug["summary"], major_str, bug["fixed_in_minor"], bug["doc_link"], )) if out: out[-1] = out[-1].rstrip(",") return out
def replace_block(sql_text: str, kind: str, new_rows: list[str]) -> str: """Replace the contents between the BEGIN/END sentinels for `kind`.""" begin = SENTINEL_BEGIN_TEMPLATE.format(kind=kind) end = SENTINEL_END_TEMPLATE.format(kind=kind)
begin_idx = sql_text.find(begin) if begin_idx == -1: raise SystemExit(f"missing sentinel {begin!r} in SQL file")
# Skip past the BEGIN line + the newline that follows. begin_idx = sql_text.find("\n", begin_idx) + 1
end_idx = sql_text.find(end, begin_idx) if end_idx == -1: raise SystemExit(f"missing sentinel {end!r} after BEGIN in SQL file")
# Strip the chunk between markers; preserve a single trailing newline so # the END sentinel lands on its own line. before = sql_text[:begin_idx] after = sql_text[end_idx:]
body = "\n".join(new_rows) + ("\n" if new_rows else "") return before + body + after
def regenerate_one(sql_path: Path, cve_rows: list[str], bug_rows: list[str]) -> str: text = sql_path.read_text() text = replace_block(text, "cves", cve_rows) text = replace_block(text, "bugs", bug_rows) return text
def main() -> int: parser = argparse.ArgumentParser(description=__doc__.split("\n", 1)[0]) parser.add_argument( "--check", action="store_true", help="exit non-zero if any SQL file would change (CI mode)", ) args = parser.parse_args()
cves_doc = json.loads(CVES_JSON.read_text()) bugs_doc = json.loads(BUGS_JSON.read_text())
cve_rows = render_cve_rows(cves_doc) bug_rows = render_bug_rows(bugs_doc)
failed = False for sql_path in SQL_FILES: current = sql_path.read_text() rendered = regenerate_one(sql_path, cve_rows, bug_rows) if current == rendered: print(f"ok {sql_path.relative_to(REPO_ROOT)}") continue diff_lines = sum(1 for a, b in zip(current.splitlines(), rendered.splitlines()) if a != b) print(f"stale {sql_path.relative_to(REPO_ROOT)} (diff {diff_lines} lines)") if args.check: failed = True else: sql_path.write_text(rendered) print(f"wrote {sql_path.relative_to(REPO_ROOT)}")
if args.check and failed: print("\nFAIL: SQL files are out of sync with data/*.json.", file=sys.stderr) print("Run `python tools/generate_cve_sql.py` to regenerate.", file=sys.stderr) return 1 return 0
if __name__ == "__main__": raise SystemExit(main())