Skip to content

Commit c0fb063

Browse files
fix(postgres): give an inherited public schema back to its default owner
A database created from template1 while it was taken over inherited a public schema owned by template1_admin. The orphaned-roles pass then handed it to cozystack:orphaned, leaving the database owner without CREATE on it from PostgreSQL 15 on. Releasing template1 now returns that schema to pg_database_owner (the superuser before 15) while template1_admin still owns it. Assisted-by: LLM Signed-off-by: Mattia Eleuteri <[email protected]>
1 parent 482b290 commit c0fb063

2 files changed

Lines changed: 16 additions & 2 deletions

File tree

‎packages/apps/postgres/templates/init-script.yaml‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -341,8 +341,9 @@ stringData:
341341
template0 accepts no connection, and the Job that commented it never got
342342
further, so only the comment is cleared. A database created from
343343
template1 while it was taken over inherited its trigger, naming
344-
template1_admin; that copy goes too, and a declared database gets its own
345-
back from the grant pass further down.
344+
template1_admin, and its public schema owned by that role; the trigger
345+
goes, public returns to its default owner, and a declared database gets
346+
its trigger back from the grant pass further down.
346347
*/}}
347348
SYSTEM_DBS=$(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database d JOIN pg_shdescription s ON d.oid = s.objoid WHERE s.description = 'database managed by helm' AND d.datname IN ('postgres', 'template0', 'template1')")
348349
echo "system databases to release: $SYSTEM_DBS"
@@ -362,6 +363,7 @@ stringData:
362363
for other in $(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"); do
363364
psql -v ON_ERROR_STOP=1 --echo-queries --dbname "$other" <<\EOT
364365
SELECT 'DROP EVENT TRIGGER IF EXISTS trigger_auto_grant', 'DROP FUNCTION auto_grant_schema_privileges()' WHERE position('''template1_admin''' IN coalesce((SELECT prosrc FROM pg_proc WHERE oid = to_regprocedure('auto_grant_schema_privileges()')), '')) > 0\gexec
366+
SELECT format('ALTER SCHEMA public OWNER TO %s', CASE WHEN current_setting('server_version_num')::int >= 150000 THEN 'pg_database_owner' ELSE 'CURRENT_USER' END) WHERE EXISTS (SELECT FROM pg_namespace n JOIN pg_roles r ON r.oid = n.nspowner WHERE n.nspname = 'public' AND r.rolname = 'template1_admin')\gexec
365367
EOT
366368
done
367369
fi

‎packages/apps/postgres/tests/system_databases_test.yaml‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,3 +109,15 @@ tests:
109109
- matchRegex:
110110
path: stringData["init.sh"]
111111
pattern: "(?s)== release system databases.*if \\[ \"\\$db\" = template1 \\]; then.*SELECT 'DROP EVENT TRIGGER IF EXISTS trigger_auto_grant', 'DROP FUNCTION auto_grant_schema_privileges\\(\\)' WHERE position\\('''template1_admin''' IN .*== delete databases"
112+
113+
# That clone's public schema also came from template1, owned by
114+
# template1_admin; left to the orphaned-roles pass it would end up owned by
115+
# cozystack:orphaned and the database owner would lose CREATE on it.
116+
- it: gives an inherited public schema back to its default owner
117+
documentSelector:
118+
path: metadata.name
119+
value: pg-test-init-script
120+
asserts:
121+
- matchRegex:
122+
path: stringData["init.sh"]
123+
pattern: "(?s)if \\[ \"\\$db\" = template1 \\]; then.*SELECT format\\('ALTER SCHEMA public OWNER TO %s', .*\\) WHERE EXISTS \\(SELECT FROM pg_namespace n JOIN pg_roles r ON r.oid = n.nspowner WHERE n.nspname = 'public' AND r.rolname = 'template1_admin'\\).*== delete databases"

0 commit comments

Comments
 (0)