Drop the dead create_direct_message_channel function #319

Key: SPR-09 · Type: AFK · Part of: #327 · Blocked by: #314 · Checked against: 3bb26de

Problem

public.create_direct_message_channel is dead code with a broken access check, and we should drop it. Until 2026-09-28, any signed-in user could call it. They could create a DIRECT channel in any workspace, with any user as the second member. Nothing in the repo calls it. On prod it is now service_role only, but the function, its webapp wrapper, and its generated type are still in the repo. The next grant sweep could reopen it.

This is finding UF-03 of the 2026-09-28 security review: severity MEDIUM, OWASP A01 (Broken Access Control) and A04 (Insecure Design).

Context

What the function does (packages/supabase/scripts/10-functions.sql ~421-525, CREATE OR REPLACE FUNCTION create_direct_message_channel(workspace_uid VARCHAR(36), user_id UUID) RETURNS JSONB):

• It is SECURITY DEFINER with search_path = public. Migration 20260512094335_dm_channel_function_definer.sql set both for remote databases.
• It takes the caller from auth.uid(). If that is null, it falls back to current_setting('app.current_user_id', TRUE) (~455). The comment says this is "for testing purposes".
• It checks only three things: the workspace exists, both users exist, and the caller is not the target.
• It does not check that the caller or the target is a workspace member.
• It inserts a channels row of type DIRECT and a channel_members row for the target, not the caller (the caller insert is commented out).

Who could do what, before the 2026-09-28 fix:

• Any signed-in user could create a DM channel in any workspace, with any user as the member. The target did not consent and did not need to belong to the workspace.
• The target then got that channel's traffic and notifications.
• Anon calls normally raised an exception.
• PostgREST does not let a client set app.* settings, so the GUC fallback is not reachable over HTTP. It is still an unsafe identity source inside a definer function.
• The email fallback in the channel name never runs, because users.username is NOT NULL.

State today (measured):

git grep -n "create_direct_message_channel\|createDirectMessageChannel" at HEAD finds no SQL caller, no cron job, and no app caller. It finds only the rows above.

What stays. The DIRECT value of channel_type stays. The UI branches stay: apps/webapp/src/components/chatroom/components/ChannelComposer/ChannelComposer.tsx ~37 (case 'DIRECT') and ChannelComposer/components/JoinDirectChannel.tsx. CHANNEL_TYPES in apps/hocuspocus.server/src/api/controllers/admin-stats.controller.ts ~13 stays. A future DM feature needs a new function that checks membership for both users and has no GUC fallback.

What to do

1. [maintainer] Read-only precheck on prod. Run in the SQL editor. Expect: query 1 returns 1 row, and proacl names only the owner and service_role. Queries 2, 3 and 4 each return 0 rows. If any returns rows, stop and report on this issue.

   -- 1. Overloads and grants
   select p.oid::regprocedure as sig, p.prosecdef, p.proacl
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'create_direct_message_channel';
   
   -- 2. Tracked dependents
   select d.classid::regclass, d.objid, d.deptype
   from pg_depend d
   join pg_proc p on p.oid = d.refobjid and d.refclassid = 'pg_proc'::regclass
   join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'create_direct_message_channel';
   
   -- 3. Other function bodies that name it (plpgsql calls are not in pg_depend)
   select p.oid::regprocedure
   from pg_proc p
   where p.prosrc ilike '%create_direct_message_channel%'
     and p.proname <> 'create_direct_message_channel';
   
   -- 4. Cron jobs that name it
   select jobid, jobname from cron.job
   where command ilike '%create_direct_message_channel%';

2. [agent] Confirm Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has landed. Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 removes 'create_direct_message_channel' from user_facing_names in packages/supabase/scripts/29-lint-hardening.sql (~216 at HEAD). Run grep -n create_direct_message_channel packages/supabase/scripts/29-lint-hardening.sql. If it prints a line, Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 has not landed: stop and report on this issue.

3. [agent] Remove the function from the script. In packages/supabase/scripts/10-functions.sql:

   • Delete the comment block and the whole function, ~421-525 (from -- SECURITY DEFINER: the function needs to read u.email to the closing $$;).
   • In its place, add drop function if exists public.create_direct_message_channel(varchar, uuid);. This follows the precedent at 10-4-func-chat-media-features.sql ~168-170 and 07-4-push-notifications-pgmq.sql ~676-678. Do not copy the cascade from the second one. A local DB that re-runs the script, without a reset, then loses the function too.
   • Delete ALTER FUNCTION public.create_direct_message_channel(workspace_uid character varying, user_id uuid) SET search_path = public; at ~1003. Do this in the same edit. If this ALTER stays, the regenerated seed alters a missing function, and db reset aborts.

4. [agent] Fix the comment. In packages/supabase/scripts/13-RLS.sql ~50, remove the parenthetical (e.g. create_direct_message_channel). End the sentence at "RPCs that legitimately need it." Make new document_views partitions start closed #315 owns the false partition comment in that file (~271); do not touch it.

5. [agent] Remove the webapp wrapper. Delete apps/webapp/src/api/rpc/createDirectMessageChannel.ts. Remove export * from './createDirectMessageChannel' from apps/webapp/src/api/rpc/index.ts ~2. Do this before step 8, because the wrapper reads the generated type.

