PostgreSQL Row-Level Security beyond the basics

PostgreSQL Row-Level Security is the answer to making multi-tenant work without paranoia. No need for SQL filters on every query, no need to do everything in application, you put a rule in the database and you’re done.

The problem is that 90% of tutorials stop at WHERE user_id = auth.uid(). Done. Works for simple cases. But when you’re doing real multi-tenant, with 50 clients sharing tables, with complex policies, with performance that matters, the story is different.

I’ll show you here what nobody talks about. Real multi-tenant setup with tenant_id in the JWT. How to use SECURITY DEFINER to keep policies fast. How to debug when RLS breaks. How to avoid deadlock. Real SQL code, ready to copy.

The problem RLS solves

Imagine this scenario: you have a scheduling SaaS. Clinic A and Clinic B use your app. Same appointments table in the database.

Without RLS:

• You need to add WHERE tenant_id = $1 to every query
• If you forget one, Clinic A’s data leaks to Clinic B
• The more queries, the higher the chance of forgetting
• Worse: your JavaScript code can have a bug. RLS at the database doesn’t.

With RLS:

• You define: “this tenant only sees their data”
• Database guarantees this at row level, doesn’t matter what query is executed
• Application can’t leak data even if it tries

This is defense in depth. Application is first line, RLS is second line.

Basic setup (what you find in every tutorial)

I’d skip this section, but need it for context later.

