In an MVP built on a backend-as-a-service database (Supabase and similar platforms), the browser talks to the database directly with a public key, and that is fine: every visitor can see the key, and it protects nothing by itself. What protects the data is Row Level Security — PostgreSQL policies that decide, row by row, whether the current user may read or change it. A table without RLS behind an exposed API is open to anyone with the public key, while the service key bypasses RLS and must stay on the server.
After this you will be able to
- explain why a public key in the browser is normal, and why a table without RLS behind it is open data
- reproduce the leak on a PostgreSQL 17 + PostgREST stand: read and change other users' rows with the public key
- turn on
ENABLE ROW LEVEL SECURITYand writeUSINGandWITH CHECKpolicies for reading, inserting, updating and deleting your own rows - verify access with requests as two users and an anonymous visitor, and find tables without RLS with a single query
Know beforehand
Verified with
PostgreSQL 17 (17.11, postgres:17-alpine)PostgREST 16.4Node.js 24 LTS (24.18)curl 8.21
Checked against docs: October 12, 2026
Contents
- How data leaks in an MVP built on a database service
- The stand: PostgreSQL 17 and PostgREST in Docker
- Reproducing the leak: the public key reads and changes other users' rows
- Enabling RLS and writing policies
- Verifying as two users and an anonymous visitor
- The service key stays on the server
- Traps: UPDATE without SELECT, views, user metadata
- Practice
How data leaks in an MVP built on a database service
Platforms such as Supabase generate an API straight from the database schema: Supabase states that its REST API runs on PostgREST, a thin layer on top of PostgreSQL. The frontend calls that API itself, without a backend of its own, and sends a public key with every request. The key sits in the JavaScript bundle and shows up in the browser's Network tab — by design: Supabase's documentation explicitly places the public (publishable, formerly anon) key in code that ships to users.
So what protects the data? Not the secrecy of the key, but the role it grants and the Row Level Security (RLS) policies in the database. Without sign-in a request runs as the anon role, after sign-in as authenticated, and the user's identifier comes from a signed token (JWT). If a table has no RLS and the role has privileges on it, any visitor with the public key can read and change every row.
- 01Browser with the public key
- 02Data API (PostgREST)
- 03Role anon or authenticated
- 04RLS policies
- 05Table
How does a table end up without RLS? Usually in a mundane way. An agent or a developer creates it with SQL: according to Supabase's documentation, tables created in the dashboard have RLS enabled by default, while tables created in the SQL editor or with other tools need it enabled explicitly. Meanwhile, tables in the public schema get SELECT, INSERT, UPDATE and DELETE privileges for the anon and authenticated roles by default (Supabase has announced a move to opt-in grants, but at the time of checking this is the behavior it describes). It is a common gap when building fast, not a sign of bad code: the agent did exactly what it was asked — create a table.
The stand: PostgreSQL 17 and PostgREST in Docker
To keep a real project out of it, we build a local stand with the same design: PostgreSQL 17 and PostgREST, the same Data API engine. The roles are named as in Supabase, but this is a simplified emulation, not the platform itself: the auth schema, key issuing and user sign-in are more involved there.
-- Local stand that mimics a "database as a service" project (Supabase-style roles).
-- Not Supabase itself: a simplified emulation for the lesson.
-- API roles: the client key maps to anon / authenticated, the server key to service_role
create role anon nologin;
create role authenticated nologin;
create role service_role nologin bypassrls;
-- PostgREST connects as authenticator and switches to the role from the JWT
create role authenticator login password 'local-test-only' noinherit;
grant anon, authenticated, service_role to authenticator;
grant usage on schema public to anon, authenticated, service_role;
-- Default grants: every new table in public is reachable by the API roles
alter default privileges in schema public
grant select, insert, update, delete on tables to anon, authenticated, service_role;
-- auth.uid(): the user id from the JWT claims of the current request
create schema auth;
grant usage on schema auth to anon, authenticated, service_role;
create function auth.uid() returns uuid
language sql stable
as $$
select nullif(current_setting('request.jwt.claims', true)::jsonb ->> 'sub', '')::uuid
$$;PostgREST connects as the authenticator role, verifies the JWT signature and runs SET LOCAL ROLE with the token's role for every request; with no token it uses the role from the db-anon-role setting. The token claims are available in SQL through current_setting('request.jwt.claims', true) — that is what auth.uid() is built on. The table is what an agent would create for the task “store customer bookings”:
-- The table as it was created from the SQL editor: no RLS
create table public.bookings (
id bigint generated always as identity primary key,
user_id uuid not null default auth.uid(),
client_name text not null,
phone text not null,
service text not null,
starts_at timestamptz not null
);
-- Demo data: fictitious users and placeholder phone numbers
insert into public.bookings (user_id, client_name, phone, service, starts_at) values
('11111111-1111-4111-8111-111111111111', 'Alice', '+7 700 000 0001', 'haircut', '2026-10-20 10:00+05'),
('11111111-1111-4111-8111-111111111111', 'Alice', '+7 700 000 0001', 'manicure', '2026-10-21 12:00+05'),
('22222222-2222-4222-8222-222222222222', 'Bob', '+7 700 000 0002', 'massage', '2026-10-20 15:00+05');We sign tokens for the stand ourselves with the same secret PostgREST knows. The anon token plays the public key from the bundle, service plays the service key.
// Signs a test JWT (HS256) for the local stand: node make-jwt.mjs <anon|alice|bob|service>
// The secret is a local placeholder; real projects never keep it in code.
import { createHmac } from "node:crypto";
const SECRET = process.env.JWT_SECRET ?? "local-test-secret-not-for-production-0123456789";
const USERS = {
anon: { role: "anon" },
alice: { role: "authenticated", sub: "11111111-1111-4111-8111-111111111111" },
bob: { role: "authenticated", sub: "22222222-2222-4222-8222-222222222222" },
service: { role: "service_role" },
};
const claims = USERS[process.argv[2]];
if (!claims) throw new Error("usage: node make-jwt.mjs anon|alice|bob|service");
const b64url = (data) => Buffer.from(JSON.stringify(data)).toString("base64url");
const body = `${b64url({ alg: "HS256", typ: "JWT" })}.${b64url({ ...claims, exp: 2000000000 })}`;
const signature = createHmac("sha256", SECRET).update(body).digest("base64url");
console.log(`${body}.${signature}`);NET=kb-g-rls-net PG=kb-g-rls-pg API=kb-g-rls-api
JWT_SECRET="local-test-secret-not-for-production-0123456789"
docker network create $NET >/dev/null
docker run -d --rm --name $PG --network $NET -e POSTGRES_PASSWORD=test postgres:17-alpine >/dev/null
until docker exec $PG pg_isready -U postgres -q 2>/dev/null; do sleep 1; done; sleep 2
psql() { docker exec -i $PG psql -U postgres -X -q -v ON_ERROR_STOP=1 "$@"; }
psql -f - < sql/01-platform.sql && psql -f - < sql/02-app.sql
docker run -d --rm --name $API --network $NET -p 3901:3000 \
-e PGRST_DB_URI="postgres://authenticator:local-test-only@$PG:5432/postgres" \
-e PGRST_DB_SCHEMAS=public -e PGRST_DB_ANON_ROLE=anon -e PGRST_JWT_SECRET="$JWT_SECRET" \
postgrest/postgrest:v16.4 >/dev/nullReproducing the leak: the public key reads and changes other users' rows
Take the public key — the one any visitor finds in the bundle — and ask the API for every booking. Then change someone else's booking.
$ API=http://localhost:3901
$ ANON_KEY=$(node make-jwt.mjs anon)
$ curl -s "$API/bookings?select=id,client_name,phone,service" -H "Authorization: Bearer $ANON_KEY"; echo
[{"id":1,"client_name":"Alice","phone":"+7 700 000 0001","service":"haircut"},
{"id":2,"client_name":"Alice","phone":"+7 700 000 0001","service":"manicure"},
{"id":3,"client_name":"Bob","phone":"+7 700 000 0002","service":"massage"}]
$ curl -s -X PATCH "$API/bookings?id=eq.3" -H "Authorization: Bearer $ANON_KEY" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"service":"cancelled"}'; echo
[{"id":3,"user_id":"22222222-2222-4222-8222-222222222222","client_name":"Bob","phone":"+7 700 000 0002","service":"cancelled","starts_at":"2026-10-20T10:00:00+00:00"}]An anonymous visitor got the names and phone numbers of all customers and cancelled Bob's booking. No sign-in was needed: the public key and the API address were enough, and every visitor's browser knows both. The same gap is easy to find in your own database with one query to the system catalog:
-- Tables in the public schema without RLS
select c.relname as table_without_rls
from pg_class c
where c.relnamespace = 'public'::regnamespace
and c.relkind in ('r', 'p')
and not c.relrowsecurity;$ docker exec -i kb-g-rls-pg psql -U postgres -X -q -f - < sql/05-no-rls.sql
table_without_rls
-------------------
bookings
(1 row)Enabling RLS and writing policies
The rule for bookings is simple: users see and change only their own rows — those where user_id matches the identifier in their token.
-- 1. Turn on RLS: from now on, no policy = no rows
alter table public.bookings enable row level security;
-- 2. One policy per operation, always with an explicit role
create policy "bookings_select_own" on public.bookings
for select to authenticated
using ((select auth.uid()) = user_id);
create policy "bookings_insert_own" on public.bookings
for insert to authenticated
with check ((select auth.uid()) = user_id);
create policy "bookings_update_own" on public.bookings
for update to authenticated
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);
create policy "bookings_delete_own" on public.bookings
for delete to authenticated
using ((select auth.uid()) = user_id);SELECT
- Clause
USING- What the database does
- Drops rows for which the condition is false; no error
INSERT
- Clause
WITH CHECK- What the database does
- Checks the new row; if the condition is false, it raises an error and aborts the whole command
UPDATE
- Clause
USINGandWITH CHECK- What the database does
USINGpicks which rows may be changed,WITH CHECKwhat a row may look like afterwards
DELETE
- Clause
USING- What the database does
- Deletes only rows that pass the condition; silently skips others
- After
ENABLE ROW LEVEL SECURITY, deny-by-default applies: according to the PostgreSQL docs, with no policy no rows are visible or can be modified. - Every policy names its role (
to authenticated). Withoutto, a policy applies toPUBLICby default — anonymous visitors included; Supabase advises always naming the role. (select auth.uid())instead ofauth.uid()is a tip from Supabase's documentation: wrapping the call inselectlets the planner evaluate it once per query instead of once per row.- Several permissive policies for the same operation are combined with
OR: one overly broad policy reopens everything the others closed.
Verifying as two users and an anonymous visitor
Policies that have not been checked with real queries are an intention, not protection. We repeat the requests through the API as an anonymous visitor, Alice and Bob.
$ API=http://localhost:3901
$ ANON_KEY=$(node make-jwt.mjs anon); ALICE_KEY=$(node make-jwt.mjs alice); BOB_KEY=$(node make-jwt.mjs bob)
$ curl -s "$API/bookings?select=id,client_name" -H "Authorization: Bearer $ANON_KEY"; echo
[]
$ curl -s "$API/bookings?select=id,client_name" -H "Authorization: Bearer $ALICE_KEY"; echo
[{"id":1,"client_name":"Alice"},
{"id":2,"client_name":"Alice"}]
$ curl -s "$API/bookings?select=id,client_name" -H "Authorization: Bearer $BOB_KEY"; echo
[{"id":3,"client_name":"Bob"}]
$ curl -s -X PATCH "$API/bookings?id=eq.3" -H "Authorization: Bearer $ALICE_KEY" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"service":"cancelled"}'; echo
[]
$ curl -s -w ' %{http_code}\n' -X POST "$API/bookings" -H "Authorization: Bearer $ALICE_KEY" -H "Content-Type: application/json" -d '{"user_id":"22222222-2222-4222-8222-222222222222","client_name":"Bob","phone":"+7 700 000 0002","service":"fake","starts_at":"2026-10-22T10:00:00+05:00"}'
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"bookings\""} 403
$ curl -s -w ' %{http_code}\n' -X POST "$API/bookings" -H "Authorization: Bearer $ALICE_KEY" -H "Content-Type: application/json" -H "Prefer: return=representation" -d '{"client_name":"Alice","phone":"+7 700 000 0001","service":"pedicure","starts_at":"2026-10-22T10:00:00+05:00"}'
[{"id":5,"user_id":"11111111-1111-4111-8111-111111111111","client_name":"Alice","phone":"+7 700 000 0001","service":"pedicure","starts_at":"2026-10-22T05:00:00+00:00"}] 201The anonymous visitor gets an empty list
RLS is on and there are no policies for
anon, so deny-by-default applies. The response is[], not an error: the database simply shows no rows.Alice and Bob see only their own bookings
The same request returns different rows: the
USINGcondition uses the identifier from the token.Someone else's row does not change
Alice's
PATCHof Bob's booking returned[]: the row did not passUSINGand was silently left out of the update.The owner cannot be forged
An insert with someone else's
user_idgot error42501and HTTP 403 —WITH CHECKkicked in. With nouser_idin the request, the defaultauth.uid()filled it in and the row was created.
The same checks are convenient to repeat inside the database — for example, in migration tests. We do what PostgREST does for every request: switch the role and put the claims into the transaction settings.
$ docker exec -i kb-g-rls-pg psql -U postgres -X -a -f - < sql/10-as-users.sql
-- What PostgREST does for every request: switch role and put JWT claims into the transaction
\set alice '{"role":"authenticated","sub":"11111111-1111-4111-8111-111111111111"}'
\set bob '{"role":"authenticated","sub":"22222222-2222-4222-8222-222222222222"}'
-- Bob: sees only his own booking
begin;
BEGIN
set local role authenticated;
SET
select set_config('request.jwt.claims', :'bob', true) is not null as claims_set;
claims_set
------------
t
(1 row)
select auth.uid() as uid, current_user;
uid | current_user
--------------------------------------+---------------
22222222-2222-4222-8222-222222222222 | authenticated
(1 row)
select id, client_name, service from public.bookings order by id;
id | client_name | service
----+-------------+---------
3 | Bob | massage
(1 row)
rollback;
ROLLBACK
-- Anonymous visitor: RLS is on, no policy for anon, so no rows
begin;
BEGIN
set local role anon;
SET
select count(*) as visible_to_anon from public.bookings;
visible_to_anon
-----------------
0
(1 row)
rollback;
ROLLBACK
-- Alice: someone else's row is silently skipped, someone else's user_id is an error
begin;
BEGIN
set local role authenticated;
SET
select set_config('request.jwt.claims', :'alice', true) is not null as claims_set;
claims_set
------------
t
(1 row)
select id, client_name, service from public.bookings order by id;
id | client_name | service
----+-------------+----------
1 | Alice | haircut
2 | Alice | manicure
5 | Alice | pedicure
(3 rows)
update public.bookings set service = 'cancelled' where id = 3;
UPDATE 0
insert into public.bookings (user_id, client_name, phone, service, starts_at)
values ('22222222-2222-4222-8222-222222222222', 'Bob', '+7 700 000 0002', 'fake', now());
psql:<stdin>:26: ERROR: new row violates row-level security policy for table "bookings"
rollback;
ROLLBACKThe service key stays on the server
The service role has the BYPASSRLS attribute: according to the PostgreSQL docs, such roles and superusers always bypass policies. The same request with the service key returns everything:
$ API=http://localhost:3901
$ SERVICE_KEY=$(node make-jwt.mjs service)
$ curl -s "$API/bookings?select=id,client_name" -H "Authorization: Bearer $SERVICE_KEY"; echo
[{"id":1,"client_name":"Alice"},
{"id":2,"client_name":"Alice"},
{"id":3,"client_name":"Bob"},
{"id":5,"client_name":"Alice"}]Supabase puts the rule unambiguously: never use a secret key in the browser or expose it to customers; it is for server-side code — servers, functions, scheduled jobs. If this key ends up in a bundle, RLS no longer protects anything: revoke the key and issue a new one.
- The service key is read only in server code and has no public prefix such as
NEXT_PUBLIC_orVITE_ - Client components, the mobile app and logs contain neither the service key nor the database connection string
- Server code that uses the service key checks the user's permissions itself — RLS will not do it for that code
- A key that ever reached a repository or a bundle has been revoked and replaced
How to store keys and what to do after a leak is covered in detail in the lesson “Secrets and environment variables”.
Traps: UPDATE without SELECT, views, user metadata
We check two traps on the stand; changes are rolled back at the end of each transaction.
$ docker exec -i kb-g-rls-pg psql -U postgres -X -a -f - < sql/20-traps.sql
\set alice '{"role":"authenticated","sub":"11111111-1111-4111-8111-111111111111"}'
-- Trap 1: an UPDATE policy without a SELECT policy updates nothing
begin;
BEGIN
drop policy "bookings_select_own" on public.bookings;
DROP POLICY
set local role authenticated;
SET
select set_config('request.jwt.claims', :'alice', true) is not null as claims_set;
claims_set
------------
t
(1 row)
update public.bookings set service = 'pedicure' where id = 1;
UPDATE 0
rollback;
ROLLBACK
-- Trap 2: a view created by the owner ignores the table's RLS
begin;
BEGIN
create view public.bookings_calendar as select starts_at, client_name, phone from public.bookings;
CREATE VIEW
set local role anon;
SET
select count(*) as rows_via_view from public.bookings_calendar;
rows_via_view
---------------
4
(1 row)
reset role;
RESET
alter view public.bookings_calendar set (security_invoker = true);
ALTER VIEW
set local role anon;
SET
select count(*) as rows_via_invoker_view from public.bookings_calendar;
rows_via_invoker_view
-----------------------
0
(1 row)
rollback;
ROLLBACK- UPDATE without a SELECT policy changes nothing: the command needs to read rows for its
WHEREclause and is not allowed to. The PostgreSQL docs require both privileges and policies forSELECTfor such anUPDATE; Supabase warns that without a SELECT policy the update “will not work as expected”. - A view created by the table owner checks permissions as the owner by default, so RLS does not apply: the anonymous visitor saw all four bookings. With
security_invoker = true(PostgreSQL 15 and later) the view follows the policies of whoever runs the query. Default privileges cover views too, so a new view is reachable through the API right away. - User metadata that users can change themselves (in Supabase,
raw_user_meta_data, updated viasupabase.auth.update()) must not be used in policies as a role marker: users would grant themselves anything. - The table owner and roles with
BYPASSRLSbypass policies unlessFORCE ROW LEVEL SECURITYis on. These cases and tenant isolation are covered in the lesson on multi-tenant SaaS.
Supabase's documentation describes a second layer: revoke the default privileges of the API roles so that a new table or function is not reachable through the API until you grant access explicitly. RLS stays mandatory on every table and view the API can reach.
Practice
How this was verified6 · passedchecked 12 October 2026
“Run” means the code was executed in a scratch project or container and the output in the article comes from that run. “Partial” means it was checked against stubs instead of external services. “Not run” means it needs a device, an account or a key; the steps were checked against the official documentation. The verification project files and run logs are kept in the InterLAB archive.