Skip to content

Commit f003851

Browse files
fix(postgres): refuse system databases and never drop them
postgres, template0 and template1 were accepted in databases and handled like an application database. The init job made <db>_admin their owner, installed the auto-grant event trigger in them and, once the entry left values, ran DROP DATABASE on them. That DROP can only fail (template databases cannot be dropped, and the job is connected to postgres), so every later upgrade failed in the init job and the release stayed in UpgradeFailed even after the entry was put back. template1 also passed the takeover on to every database created from it afterwards, where the trigger, naming a role about to be dropped, makes CREATE SCHEMA fail. A release a previous version let take one of them over has to be repaired without dropping it, and without touching what this chart did not create: a role or owner that merely carries an expected name may belong to a tenant or an administrator, and a role comment can be set by any CREATEROLE holder, so only the cozystack:managed mark is trusted. What the marked roles own goes to cozystack:orphaned rather than to the superuser, since a tenant's SECURITY DEFINER function would otherwise run with superuser rights. A failed lookup of the databases cloned from template1 has to stop the run before template1 stops being selected, or those clones would never be repaired. Assisted-by: LLM Signed-off-by: Mattia Eleuteri <[email protected]>
1 parent 0c48626 commit f003851

10 files changed

Lines changed: 635 additions & 12 deletions

File tree

‎api/apps/v1alpha1/postgresql/types.go‎

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎hack/e2e-chainsaw/postgres/chainsaw-test.yaml‎

