How I Made It Impossible for One Parent to Read Another's Data

I'm building FamNest, a wellness app for parents. People hand it some of the most sensitive data they have: how stressed they are, how they slept, how they're really coping with a toddler at 9pm. If that data ever leaked between accounts, the app would be dead on arrival — and rightly so.

So before I wrote a single feature, I had to answer one question: how do I make it physically impossible for one user to read another user's data?

The naive answer is "I'll add a where user_id = ? to every query." That works right up until the day you forget one. With Supabase, there's a much stronger option — one that lives in the database itself, not in your app code: Row-Level Security (RLS).

This is how I set it up, the mistakes that are easy to make, and the two security warnings that bit me after I thought I was done.

Why app-level filtering isn't enough

Supabase exposes your Postgres database directly over a REST and client API. That's the superpower and the footgun. With the anon key shipped to the browser, a user can in principle query any table you've exposed:

const { data } = await supabase.from('daily_checkins').select('*')

If you're relying on your own code to always add .eq('user_id', currentUser.id), then your security is only as good as your least careful query. One forgotten filter in one API route, and every parent's check-ins are public.

RLS flips the model. Instead of trusting every query to filter correctly, the database refuses to return rows the current user isn't allowed to see — no matter how the query is written, no matter which key is used. Your app code can't opt out of it.

The data model

Here's the shape of FamNest. Every table that holds personal data hangs off a user_id:

create table public.users (
  id uuid references auth.users(id) on delete cascade primary key,
  name text not null,
  email text not null,
  created_at timestamptz default now()
);

create table public.daily_checkins (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.users(id) on delete cascade not null,
  mood integer not null check (mood between 1 and 10),
  stress_level integer not null check (stress_level between 1 and 10),
  sleep_hours numeric(3,1) not null check (sleep_hours between 0 and 24),
  available_minutes integer not null check (available_minutes > 0),
  goal text not null,
  created_at timestamptz default now()
);

create table public.recommendations (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.users(id) on delete cascade not null,
  checkin_id uuid references public.daily_checkins(id) on delete cascade not null,
  content jsonb not null,
  created_at timestamptz default now()
);

The user_id column is the linchpin of the whole security model. Note on delete cascade on the foreign key back to auth.users — when someone deletes their account, every row they ever created goes with it. For a privacy-sensitive app, that's not a nice-to-have, it's the right default.

Step 1: Enable RLS (the part everyone forgets)

Here's the gotcha that trips up every Supabase beginner: when you create a table, RLS is off by default. An empty policy list with RLS off means the table is wide open. You have to turn it on explicitly, per table:

alter table public.users enable row level security;
alter table public.wellness_profiles enable row level security;
alter table public.daily_checkins enable row level security;
alter table public.recommendations enable row level security;
alter table public.subscriptions enable row level security;
alter table public.weekly_reports enable row level security;

The mental model that makes this click: once RLS is on, the default is deny. No rows in, no rows out — until you write a policy that explicitly allows something. That's exactly the posture you want for family data.

Step 2: Write "own rows only" policies

The core rule for FamNest is simple: you can only touch rows that belong to you. The magic ingredient is auth.uid() — a Supabase helper that returns the ID of the currently authenticated user, straight from their JWT. It cannot be spoofed from the client.

For the users table, "yours" means the row whose primary key is you:

create policy "users_select_own"
  on public.users for select
  using (auth.uid() = id);

create policy "users_insert_own"
  on public.users for insert
  with check (auth.uid() = id);

create policy "users_update_own"
  on public.users for update
  using (auth.uid() = id);

For every other table, "yours" means user_id matches you:

-- Daily check-ins
create policy "checkins_select_own"
  on public.daily_checkins for select
  using (auth.uid() = user_id);

create policy "checkins_insert_own"
  on public.daily_checkins for insert
  with check (auth.uid() = user_id);

-- AI recommendations
create policy "recommendations_select_own"
  on public.recommendations for select
  using (auth.uid() = user_id);

create policy "recommendations_insert_own"
  on public.recommendations for insert
  with check (auth.uid() = user_id);

using vs. with check — know the difference

This is the single most important RLS concept and the one most people get fuzzy on:

• using is the filter applied to rows that already exist — it governs select, update, and delete. "Which rows are you even allowed to see/touch?"

• with check is applied to the new values of a row — it governs insert and update. "Are you allowed to write a row that looks like this?"

Why does insert use with check instead of using? Because there's no existing row to filter — you're validating the row that's about to be created. The with check (auth.uid() = user_id) policy is what stops a malicious user from inserting a check-in with someone else's user_id stamped on it. Without it, a user could happily write data into another family's account.

