RLS Simplified

Last edited: 1/18/2025

TL;DR: Basic summary

Row-Level Security (RLS) Policy: A WHERE or CHECK condition applied automatically to database queries

Key features:

  • Applies without being explicitly added to each query, which makes it good for policing row access from unknown entities, such as those leveraging the anon or authenticated roles.
  • Can be set for specific actions (e.g., SELECT, INSERT)
  • Can target particular database roles (e.g., "anon", "authenticated")

Contrast with regular conditions:

  • Regular conditions: Apply to all roles and must be added manually to each query
  • RLS policies: Applied automatically to specified actions and roles

Hands on walk-through for conditions

USING:

The USING keyword inspects the value of row to see if it should be made visible to the query.

When you SELECT, UPDATE, or DELETE, you have to use a WHERE statement to search for specific rows:


_13
-- select
_13
select *
_13
from some_table
_13
where id = 5;
_13
_13
-- update
_13
update some_table
_13
set id = 6
_13
where id = 5;
_13
_13
-- delete
_13
delete from some_table
_13
where id = 6;

Even when you don't use a WHERE statement, there's still an implicit one:


_10
-- ...your query
_10
WHERE true;

The USING clause appends more to the WHERE statement:


_15
-- Your Using condition
_15
USING (
_15
(select auth.uid()) = user_id
_15
);
_15
_15
-- Your query without RLS:
_15
SELECT * FROM some_table
_15
WHERE id = 5 OR id = 6;
_15
_15
-- Your query after RLS
_15
SELECT * FROM some_table
_15
WHERE
_15
(id = 5 OR id = 6)
_15
AND
_15
(select auth.uid()) = user_id) -- <--- added by the USING clause;

WITH CHECK:

Let's say you have a profile table. Well, you don't want user's to be able to modify their user_id when they make an insert, do you?

The WITH CHECK condition inspects values that are being added or modified. For INSERT you'd use it by itself. There's no need for a using clause:


_18
-- Allow users to add to table, but make sure their user_id matches the one in their JWT:
_18
_18
create policy "Allow user to add posts"
_18
on "public"."posts"
_18
as PERMISSIVE
_18
for INSERT
_18
to authenticated
_18
with check(
_18
(select auth.uid()) = user_id
_18
);
_18
_18
-- Example: failing insert
_18
INSERT INTO posts
_18
VALUES (<false id>, <comment>);
_18
_18
-- Example: successful insert
_18
INSERT INTO posts
_18
VALUES (<real id>, <comment>);

INSERTs do not rely on WHERE clauses, but they can have constraints. In this case, the RLS acts as a CHECK constraint against a column, e.g.:


_10
ALTER TABLE table_name
_10
ADD CONSTRAINT constraint_name CHECK (condition);

What distinguishes it from normal CHECK constraints is that it is only activate for certain roles or methods.

UPDATEs:

UPDATE both filters for rows to change and then adds new values to the table, so it requires both USING and WITH CHECK conditions:


_11
create policy "Allow user to edit their stuff"
_11
on "public"."<SOME TABLE NAME>"
_11
as RESTRICTIVE
_11
for UPDATE
_11
to authenticated
_11
using (
_11
(select auth.uid()) = user_id
_11
)
_11
with check(
_11
(select auth.uid()) = user_id
_11
);