Supabase RLS policy examples for profiles, posts, orders, messages and file uploads

Most apps need the same five sets of Supabase Row Level Security policies: each user reads and edits their own profile, anyone reads published posts but only authors change them, users read their own orders but only your server writes them, messages are visible to sender and recipient only, and uploaded files live in a folder named after the user’s id. Every policy below compares a column with (select auth.uid()), and each block is ready to paste into the Supabase SQL editor once you’ve matched the table and column names.

If you haven’t switched RLS on yet, start with how to enable Row Level Security. If a policy here makes your app show nothing, read why Supabase RLS returns an empty array before loosening anything.

Before you paste: three rules

• RLS must be on. A policy on a table with RLS off does nothing. Each block below starts with enable row level security; running it twice is harmless.
• Policies only add access. With RLS on and no policy for an action, that action is refused (Postgres calls this “default deny”). So if you leave out a delete policy, nobody can delete through the API. That’s often what you want.
• Updates need both halves. using decides which existing rows a user may touch; with check decides what the row may look like afterwards. Without with check on a different condition, a user could change user_id and hand a row to someone else.

1. Profiles (one row per user)

Assumes profiles.id is the user’s id (a uuid referencing auth.users).

alter table public.profiles enable row level security;

create policy "profiles: read own"
  on public.profiles for select to authenticated
  using ((select auth.uid()) = id);

create policy "profiles: create own"
  on public.profiles for insert to authenticated
  with check ((select auth.uid()) = id);

create policy "profiles: update own"
  on public.profiles for update to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

-- No delete policy: profiles go when the auth user is deleted
-- (use "on delete cascade" on the foreign key).

Need public profiles (usernames, avatars)? Don’t add using (true) to this table: it would publish every column, including email or phone if you store them. Put the public fields in a separate table (for example public_profiles) with a read-for-everyone policy, and keep this one private.

Roles and admin flags: if profiles has an is_admin or plan column, the update policy above lets users change it on their own row. Move those columns to a table users can’t write, or set them only from your server.

2. Posts (public when published, private drafts)

Assumes author_id uuid and published boolean.

alter table public.posts enable row level security;

create policy "posts: anyone reads published"
  on public.posts for select to anon, authenticated
  using (published = true);

create policy "posts: authors read own drafts"
  on public.posts for select to authenticated
  using ((select auth.uid()) = author_id);

create policy "posts: authors create"
  on public.posts for insert to authenticated
  with check ((select auth.uid()) = author_id);

create policy "posts: authors update own"
  on public.posts for update to authenticated
  using ((select auth.uid()) = author_id)
  with check ((select auth.uid()) = author_id);

create policy "posts: authors delete own"
  on public.posts for delete to authenticated
  using ((select auth.uid()) = author_id);

create index if not exists posts_author_id_idx on public.posts (author_id);

Two select policies are fine: Postgres combines permissive policies with OR, so a signed-in author sees published posts plus their own drafts.

3. Orders (users read, only your server writes)

Assumes user_id uuid. The important part is what’s missing:

alter table public.orders enable row level security;

create policy "orders: read own"
  on public.orders for select to authenticated
  using ((select auth.uid()) = user_id);

create index if not exists orders_user_id_idx on public.orders (user_id);

-- Deliberately no insert, update or delete policies.
-- Orders are created and marked paid by your payment webhook or a server
-- function using the secret key, which bypasses RLS.

Why: if the browser can insert or update orders, a user can write status = 'paid' or amount = 0 themselves. Prices and payment status should only come from a server that has checked with your payment provider (for example a verified Stripe webhook).

4. Messages (sender and recipient only)

Assumes sender_id uuid and recipient_id uuid.

alter table public.messages enable row level security;

create policy "messages: sender or recipient reads"
  on public.messages for select to authenticated
  using ((select auth.uid()) in (sender_id, recipient_id));

create policy "messages: send as yourself"
  on public.messages for insert to authenticated
  with check ((select auth.uid()) = sender_id);

