Implement Row-Level Security

Imagine building a multi-tenant SaaS application where every query must only return rows belonging to the current customer. You could add WHERE tenant_id = $1 to every single query, but that's repetitive, error-prone, and every developer on your team has to remember it. Even worse, a single forgotten filter turns into a data breach. Row-level security (RLS) in PostgreSQL solves this by putting the restriction right into the database: rows that a role isn't allowed to see are invisible to that role, no matter how the query is written. This lesson teaches you how to implement row-level security — enable it on a table, create policies that define visibility, and test it with different roles to prove your data stays locked down.

The problem this lesson solves

In most applications, you control data access at the application layer — your ORM or query builder adds a WHERE clause that filters by the current user. But this approach has a fundamental flaw: it depends on every developer writing correct queries every time. If someone forgets the filter, runs a raw SQL query, or a new endpoint is added without the filter, that user can see every row in the table.

This is especially dangerous in multi-tenant architectures where different customers share the same database table. A bug like forgetting WHERE tenant_id = $1 can leak one customer's private data to another. RLS gives you a defense-in-depth layer: even if the application layer fails, the database itself enforces the policy. No matter what query is executed — whether it's a SELECT, UPDATE, or DELETE — the database silently filters out rows the requesting role is not authorized to see.

For example, a support agent might be allowed to see all invoices, while a customer sees only their own. Without RLS, you'd need to branch your code for every role and every query. With RLS, you define the policy once and every role automatically abides by it.

Core concept / mental model

