Build a secure realtime chat application with RDS Supabase Realtime

Create the business tables

The application uses four tables. Run the following statements in the Supabase SQL Editor:

-- User profiles (populated automatically by an auth trigger)
CREATE TABLE public.profiles (
    id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email text,
    created_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    INSERT INTO public.profiles (id, email) VALUES (NEW.id, NEW.email);
    RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Conversations (one row per DM or group)
CREATE TABLE public.conversations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    topic text NOT NULL UNIQUE,          -- Realtime channel topic
    name text,                           -- Group name (null for DMs)
    is_group boolean DEFAULT false,      -- true = group, false = 1:1 DM
    created_at timestamptz DEFAULT now()
);

-- Membership (with redundant conversation_topic for fast RLS lookups)
CREATE TABLE public.conversation_members (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    conversation_id bigint REFERENCES public.conversations(id) ON DELETE CASCADE,
    user_id uuid NOT NULL,
    conversation_topic text NOT NULL,
    joined_at timestamptz DEFAULT now(),
    UNIQUE(conversation_id, user_id)
);

CREATE INDEX idx_members_user_topic ON public.conversation_members(user_id, conversation_topic);
CREATE INDEX idx_members_conversation ON public.conversation_members(conversation_id);

-- Messages
CREATE TABLE public.dm_messages (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    conversation_id bigint REFERENCES public.conversations(id) ON DELETE CASCADE,
    sender_id uuid NOT NULL,
    content text NOT NULL,
    created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_messages_conversation ON public.dm_messages(conversation_id, created_at DESC);

Enable RLS and grant privileges

RLS must be enabled on every business table; policies are inert otherwise. Then grant the minimum required privileges to the authenticated role.

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.conversation_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.dm_messages ENABLE ROW LEVEL SECURITY;

-- Profiles policies: readable by all authenticated users; users can only insert their own row.
CREATE POLICY profiles_select ON public.profiles
FOR SELECT TO authenticated
USING (true);

CREATE POLICY profiles_insert ON public.profiles
FOR INSERT TO authenticated
WITH CHECK (id = auth.uid());

-- Grants
GRANT SELECT ON public.profiles TO authenticated;
GRANT SELECT ON public.conversations TO authenticated;
GRANT SELECT ON public.conversation_members TO authenticated;
GRANT SELECT, INSERT ON public.dm_messages TO authenticated;

Define the RLS policies

Three sets of policies work together to enforce the double-layer security model.

Channel authorization — the first layer, evaluated when a client joins a WebSocket topic.

CREATE POLICY dm_channel_subscribe
ON realtime.messages
FOR SELECT TO authenticated
USING (
    EXISTS (
        SELECT 1 FROM public.conversation_members m
        WHERE m.user_id = auth.uid()
          AND m.conversation_topic = realtime.topic()
    )
);

realtime.topic() returns the topic name of the current subscription; auth.uid() returns the caller's UUID. The policy allows the subscription only when the caller is a member of the conversation that owns the topic.

Message read/write — the second layer, evaluated on every query and insert.

-- Members can read messages from their conversations.
CREATE POLICY member_select ON public.dm_messages
FOR SELECT TO authenticated
USING (
    EXISTS (
        SELECT 1 FROM public.conversation_members m
        WHERE m.conversation_id = dm_messages.conversation_id
          AND m.user_id = auth.uid()
    )
);

-- Members can send messages only as themselves.
CREATE POLICY member_insert ON public.dm_messages
FOR INSERT TO authenticated
WITH CHECK (
    sender_id = auth.uid()
    AND EXISTS (
        SELECT 1 FROM public.conversation_members m
        WHERE m.conversation_id = dm_messages.conversation_id
          AND m.user_id = auth.uid()
    )
);

The INSERT policy checks two conditions: the sender_id must match the caller (preventing impersonation), and the caller must be a member of the target conversation.

Conversation and membership visibility — required so that the client can list conversations and members.

-- Authenticated users can read membership rows (for member lists in the UI).
CREATE POLICY members_auth_select ON public.conversation_members
FOR SELECT TO authenticated
USING (true);

-- Users can only see conversations they are a member of.
CREATE POLICY conv_member_select ON public.conversations
FOR SELECT TO authenticated
USING (
    EXISTS (
        SELECT 1 FROM public.conversation_members m
        WHERE m.conversation_id = conversations.id
          AND m.user_id = auth.uid()
    )
);

Configure Realtime

Two settings are required for the message table to be visible to Realtime:

-- 1. Emit complete row data in WAL events.
ALTER TABLE public.dm_messages REPLICA IDENTITY FULL;

-- 2. Add the message table to the Realtime publication.
ALTER PUBLICATION supabase_realtime ADD TABLE public.dm_messages;

Create the helper functions

These helper functions bundle multi-table writes into a single RPC call and are safe to call from the client. Both are SECURITY DEFINER, so validate inputs inside the function body.

Idempotent one-to-one conversation. Returns the existing conversation if one already exists between the two users; otherwise creates a new one.

CREATE OR REPLACE FUNCTION public.create_dm_conversation(other_user_id uuid)
RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    conv_id bigint;
    conv_topic text;
    current_uid uuid := auth.uid();
BEGIN
    IF current_uid = other_user_id THEN
        RAISE EXCEPTION 'Cannot create DM with yourself';
    END IF;

    -- Return the existing DM if one already exists.
    SELECT c.id INTO conv_id
    FROM public.conversations c
    JOIN public.conversation_members m1 ON m1.conversation_id = c.id AND m1.user_id = current_uid
    JOIN public.conversation_members m2 ON m2.conversation_id = c.id AND m2.user_id = other_user_id
    WHERE c.is_group = false
    LIMIT 1;

    IF conv_id IS NOT NULL THEN RETURN conv_id; END IF;

    -- Insert with a placeholder topic, then rewrite once the id is known.
    INSERT INTO public.conversations (topic, is_group) VALUES ('temp', false) RETURNING id INTO conv_id;
    conv_topic := 'dm:' || conv_id;
    UPDATE public.conversations SET topic = conv_topic WHERE id = conv_id;

    INSERT INTO public.conversation_members (conversation_id, user_id, conversation_topic)
    VALUES (conv_id, current_uid, conv_topic), (conv_id, other_user_id, conv_topic);

    RETURN conv_id;
END;
$$;

Group conversation. The same RLS policies work for groups; you only need to insert more member rows.

CREATE OR REPLACE FUNCTION public.create_group_conversation(group_name text, member_ids uuid[])
RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    conv_id bigint;
    conv_topic text;
    current_uid uuid := auth.uid();
    uid uuid;
BEGIN
    INSERT INTO public.conversations (topic, name, is_group)
    VALUES ('group:placeholder', group_name, true) RETURNING id INTO conv_id;
    conv_topic := 'group:' || conv_id;
    UPDATE public.conversations SET topic = conv_topic WHERE id = conv_id;

    INSERT INTO public.conversation_members (conversation_id, user_id, conversation_topic)
    VALUES (conv_id, current_uid, conv_topic);

    FOREACH uid IN ARRAY member_ids LOOP
        IF uid != current_uid THEN
            INSERT INTO public.conversation_members (conversation_id, user_id, conversation_topic)
            VALUES (conv_id, uid, conv_topic)
            ON CONFLICT (conversation_id, user_id) DO NOTHING;
        END IF;
    END LOOP;

    RETURN conv_id;
END;
$$;

-- Grant execute privileges to the authenticated role.
GRANT EXECUTE ON FUNCTION public.create_dm_conversation(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.create_group_conversation(text, uuid[]) TO authenticated;

Subscribe to a private channel

Set private: true in the channel configuration. The SDK then attaches the caller's JWT to the subscription and Realtime evaluates the realtime.messages RLS policy before joining the topic.

const channel = supabase.channel(conversation.topic, {
  config: {
    private: true,
    postgres_changes: [
      {
        event: 'INSERT',
        schema: 'public',
        table: 'dm_messages',
        filter: `conversation_id=eq.${conversationId}`,
      },
    ],
  },
})

channel.on(
  'postgres_changes',
  { event: 'INSERT', schema: 'public', table: 'dm_messages' },
  (payload) => {
    console.log('New message:', payload.new)
  }
)

channel.subscribe((status) => {
  // status: 'SUBSCRIBED' | 'CHANNEL_ERROR' | 'TIMED_OUT' | 'CLOSED'
  if (status === 'SUBSCRIBED') {
    console.log('Subscribed')
  }
})

Key parameters

• private: true: enables RLS evaluation on realtime.messages. Omitting this flag skips the check entirely.

• filter: server-side filter so Realtime only pushes rows for the current conversation, reducing bandwidth and client work.

• The SDK handles the 30-second heartbeat and JWT refresh automatically.

Send a message

Insert into the message table over REST. RLS validates the sender identity and membership; the WAL captures the change and Realtime pushes it to all authorized subscribers—including the sender, so you do not need to append the new message locally.

const { error } = await supabase.from('dm_messages').insert({
  conversation_id: conversationId,
  sender_id: user.id,
  content: 'Hello!',
})

Load message history

A standard SELECT returns only the caller's own conversations because RLS filters the result set.

const { data: messages } = await supabase
  .from('dm_messages')
  .select('*')
  .eq('conversation_id', conversationId)
  .order('created_at', { ascending: true })
  .limit(100)

Clean up the subscription

Always remove the channel when the component unmounts. Leaked channels tie up server resources and leave the client sending heartbeats for a connection nobody reads.

return () => {
  supabase.removeChannel(channel)
}

End-to-end message flow

Following a message from the client back to the client makes the double-layer security visible:

User A sends a message
  |
  v
INSERT INTO dm_messages  (RLS: member + sender_id matches auth.uid())
  |
  v
PostgreSQL WAL captures the change
  |
  v
Realtime CDC evaluates RLS per subscriber
  |
  +--> User B is a member  --> push
  +--> User A is the sender --> push (sender receives own message)
  +--> User C is not a member --> no push

When a non-member tries to subscribe, the subscribe callback receives status CHANNEL_ERROR with the message You do not have permissions to read from this Channel topic: dm:xxx.

Broadcast — transient signals

Broadcast is best for signals that do not need to persist, such as typing indicators or read receipts. Because Broadcast bypasses PostgreSQL, its latency is lower than a full CDC round-trip.

// Send a typing signal
channel.send({
  type: 'broadcast',
  event: 'typing',
  payload: { user_id: user.id, is_typing: true },
})

// Receive
channel.on('broadcast', { event: 'typing' }, (payload) => {
  console.log(`${payload.user_id} is typing...`)
})

Presence — online state

Presence tracks the set of users connected to a channel, so the UI can show who is currently online in a conversation.

// Announce yourself
channel.track({ user_id: user.id, online_at: new Date().toISOString() })

// React to state changes
channel.on('presence', { event: 'sync' }, () => {
  const state = channel.presenceState()
  console.log('Online:', Object.keys(state))
})

Read receipts

Store a per-user cursor in a small table and combine it with Broadcast to push read-state updates without polling.

CREATE TABLE public.read_receipts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    conversation_id bigint REFERENCES conversations(id),
    user_id uuid NOT NULL,
    last_read_message_id bigint REFERENCES dm_messages(id),
    read_at timestamptz DEFAULT now(),
    UNIQUE(conversation_id, user_id)
);

Update the receipt row when the user scrolls past a message, and send a Broadcast event so other clients can update their unread badges without waiting for a WAL round-trip.