create policy "messages: sender deletes own"
  on public.messages for delete to authenticated
  using ((select auth.uid()) = sender_id);

create index if not exists messages_sender_idx on public.messages (sender_id);
create index if not exists messages_recipient_idx on public.messages (recipient_id);

No update policy: once sent, a message can’t be edited through the API. If you need “mark as read”, a plain update policy would let the recipient rewrite the message text too, so use a small security definer function that only flips the flag after checking auth.uid() = recipient_id, or keep read receipts in their own table.

For group chats, the usual pattern is a conversation_members table and a policy that checks membership with exists (select 1 from conversation_members m where m.conversation_id = messages.conversation_id and m.user_id = (select auth.uid())). RLS must be on for the members table too.

5. File uploads (Supabase Storage)

Make the bucket private (public buckets serve files to anyone with the URL, whatever your policies say), and upload each file under a folder named after the user’s id: <user id>/avatar.png. Storage policies live on storage.objects:

create policy "files: upload to own folder"
  on storage.objects for insert to authenticated
  with check (
    bucket_id = 'files'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );

create policy "files: read own"
  on storage.objects for select to authenticated
  using (
    bucket_id = 'files'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );

create policy "files: replace own"
  on storage.objects for update to authenticated
  using (
    bucket_id = 'files'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  )
  with check (
    bucket_id = 'files'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );

create policy "files: delete own"
  on storage.objects for delete to authenticated
  using (
    bucket_id = 'files'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );

Supabase’s storage docs note that a plain upload needs only INSERT, but overwriting a file (upsert) needs SELECT and UPDATE as well, which is why all four are here. Change 'files' to your bucket’s name. To share a private file, create a signed URL from your app rather than making the bucket public.

How to test your policies

1. In the SQL editor, pretend to be a user. auth.uid() reads the user id from the request’s JWT claims, so you can set them by hand inside a transaction and roll back afterwards. Replace the uuid with a real test user’s id:

begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"00000000-0000-0000-0000-000000000000","role":"authenticated"}', true);

select count(*) from public.orders;   -- only this user's orders
select count(*) from public.messages; -- only their conversations

rollback;

Repeat with set local role anon; (and no claims) to see what a signed-out visitor gets. Private tables should return 0 rows.

2. Through the API, as a stranger. This is what an attacker actually does:

curl "https://YOUR_PROJECT_REF.supabase.co/rest/v1/orders?select=*&limit=1" \
  -H "apikey: YOUR_PUBLISHABLE_OR_ANON_KEY"

[] or a permission error is good. A row is a leak.

3. With two test accounts. Sign in as user A, create a post, order and message; sign in as user B and confirm none of A’s private data shows up and B can’t edit A’s rows.

4. Automate it. Supabase documents pgTAP tests in supabase/tests/, run with supabase test db. Their advice is to assert both the allowed and the denied case for every action.

5. Run the audit query. Our free Supabase RLS audit SQL lists tables with RLS off, using (true) policies and public buckets in one go.

Prompt for your AI builder

Add Row Level Security policies to my Supabase tables using (select auth.uid()).
- profiles: users select, insert and update only their own row (id = auth uid). No delete.
- posts: anyone can select rows where published = true; authors can select, insert,
  update and delete their own rows (author_id). Updates need USING and WITH CHECK.
- orders: users can select their own rows only. No insert/update/delete policies;
  orders are written by the server with the secret key.
- messages: sender or recipient can select; insert only with sender_id = auth uid;
  sender can delete. No update policy.
- storage bucket "files": private; users can insert/select/update/delete only objects
  whose first folder is their user id.
Enable RLS on every table first. Show me the SQL before running it.

FAQ

Check it for free

Our free scan checks your live app from the outside (after you prove you own it): it asks your Supabase API what an anonymous visitor can read, reads at most one row to confirm, and keeps none of it. It can’t sign in as your users, so tests 1, 3 and 4 above are still worth doing. Run a free scan, or check a key with the key checker.