Доступ к данным в AI-собранном MVP: роли, ключи на клиенте и RLS

-- 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
$$;

-- 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');

// 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/null

$ 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"}]

-- 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)

-- 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);

$ 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"}] 201

$ 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;
ROLLBACK

$ 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"}]

$ 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