*Edited: reworded the framing after good feedback in the comments. The profiles policy is the bug, not "the combination".*
I found this in LabStock, an open-source inventory app built on Supabase by a Turkish developer (that's why the column is called `gozlemci_of`, "observer of"). I reported it privately in October, it was fixed on Nov 7, and he said I could write about it.
**The setup**
```sql
-- users manage their own profile row
create policy profiles_self on profiles for all to authenticated
using (id = auth.uid()) with check (id = auth.uid());
-- observers can read the stock of the account they observe
create policy stock_observer_read on stock_items for select to authenticated
using (exists (
select 1 from profiles p
where p.id = auth.uid() and p.gozlemci_of = stock_items.owner
));
```
`gozlemci_of` was meant to be set only by a SECURITY DEFINER function that checks an invite code.
**The bug**
The read policy does exactly what it says. The problem is the write side: `profiles_self` pins writes to your own row, but RLS picks rows, not columns. The table had the old default grants, so `authenticated` could update every column on its own row, `gozlemci_of` included. That means you could skip the invite code entirely:
```js
await supabase.from('profiles')
.update({ gozlemci_of: '<another user id>' })
.eq('id', myUserId)
```
It's still your own row, so it passes. From then on the observer policy shows you the other user's stock, prices, suppliers and BOMs. User IDs were easy to collect from a shared catalogue table.
The rule: **any column that another policy reads to grant access must not be writable by the client.** It's a permission, not a preference. This isn't Supabase-specific either; plain Postgres with RLS behaves the same.
Side note: since Oct 30 new tables get no Data API grants, but the common fix for the 42501 error (`grant select, insert, update, delete on profiles to authenticated`) puts the table-wide update right back.
**The fix**
Option 1, column grants. Revoke the table-wide write, then grant back only the columns users should edit:
```sql
revoke insert, update on public.profiles from anon, authenticated;
grant insert (id, name, phone) on public.profiles to authenticated; -- your editable columns
grant update (name, phone) on public.profiles to authenticated;
```
`revoke update (gozlemci_of)` on its own does nothing while the table-wide grant exists, which is why the revoke comes first. The SECURITY DEFINER function can still write the column.
Option 2, a trigger (what LabStock used). It lets users clear the link but never set it themselves:
```sql
create function public.guard_observer_link() returns trigger
language plpgsql as $$
begin
-- inside a SECURITY DEFINER function current_user is the owner,
-- so the invite-code function can still set the link
if current_user in ('authenticated', 'anon')
and new.gozlemci_of is not null
and (tg_op = 'INSERT' or new.gozlemci_of is distinct from old.gozlemci_of) then
raise exception 'Observer links can only be set with an invite code';
end if;
return new;
end $$;
create trigger guard_observer_link
before insert or update on public.profiles
for each row execute function public.guard_observer_link();
```
Either way, review existing links afterwards. A fix doesn't undo a link someone already made.
**Check your own project**
This lists columns `authenticated` can update that a policy on another table mentions. Run it in the SQL editor:
```sql
with writable as (
select c.relname as table_name, a.attname as column_name
from pg_attribute a
join pg_class c on c.oid = a.attrelid
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind in ('r', 'p')
and a.attnum > 0 and not a.attisdropped
and a.attname not in ('id', 'user_id', 'created_at', 'updated_at')
and has_column_privilege('authenticated', a.attrelid, a.attnum, 'UPDATE')
)
select w.table_name, w.column_name, p.tablename as checked_by_table, p.policyname
from writable w
join pg_policies p
on p.schemaname = 'public'
and p.tablename <> w.table_name
and concat_ws(' ', p.qual, p.with_check) ~ ('\m' || w.column_name || '\M')
and concat_ws(' ', p.qual, p.with_check) ~ ('\m' || w.table_name || '\M')
order by 1, 2;
```
A row in the result needs a closer look, not necessarily a fix. It matters when users can also write their own row in that table. The query can't see checks hidden inside helper functions like `is_member()`, so read those too.
Column names worth a second look: `team_id`, `org_id`, `manager_of`, `linked_account`, `shared_with`.
One more review tip from the comments (thanks u/Inner_Storage_3374): permissive policies are OR'd together, so for any table with more than one policy per command, ask whether each one would still be fine as the only rule.
Full write-up: https://www.guardlayer.io/blog/supabase-rls-own-row-policy-case-study
Disclosure: I build GuardLayer, which checks for this now. The query above does the same job for free.