tutorial··5 min read·WoWSQL Team

What Is Row Level Security in Postgres? With Examples

What is row level security in Postgres? RLS explained with USING vs WITH CHECK, an auth.uid() example, and how WoWSQL applies each user's JWT.

Row Level Security (RLS) is a PostgreSQL feature that restricts which rows a role can read or write, using policies that run inside the database. On a Postgres backend as a service such as WoWSQL or Supabase, those policies usually compare a user_id column to the signed-in user, so one client key cannot fetch another person's rows.

This guide explains what RLS is, the difference between USING and WITH CHECK, a todos example, the mistakes that quietly turn policies into a hole, and how WoWSQL applies the user's JWT on the auto-generated REST API. The SQL below is standard Postgres. The auth.uid() helper is provided by Postgres BaaS platforms (it is not built into stock PostgreSQL). Official reference: PostgreSQL row security policies.

Key facts

  • What RLS does: after you run ALTER TABLE ... ENABLE ROW LEVEL SECURITY, the table owner and superuser still see all rows, but ordinary roles only see or change rows allowed by a policy. With RLS on and no policy, those roles see nothing.
  • USING vs WITH CHECK: USING filters existing rows for SELECT, UPDATE and DELETE. WITH CHECK validates new or updated row values for INSERT and UPDATE.
  • On WoWSQL: send the anon key (wowsql_anon_…) in the apikey header and the signed-in user's access token in Authorization: Bearer …. The service key (wowsql_service_…) bypasses RLS and belongs on a server only. The API is PostgREST at https://<project>.wowsqlconnect.com/rest/v1. There is no GraphQL API.
  • auth.uid(): on WoWSQL and Supabase this helper returns the user id from the JWT. In stock Postgres you would compare against a session variable or a function you define yourself.

What is row level security?

Without RLS, table privileges in Postgres are all-or-nothing at the table level: a role that can SELECT on todos can read every row. That is fine for an admin connection. It is not fine when a browser or mobile app talks to the database through a shared API role.

RLS adds a policy layer. Each policy is a boolean expression. If it returns true for a row, that command is allowed on that row. PostgreSQL documents this as row security policies that "restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted" (Postgres docs).

A typical app policy is: "a user may only read and write rows where user_id equals their auth id."

USING vs WITH CHECK

Clause Applies to What it means
USING (expression) SELECT, UPDATE, DELETE Only existing rows for which the expression is true are visible or eligible to change.
WITH CHECK (expression) INSERT, UPDATE The new row (or the row after the update) must satisfy the expression, or the statement fails.

A short way to remember it: USING is about rows that already exist; WITH CHECK is about the row you are trying to write.

For "users only see and write their own todos" you usually want both: USING (auth.uid() = user_id) on SELECT, UPDATE and DELETE, and WITH CHECK (auth.uid() = user_id) on INSERT and UPDATE so nobody can insert or reassign a row under someone else's id.

A todos example

Enable auth in the WoWSQL dashboard, then create a table and policies. This example uses auth.uid(), which reads the signed-in user from the request JWT on a WoWSQL or Supabase project. The ENABLE ROW LEVEL SECURITY and CREATE POLICY statements themselves are stock Postgres.

create table public.todos (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  title text not null,
  completed boolean not null default false,
  created_at timestamptz not null default now()
);

alter table public.todos enable row level security;

create policy "Users read own todos"
  on public.todos
  for select
  using (auth.uid() = user_id);

create policy "Users insert own todos"
  on public.todos
  for insert
  with check (auth.uid() = user_id);

create policy "Users update own todos"
  on public.todos
  for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create policy "Users delete own todos"
  on public.todos
  for delete
  using (auth.uid() = user_id);

In stock Postgres, without a BaaS helper, the same idea looks like this, using a session setting you would set on each request (PostgREST uses a similar pattern under the hood):

create policy "Users read own todos"
  on public.todos
  for select
  using (user_id = nullif(current_setting('request.jwt.claim.sub', true), '')::uuid);

