0Pricing
SQL Academy · Lesson

Row-Level Security Policies

Filter rows per user automatically.

Row-Level Security Policies is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is Row-Level Security?

Row-Level Security (RLS) is a PostgreSQL feature that lets you control which rows of a table a given database user or role can see or modify. Instead of filtering rows in every query, you define a policy once and PostgreSQL enforces it automatically on every SELECT, INSERT, UPDATE, and DELETE.

Think of it as an invisible WHERE clause that is attached to the table itself rather than to any specific query.

Enabling RLS on a Table

RLS is disabled by default. You must explicitly turn it on for each table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Once enabled, any role that is not the table owner will see zero rows until at least one policy is created.

-- Create a sample table
CREATE TABLE orders (
  id        SERIAL PRIMARY KEY,
  owner     TEXT NOT NULL,
  amount    NUMERIC(10,2)
);

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Creating Your First Policy

A policy is created with CREATE POLICY. You give it a name, specify the table, and provide a USING expression. The USING clause is a Boolean expression that is evaluated for each row — only rows where the expression returns TRUE are visible to the user.

-- Allow each user to see only their own orders
CREATE POLICY orders_owner_policy
  ON orders
  FOR SELECT
  USING (owner = current_user);

The USING vs WITH CHECK Clauses

Policies have two filter clauses that serve different purposes:

  • USING — filters rows on read operations (SELECT, UPDATE, DELETE). A row is visible only if USING returns TRUE.
  • WITH CHECK — validates rows on write operations (INSERT, UPDATE). A write is allowed only if WITH CHECK returns TRUE. If omitted, USING is reused for write checks.
-- Allow users to select and insert only their own rows
CREATE POLICY orders_isolation
  ON orders
  FOR ALL
  USING       (owner = current_user)
  WITH CHECK  (owner = current_user);

Policy Scope: FOR SELECT, INSERT, UPDATE, DELETE

A single policy can cover all commands (FOR ALL) or a specific one. Separating policies by command gives you fine-grained control — for example, letting every user read all rows but only modify their own.

-- Everyone can read all orders
CREATE POLICY read_all_orders
  ON orders
  FOR SELECT
  USING (true);

-- But each user can only update their own orders
CREATE POLICY update_own_orders
  ON orders
  FOR UPDATE
  USING       (owner = current_user)
  WITH CHECK  (owner = current_user);

Using session_user and current_user

PostgreSQL provides built-in functions to identify the active user inside a policy expression:

  • current_user — the role whose privileges are currently active (may change after SET ROLE).
  • session_user — the role that opened the connection (never changes during the session).

Most RLS policies rely on current_user because it reflects the effective role after role switching.

-- Inspect the current identity inside a query
SELECT current_user, session_user;

Applying RLS to Specific Roles

By default a policy applies to PUBLIC (all roles). You can restrict it to a specific role using the TO clause. This is useful when you want one policy for regular users and a different one for an admin role.

-- Policy only for the 'app_user' role
CREATE POLICY app_user_policy
  ON orders
  FOR SELECT
  TO app_user
  USING (owner = current_user);

-- Separate permissive policy for 'admin' role
CREATE POLICY admin_full_access
  ON orders
  FOR ALL
  TO admin
  USING (true)
  WITH CHECK (true);

Permissive vs Restrictive Policies

Multiple policies on the same table can interact in two ways:

  • PERMISSIVE (default) — all permissive policies are OR-ed together. A row is accessible if any permissive policy allows it.
  • RESTRICTIVE — restrictive policies are AND-ed with the permissive result. A row is accessible only if it passes the restrictive policy and at least one permissive policy.
-- Restrictive policy: block access to archived orders for everyone
CREATE POLICY no_archived_rows
  ON orders
  AS RESTRICTIVE
  FOR SELECT
  USING (amount > 0);

Bypassing RLS: BYPASSRLS and Table Owners

The table owner and superusers bypass RLS by default and always see all rows. You can grant the BYPASSRLS attribute to a role if it needs unrestricted access without being a superuser. Conversely, you can force the owner to obey RLS using FORCE ROW LEVEL SECURITY.

-- Force the table owner to also obey RLS policies
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Grant BYPASSRLS to a trusted service account
ALTER ROLE service_account BYPASSRLS;

Modifying and Dropping Policies

You can update an existing policy with ALTER POLICY or remove it entirely with DROP POLICY. Dropping all policies while RLS is still enabled means no rows are accessible to non-owner roles. To completely remove RLS, disable it with ALTER TABLE.

-- Rename a policy
ALTER POLICY orders_owner_policy ON orders
  RENAME TO user_isolation_policy;

-- Update the USING expression
ALTER POLICY user_isolation_policy ON orders
  USING (owner = current_user AND amount >= 0);

-- Remove a policy
DROP POLICY admin_full_access ON orders;

-- Disable RLS entirely on the table
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

Real-World Pattern: Multi-Tenant Data Isolation

A common RLS pattern in multi-tenant SaaS apps stores a tenant_id column on every table and uses a session-level variable (set_config) to pass the tenant identifier at connection time. The policy then compares each row's tenant_id against that setting.

-- Table with tenant isolation column
CREATE TABLE documents (
  id         SERIAL PRIMARY KEY,
  tenant_id  TEXT NOT NULL,
  title      TEXT
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy reads the tenant from a session variable
CREATE POLICY tenant_isolation
  ON documents
  FOR ALL
  USING       (tenant_id = current_setting('app.tenant_id'))
  WITH CHECK  (tenant_id = current_setting('app.tenant_id'));

-- Application sets the variable before running queries
SELECT set_config('app.tenant_id', 'tenant_42', true);

-- Now only tenant_42 documents are visible
SELECT * FROM documents;

Knowledge Check

Test your understanding of Row-Level Security policies in PostgreSQL.

Lesson Recap

In this lesson you learned how Row-Level Security gives you automatic, policy-driven row filtering directly at the database level:

  • Enable RLS on a table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
  • Use CREATE POLICY with a USING clause to filter readable rows and a WITH CHECK clause to validate written rows.
  • Scope policies to specific commands (SELECT, INSERT, UPDATE, DELETE, ALL) and specific roles using the TO clause.
  • Combine PERMISSIVE (OR logic) and RESTRICTIVE (AND logic) policies for layered access control.
  • Table owners and superusers bypass RLS by default; use FORCE ROW LEVEL SECURITY to override this.
  • The multi-tenant pattern using current_setting() is a powerful real-world application of RLS.

RLS is the standard way to enforce data isolation cleanly and consistently without scattering WHERE clauses across every application query.

Frequently asked questions

Is the “Row-Level Security Policies” lesson free?

Yes — the full text of “Row-Level Security Policies” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Row-Level Security Policies”?

Filter rows per user automatically. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Row-Level Security Policies” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Roles and Privileges
  2. Row-Level Security Policies
  3. Column-Level Permissions
  4. Auditing Access
← Back to SQL Academy