You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 482b290
Browse filesBrowse the repository at this point in the historyBrowse 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]>
Copy file name to clipboardExpand all lines: packages/apps/postgres/templates/init-script.yaml
+25-9Lines changed: 25 additions & 9 deletions
Original file line number
Diff line number
Diff line change
@@ -330,13 +330,19 @@ stringData:
330
330
only fail (template databases cannot be dropped, and this session is
331
331
connected to postgres), which wedged every later upgrade, so they are
332
332
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.
340
346
*/}}
341
347
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')")
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
350
359
EOT
351
360
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
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
Copy file name to clipboardExpand all lines: packages/apps/postgres/tests/system_databases_test.yaml
+29-1Lines changed: 29 additions & 1 deletion
Original file line number
Diff line number
Diff line change
@@ -80,4 +80,32 @@ tests:
80
80
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'\\)\"\\)"
81
81
- matchRegex:
82
82
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