Prefer auth.uid() on WoWSQL when the helper is available in your project; it is the readable form of that JWT claim.

How WoWSQL applies RLS

Authentication tells you who the user is. RLS decides which rows they can touch. The hosted API is PostgREST:

  1. Put the project anon key in apikey. This key is safe in client apps because it only allows what your policies allow.
  2. After login, put the user's access token in Authorization: Bearer <access_token>. That is what makes auth.uid() resolve to the signed-in user.
  3. Never put the service key in a browser bundle or a mobile app. It bypasses RLS.

The TypeScript SDK (@wowsql/sdk 3.10.0) signs users in with ProjectAuthClient. To run a table request as that user, the documented pattern is to call the REST API with both headers. This is also what the SDK connection guide describes:

import { ProjectAuthClient } from '@wowsql/sdk'

const auth = new ProjectAuthClient({
  projectUrl: 'myproject', // → https://myproject.wowsqlconnect.com
  apiKey: 'wowsql_anon_your_key_here',
})

const login = await auth.signIn({
  email: 'user@example.com',
  password: 'SecurePassword123',
})

const accessToken = login.session?.accessToken

const mine = await fetch('https://myproject.wowsqlconnect.com/rest/v1/todos?select=*', {
  headers: {
    apikey: 'wowsql_anon_your_key_here',
    Authorization: `Bearer ${accessToken}`,
  },
})

Take the user id from the verified token (for example auth.getUser()), never from a field the client can edit in the JSON body. For a full login walkthrough, see How to add login to your app without a backend. For adding auth around an existing Postgres app, see Add auth to a Postgres app. Multi-tenant patterns are covered in Multi-tenant SaaS on Postgres.

Common RLS mistakes

  • USING (true) on a sensitive table. That policy allows every row to every role the policy applies to. It is sometimes used deliberately for public read data. It is a bug if the table holds private user records.
  • Enabling RLS and forgetting a policy. With RLS on and no policy, anon and authenticated queries return empty results (or fail writes). That fails safe, but your app looks "broken" until you add the right policy.
  • Only filtering in the client. A where user_id = myId clause in application code is not security. Anyone can call the API without that filter. Put the rule in a policy.
  • Putting the service key in the client. The service key bypasses RLS. A leaked service key is equivalent to a database admin for your project data.
  • INSERT without WITH CHECK. A USING policy does not constrain the new row. Without WITH CHECK, a user may be able to insert a row with someone else's user_id.
  • Policies that use a client-supplied user id instead of auth.uid(). If the policy reads a value from the request body, the client can change it.

Firebase Security Rules play a similar role on Firestore, but they live in a rules language outside the database. RLS lives next to the data, in SQL, which is why Postgres BaaS platforms lean on it. A broader walkthrough of shipping an app this way is in How to launch an app without building a backend.

FAQ

What is row level security in Postgres?

It is a built-in PostgreSQL feature: you enable it on a table and add policies that decide, row by row, whether a role can select, insert, update or delete that row. It complements table-level GRANT privileges rather than replacing them.

How do I make users only see their own data?

Store the auth user id on each row and add RLS policies that compare it to auth.uid() (on a BaaS) or to a session variable you set (in stock Postgres). Send the user's access token with each API request so the policy can see who they are.

What is an RLS policy example with auth.uid()?

create policy "Users read own todos" on public.todos for select using (auth.uid() = user_id); plus a matching insert policy with with check (auth.uid() = user_id). Enable RLS on the table first, or the policies will not run for ordinary roles.

How is Supabase RLS different from Firebase Security Rules?

Supabase and WoWSQL express access rules as SQL policies inside Postgres. Firebase Security Rules are a separate rules language evaluated by Firebase for Firestore, Realtime Database and Storage. The goal is the same (restrict what each user can touch); the place the rule lives is different.

Does the WoWSQL service key respect RLS?

No. The service key (wowsql_service_…) bypasses RLS. Use it only in a trusted server environment. Client apps should use the anon key plus the signed-in user's JWT.

Create a free WoWSQL project · Pricing · Docs