Think of a PostgreSQL table as a building with a secure entrance. By default, the entrance is open — anyone with access to the table can see every row. RLS is like installing a smart door lock on that table. Once enabled, no one gets in unless a policy (the lock's rules) says so.

• Enabling RLS turns the lock on, but with no policies, the door is locked for everyone (except the table owner and superusers).
• Policies are the rules that define who can see or modify which rows. They're like keycards with specific permissions.
• Roles are the people or applications holding those keycards. Each query runs as a role, and the policies let the database decide row-by-row whether that role is allowed to access a particular row.

In the SQL world, a policy is essentially a WHERE clause that's automatically appended to every query on that table. When a user runs SELECT * FROM invoices, the database rewrites the query as SELECT * FROM invoices WHERE [policy condition]. The user never sees the filter, but the results are safe.

How it works step by step

Implementing RLS involves a clear sequence of steps. Here's the order you'll follow, and why that order matters.

1. Create or choose a role — RLS is role-based. You'll need at least one role that represents the 'user' or 'tenant' in your application.
2. Create a table (or use an existing one) that will hold the protected data.
3. Enable RLS on the table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY;. This is the critical switch — without it, policies have no effect.
4. Create policies with CREATE POLICY to define what each role can do on which rows.
5. Test with different roles — switch to a restricted role and run queries to confirm the policy is enforced.
6. (Optional) Force RLS for the table owner — by default the table owner bypasses RLS. Use FORCE ROW LEVEL SECURITY to close that loophole.

Pro tip: Always test as a non-owner role. Table owners and superusers bypass RLS by default, so if you test as the owner, you'll think RLS is broken when it's actually working perfectly.

The key is to create policies that are permissive or restrictive. Permissive policies are OR'd together — if any policy allows access, the row is visible. Restrictive policies are AND'd together — a row is visible only if all restrictive policies allow it. In practice, you'll mostly use permissive policies for simplicity.

Hands-on walkthrough

Let's build a real example. Imagine a sales database with a payments table that stores payments for different customers. We'll use a role called app_user that represents a generic application user, and we'll add a customer_id column to identify which customer owns each payment.

Step 1: Set up the environment

-- Create a role for the application
CREATE ROLE app_user;

-- Create a table to hold payments
CREATE TABLE payments (
    id serial PRIMARY KEY,
    customer_id integer NOT NULL,
    amount numeric(10,2) NOT NULL,
    payment_date date NOT NULL
);

-- Insert some sample rows
INSERT INTO payments (customer_id, amount, payment_date) VALUES
    (1, 100.00, '2025-01-10'),
    (1, 250.50, '2025-01-12'),
    (2, 75.00, '2025-01-11');

Step 2: Enable row-level security

ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

Now if you try to query the table as app_user, you'll see zero rows.

SET ROLE app_user;
SELECT * FROM payments;
-- (0 rows)

That's because no policies exist yet — the lock is on, but no one has a keycard.

Step 3: Create a policy

We need a policy that says: 'a user can only see payments where customer_id matches some criteria.' In a real multi-tenant app, you'd use a session variable or a function that returns the current tenant ID. For this example, we can use the current_user to make it simple.

Let's assume each app_user can only see rows with customer_id = 1. We'll create a policy for SELECT:

CREATE POLICY payments_select ON payments
    FOR SELECT
    TO app_user
    USING (customer_id = 1);

Now when app_user queries, they only see customer 1's payments:

SET ROLE app_user;
SELECT * FROM payments;
-- Should return:
--  id | customer_id | amount | payment_date
--  1  |      1      | 100.00 |   2025-01-10
--  2  |      1      | 250.50 |   2025-01-12

Step 4: Add a full set of policies

To make RLS practical, you need policies for all operations — SELECT, INSERT, UPDATE, and DELETE. For example:

-- Allow customers to view own payments
CREATE POLICY payments_select ON payments
    FOR SELECT TO app_user USING (customer_id = current_setting('app.customer_id', true)::int);

-- Allow customers to insert new payments for themselves
CREATE POLICY payments_insert ON payments
    FOR INSERT TO app_user WITH CHECK (customer_id = current_setting('app.customer_id', true)::int);

-- Allow customers to update their own payments
CREATE POLICY payments_update ON payments
    FOR UPDATE TO app_user USING (customer_id = current_setting('app.customer_id', true)::int)
    WITH CHECK (customer_id = current_setting('app.customer_id', true)::int);

-- Allow customers to delete their own payments
CREATE POLICY payments_delete ON payments
    FOR DELETE TO app_user USING (customer_id = current_setting('app.customer_id', true)::int);

The WITH CHECK clause for INSERT and UPDATE ensures that the new row still satisfies the policy — a user can't write a row that belongs to someone else.

Step 5: Test the full setup

Set the app.customer_id session variable and pretend you're a customer:

SET ROLE app_user;
SET app.customer_id = 2;

SELECT * FROM payments;
-- Rows with customer_id = 2 only

INSERT INTO payments (customer_id, amount, payment_date)
VALUES (2, 500.00, '2025-02-01');
-- Works! Customer 2 can insert their own row

INSERT INTO payments (customer_id, amount, payment_date)
VALUES (1, 999.00, '2025-02-02');
-- Fails! Violates WITH CHECK

Expected output for the failing insert:

ERROR:  new row violates row-level security policy

This is the power of RLS — the database enforces the rule even if the application tries to sneak in a bad insert.

Compare options / when to choose what

You might wonder: 'Why not just use views or add filters in the app?' Here's a comparison:

When to choose RLS: - Multi-tenant apps where data isolation is critical. - When you need a hard guarantee that the database won't leak rows. - When you have many different roles with different access levels.

When to avoid RLS: - Small internal apps with a single trusted role. - When performance is paramount and you need the fastest possible queries — RLS adds a small overhead. - When you're comfortable with tight application control and don't fear developer mistakes.

Variations

• Policy per role: You can create different policies for different roles (e.g., an admin role with no restrictions, a customer role with restrictions).
• Using session variables: Instead of hardcoding conditions, use current_setting('app.customer_id', true) to make policies dynamic.
• Using a security function: You can call a function inside the policy that returns the current user's tenant ID based on a JWT or API token.

Troubleshooting & edge cases

When RLS doesn't behave as expected, it's often because of these gotchas:

1. Table owner bypasses RLS. If you're testing as the table owner (or a superuser), RLS won't apply. This is by design. To force RLS on the owner, use FORCE ROW LEVEL SECURITY:

ALTER TABLE payments FORCE ROW LEVEL SECURITY;

2. Forgetting to enable RLS. If you create policies but never run ENABLE ROW LEVEL SECURITY, nothing is enforced. Double-check your migration scripts.

3. Using SET ROLE vs SET SESSION AUTHORIZATION. SET ROLE changes the current role but loses superuser privileges. SET SESSION AUTHORIZATION is heavier and changes the session's identity. For testing, use SET ROLE — it's simpler and less risky.

4. Policies don't work without WITH CHECK. For INSERT and UPDATE, you need both USING and WITH CHECK. USING filters existing rows; WITH CHECK validates new rows. If you only have USING, inserts can still violate the policy.

5. Session variables are not set. If you rely on current_setting('app.customer_id', true), the true parameter means 'return NULL if not set.' If it's not set, the policy may deny everything — which is often desired, but can confuse developers.

Common mistake example:

-- This policy is useless without ENABLE ROW LEVEL SECURITY
CREATE POLICY p ON payments FOR SELECT USING (true);
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

Always enable RLS before creating policies (or at least before testing).

What you learned & what's next

You now know how to implement row-level security in PostgreSQL: you can enable RLS on a table, create policies for SELECT, INSERT, UPDATE, and DELETE, and test the enforcement using different roles. You also learned why RLS is important for multi-tenant security, how to use session variables to make policies dynamic, and common pitfalls to avoid.

You've covered all the learning objectives: you can explain the core idea behind RLS and complete a practical exercise to implement it.

What's next? In the next lesson, you'll explore column-level security or encryption to protect sensitive data even further. You'll see how to use pg_crypto or VERSION tables to hide data from unauthorized eyes.