Notice that daily_checkins and recommendations have no update or delete policies on purpose. A check-in is a historical record — once logged, it shouldn't be editable. RLS lets me express that as a security guarantee, not just a UI decision: with no update policy and default-deny in force, the database itself rejects the edit.

Step 3: Auto-provision the user row on signup

When someone signs up through Supabase Auth, they land in auth.users — but my app needs a matching row in public.users and a default subscriptions row. I do that with a trigger:

create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id, name, email)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'name', split_part(new.email, '@', 1)),
    new.email
  );

  insert into public.subscriptions (user_id, status, plan)
  values (new.id, 'free', 'free');

  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

See that security definer? It means the function runs with the privileges of the function's owner, not the calling user. It has to — the new user isn't authenticated yet at the moment of signup, so under normal RLS they couldn't insert anything. security definer lets the trigger bypass RLS just for this bootstrap step.

And that is exactly where I got a nasty surprise.

The part nobody tells you: security definer has sharp edges

After deploying, Supabase's database linter lit up with security warnings about handle_new_user(). It turns out a security definer function is a small privilege-escalation engine, and if you're careless it can be turned against you. Two fixes were needed.

1. Pin the search_path

A security definer function that doesn't pin its search_path can be hijacked. An attacker who can create objects in another schema could shadow public.users with a malicious table, and your elevated-privilege function would happily write to their table instead. The fix is to nail the search path down:

ALTER FUNCTION public.handle_new_user() SET search_path = public, pg_temp;

Now the function always resolves users and subscriptions to the real public tables, no matter what the caller's session settings are.

2. Revoke direct execute rights

By default, Supabase exposes functions over /rest/v1/rpc/. That means handle_new_user() — a function that runs with elevated privileges — was callable directly by the anon and authenticated roles. It's only ever meant to be fired by the trigger. So I slammed that door:

REVOKE EXECUTE ON FUNCTION public.handle_new_user() FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION public.handle_new_user() FROM anon;
REVOKE EXECUTE ON FUNCTION public.handle_new_user() FROM authenticated;

The trigger still works fine — triggers run as the table owner regardless of these grants. But nobody can poke the function manually anymore.

You can verify the lockdown:

SELECT grantee, privilege_type
FROM information_schema.routine_privileges
WHERE routine_schema = 'public'
  AND routine_name = 'handle_new_user';

If that returns no rows for anon, authenticated, or PUBLIC, you're good.

The lesson: RLS protects your tables, but security definer functions are a side door. Lock them down separately.

How to actually test your policies

Writing policies and trusting them are different things. The fastest way I've found to gain confidence:

Use the Supabase "Impersonate user" feature in the SQL editor / table view. Run a select * on daily_checkins as User A, then as User B. You should only ever see your own rows.

Try to be evil. Sign in as a real user from the browser and attempt the attack you're defending against:

// Logged in as user A, try to read everything:
const { data } = await supabase.from('daily_checkins').select('*')
// Expected: only user A's check-ins, never anyone else's.

// Try to insert a row pretending to be someone else:
const { error } = await supabase.from('daily_checkins').insert({
  user_id: 'some-other-users-uuid',
  mood: 5,
  stress_level: 5,
  sleep_hours: 7,
  available_minutes: 30,
  goal: 'test'
})
// Expected: error — the with check policy rejects it.

If the second insert succeeds, your with check policy is missing or wrong. Better to find that on your own laptop than in a breach report.

The checklist I now run for every new table

Every time I add a table that holds user data, I run through this:

• Does it have a user_id column tied to auth.users (ideally on delete cascade)?

• Did I run alter table … enable row level security?

• Is there a select policy with using (auth.uid() = user_id)?

• Is there an insert policy with with check (auth.uid() = user_id)?

• Do I intend to allow updates/deletes? If not, leave those policies out — default-deny does the work.

• If I added a security definer function, did I pin search_path and revoke public execute?

• Did I test it by impersonating a second user and trying to break in?

Closing thought

The thing I like about RLS is that it moves the security guarantee to the layer that's hardest to bypass — the database — instead of leaving it scattered across every API route I'll ever write. For an app holding family wellness data, "I promise I'll always remember the filter" isn't good enough. "The database will reject it even if I forget" is.

If you're building anything multi-tenant on Supabase, do this before you build features, not after. Future-you, staring at a security warning at 11pm, will be grateful.

---

I'm building FamNest in public — a wellness app for working parents. If posts like this are useful, come along for the build.