Row-Level Security

Row-Level Security (RLS) restricts which rows a given role can see or modify. When enabled on a table, every query is filtered through one or more policies that define access rules — no application-layer filtering required.

RLS is the foundation of the Browser SDK data access model: the public data API issues queries under a role resolved from the user’s JWT, and RLS policies enforce per-user visibility.

Enable RLS

SQL

-- Enable RLS (default-deny: no policies = no rows visible to non-owners)
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

-- Disable RLS (all rows visible to all roles)
ALTER TABLE todos DISABLE ROW LEVEL SECURITY;

Once enabled, non-superuser and non-owner roles see zero rows until you create at least one policy.

Force RLS on Table Owners

By default, the table owner bypasses RLS. Use FORCE to apply policies to the owner as well:

SQL

ALTER TABLE todos FORCE ROW LEVEL SECURITY;

-- Remove force
ALTER TABLE todos NO FORCE ROW LEVEL SECURITY;

Note: Superusers always bypass RLS, even with FORCE.

Create Policies

A policy defines a boolean expression that filters rows for a specific operation. If the expression evaluates to true, the row is accessible.

SQL

CREATE POLICY policy_name ON table_name
  [AS { PERMISSIVE | RESTRICTIVE }]
  [FOR { SELECT | INSERT | UPDATE | DELETE }]
  [TO role_name [, ...]]
  [USING (expression)]
  [WITH CHECK (expression)];

USING vs WITH CHECK

Clause	Purpose	Applies to
USING	Filter which existing rows are visible/modifiable	SELECT, UPDATE, DELETE
WITH CHECK	Validate new or modified row values	INSERT, UPDATE

Per-Operation Rules

Operation	USING	WITH CHECK	Behavior
SELECT	Required	Not allowed	Filters visible rows.
INSERT	Not allowed	Required	Validates inserted values.
UPDATE	Required	Optional	USING selects updatable rows; WITH CHECK validates new values. Defaults WITH CHECK to USING if omitted.
DELETE	Required	Not allowed	Filters deletable rows.

Examples

Row ownership — users see only their own data:

SQL

CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  user_id TEXT NOT NULL,
  task TEXT,
  done BOOLEAN DEFAULT false
);

ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

-- Users can only see their own todos
CREATE POLICY user_select ON todos
  FOR SELECT
  USING (user_id = current_user);

-- Users can only insert todos for themselves
CREATE POLICY user_insert ON todos
  FOR INSERT
  WITH CHECK (user_id = current_user);

-- Users can only update their own todos
CREATE POLICY user_update ON todos
  FOR UPDATE
  USING (user_id = current_user)
  WITH CHECK (user_id = current_user);

-- Users can only delete their own todos
CREATE POLICY user_delete ON todos
  FOR DELETE
  USING (user_id = current_user);

Public + private visibility:

SQL

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author TEXT,
  published BOOLEAN DEFAULT false,
  content TEXT
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Anyone can see published posts
CREATE POLICY see_published ON posts
  FOR SELECT
  USING (published = true);

-- Authors can also see their own unpublished drafts
CREATE POLICY see_own ON posts
  FOR SELECT
  USING (author = current_user);

Policy Combination Rules

Permissive Policies (Default)

Multiple PERMISSIVE policies for the same operation are combined with OR — if any policy allows the row, it is accessible.

SQL

-- Row is visible if published=true OR author=current_user
CREATE POLICY see_published ON posts FOR SELECT USING (published = true);
CREATE POLICY see_own ON posts FOR SELECT USING (author = current_user);

Restrictive Policies

RESTRICTIVE policies are combined with AND on top of the permissive result. Every restrictive policy must pass.

SQL

-- Must be published AND visible by permissive policies
CREATE POLICY must_be_published ON posts
  AS RESTRICTIVE
  FOR SELECT
  USING (published = true);

Final evaluation:

(permissive_1 OR permissive_2 OR ...) AND restrictive_1 AND restrictive_2 AND ...

At least one PERMISSIVE policy must match for any row to be accessible, regardless of restrictive policies.

Alter and Drop Policies

SQL

-- Change the USING expression
ALTER POLICY see_own ON posts USING (author = current_user OR role = 'admin');

-- Drop a policy
DROP POLICY see_own ON posts;

-- Drop only if it exists (no error)
DROP POLICY IF EXISTS see_own ON posts;

Bypass Mechanisms

Mechanism	Bypasses RLS?	Bypasses FORCE RLS?
Superuser	Always	Always
Table owner	Yes	No (must obey policies)
BYPASSRLS role attribute	Yes	Yes

BYPASSRLS Role Attribute

Grant a role the ability to bypass all RLS policies:

SQL

CREATE ROLE admin_role LOGIN PASSWORD 'pw' BYPASSRLS;

-- Grant or revoke later
ALTER ROLE admin_role BYPASSRLS;
ALTER ROLE admin_role NOBYPASSRLS;

SECURITY DEFINER Functions

Functions declared SECURITY DEFINER execute with the identity of the function owner (typically the table owner), bypassing RLS:

SQL

CREATE FUNCTION all_posts()
RETURNS SETOF posts
LANGUAGE SQL
SECURITY DEFINER
AS $$ SELECT * FROM posts $$;

Functions declared SECURITY INVOKER (the default) execute with the caller’s identity and obey RLS normally.

Security Barrier

RLS predicates are applied as security barrier qualifiers. This prevents user-supplied WHERE clauses from leaking data through side-channel functions:

SQL

-- Even if a malicious function is used in WHERE, it cannot see rows hidden by RLS
SELECT * FROM secrets WHERE side_channel_fn(secret_column);
-- RLS USING clause filters rows BEFORE the WHERE clause evaluates

System Catalog

Inspect RLS configuration through standard catalog views:

SQL

-- Check if RLS is enabled on a table
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = 'todos';

-- List all policies on a table
SELECT policyname, cmd, permissive, roles, qual, with_check
FROM pg_policies
WHERE tablename = 'todos';

-- Check role attributes
SELECT rolname, rolbypassrls
FROM pg_roles
WHERE rolname = 'app_user';

RLS with the Browser SDK

The Browser SDK uses RLS to enforce per-user data access. The flow:

1. Configure BYO JWT authentication on your database.
2. Enable RLS and create policies that reference current_user or a custom claim.
3. The browser SDK attaches the user’s JWT to each request.
4. The public data API validates the JWT and issues a connect token with the resolved role.
5. Queries execute under that role — RLS policies filter results automatically.

SQL

-- Example: todos visible only to the JWT subject
CREATE POLICY user_todos ON todos
  FOR SELECT
  USING (user_id = current_user);

TypeScript

// Client-side — SDK handles auth and RLS transparently
db9.auth.setSession({ accessToken: userJwt });
const { data } = await db9.from('todos').select('*');
// Only rows where user_id matches the JWT subject are returned

Related Docs

• Browser SDK — client-side data access with RLS
• Auth & Roles — role management and grants
• Security & Auth — publishable keys and BYO JWT
• Production Checklist — RLS setup guidance