6. [agent] Write the paired migration by hand. Create packages/supabase/migrations/<timestamp>_drop_create_direct_message_channel.sql. The timestamp must be later than 20260928130000 and later than the Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 migration. It drops every overload by name, with no CASCADE. An unknown dependent then stops the push, and does not remove something silently.

   -- Drops create_direct_message_channel (UF-03). No caller in the repo.
   -- Every overload, no CASCADE. A no-op when the function is already gone.
   do $$
   declare
     f record;
   begin
     for f in
       select p.proname, pg_get_function_identity_arguments(p.oid) as args
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.proname = 'create_direct_message_channel'
     loop
       execute format('drop function public.%I(%s)', f.proname, f.args);
     end loop;
   
     if exists (
       select 1 from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public' and p.proname = 'create_direct_message_channel'
     ) then
       raise exception 'create_direct_message_channel still exists';
     end if;
   end
   $$;

7. [agent] Prove the migration locally. The local DB still has the function from the old seed. db reset skips migrations (packages/supabase/config.toml ~53-58, enabled = false), so apply the file with psql. Run it twice, from the repo root, with the real timestamp. The first run drops the function. The second run is a no-op.

   docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres -v ON_ERROR_STOP=1 < packages/supabase/migrations/<timestamp>_drop_create_direct_message_channel.sql

8. [agent] Regenerate seed and types. From the repo root, run the commands below. reset regenerates seed.sql from scripts/, then resets the local DB. It wipes local state for every worktree that shares this DB.

   bun run --filter @docs.plus/supabase_back reset
   bun run --filter @docs.plus/supabase_back types

9. [agent] Check the webapp still builds. Run bun run --filter @docs.plus/webapp typecheck and bun run lint.

10. [agent] Stop at a review checkpoint. Post the diff summary and the step 7-9 results as a comment on this issue. Do not commit.

11. [maintainer] Commit with /commit-review. Commit the script edits, the new migration, and the regenerated seed.sql and supabase.ts together.

12. [maintainer] Push to prod after Ship the three migrations that prod's history lacks #313 and Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314 land. Follow the house procedure in packages/supabase/CLAUDE.md §"Remote db push vs local reset". Run bunx supabase migration list first. Set [db.migrations] enabled = true only while pushing, then set it back to false. Never mark this new version applied with migration repair; it must be pushed. After the push, run precheck query 1 again. Expect 0 rows.

Out of scope

• Porting the 2026-09-28 grant fix into scripts, including the §6 removal of create_direct_message_channel and get_bookmark_count: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.
• Pushing the three missing migrations: Ship the three migrations that prod's history lacks #313.
• Default privileges: Stop new tables and functions from opening by default #316. The grant guard: Add a read-only grant guard to run after each db push #317.
• Other lint 0028 functions: Close the remaining 47 linter warnings on functions #318.
• Prod-only functions such as reset_workspace_unread_counts: Remove prod-only drift: four functions and the PushSentLog table #320.
• The DIRECT enum value, the chat UI branches, and admin-stats.controller.ts. They stay.
• Building a DM feature.

Acceptance criteria

• packages/supabase/scripts/10-functions.sql has no CREATE and no ALTER for create_direct_message_channel. It has one drop function if exists public.create_direct_message_channel(varchar, uuid);.
• 29-lint-hardening.sql §6 no longer lists create_direct_message_channel.
• 13-RLS.sql ~50 no longer names the function.
• apps/webapp/src/api/rpc/createDirectMessageChannel.ts is deleted, and apps/webapp/src/api/rpc/index.ts no longer exports it.
• One new migration drops every overload by name, without CASCADE, and raises if the function remains. It ran twice locally without error.
• seed.sql and apps/webapp/src/types/supabase.ts were regenerated by the commands in step 8, not edited by hand. Neither names the function.
• bun run --filter @docs.plus/supabase_back reset completes without error.
• bun run --filter @docs.plus/webapp typecheck and bun run lint pass.
• No applied migration file changed. git status --short -- packages/supabase/migrations shows only the new file, as ??.
• The grep in Verification returns exactly the expected files.
• [maintainer] After the prod push, precheck query 1 returns 0 rows.

Verification

1. Grep proof. Run git grep --untracked -l "create_direct_message_channel\|createDirectMessageChannel". --untracked makes it see the new migration before anyone stages it. Expect exactly these five files, and nothing in apps/, seed.sql, or supabase.ts:

   • packages/supabase/migrations/20260512094335_dm_channel_function_definer.sql
   • packages/supabase/migrations/20260513130000_chat_rls_rollout_catchup.sql
   • packages/supabase/migrations/20260519200000_scripts_functions_triggers_parity.sql
   • the new <timestamp>_drop_create_direct_message_channel.sql
   • packages/supabase/scripts/10-functions.sql (the drop function if exists line only)

2. Local DB after reset. Run this through docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres. Expect 0.

   select count(*) from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public' and p.proname = 'create_direct_message_channel';

3. Chat still works. Start the local stack with make dev-local. Then run the chatroom specs with bash scripts/run-tests.sh --e2e --scope chatroom. Expect every spec to pass. Or open a pad as a guest and as a signed-in user, and send a message as the signed-in user. No console error should name the function.

4. [maintainer] On prod after the push: precheck query 1 returns 0 rows. The security linter shows no row for create_direct_message_channel.

House rules

• Scripts first, with a paired migration. Edit scripts/*.sql, then write the migration, then regenerate seed and types in the same change (packages/supabase/CLAUDE.md).
• Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts. Only reset / seed and types write them.
• Never edit an applied migration. Trap: do not run generate:functions-parity-migration. It rewrites 20260519200000_scripts_functions_triggers_parity.sql in place (generate-functions-parity-migration.ts ~35 MIGRATION_BASENAME, ~154), and that migration is already applied on prod. This drop migration is hand-written.
• Bun only. No npm, npx, yarn or pnpm.
• No commits unless the maintainer asks. End at a review checkpoint. Tests are opt-in; this change needs none.