Skip to content

Commit 482b290

Browse files
fix(postgres): orphan a released system database's objects, not hand them to the superuser
The release pass reassigned what <db>_admin and <db>_readonly owned in a taken-over system database to the superuser. A tenant that held <db>_admin could have left a SECURITY DEFINER function there, which would then run with superuser rights for anyone PUBLIC can execute it. The objects now go to cozystack:orphaned and are made SECURITY INVOKER, as in the orphaned-roles pass, and the database and public get their owners back separately. A database created from template1 while it was taken over inherited the auto-grant trigger naming template1_admin. Once the role is dropped, CREATE SCHEMA fails there, so releasing template1 also drops that inherited copy; a declared database gets its own back from the grant pass. Assisted-by: LLM Signed-off-by: Mattia Eleuteri <[email protected]>
1 parent 0407214 commit 482b290

2 files changed

Lines changed: 54 additions & 10 deletions

File tree

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

Lines changed: 25 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -330,13 +330,19 @@ stringData:
330330
only fail (template databases cannot be dropped, and this session is
331331
connected to postgres), which wedged every later upgrade, so they are
332332
handed back instead: the auto-grant trigger goes, since it names a role
333-
about to be dropped and would make every CREATE SCHEMA fail, what the
334-
<db>_admin and <db>_readonly roles own or were granted there goes back to
335-
the superuser, public goes back to pg_database_owner (PostgreSQL 15 and
336-
later), and the comment is cleared. The orphaned-roles pass below then
337-
drops the roles; it does not visit template1, hence the per-database
338-
REASSIGN/DROP OWNED here. template0 accepts no connection, and the Job
339-
that commented it never got further, so only the comment is cleared.
333+
about to be dropped and would make every CREATE SCHEMA fail; what the
334+
<db>_admin and <db>_readonly roles own there goes to cozystack:orphaned,
335+
as in the orphaned-roles pass and for the same reason (a tenant's
336+
SECURITY DEFINER function must not end up owned by the superuser), and is
337+
made SECURITY INVOKER; the database itself returns to the superuser,
338+
public to pg_database_owner (to the superuser before PostgreSQL 15), and
339+
the comment is cleared. The orphaned-roles pass below then drops the
340+
roles; it does not visit template1, hence the per-database pass here.
341+
template0 accepts no connection, and the Job that commented it never got
342+
further, so only the comment is cleared. A database created from
343+
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.
340346
*/}}
341347
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')")
342348
echo "system databases to release: $SYSTEM_DBS"
@@ -345,10 +351,20 @@ stringData:
345351
psql -v ON_ERROR_STOP=1 --echo-all --dbname "$db" -v db="$db" <<\EOT
346352
DROP EVENT TRIGGER IF EXISTS trigger_auto_grant;
347353
DROP FUNCTION IF EXISTS auto_grant_schema_privileges();
348-
SELECT format('REASSIGN OWNED BY %I TO CURRENT_USER', r), format('DROP OWNED BY %I', r) FROM unnest(ARRAY[:'db' || '_admin', :'db' || '_readonly']) AS r WHERE EXISTS (SELECT FROM pg_roles WHERE rolname = r)\gexec
349-
SELECT 'ALTER SCHEMA public OWNER TO pg_database_owner' WHERE current_setting('server_version_num')::int >= 150000 AND EXISTS (SELECT FROM pg_namespace WHERE nspname = 'public')\gexec
354+
SELECT format('REASSIGN OWNED BY %I TO "cozystack:orphaned"', r), format('DROP OWNED BY %I', r) FROM unnest(ARRAY[:'db' || '_admin', :'db' || '_readonly']) AS r WHERE EXISTS (SELECT FROM pg_roles WHERE rolname = r)\gexec
355+
ALTER DATABASE :"db" OWNER TO CURRENT_USER;
356+
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 WHERE nspname = 'public')\gexec
357+
SELECT format('ALTER %s %s SECURITY INVOKER', CASE p.prokind WHEN 'p' THEN 'PROCEDURE' ELSE 'FUNCTION' END, p.oid::regprocedure) FROM pg_proc p WHERE p.proowner = '"cozystack:orphaned"'::regrole AND p.prosecdef\gexec
358+
SELECT format('ALTER VIEW %s SET (security_invoker = true)', c.oid::regclass) FROM pg_class c WHERE c.relowner = '"cozystack:orphaned"'::regrole AND c.relkind = 'v' AND current_setting('server_version_num')::int >= 150000 AND NOT coalesce('security_invoker=true' = ANY (c.reloptions), false)\gexec
350359
EOT
351360
fi
361+
if [ "$db" = template1 ]; then
362+
for other in $(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"); do
363+
psql -v ON_ERROR_STOP=1 --echo-queries --dbname "$other" <<\EOT
364+
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
365+
EOT
366+
done
367+
fi
352368
psql -v ON_ERROR_STOP=1 --echo-all -v db="$db" <<\EOT
353369
COMMENT ON DATABASE :"db" IS NULL;
354370
EOT

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

Lines changed: 29 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -80,4 +80,32 @@ tests:
8080
pattern: "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'\\)\"\\)"
8181
- matchRegex:
8282
path: stringData["init.sh"]
83-
pattern: '(?s)== release system databases.*DROP EVENT TRIGGER IF EXISTS trigger_auto_grant;\s+DROP FUNCTION IF EXISTS auto_grant_schema_privileges\(\);.*REASSIGN OWNED BY %I TO CURRENT_USER.*DROP OWNED BY %I.*ALTER SCHEMA public OWNER TO pg_database_owner.*COMMENT ON DATABASE :"db" IS NULL;.*== delete databases'
83+
pattern: '(?s)== release system databases.*DROP EVENT TRIGGER IF EXISTS trigger_auto_grant;\s+DROP FUNCTION IF EXISTS auto_grant_schema_privileges\(\);.*REASSIGN OWNED BY %I TO "cozystack:orphaned"''.*DROP OWNED BY %I.*ALTER DATABASE :"db" OWNER TO CURRENT_USER;.*ALTER SCHEMA public OWNER TO %s.*COMMENT ON DATABASE :"db" IS NULL;.*== delete databases'
84+
85+
# A tenant holding <db>_admin may have created a SECURITY DEFINER function
86+
# there; handed to the superuser it would run with superuser rights for
87+
# anyone PUBLIC can execute it. It goes to cozystack:orphaned and is made
88+
# SECURITY INVOKER, as in the orphaned-roles pass.
89+
- it: never hands a released system database's objects to the superuser
90+
documentSelector:
91+
path: metadata.name
92+
value: pg-test-init-script
93+
asserts:
94+
- notMatchRegex:
95+
path: stringData["init.sh"]
96+
pattern: 'REASSIGN OWNED BY %I TO CURRENT_USER'
97+
- matchRegex:
98+
path: stringData["init.sh"]
99+
pattern: '(?s)== release system databases.*ALTER %s %s SECURITY INVOKER.*ALTER VIEW %s SET \(security_invoker = true\).*== delete databases'
100+
101+
# A database created from template1 while it was taken over inherited the
102+
# trigger naming template1_admin, which the orphaned-roles pass then drops;
103+
# CREATE SCHEMA would fail there from that point on.
104+
- it: drops the template1 trigger inherited by other databases
105+
documentSelector:
106+
path: metadata.name
107+
value: pg-test-init-script
108+
asserts:
109+
- matchRegex:
110+
path: stringData["init.sh"]
111+
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"

0 commit comments

Comments
 (0)