Row-Level Security Policies

You've built a solid PostgreSQL schema, mastered joins, and learned to write efficient queries. But what happens when you need to enforce that a user can only see their own data — and nothing else? Hardcoding WHERE user_id = current_user in every query is a recipe for disaster; it's error-prone, easy to forget, and impossible to enforce at the database level. That's exactly the pain that row-level security (RLS) solves. In this lesson, you'll learn how to implement row-level security policies in PostgreSQL, turning your database into the single source of truth for data access control.

The problem this lesson solves

Imagine you're building a multi-tenant SaaS app where customers store invoices, messages, or personal notes. You've got a users table and an orders table, and every query needs to filter by the logged-in user. Without a centralized mechanism, you rely on every developer remembering to add WHERE user_id = $1. Forget it once, and one user can see another's data — a critical security breach.

You could also create a separate table per user, but that explodes your schema and makes cross-user analytics nearly impossible. The real solution is to enforce data isolation at the database level, so that no matter how a query is written — whether it's from an ORM, a raw SQL tool, or a misbehaving script — the database itself returns only the rows the user is allowed to see.

That's the problem this lesson solves: how to implement row-level security policies to guarantee that each database role only sees (or modifies) its own rows, without sprinkling WHERE clauses everywhere.

Core concept / mental model

Think of row-level security as a bouncer at the door of each table. Normally, anyone with access to a table can see every row — like a public library where every book is on the open shelf. RLS flips that: you install a bouncer who checks each row against a set of rules (called policies) before letting you see it.

Here's the mental model:

• Table: the room full of books (rows).
• Role: the person trying to enter (the database user).
• Policy: the bouncer's instruction list, which says "this role may see rows where user_id = current_user".
• Enable RLS: the act of locking the door so the bouncer's checks are mandatory.

Technically, RLS is implemented via CREATE POLICY statements that define SELECT, INSERT, UPDATE, or DELETE rules. When RLS is enabled on a table (using ALTER TABLE ... ENABLE ROW LEVEL SECURITY), every query against that table is automatically rewritten to include the policy's predicate — you don't have to change your SQL at all.

Key terms you'll see:

• FORCE ROW LEVEL SECURITY: Makes RLS apply even to the table owner (by default, the table owner bypasses RLS).
• PERMISSIVE vs RESTRICTIVE: Permissive policies are combined with OR; restrictive ones with AND. You'll mostly use permissive.
• USING: Defines the predicate for rows that are visible (for SELECT) or that can be modified (for UPDATE/DELETE).
• WITH CHECK: Defines the predicate for new rows inserted or updated (for INSERT/UPDATE).

Pro tip: Think of USING as "the rows I can see", and WITH CHECK as "the rows I can create". They can be different — for example, you can see all active orders but only insert orders with a status of 'pending'.

How it works step by step

Here's the logical sequence to implement row-level security policies:

1. Identify the table(s) that need RLS. These are usually tables containing sensitive data tied to a user or tenant.
2. Enable RLS on the table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Until you do, the table is open (unless you also FORCE it).
3. Create policies with CREATE POLICY that define the rules for each operation (SELECT, INSERT, UPDATE, DELETE). You can use a single policy that applies to all commands, or separate ones for fine-grained control.
4. Test with a non-superuser role. RLS doesn't apply to superusers or table owners by default, so you must test with a role that doesn't bypass RLS.
5. Force RLS if needed to also restrict the table owner, which is often a good idea for production.

A typical policy template looks like this:

CREATE POLICY policy_name
  ON table_name
  FOR SELECT
  USING (user_id = current_setting('app.current_user')::int);

Note: you can reference current_user (the role name) or a session variable like app.current_user that your application sets after authentication. The latter is more flexible because it lets you map an authenticated web user to a numeric user_id.

Hands-on walkthrough

Let's implement row-level security policies on a realistic orders table. We'll create a table, enable RLS, and create policies to restrict access per user.

1. Set up the table

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product TEXT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (user_id, product, amount, status) VALUES
    (1, 'Laptop', 1200.00, 'shipped'),
    (1, 'Mouse', 25.50, 'pending'),
    (2, 'Keyboard', 75.00, 'delivered');

2. Enable RLS

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Now, without any policies, the table is effectively empty for everyone except superusers and the owner. Let's verify:

SELECT * FROM orders;

You'll get zero rows. The bouncer is there, but he has no instructions — so he lets nobody in.

3. Create policies

