Tutorial: a realtime chat with row-level security

Build chat rooms where only members read and post, new messages appear at once on every device, and nothing leaks to people outside a room: tables, row rules, a membership function, change events and a channel rule.

Updated Oct 24, 2026

You will build the backend and client code for chat rooms. Messages live in Postgres under row-level security, and realtime only tells clients that something changed. Clients then read the new messages through the same rules, so the realtime path can't leak a message to someone outside the room. You need a project and the JavaScript SDK (Quickstart).

#1. Tables and row rules

Run this in the SQL Editor. is_member is a security definer function, so the policies can check membership without giving users read access to everyone's memberships.

create table rooms (
  id bigint generated always as identity primary key,
  name text not null check (length(name) between 1 and 80),
  created_by uuid not null default auth.uid(),
  created_at timestamptz not null default now()
);
create table room_members (
  room_id bigint not null references rooms on delete cascade,
  user_id uuid not null,
  primary key (room_id, user_id)
);
create table messages (
  id bigint generated always as identity primary key,
  room_id bigint not null references rooms on delete cascade,
  author_id uuid not null default auth.uid(),
  body text not null check (length(body) between 1 and 4000),
  created_at timestamptz not null default now()
);
create index on messages (room_id, id);

create or replace function public.is_member(room bigint)
returns boolean language sql stable security definer set search_path = ''
as $$ select exists (select 1 from public.room_members where room_id = room and user_id = auth.uid()) $$;

alter table rooms enable row level security;
alter table rooms force row level security;
alter table room_members enable row level security;
alter table room_members force row level security;
alter table messages enable row level security;
alter table messages force row level security;

-- Rooms: members see them; anyone signed in can create one.
create policy "members read rooms" on rooms for select using (public.is_member(id) or created_by = auth.uid());
create policy "signed-in create rooms" on rooms for insert with check (auth.uid() is not null and created_by = auth.uid());

-- Memberships: see your own rooms' members; the room's creator adds people.
create policy "members see members" on room_members for select using (public.is_member(room_id));
create policy "creator adds members" on room_members for insert
  with check (exists (select 1 from public.rooms r where r.id = room_id and r.created_by = auth.uid()));

-- Messages: members read and post, as themselves.
create policy "members read messages" on messages for select using (public.is_member(room_id));
create policy "members post messages" on messages for insert
  with check (public.is_member(room_id) and author_id = auth.uid());

There is no update or delete policy on messages, so nobody can edit or delete a message (the service role key still can, for moderation).
A room's creator should add themselves as its first member: insert into room_members values (<room id>, auth.uid()).
Write every name with its schema inside the definer function (public.room_members) because its search_path is empty (Database functions).

#2. Turn on change events for messages

Realtime › Database change data capture
Switch it on and pick messages. Every insert now publishes an event on the channel db.table.messages (Database change events).

Let signed-in users listen

Under Policies, add a channel rule. App users get no channel until a rule allows it.

ALLOW IF ctx.resource.name == "db.table.messages" AND ctx.action == "SUBSCRIBE" AND ctx.actor.is_anonymous == False

Change events carry the table, the kind of change and how many rows changed, never the rows themselves. A user outside a room learns only that *some* message was posted somewhere. If even that is too much, use a private channel per user instead (Channels and connections) and publish to the members from a trigger function.

#3. The client

import { createClient } from '@sovaria/sdk'

export const sovaria = createClient('https://<project-ref>.sovarias.in', '<anon-key>')

type Message = { id: number; author_id: string; body: string; created_at: string }
const toObjects = <T,>(r: { columns: string[]; rows: unknown[][] }) =>
  r.rows.map((row) => Object.fromEntries(r.columns.map((c, i) => [c, row[i]])) as T)

export async function createRoom(name: string, memberIds: string[]) {
  const r = (await sovaria.data.sql('insert into rooms (name) values ($1) returning id', [name])) as any
  const roomId = r.rows[0][0] as number
  // The creator joins first (auth.uid() is the signed-in user), then adds the others.
  await sovaria.data.sql('insert into room_members (room_id, user_id) values ($1, auth.uid())', [roomId])
  for (const id of memberIds) {
    await sovaria.data.sql('insert into room_members (room_id, user_id) values ($1, $2)', [roomId, id])
  }
  return roomId
}

export const send = (roomId: number, body: string) =>
  sovaria.data.sql('insert into messages (room_id, body) values ($1, $2)', [roomId, body])

// Load the latest messages, then follow new ones. Returns a function that stops listening.
export function openRoom(roomId: number, render: (all: Message[]) => void) {
  let messages: Message[] = []
  let lastId = 0

  async function fetchNew() {
    const r = await sovaria.data.sql(
      'select id, author_id, body, created_at from messages where room_id = $1 and id > $2 order by id limit 200',
      [roomId, lastId],
    )
    const fresh = toObjects<Message>(r as never)
    if (!fresh.length) return
    lastId = fresh[fresh.length - 1].id
    messages = [...messages, ...fresh].slice(-500)
    render(messages)
  }

  fetchNew()
  const channel = sovaria.realtime.channel('db.table.messages', {
    onMessage: (event) => { if (event.event_type === 'row.inserted') fetchNew() },
    onStatus: (status) => { if (status === 'live') fetchNew() },   // catch up after a reconnect
  })
  return () => channel.close()
}

fetchNew asks only for messages after the last one shown, so every event costs one small, indexed query.
The query has where room_id = $1 for speed, but security comes from the row rules: a user who isn't a member gets no rows even if they change the code.
Rapid messages may arrive as several events; each fetchNew picks up everything since lastId, so nothing is shown twice.

#4. Typing indicators (optional)

Typing indicators don't need to be stored. Publish them on a channel that only signed-in users can use, and include the room id. They carry nothing private, but keep them out of rooms you can't see by ignoring rooms you aren't showing.

ALLOW IF ctx.resource.name == "typing" AND ctx.actor.is_anonymous == False

#5. Prove nobody else can read a room

Before you ship, run a simulation: it tries every table and channel as a visitor, a signed-in user and each role, changes nothing, and reports any access you didn't intend (Simulate access before you ship).

sov sim run --fail-on critical

#Coming from Firebase or Supabase?

Platform	What this is called there
Firebase	Firebase chat app tutorial (Firestore listeners)
Supabase	Supabase realtime chat tutorial
Sovaria	Tutorial: a realtime chat with row-level security

Moving an existing app? Migrate from Supabase or Firebase imports users (with their passwords), data, files and rules.