How do you model shared workspaces with row level security?
Key rows on the workspace, not the user, from day one. The membership lookup that causes recursive policy errors, and how to avoid it.
Answer
Put workspace_id on every user-owned row from the start, even if sharing ships much later, and create a personal workspace at signup so there is no null case. Policies check membership through a security-definer function rather than a subquery on the membership table, which is what causes the recursive policy errors people hit.
Leutrim Miftaraj
Founder, SubTracker · Updated September 5, 2026
The short answer
The expensive decision is not the policies, it is the key. Retrofitting workspace_id onto a schema keyed on user_id means touching every query, every policy and every index — so add the column on day one and default it to a personal workspace, even if the sharing UI is a year away.
Add the column before you need it
""`sql create table workspaces ( id uuid primary key default gen_random_uuid(), name text not null, owner_id uuid not null references auth.users(id), is_personal boolean not null default true, created_at timestamptz not null default now() );
create table workspace_members ( workspace_id uuid not null references workspaces(id) on delete cascade, user_id uuid not null references auth.users(id) on delete cascade, role text not null default 'member' check (role in ('member','owner')), primary key (workspace_id, user_id) );
alter table subscriptions add column workspace_id uuid not null references workspaces(id); create index on subscriptions (workspace_id); ""`
Create a personal workspace at signup and put every new subscription in it. That removes the null case entirely — there is never a row that belongs to a user rather than a workspace, so there is never a query that has to handle both.
Sharing then becomes a UI feature over an existing shape rather than a migration.
The recursion trap
The obvious policy is a subquery against the membership table:
""`sql -- Do not do this. create policy "members read" on subscriptions for select using ( workspace_id in ( select workspace_id from workspace_members where user_id = auth.uid() ) ); ""`
This works until you put RLS on "workspace_members" itself — which you must, or any user can read the whole membership table. Then the policy on "subscriptions" queries "workspace_members", whose own policy queries "workspace_members", and Postgres reports infinite recursion in policy.
The fix is a security-definer function, which runs with the definer's rights and therefore bypasses the policy on the inner table:
""`sql create or replace function is_workspace_member(ws uuid) returns boolean language sql stable security definer set search_path = public as $$ select exists ( select 1 from workspace_members where workspace_id = ws and user_id = auth.uid() ); $$;
create policy "members read" on subscriptions for select using (is_workspace_member(workspace_id)); ""`
"set search_path" is not optional on a security-definer function. Without it the function is a privilege-escalation vector, because a caller can influence which schema the inner names resolve to.
Separate policies per operation
One policy for everything is a common shortcut and it conflates two different questions: who can see this, and who can change it.
""`sql create policy "members read" on subscriptions for select using (is_workspace_member(workspace_id));
create policy "members write" on subscriptions for insert with check (is_workspace_member(workspace_id));
create policy "members update" on subscriptions for update using (is_workspace_member(workspace_id)) with check (is_workspace_member(workspace_id));
create policy "owners delete" on subscriptions for delete using (is_workspace_owner(workspace_id)); ""`
Note "using" and "with check" on update. "using" controls which rows you may update; "with check" controls what they may become. Omitting the second lets a member move a row into a workspace they do not belong to, which is a quiet and complete authorisation bypass.
What the service role does not do
The service-role key bypasses RLS entirely. That is correct for cron jobs and admin functions and it means RLS is not protecting those paths at all.
Two consequences worth stating.
Every service-role query must filter explicitly. The policy is not there to catch a mistake. A cron job that forgets a workspace filter reads every row in the table and will do so without complaint.
Entitlement checks belong in the job's own query. We shipped a reminder cron with no plan filter, so free-plan users received a paid feature. RLS would not have caught it and was never going to — the service role sees everything by design.
Testing policies
Policies are the part of the schema most likely to be wrong and least likely to be tested, because testing them requires acting as a user rather than as the service role.
Three cases per table, and they are worth the setup:
A member of the workspace can read and write. The happy path.
A non-member gets zero rows, not an error. RLS filters rather than rejects, which is easy to mistake for an empty table.
A member cannot move a row out of their workspace. The "with check" case above. This is the one that is usually missing and the one that matters most.
Die ehrliche Bau-oder-Kauf-Linie
Das Schema ist der einfache Teil. Es ist auch der Teil, der sich nach Fortschritt anfühlt — weshalb das Projekt unmittelbar danach aufhört, Spass zu machen.
Was tatsächlich Zeit kostet, in der Reihenfolge, in der es zubeisst: geplante Erinnerungen mit Idempotenz und Zeitzonen, Wiederholungs-Arithmetik über unregelmässige Intervalle, Währungsbehandlung, und ein Import, aus dem heraus überhaupt etwas entsteht.
Nichts davon ist intellektuell schwierig. Es ist schwierig im Sinne von vier Wochenenden, und das vierte ist das, an dem das Projekt stirbt.
Wenn das Ziel Lernen ist: bau es. Der Code hier ist echt und funktioniert. Wenn das Ziel ist, kein Geld mehr an vergessene Abos zu verlieren, ist der Bau ein Umweg.
Was SubTracker damit macht
Zur Einordnung, weil die Entscheidungen oben aus einem laufenden System stammen und nicht aus einer Entwurfsübung.
SubTracker ist eine Next.js- und PostgreSQL-Anwendung (Supabase), die Abos manuell verfolgt — es gibt keine Bankverbindung, jede Zeile stammt aus einem Formular oder einem CSV-Import. Diese Einschränkung prägt das Schema: kein „erkannt"-Zustand, kein Konfidenzwert, keine Händler-Zuordnungstabelle. Jede Zeile ist eine Behauptung, die jemand aufgestellt hat.
Der kostenlose Plan verfolgt unbegrenzt viele Abos inklusive CSV-Import und Live-Kalender-Feed. Erinnerungen und Preis-Alarme sind Plus für $5.90/Monat; Family kostet $9.90/Monat und fügt einen geteilten Workspace für bis zu zehn Personen hinzu — an dem Punkt hören die Besitzverhältnisse im Schema auf, theoretisch zu sein.
Frequently asked questions
Should I add workspace_id before building sharing?+
Yes. Retrofitting it onto a schema keyed on user_id means touching every query, policy and index. Adding the column and creating a personal workspace at signup removes the null case and makes sharing a UI feature rather than a migration.
Why does my RLS policy cause infinite recursion?+
Because the policy subqueries a membership table that has its own policy referencing itself. Move the membership check into a security-definer function, which runs with the definer's rights and bypasses the inner policy.
Why do update policies need both using and with check?+
using controls which rows may be updated; with check controls what they may become. Without the second, a member can move a row into a workspace they do not belong to, which is a complete authorisation bypass.
Does row level security protect cron jobs?+
No. The service role bypasses RLS by design, so every service-role query must filter explicitly and every entitlement check must live in the job's own query. A cron that forgets a filter reads the whole table without complaint.