Row-Level Security (RLS)

RLS is how you decide which rows each user can see and change. It's a Postgres feature: you turn it on for a table and write policies in SQL. Every query — from any client, with any key — is filtered by those policies inside the database. It is the real security boundary for a Postgres project.

Why it matters

Your publishable (anon) key ships in your app, so anyone can send queries to your project. RLS is what stops them reading or writing rows they shouldn't. The key identifies the project; policies decide access. A table with RLS turned off — or turned on with no matching policy — behaves as described below, so get this right before you ship.

The three roles

Every request runs as one of three Postgres roles, chosen by the key/JWT it carries. PostgREST logs in as your project's authenticator role and then SET ROLE to one of:

service_role is BYPASSRLS— it ignores every policy. That's the admin key's superpower, and exactly why you never ship it to a client. Use it only server-side (the JSR SDKs), never in @ichibase/client.

Enabling RLS & writing policies

Policies are SQL. Write them in the dashboard's SQL editor (or the RLS editor). A policy has a name, the command(s) it applies to (SELECT / INSERT / UPDATE / DELETE / ALL), the role(s) it's TO, and a boolean expression: USING (which existing rows are visible/affected) and WITH CHECK (what new/updated rows are allowed to look like).

-- 1. Turn RLS on (now default-deny until policies exist)
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- 2. Anyone may read published posts
CREATE POLICY "read published"
  ON public.posts FOR SELECT
  USING (published = true);

-- 3. A logged-in user may insert posts they own
CREATE POLICY "insert own"
  ON public.posts FOR INSERT TO authenticated
  WITH CHECK (author_id = auth.uid());

-- 4. ...and update/delete only their own
CREATE POLICY "modify own"
  ON public.posts FOR UPDATE TO authenticated
  USING (author_id = auth.uid())
  WITH CHECK (author_id = auth.uid());

Identifying the user: claims helpers

Inside a policy you read the current user from their JWT claims:

• auth.uid() — the user's id (the JWT sub claim), or null when anonymous.
• auth.role() — anon, authenticated, or service_role.
• auth.jwt() — the full claims object, e.g. auth.jwt() ->> 'email' or a custom claim like auth.jwt() -> 'org_id'.

Using it from the client

RLS sees the logged-in user automatically — after auth.login()the SDK sends the user's access token, so auth.uid() is populated. No special call needed.

// Anonymous: only rows the anon policies allow (e.g. published posts)
const { data: pub } = await ichi.from('posts').select('*');

// After login: 'authenticated' role; auth.uid() === this user
await ichi.auth.login({ email, password });
await ichi.from('posts').insert({ title: 'mine', author_id: /* set by policy/trigger */ });
const { data: mine } = await ichi.from('posts').select('*'); // their rows + published

Common patterns

-- Own rows only (read + write)
CREATE POLICY "own" ON public.notes FOR ALL TO authenticated
  USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

-- Public read, authenticated write
CREATE POLICY "read all" ON public.articles FOR SELECT USING (true);
CREATE POLICY "write auth" ON public.articles FOR INSERT TO authenticated
  WITH CHECK (author_id = auth.uid());

-- Per-organization (custom claim org_id on the JWT)
CREATE POLICY "same org" ON public.projects FOR ALL TO authenticated
  USING (org_id = (auth.jwt() ->> 'org_id'))
  WITH CHECK (org_id = (auth.jwt() ->> 'org_id'));

-- Admin override via a custom claim
CREATE POLICY "admins" ON public.everything FOR ALL TO authenticated
  USING ((auth.jwt() ->> 'is_admin')::boolean = true);