Row-Level Security Recursion: A Debugging Story

From Subquery Trouble to Clean Views: Taming RLS in Supabase

📌 The Situation

I was working on a React Native app with Supabase as the backend.

Part of the app’s requirements was role-based access control: every user could either be a user or an admin, stored in a table like this:

create table public.user_roles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique,
  role text not null check (role in ('user', 'admin')),
  created_at timestamptz default now(),
  foreign key (user_id) references auth.users (id) on delete cascade
);

The goal seemed simple:

When a user logs in, check their role.

If they’re an admin, then show them the special Admin tab in the app’s UI.

📌 The Plan: Use Row-Level Security (RLS)

Now, if you haven’t used it before, Row-Level Security (RLS) is a Postgres feature that lets you control which rows in a table each user is allowed to read, insert, update, or delete.

It’s perfect for multi-tenant apps or apps with fine-grained permissions.

You turn it on like this:

alter table user_roles enable row level security;

Thereafter, write policies — basically rules that say, “users can only see or modify rows if this condition is true.”

For example:

create policy "Users can read their own role"
on user_roles
for select
using (user_id = auth.uid());

This says:

“A user can only read rows where the user_id matches their authenticated user ID (auth.uid()).”

If the condition is true, the row is visible to them; otherwise, it’s filtered out by Row-Level Security.

I added a second policy too:

create policy "Admins can read all roles"
on user_roles
for select
using (
  exists (
    select 1 from user_roles ur
    where ur.user_id = auth.uid() and ur.role = 'admin'
  )
);

This says:

“Allow a user to see any row in user_roles if there exists a row in user_roles where their user_id matches the currently authenticated user’s ID (auth.uid()) and their role is 'admin'.”

The exists (select 1 ...) part is a common SQL pattern to check whether any rows meet certain criteria.

It doesn’t care about the actual value being selected (we just write select 1 as a placeholder) — it simply checks if at least one row exists.

If it finds a match, exists evaluates to true, and the policy lets the query proceed.

If no match is found, it evaluates to false, and the row is filtered out silently by Postgres’ Row-Level Security.

So far so good.

📌 The First Problem: Nothing Came Back

I fired up the app, authenticated as a user with the admin role in user_roles, ran this query:

const { data, error } = await supabase
  .from('user_roles')
  .select('role')
  .eq('user_id', userId)
  .single();

Result?

An empty object.

No error — just nothing.

📌 The Debugging Spiral

First instinct: did the user even have a record?

Checked via direct SQL query in Supabase Studio:

select * from user_roles where user_id = '...';

Yep — the record was there.

Next: was RLS enabled?

Confirmed. ✅

Then: were my policies applied to the authenticated role?

Yep. ✅

Was auth.uid() returning what I expected?

Logged it — and yep, it matched the authenticated user’s ID. ✅

So why was my admin not seeing their own role?

📌 The Gotcha: RLS Recursion

It took a while to click, but here’s what happened:

That second policy:

exists (
  select 1 from user_roles ur
  where ur.user_id = auth.uid() and ur.role = 'admin'
)

queries the same user_roles table that’s protected by RLS, and guess what — RLS applies to subqueries too.

Which means:

In order for Postgres to evaluate “is this user an admin?” by looking at the user_roles table, it needed to check if they could see their own record.

However, they couldn’t see their own record without passing that same admin check.

This is known as a recursive permission deadlock.

📌 The Solution: Break the Recursion with a View

The cleanest fix was to move the list of admin user IDs into a view.

What’s a view?

A view is a saved SQL query that behaves like a virtual table. It lets you encapsulate a query’s results and reuse them elsewhere, and by default, views don’t have RLS policies applied (unless you explicitly add them).

They’re perfect for situations like this where you need to reference sensitive data without triggering recursion.So I created:

create view public.admin_users as
select user_id from public.user_roles where role = 'admin';

Then updated my policy to:

create policy "Users and Admins can read roles"
on user_roles
for select
using (
  user_id = auth.uid()
  or auth.uid() in (select user_id from public.admin_users)
);

Now:

• Regular users can see their own role.

• Admin users are looked up via the admin_users view — which isn’t blocked by RLS.

• No recursion. No deadlock.

📌 The Moment It Worked

I saved the policy, ran the query again from my app, and finally, the role came back.

That Admin tab showed up.

Victory.

📌 What I Learned

🔹 Postgres RLS is powerful, but subqueries inside policies can get tricky.

🔹 Policies apply to subqueries inside themselves — leading to recursive permission issues.

🔹 Views are a clean way to break recursion and avoid weird policy evaluation order problems.

📌 Key Takeaways for Beginners

✅ Always test RLS policies carefully — especially when using subqueries.

✅ Use views to separate logic when permission checks start nesting.

✅ Remember that views act like virtual tables and can simplify complex RLS situations.

📌 What is RLS, a Policy, and a View Again?

• Row-Level Security (RLS): Lets you control which rows each user can access in a table.

• Policy: A rule that says which rows a user can see or modify, and under what conditions.

• View: A saved SQL query that acts like a table, and by default skips RLS unless explicitly enabled.

📌 Final Thought

This was one of those situations where a simple requirement exposed a deep implementation detail in how Postgres handles RLS policies.

I learned a ton about how security policies are evaluated row-by-row and query-by-query — and how careful you need to be when those queries reference the same table.

If you’re adding RLS to your Postgres tables, plan your policy logic carefully, test recursive cases, and don’t hesitate to lean on views to simplify role checks.

---