Nov 26, 2025 · Nov 24, 2025 · Nov 24, 2025 · Nov 24, 2025 · Nov 25, 2025
diff --git a/coderd/database/migrations/000371_api_key_scopes_array_allow_list.up.sql b/coderd/database/migrations/000371_api_key_scopes_array_allow_list.up.sql
 ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:update';
 -- End enum extensions

 -- Purge old API keys to speed up the migration for large deployments.
 -- Note: that problem should be solved in coderd once PR 20863 is released:
 -- https://github.com/coder/coder/blob/main/coderd/database/dbpurge/dbpurge.go#L85
 DELETE FROM api_keys WHERE expires_at < NOW() - INTERVAL '7 days';

 -- Add new columns without defaults; backfill; then enforce NOT NULL
 ALTER TABLE api_keys ADD COLUMN scopes api_key_scope[];
 ALTER TABLE api_keys ADD COLUMN allow_list text[];

 -- Backfill existing rows for compatibility
 UPDATE api_keys SET scopes = ARRAY[scope::api_key_scope];
 UPDATE api_keys SET allow_list = ARRAY['*:*'];
 UPDATE api_keys SET
    scopes = ARRAY[scope::api_key_scope],
    allow_list = ARRAY['*:*'];

 -- Enforce NOT NULL
 ALTER TABLE api_keys ALTER COLUMN scopes SET NOT NULL;
diff --git a/...base/migrations/testdata/fixtures/000371_add_api_key_and_oauth2_provider_app_token.up.sql b/...base/migrations/testdata/fixtures/000371_add_api_key_and_oauth2_provider_app_token.up.sql
 -- Ensure api_keys and oauth2_provider_app_tokens have live data after
 -- migration 000371 deletes expired rows.
 INSERT INTO api_keys (
 id,
 hashed_secret,
 user_id,
 last_used,
 expires_at,
 created_at,
 updated_at,
 login_type,
 lifetime_seconds,
 ip_address,
 token_name,
 scopes,
 allow_list
 )
 VALUES (
 'fixture-api-key',
 '\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
 '30095c71-380b-457a-8995-97b8ee6e5307',
 NOW() - INTERVAL '1 hour',
 NOW() + INTERVAL '30 days',
 NOW() - INTERVAL '1 day',
 NOW() - INTERVAL '1 day',
 'password',
 86400,
 '0.0.0.0',
 'fixture-api-key',
 ARRAY['workspace:read']::api_key_scope[],
 ARRAY['*:*']
 )
 ON CONFLICT (id) DO NOTHING;

 INSERT INTO oauth2_provider_app_tokens (
 id,
 created_at,
 expires_at,
 hash_prefix,
 refresh_hash,
 app_secret_id,
 api_key_id,
 audience,
 user_id
 )
 VALUES (
 '9f92f3c9-811f-4f6f-9a1c-3f2eed1f9f15',
 NOW() - INTERVAL '30 minutes',
 NOW() + INTERVAL '30 days',
 CAST('fixture-hash-prefix' AS bytea),
 CAST('fixture-refresh-hash' AS bytea),
 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
 'fixture-api-key',
 'https://coder.example.com',
 '30095c71-380b-457a-8995-97b8ee6e5307'
 )
 ON CONFLICT (id) DO NOTHING;
Original file line number	Diff line number	Diff line change
Expand Up		@@ -141,13 +141,19 @@ ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:read';
		ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:update';
		-- End enum extensions

		-- Purge old API keys to speed up the migration for large deployments.
		-- Note: that problem should be solved in coderd once PR 20863 is released:
		-- https://github.com/coder/coder/blob/main/coderd/database/dbpurge/dbpurge.go#L85
		DELETE FROM api_keys WHERE expires_at < NOW() - INTERVAL '7 days';

		-- Add new columns without defaults; backfill; then enforce NOT NULL
		ALTER TABLE api_keys ADD COLUMN scopes api_key_scope[];
		ALTER TABLE api_keys ADD COLUMN allow_list text[];

		-- Backfill existing rows for compatibility
		UPDATE api_keys SET scopes = ARRAY[scope::api_key_scope];
		UPDATE api_keys SET allow_list = ARRAY[':'];
		UPDATE api_keys SET
		scopes = ARRAY[scope::api_key_scope],
		allow_list = ARRAY[':'];

		-- Enforce NOT NULL
		ALTER TABLE api_keys ALTER COLUMN scopes SET NOT NULL;
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,57 @@
		-- Ensure api_keys and oauth2_provider_app_tokens have live data after
		-- migration 000371 deletes expired rows.
		INSERT INTO api_keys (
		id,
		hashed_secret,
		user_id,
		last_used,
		expires_at,
		created_at,
		updated_at,
		login_type,
		lifetime_seconds,
		ip_address,
		token_name,
		scopes,
		allow_list
		)
		VALUES (
		'fixture-api-key',
		'\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
		'30095c71-380b-457a-8995-97b8ee6e5307',
		NOW() - INTERVAL '1 hour',
		NOW() + INTERVAL '30 days',
		NOW() - INTERVAL '1 day',
		NOW() - INTERVAL '1 day',
		'password',
		86400,
		'0.0.0.0',
		'fixture-api-key',
		ARRAY['workspace:read']::api_key_scope[],
		ARRAY[':']
		)
		ON CONFLICT (id) DO NOTHING;

		INSERT INTO oauth2_provider_app_tokens (
		id,
		created_at,
		expires_at,
		hash_prefix,
		refresh_hash,
		app_secret_id,
		api_key_id,
		audience,
		user_id
		)
		VALUES (
		'9f92f3c9-811f-4f6f-9a1c-3f2eed1f9f15',
		NOW() - INTERVAL '30 minutes',
		NOW() + INTERVAL '30 days',
		CAST('fixture-hash-prefix' AS bytea),
		CAST('fixture-refresh-hash' AS bytea),
		'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
		'fixture-api-key',
		'https://coder.example.com',
		'30095c71-380b-457a-8995-97b8ee6e5307'
		)
		ON CONFLICT (id) DO NOTHING;