SQL RLS Rule

January 10, 2026 6 minute read

PostgreSQL Row-Level Security (RLS)

1. What is RLS?

Row-Level Security is a PostgreSQL feature that lets you control which rows a user can see or modify in a table — at the database level. Instead of filtering data in your app code, the database itself enforces the rules.

This is especially useful when:

Multiple users share the same table (multi-tenant apps)
You want to guarantee data isolation regardless of how the data is queried
You’re building on top of platforms like Supabase that rely heavily on RLS
You’re using Apache Superset to serve dashboards to different users or teams and need each person to only see their own data

2. How It Works

RLS boils down to three steps:

Enable RLS on a table
Create policies that define who can see/do what
PostgreSQL automatically applies those policies to every query

Without a policy, no rows are returned once RLS is enabled — it’s deny-by-default.

-- Step 1: Enable RLS on the table
ALTER TABLE workouts ENABLE ROW LEVEL SECURITY;

-- Step 2: Create a policy
CREATE POLICY user_isolation ON workouts
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

Now every SELECT, UPDATE, or DELETE on workouts only returns rows where user_id matches the current session’s user.

3. Policy Types

PostgreSQL supports different policy types depending on the operation:

Policy Clause	Controls	When It’s Checked
`USING`	Which rows are visible	`SELECT`, `UPDATE`, `DELETE`
`WITH CHECK`	Which rows can be written	`INSERT`, `UPDATE`

You can also scope policies to specific commands:

-- Read-only: users can only SELECT their own rows
CREATE POLICY select_own ON workouts
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::uuid);

-- Insert: users can only insert rows for themselves
CREATE POLICY insert_own ON workouts
    FOR INSERT
    WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

-- Update: users can only update their own rows
CREATE POLICY update_own ON workouts
    FOR UPDATE
    USING (user_id = current_setting('app.current_user_id')::uuid)
    WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

-- Delete: users can only delete their own rows
CREATE POLICY delete_own ON workouts
    FOR DELETE
    USING (user_id = current_setting('app.current_user_id')::uuid);

4. Real-World Example: Multi-Tenant App

Say you have a fitness app where each user should only see their own data. Here’s a full setup:

-- Create the table
CREATE TABLE conditions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID NOT NULL,
    condition_date DATE NOT NULL,
    score INTEGER,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);

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

-- Users can only access their own rows
CREATE POLICY user_access ON conditions
    FOR ALL
    USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

auth.uid() is a Supabase helper that returns the authenticated user’s ID from the JWT. If you’re not on Supabase, use current_setting('app.current_user_id')::uuid or a similar session variable.

5. Admin / Service Role Bypass

Sometimes you need a backend service or admin to access all rows. You can do this with BYPASSRLS or by creating permissive policies for specific roles:

-- Option 1: Grant a role the ability to bypass RLS entirely
ALTER ROLE service_role BYPASSRLS;

-- Option 2: Create a separate policy for admins
CREATE POLICY admin_full_access ON conditions
    FOR ALL
    TO admin_role
    USING (true)
    WITH CHECK (true);

The table owner also bypasses RLS by default. To force policies on the owner too:

ALTER TABLE conditions FORCE ROW LEVEL SECURITY;

6. RLS with Supabase

If you’re using Supabase, RLS is the primary security layer. A few patterns I use:

Authenticated users see only their data:

CREATE POLICY "Users read own data" ON conditions
    FOR SELECT
    TO authenticated
    USING (user_id = auth.uid());

Allow inserts only for the authenticated user:

CREATE POLICY "Users insert own data" ON conditions
    FOR INSERT
    TO authenticated
    WITH CHECK (user_id = auth.uid());

Public read access (e.g. leaderboards):

CREATE POLICY "Public leaderboard" ON leaderboard
    FOR SELECT
    TO anon, authenticated
    USING (true);

Group/team access:

CREATE POLICY "Team members access" ON team_data
    FOR SELECT
    USING (
        team_id IN (
            SELECT team_id FROM team_members
            WHERE member_id = auth.uid()
        )
    );

7. RLS in Apache Superset