Tables:

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  patient_name TEXT NOT NULL,
  scheduled_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE auth.users (
  id UUID PRIMARY KEY,
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Basic policy (RLS 101):

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users can see their clinic's appointments" ON appointments
  FOR SELECT
  USING (clinic_id = (SELECT clinic_id FROM auth.users WHERE id = auth.uid()));

Now when a Clinic A user runs SELECT * FROM appointments, they only see appointments where clinic_id equals their Clinic A id. Database automatically executes:

SELECT * FROM appointments 
WHERE clinic_id = (SELECT clinic_id FROM auth.users WHERE id = auth.uid());

Automatic. Without you thinking.

OK, this works. But it has three problems that appear in production.

Problem 1: JWT doesn’t pass tenant_id, requires subquery

In the basic setup, you’re running a subquery every time:

(SELECT clinic_id FROM auth.users WHERE id = auth.uid())

This subquery runs on every row of your policy. If your table has 100k rows, it runs 100k times (or database optimizes it, but not guaranteed).

Solution: put tenant_id directly in the JWT.

Your login (example with Supabase):

const { data, error } = await supabase.auth.signInWithPassword({
  email: user.email,
  password: user.password,
});

Supabase is grabbing user.id and putting it in the JWT. But you need clinic_id too.

Supabase has user_metadata. You can put clinic_id there:

const { data, error } = await supabase.auth.admin.updateUserById(user.id, {
  user_metadata: {
    clinic_id: clinic.id,
  },
});

Then your JWT looks like this:

{
  "sub": "user-id-xxx",
  "user_metadata": {
    "clinic_id": "clinic-id-yyy"
  }
}

Now your policy becomes:

CREATE POLICY "users see their clinic's data" ON appointments
  FOR SELECT
  USING (clinic_id = (auth.jwt() ->> 'user_metadata')::jsonb ->> 'clinic_id');

This removes the subquery. You’re pulling directly from the JWT (which is already in memory).

Can be cleaner:

CREATE POLICY "users see their clinic's data" ON appointments
  FOR SELECT
  USING (clinic_id::text = auth.jwt() -> 'user_metadata' ->> 'clinic_id');

Or if you control JWT directly (like with supabase.rpc()):

CREATE POLICY "users see their clinic data" ON appointments
  FOR SELECT
  USING (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

Performance win: no subquery, RLS runs in O(1) access time, not O(n).

Problem 2: DELETE/UPDATE need different access than SELECT

You want users to see all appointments of their clinic.

But you don’t want them to delete an appointment created by another user in the same clinic.

Solution: different policies for SELECT, INSERT, UPDATE, DELETE.

-- SELECT: see all clinic's appointments
CREATE POLICY "select_own_clinic_appointments" ON appointments
  FOR SELECT
  USING (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

-- UPDATE: only update appointment they created
CREATE POLICY "update_own_appointments" ON appointments
  FOR UPDATE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  )
  WITH CHECK (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  );

-- DELETE: only delete appointment they created
CREATE POLICY "delete_own_appointments" ON appointments
  FOR DELETE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  );

-- INSERT: only insert in their clinic
CREATE POLICY "insert_own_clinic_appointments" ON appointments
  FOR INSERT
  WITH CHECK (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

Notice the WITH CHECK. It’s an important part many people forget.

USING = condition to read/delete WITH CHECK = condition to write/update

If you only put USING on UPDATE, user can run:

UPDATE appointments SET clinic_id = another_clinic WHERE id = 'your-id'

With WITH CHECK, you guarantee that clinic_id stays the same forever.

Problem 3: Policies with SECURITY DEFINER are the only way to do complex permissions

There’s a case where you need: user can see appointment if they’re assigned to it OR they’re clinic admin.

Doing this in a policy gets complex:

CREATE POLICY "view_if_assigned_or_admin" ON appointments
  FOR SELECT
  USING (
    (assigned_to = auth.uid())
    OR (
      EXISTS (
        SELECT 1 FROM clinic_staff
        WHERE clinic_staff.user_id = auth.uid()
        AND clinic_staff.clinic_id = appointments.clinic_id
        AND clinic_staff.role = 'admin'
      )
    )
  );

This works, but it has a problem: OUTER JOIN to another table.

If your appointments table has 1M rows and you run SELECT, this policy runs EXISTS on every row. It’s slow.

Solution: use a function with SECURITY DEFINER.

CREATE OR REPLACE FUNCTION can_view_appointment(appointment_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM appointments
    WHERE id = appointment_id
    AND (
      assigned_to = auth.uid()
      OR (
        SELECT role FROM clinic_staff
        WHERE user_id = auth.uid()
        AND clinic_id = appointments.clinic_id
      ) = 'admin'
    )
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE POLICY "view_if_assigned_or_admin" ON appointments
  FOR SELECT
  USING (can_view_appointment(id));

SECURITY DEFINER means function runs with permissions of whoever created it (usually admin), not the current user. Then you can do JOIN/EXISTS without worry, database optimizes better.

Trade-off: you lose a level of protection. If function has a bug, could leak data. But if function is well-tested, you gain performance.

In practice: use SECURITY DEFINER for complex logic, use simple policies for trivial logic.

Problem 4: Debugging broken RLS

User complains: “I can’t see my data”.

How do you debug this?

First: enable RLS logging.

ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_row_security = on;
SELECT pg_reload_conf();

Then tail the log:

tail -f /var/lib/postgresql/data/log/postgresql.log | grep "policy"

You’ll see something like:

QUERY (filtered) SELECT ... USING (clinic_id = ...)

(filtered) = RLS kicked in.

Second: validate JWT manually.

If you use Supabase, JWT should look like this:

SELECT auth.jwt();
-- Returns: {..., "user_metadata": {..., "clinic_id": "xxx"}}

If it doesn’t return clinic_id, the problem is in your login function (not putting clinic_id in user_metadata).

Third: test policy without RLS.

Temporarily disable RLS:

ALTER TABLE appointments DISABLE ROW LEVEL SECURITY;
SELECT * FROM appointments; -- Now you see everything
ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

If SELECT returns different with RLS on/off, problem is the policy. If it returns the same, problem is in the app (not sending clinic_id in JWT).

Fourth: debug specific policy.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM appointments 
WHERE clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id';

EXPLAIN shows which policy is being applied and how many rows it’s filtering.

If it shows “0 rows returned” but should have rows, problem is in the policy condition (clinic_id doesn’t match JWT).

Problem 5: Deadlock in SECURITY DEFINER functions

Imagine: you have a policy that admin can create appointment, system can update appointment.

CREATE OR REPLACE FUNCTION system_update_appointment(id UUID, status TEXT)
RETURNS VOID AS $$
BEGIN
  UPDATE appointments SET status = status WHERE id = id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Admin calls the function like this:

SELECT system_update_appointment('appt-id', 'completed');

What happens:

1. Admin runs function (with admin permissions)
2. Function tries UPDATE on appointments
3. RLS validates: can admin update? Yes.
4. Update happens.

Problem: if you have triggers or other updates running in parallel, you can get deadlock.

Solution: set lock_timeout and statement_timeout:

CREATE OR REPLACE FUNCTION system_update_appointment(id UUID, status TEXT)
RETURNS VOID AS $$
BEGIN
  SET lock_timeout = '5s';
  SET statement_timeout = '10s';
  
  UPDATE appointments SET status = status WHERE id = id;
  
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Appointment not found or no permission';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Then if the function hangs waiting for a lock for more than 5 seconds, it fails gracefully instead of infinite deadlock.

Another solution: good indexes.

CREATE INDEX idx_appointments_clinic_id ON appointments(clinic_id);
CREATE INDEX idx_appointments_created_by ON appointments(created_by);
CREATE INDEX idx_clinic_staff_user_id ON clinic_staff(user_id);

RLS runs with indexes. If indexes aren’t there, PostgreSQL does table scan, which locks more easily.

Problem 6: GRANT is confusing with RLS

You created a policy, tested it, it works. Then you run it in production and the user gets a permission error.

Problem: RLS requires GRANT on the table.

GRANT SELECT, INSERT, UPDATE, DELETE ON appointments TO "anon";
GRANT SELECT, INSERT, UPDATE, DELETE ON appointments TO "authenticated";

With Supabase:

GRANT ALL ON public.appointments TO authenticated;

If you don’t do GRANT, user gets error “permission denied for table appointments” before RLS even runs.

Order is:

1. GRANT (can user access the table?)
2. RLS (can user access the ROW?)

If it fails at 1, it fails. If it passes 1, RLS filters.

Complete example: real multi-tenant

Here I’m going to set up a real multi-tenant system with RLS.

Tables:

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE auth.users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE,
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  role TEXT DEFAULT 'user',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  patient_name TEXT NOT NULL,
  assigned_to UUID REFERENCES auth.users(id),
  status TEXT DEFAULT 'scheduled',
  scheduled_at TIMESTAMP NOT NULL,
  created_by UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_appointments_clinic_id ON appointments(clinic_id);
CREATE INDEX idx_appointments_created_by ON appointments(created_by);

Policies:

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

-- SELECT: see if it's from their clinic
CREATE POLICY "select_own_clinic" ON appointments
  FOR SELECT
  USING (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

-- INSERT: only in their clinic
CREATE POLICY "insert_own_clinic" ON appointments
  FOR INSERT
  WITH CHECK (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

-- UPDATE: only appointment they created
CREATE POLICY "update_own_appointment" ON appointments
  FOR UPDATE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  )
  WITH CHECK (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  );

-- DELETE: clinic admin can delete any
CREATE OR REPLACE FUNCTION is_clinic_admin()
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (
    SELECT role FROM auth.users 
    WHERE id = auth.uid()
  ) = 'admin';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE POLICY "delete_if_admin" ON appointments
  FOR DELETE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND is_clinic_admin()
  );

Grants:

GRANT SELECT, INSERT, UPDATE, DELETE ON appointments TO authenticated;
GRANT SELECT ON clinics TO authenticated;
GRANT SELECT ON auth.users TO authenticated;

Checklist:

- RLS enabled: ALTER TABLE ... ENABLE ROW LEVEL SECURITY
- GRANT done for users to access table
- JWT contains clinic_id in user_metadata
- Policies exist for SELECT, INSERT, UPDATE, DELETE
- UPDATE has WITH CHECK to prevent write to another tenant
- SECURITY DEFINER functions used only for complex logic
- Indexes created on foreign keys
- Logging tested with ALTER SYSTEM SET log_row_security

Next steps

RLS is reliable for production when done right. But it requires:

1. Understanding multi-tenant design to know which data to segregate
2. Knowing Supabase + React to use Supabase well
3. Documenting policies so other devs don’t break them

RLS is not overkill. It’s basic security that applications alone can’t guarantee.

• Create JWT with clinic_id in user_metadata
• Create SELECT policy for each table
• Create different INSERT/UPDATE/DELETE policies
• Add GRANT for users to access
• Use indexes on foreign keys (clinic_id, user_id)
• Test RLS with ON and OFF, see the difference
• Debug with EXPLAIN ANALYZE if policy is slow
• Create SECURITY DEFINER function for complex logic
• Add log_row_security in staging, validate queries
• Test multi-tenant: create 2 users from different clinics, validate isolation