We'll create permissives for SELECT, INSERT, UPDATE, and DELETE.

-- Allow users to see their own orders
CREATE POLICY select_own_orders
  ON orders
  FOR SELECT
  USING (user_id = current_setting('app.current_user')::int);

-- Allow users to insert orders for themselves
CREATE POLICY insert_own_orders
  ON orders
  FOR INSERT
  WITH CHECK (user_id = current_setting('app.current_user')::int);

-- Allow users to update only their pending orders
CREATE POLICY update_own_pending_orders
  ON orders
  FOR UPDATE
  USING (user_id = current_setting('app.current_user')::int AND status = 'pending')
  WITH CHECK (user_id = current_setting('app.current_user')::int);

-- Allow users to delete their own pending orders
CREATE POLICY delete_own_pending_orders
  ON orders
  FOR DELETE
  USING (user_id = current_setting('app.current_user')::int AND status = 'pending');

4. Test with a restricted role

-- Create an application role and a test user role
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

SET ROLE app_user;
SELECT set_config('app.current_user', '1', false);

-- Now query
SELECT * FROM orders;

Expected output:

 id | user_id | product | amount | status
----+---------+---------+--------+---------
  1 |       1 | Laptop  | 1200.00 | shipped
  2 |       1 | Mouse   | 25.50 | pending
(2 rows)

Only user 1's orders appear. User 2's row is hidden. Try inserting an order for user 2:

INSERT INTO orders (user_id, product, amount) VALUES (2, 'Desk', 300.00);

This fails with a new row violates row-level security policy error — the WITH CHECK blocks it. Perfect.

5. Force RLS (optional)

If you want to make sure the table owner can't bypass RLS (bad practice, but sometimes needed), use:

ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Now even the owner must obey the policies.

Troubleshooting tip: If you get zero rows after enabling RLS, double-check that you've created policies and that you're not the table owner (superusers bypass RLS). Also ensure app.current_user is set correctly in your session.

Compare options / when to choose what

There are several ways to enforce data isolation in PostgreSQL. Here's a comparison:

For most production multi-tenant applications, RLS is the winner. It's mandatory, unforgeable, and works transparently with ORMs like Django or Rails, as long as you set the session variable after authentication.

Variations to consider:

• RLS with a tenant_id column instead of user_id, if you need to scope by company rather than individual user.
• Using a JWT claim or a custom GUC (like app.current_user) that your application sets once per request.
• Using BY DEFAULT vs MANAGED policy (Postgres 15+) to separate policy creation from table creation.

Troubleshooting & edge cases

• Superuser bypasses RLS: By default, superusers and the table owner are not subject to policies. Always test with a restricted role, and use FORCE ROW LEVEL SECURITY if needed.
• Zero rows after enabling RLS: This means no policy grants access. Create at least one permissive SELECT policy.
• WITH CHECK fails on INSERT: Your INSERT must satisfy the WITH CHECK predicate. Ensure you're setting the correct user_id in the statement.
• Session variable not set: If you're using current_setting('app.current_user'), you must call set_config before the query. Otherwise it returns NULL, and the policy filters out everything.
• Policy conflicts: Multiple permissive policies are OR'ed together. If you need strict AND logic, use restrictive policies or combine predicates in one policy.
• Performance: RLS adds a predicate check to every query — index the columns used in your policy (e.g., user_id) to avoid full table scans.
• Migration complexity: Enabling RLS on an existing table with data is fine, but test thoroughly — existing application code might break if it expects to see all rows.

Common mistakes to avoid:

• Forgetting to ENABLE ROW LEVEL SECURITY after creating policies.
• Not testing with a non-owner role, leading to a false sense of security.
• Using user_id in USING but forgetting to set the session variable from the application.
• Mixing permissive and restrictive policies without understanding how they combine.

What you learned & what's next

You now know how to implement row-level security policies in PostgreSQL. You can:

• Explain the problem RLS solves: enforcing data isolation at the database level.
• Enable RLS on a table and create policies for SELECT, INSERT, UPDATE, and DELETE.
• Test with a restricted role to verify the policies work.
• Choose between RLS, separate tables, and application-level filtering.
• Troubleshoot common issues like zero rows and WITH CHECK failures.

Your next step in the PostgreSQL Tutorial track is to explore secure functions and audit triggers. You'll learn how to combine RLS with functions that run with definer privileges to create powerful, secure APIs, and how to log all sensitive operations automatically. Armed with RLS knowledge, you're well on your way to building robust, production-grade database security.