Lines changed: 145 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -347,3 +347,148 @@ spec:
347347
set -eu
348348
cd ../../..
349349
NAMESPACE="$NAMESPACE" examples/backups/postgres/cleanup.sh
350+
---
351+
# A system database that a version before the refusal took over is handed back
352+
# by the next init-job run, and only what this chart marked changes hands.
353+
#
354+
# The helm-unittest and bats suites check the rendered SQL against a fake psql;
355+
# only a real PostgreSQL can say that the predicates select what they claim. The
356+
# state an older version left is rebuilt by hand as the superuser, since the
357+
# chart now refuses to produce it:
358+
# * postgres is commented as managed and owned by a postgres_admin role that
359+
# carries the cozystack:managed mark, as the chart left it: it must come back
360+
# to the superuser, and the role must be dropped;
361+
# * template1 is commented as managed, but its template1_admin carries only the
362+
# role comment, which a CREATEROLE holder can forge, and not the mark: the
363+
# comment must be cleared and everything that role owns or was granted must
364+
# stay as it is, in template1 and in a database cloned from it.
365+
apiVersion: chainsaw.kyverno.io/v1alpha1
366+
kind: Test
367+
metadata:
368+
name: postgres-3-system-databases
369+
spec:
370+
catch:
371+
- events: {}
372+
- describe:
373+
apiVersion: helm.toolkit.fluxcd.io/v2
374+
kind: HelmRelease
375+
name: postgres-sysdb
376+
- podLogs:
377+
selector: job-name=postgres-sysdb-init-job
378+
tail: 200
379+
steps:
380+
- name: create-postgres
381+
try:
382+
- apply:
383+
file: postgres-sysdb.yaml
384+
# 11m: above the 600s install timeout cozystack-api gives this release;
385+
# see "Sizing an HR-Ready budget" in docs/agents/e2e-testing.md.
386+
- assert:
387+
timeout: 11m
388+
resource:
389+
apiVersion: helm.toolkit.fluxcd.io/v2
390+
kind: HelmRelease
391+
metadata:
392+
name: postgres-sysdb
393+
status:
394+
(conditions[?type == 'Ready']):
395+
- status: "True"
396+
- assert:
397+
timeout: 2m
398+
resource:
399+
apiVersion: batch/v1
400+
kind: Job
401+
metadata:
402+
name: postgres-sysdb-init-job
403+
status:
404+
(conditions[?type == 'Complete']):
405+
- status: "True"
406+
407+
- name: rebuild-taken-over-system-databases
408+
try:
409+
- script:
410+
timeout: 3m
411+
content: |
412+
set -eu
413+
pod=$(kubectl -n "$NAMESPACE" get pod \
414+
-l cnpg.io/cluster=postgres-sysdb,cnpg.io/instanceRole=primary -o name | head -n1)
415+
[ -n "$pod" ] || { echo "no primary pod for postgres-sysdb" >&2; exit 1; }
416+
sql() { kubectl -n "$NAMESPACE" exec -i "$pod" -c postgres -- psql -v ON_ERROR_STOP=1 -U postgres -d "$1"; }
417+
sql postgres <<'SQL'
418+
CREATE ROLE postgres_admin NOLOGIN;
419+
COMMENT ON ROLE postgres_admin IS 'role managed by helm';
420+
GRANT "cozystack:managed" TO postgres_admin WITH INHERIT FALSE, SET FALSE;
421+
COMMENT ON DATABASE postgres IS 'database managed by helm';
422+
ALTER DATABASE postgres OWNER TO postgres_admin;
423+
CREATE ROLE template1_admin NOLOGIN;
424+
COMMENT ON ROLE template1_admin IS 'role managed by helm';
425+
CREATE ROLE template1_readonly NOLOGIN;
426+
COMMENT ON DATABASE template1 IS 'database managed by helm';
427+
ALTER DATABASE template1 OWNER TO template1_admin;
428+
SQL
429+
sql template1 <<'SQL'
430+
ALTER SCHEMA public OWNER TO template1_admin;
431+
CREATE TABLE public.kept (id int);
432+
ALTER TABLE public.kept OWNER TO template1_admin;
433+
GRANT SELECT ON public.kept TO template1_readonly;
434+
SQL
435+
sql postgres <<'SQL'
436+
CREATE DATABASE sysdb_clone TEMPLATE template1;
437+
SQL
438+
# The next run must be a new Job: remove the completed one so the
439+
# assert below cannot match it.
440+
kubectl -n "$NAMESPACE" delete job postgres-sysdb-init-job --wait=true
441+
# Declaring a user is enough for an upgrade, and runs the init-job.
442+
kubectl -n "$NAMESPACE" patch postgres.apps.cozystack.io sysdb --type merge \
443+
-p '{"spec":{"users":{"sysdbuser":{},"sysdbprobe":{}}}}'
444+
check:
445+
($error == null): true
446+
- assert:
447+
timeout: 11m
448+
resource:
449+
apiVersion: batch/v1
450+
kind: Job
451+
metadata:
452+
name: postgres-sysdb-init-job
453+
status:
454+
(conditions[?type == 'Complete']):
455+
- status: "True"
456+
457+
- name: verify-release
458+
try:
459+
- script:
460+
timeout: 2m
461+
content: |
462+
set -eu
463+
pod=$(kubectl -n "$NAMESPACE" get pod \
464+
-l cnpg.io/cluster=postgres-sysdb,cnpg.io/instanceRole=primary -o name | head -n1)
465+
q() { kubectl -n "$NAMESPACE" exec "$pod" -c postgres -- psql -v ON_ERROR_STOP=1 -U postgres -tA -d "$1" -c "$2"; }
466+
fail=0
467+
expect() {
468+
got=$(q "$1" "$3")
469+
if [ "$got" != "$4" ]; then
470+
echo "FAIL: $2: got '$got', want '$4'" >&2
471+
fail=1
472+
fi
473+
}
474+
comment="SELECT coalesce(shobj_description(oid, 'pg_database'), '<none>') FROM pg_database WHERE datname = "
475+
owner="SELECT pg_get_userbyid(datdba) FROM pg_database WHERE datname = "
476+
expect postgres "postgres comment cleared" "$comment'postgres'" '<none>'
477+
expect postgres "postgres back to the superuser" "$owner'postgres'" postgres
478+
expect postgres "marked postgres_admin dropped" "SELECT count(*) FROM pg_roles WHERE rolname = 'postgres_admin'" 0
479+
expect postgres "template1 comment cleared" "$comment'template1'" '<none>'
480+
expect postgres "unmarked template1_admin still exists" "SELECT count(*) FROM pg_roles WHERE rolname = 'template1_admin'" 1
481+
expect postgres "template1 still owned by the unmarked role" "$owner'template1'" template1_admin
482+
for db in template1 sysdb_clone; do
483+
expect "$db" "$db public still owned by the unmarked role" \
484+
"SELECT pg_get_userbyid(nspowner) FROM pg_namespace WHERE nspname = 'public'" template1_admin
485+
expect "$db" "$db kept table still owned by the unmarked role" \
486+
"SELECT tableowner FROM pg_tables WHERE schemaname = 'public' AND tablename = 'kept'" template1_admin
487+
expect "$db" "$db grant to the unmarked readonly role kept" \
488+
"SELECT has_table_privilege('template1_readonly', 'public.kept', 'SELECT')" t
489+
done
490+
exit "$fail"
491+
check:
492+
($error == null): true
493+
# The Postgres CR was applied by chainsaw; deleting it removes the whole
494+
# cluster, the databases and roles built above included.
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
apiVersion: apps.cozystack.io/v1alpha1
2+
kind: Postgres
3+
metadata:
4+
name: sysdb
5+
spec:
6+
external: false
7+
size: 1Gi
8+
replicas: 1
9+
storageClass: ""
10+
quorum:
11+
minSyncReplicas: 0
12+
maxSyncReplicas: 0
13+
users:
14+
sysdbuser: {}
15+
databases:
16+
appdb: {}
17+
resources: {}
18+
resourcesPreset: "nano"

0 commit comments

Comments
 (0)