This is how I actually use RLS day-to-day. I run Apache Superset dashboards for ONWRD — personal dashboards for individual users and group dashboards for teams/coaches. The problem is everyone hits the same tables, so I need each user to only see their own data on the charts.

Superset has a built-in Row Level Security feature that works separately from PostgreSQL’s RLS. You configure it through the Superset UI:

Settings → Row Level Security → + Add

Field	What to set
Name	e.g. `User isolation - conditions`
Filter Type	Regular
Tables	Select the tables this rule applies to
Roles	Which Superset roles are affected (e.g. `Gamma`, `Public`)
Clause	SQL WHERE clause, e.g. `user_id = ''`

Superset injects this clause as a WHERE filter on every query that touches those tables — so the charts automatically show only the relevant data.

Example: Personal dashboard

Each user logs into Superset and should only see their own condition scores:

Clause: user_id = ''

Or if you store a user ID mapping:

Clause: user_id = (SELECT id FROM users WHERE email = '')

Example: Group/team dashboard

Coaches should see data for all athletes in their team:

Clause: team_id IN (SELECT team_id FROM team_members WHERE coach_email = '')

Superset RLS vs PostgreSQL RLS — which to use?

	Superset RLS	PostgreSQL RLS
Where it runs	Superset app layer	Database level
Scope	Only Superset queries	All queries (app, API, direct SQL)
Config	UI-based, per role	SQL policies, per table
Best for	Dashboard-level filtering	Full data isolation across all access paths

I use both — Superset RLS for the dashboard layer (quick to set up, role-based), and PostgreSQL RLS on the Supabase side for the mobile app and API. Belt and suspenders.

Tip: If your Superset service account connects to PostgreSQL with a role that has BYPASSRLS, Superset RLS is your only line of defense on the dashboard side. Make sure it’s set up correctly.

8. Testing RLS Policies

Always test your policies. You can simulate a user session like this:

-- Set the session user
SET app.current_user_id = 'some-uuid-here';

-- This should only return rows for that user
SELECT * FROM conditions;

-- Try accessing someone else's data (should return nothing)
SET app.current_user_id = 'different-uuid';
SELECT * FROM conditions;

In Supabase, you can test by calling the API with different JWT tokens and verifying the response only contains the expected rows.

9. Performance Considerations

Index the columns used in policies — If your policy checks user_id, make sure it’s indexed. Otherwise every query does a full table scan.
```
CREATE INDEX idx_conditions_user_id ON conditions(user_id);
```
Keep policies simple — Subqueries in USING clauses (like the team access example) can get expensive. Consider materializing group memberships if queries are slow.
Use EXPLAIN ANALYZE — Check that the RLS filter is pushed down into the query plan efficiently.
Avoid too many policies — Multiple permissive policies on the same table are OR’d together, which can get complex. Restrictive policies are AND’d.

10. Common Pitfalls

Forgetting to create a policy — RLS enabled + no policies = nobody can see anything. This catches people off guard.
Table owner bypass — The table owner skips RLS by default. Use FORCE ROW LEVEL SECURITY if you want policies to apply to everyone.
Joins leaking data — If table A has RLS but table B doesn’t, a join can expose filtered rows through B. Enable RLS on all tables that hold sensitive data.
Migrations breaking policies — Dropping and recreating a table removes its policies. Always re-apply RLS in your migration scripts.

11. Summary

Concept	Key Point
Enable RLS	`ALTER TABLE ... ENABLE ROW LEVEL SECURITY`
Default behavior	Deny all — no rows returned without a policy
`USING`	Controls which rows are visible (reads)
`WITH CHECK`	Controls which rows can be written (inserts/updates)
Admin bypass	`BYPASSRLS` role or permissive admin policy
Force on owner	`FORCE ROW LEVEL SECURITY`
Superset RLS	UI-based row filtering per role — great for dashboards
Best practice	Use both Superset RLS + PostgreSQL RLS for full coverage
Performance	Index policy columns, keep policies simple

RLS is one of those features that, once you start using it, you wonder how you ever built multi-user apps without it. It moves the security boundary into the database where it belongs.