Stop clients from writing server-owned columns on chat messages #400

Summary

authenticated holds INSERT and UPDATE on every column of public.messages. RLS checks only user_id and channel read access. So any signed-in user can set columns the server should own: seq, type, created_at, reactions, readed_at and, on UPDATE, channel_id.

• Severity: High. Impact:
  • Lost messages, platform-wide. A user inserts rows that take the next values of messages_seq_seq. Later sends fail with 23505, and the client reports success. Other users' messages disappear with no error.
  • Forged notices. A type = 'notification' row with a title_changed payload shows in History as the newest rename, under any user_name the writer chooses. A far-future created_at keeps it on top.
  • Broken catch-up. A very large seq passes the fetch_messages_since cursor, so clients can miss live messages after a reconnect.
  • Channel move. An UPDATE moves the caller's own message into a channel they cannot read, past the INSERT check.
  • Forged reactions. A user can write reactions in other users' names onto their own message. The direct UPDATE skips add_reaction and remove_reaction.
• Area: Supabase grants and RLS on public.messages
• Source: security review of 2026-10-06, finding H4

Production check (2026-10-06, read-only)

• information_schema.column_privileges shows INSERT and UPDATE for authenticated on all 22 columns of messages, including seq, type, created_at, channel_id and reactions.
• messages_self_insert: user_id = auth.uid() AND internal.can_read_channel(channel_id).
• messages_self_update: user_id = auth.uid() only. It does not re-check the channel.

Where

• Table-wide grant: packages/supabase/scripts/13-RLS.sql:34, mirrored by packages/supabase/migrations/20260716130500_grant_authenticated_chat_table_privileges.sql:11.
• Policies: messages_self_insert and messages_self_update in packages/supabase/scripts/13-RLS.sql:195-205.
• seq is only a column default (packages/supabase/scripts/05-0-message.sql:11) with a unique constraint (:31). No trigger overrides it.
• isDuplicateKeyError treats any 23505 as a successful send: apps/webapp/src/components/chatroom/utils/postgresErrors.ts:7-13, used at apps/webapp/src/components/chatroom/utils/sendChatMessage.ts:76,115,127.
• History trusts type = 'notification' rows and paints metadata.user_name: apps/webapp/src/components/pages/history/getLatestPadTitleChange.ts:16-37.
• Catch-up cursor m.seq > p_since_seq in fetch_messages_since: packages/supabase/scripts/10-functions.sql:1251.
• Every client write to messages:
  • INSERT: persistChatMessage (apps/webapp/src/components/chatroom/utils/sendChatMessage.ts:40-52,74), sendMessage (apps/webapp/src/api/messages/sendMessage.ts:20-35), sendCommentMessage (apps/webapp/src/api/messages/sendCommentMessage.ts:20-33).
  • UPDATE: updateMessage (apps/webapp/src/api/messages/updateMessage.ts:11-37), deleteMessage (apps/webapp/src/api/messages/deleteMessage.ts:3-10).
  • The MCP postMessage (apps/hocuspocus.server/src/modules/mcp/infra/chatStore.ts:98-113) uses the service role, so this change does not affect it.

Fix plan

Column grants close seq, created_at, reactions, readed_at and the channel move. One policy clause closes type = 'notification'. No new trigger is needed.

1. Column grants. In scripts/13-RLS.sql, replace line 34 with GRANT SELECT ON public.messages TO authenticated plus two column grants:
   • INSERT (id, channel_id, user_id, content, html, medias, type, metadata, reply_to_message_id). These are the columns the three INSERT writers send. client_id is not written (see apps/webapp/src/components/chatroom/components/MessageCard/MessageCardContext.tsx:128-129).
   • UPDATE (content, html, medias, type, deleted_at). These are the columns updateMessage and deleteMessage send. edited_at is set by the set_message_edited_at trigger, which needs no grant.
   • In the migration, first revoke insert, update on public.messages from authenticated. A table-wide grant hides any column-level revoke.
2. Policy clause. Add AND type IS DISTINCT FROM 'notification' to the WITH CHECK of messages_self_insert and messages_self_update.
   • notify_user_join_workspace and notify_document_title_change still post notices. They are security definer and run as the table owner, so RLS does not apply to them.
   • Do not use auth.role() here. The join notice is inserted while the JWT role is authenticated.
3. Production pre-check, read-only. Before you apply the migration, count rows with a client-forged seq: select count(*) from public.messages where seq > (select last_value from public.messages_seq_seq);. If the count is not 0, stop and report it on this issue.

Repo rules that apply:

• Ship the SQL as one new migration and mirror it in packages/supabase/scripts/13-RLS.sql.
• Regenerate the seed with bun run --filter @docs.plus/supabase_back seed. Never hand-edit packages/supabase/seed.sql.
• Run bun run --filter @docs.plus/supabase_back types. Grants and policies do not change the generated types, so expect no diff.

Out of scope

• Narrowing isDuplicateKeyError to messages_pkey. Once clients cannot write seq, a client cannot cause a seq conflict. The helper's comment already says it means the primary key.
• Locking the keys a client may put in metadata on INSERT.

Acceptance criteria

• An insert that names seq or created_at is refused with 42501.
• An insert or update with type = 'notification' from a signed-in client is refused.
• An update that names channel_id, user_id or reactions is refused with 42501.
• Send, edit, delete, reply, comment and media messages still work in the webapp.
• Reactions still work through add_reaction and remove_reaction.
• The "joined" notice and the title_changed notice still post.

Verify

1. Apply locally: docker exec -i supabase_db_docsplus_supabase psql -U postgres -d postgres < packages/supabase/migrations/<new file>.sql.
2. In psql, inside begin; ... rollback;, act as a signed-in user:
   set local role authenticated; then select set_config('request.jwt.claims', '{"sub":"<user id>","role":"authenticated"}', true);
   Run one insert or update per refusal above, and expect the error code.
3. In the local webapp, send, edit, delete, reply, react, comment and attach media. Rename a Pad title and open History.
4. The chatroom Cypress suite (bash scripts/test-chatroom.sh) intercepts Supabase HTTP. It checks the client payloads, not the grants.

Related

• Stop channel members from muting or renaming a channel for everyone #401 and Stop signed-in users from squatting a workspace slug to block chat #409 closed the client UPDATE paths on channels and workspaces in migration 20261006130000_close_client_write_and_grant_gaps.sql.
• Make a Private document's chat readable and writable only by its owner #396 changes messages_self_insert through internal.can_read_channel. Land the two migrations in either order; they touch different clauses.

Generated by Claude Code