Four row-level security patterns that cover most small business apps
Four access rules cover most small apps: owner only, shared workspace, public read, and a public form. The SQL for each, how to test it as the person it is for, and where it quietly fails.
Row-level security is a rule attached to a table that decides, row by row, who may read or change what. Switch it on with no rules and nobody can touch the table through the public interface. You then add policies that open exactly the access you want. The scan post covers why this matters. This one is about what to write.
Two terms first. USING decides which existing rows a person can see, update or delete. WITH CHECK decides what a person may write, which stops them inserting a row that belongs to someone else. Supabase gives the public interface two database roles: anon for visitors who are not logged in and authenticated for those who are.
Only the owner sees their rows
For personal data: expenses, notes, a customer’s own orders. The table needs a user_id column.
ALTER TABLE public.expenses ENABLE ROW LEVEL SECURITY;
CREATE POLICY "expenses_owner" ON public.expenses
FOR ALL TO authenticated
USING ((select auth.uid()) = user_id)
WITH CHECK ((select auth.uid()) = user_id);Write (select auth.uid()) rather than auth.uid(). Postgres then evaluates it once per query instead of once per row, which you will notice when the table grows. Index user_id too.
Everyone in a workspace shares rows
For a team, a school or a shop with staff. Keep membership in its own table and give every shared table an org_id.
CREATE TABLE public.memberships (
user_id uuid REFERENCES auth.users NOT NULL,
org_id uuid NOT NULL,
role text NOT NULL DEFAULT 'member',
PRIMARY KEY (user_id, org_id)
);
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "invoices_members" ON public.invoices
FOR ALL TO authenticated
USING (org_id IN (
SELECT org_id FROM public.memberships WHERE user_id = (select auth.uid())))
WITH CHECK (org_id IN (
SELECT org_id FROM public.memberships WHERE user_id = (select auth.uid())));To let only admins delete, add a second policy FOR DELETE that also checks role = 'admin'. Policies for the same action are combined with OR, so keep each one narrow. A wide one added later quietly widens everything.
Everyone reads, only the owner writes
For a catalogue, a blog, a public profile.
CREATE POLICY "products_read" ON public.products
FOR SELECT TO anon, authenticated USING (true);
CREATE POLICY "products_write" ON public.products
FOR ALL TO authenticated
USING ((select auth.uid()) = owner_id)
WITH CHECK ((select auth.uid()) = owner_id);USING (true) is right here and wrong almost everywhere else. Our scan will list this table as publicly readable and ask whether you meant it. You did, so confirm.
Anyone can submit, only you can read
Enquiry forms, bookings, sign-ups on a website. Visitors are not logged in, so they arrive as anon.
CREATE POLICY "enquiries_submit" ON public.enquiries
FOR INSERT TO anon, authenticated WITH CHECK (true);
CREATE POLICY "enquiries_read" ON public.enquiries
FOR SELECT TO authenticated
USING (EXISTS (SELECT 1 FROM public.memberships m
WHERE m.user_id = (select auth.uid()) AND m.role = 'admin'));There is no SELECT policy for anon, so a stranger can drop a message in and can never read one back. Expect spam on an open insert. A hidden honeypot field and a length limit on the text columns stop most of it.
Test it as the person it is for
A policy you have not run as a restricted user is a guess. In the Supabase SQL editor you can impersonate one:
begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"11111111-1111-1111-1111-111111111111"}';
select count(*) from public.expenses; -- only that user's rows
rollback;Repeat as anon and confirm private tables return nothing, then run the curl check from the scan post against the live URL.
Where it fails without telling you
The service role key skips RLS completely, so it belongs on a server and nowhere near front-end code. If it ever appears in a browser bundle, every policy above is decoration.
Views run with their owner’s rights and can show rows your policies would hide. On Postgres 15 and later, create them with WITH (security_invoker = true).
Storage buckets have their own policies, separate from your tables. A locked invoices table next to a public invoices bucket is still a leak.
See what your public key can read
The free scan tries your database the way a stranger would and shows which tables answer.
Run the free scanFound something wrong or out of date? Write to hello@wyberai.com and we will fix it and say so here. Rules, fees and rates mentioned in our posts change, and this one was last checked on 18 November 2026.