Row Level Security
Row Level Security (RLS) is a PostgreSQL feature that lets you attach access rules directly to a database table, controlling which rows a user can read, insert, update, or delete. The database enforces these rules itself, not the application.
The key word is row-level: unlike granting or revoking access to a whole table, RLS can restrict access to specific rows based on who is asking. For example: "a user can only see their own bookings" or "only the admin role can read all rows."
Why it matters
Without RLS, your access control lives entirely in the application layer. If someone bypasses the app (by hitting the database API directly with your public key, or through a misconfigured endpoint), they can read everything.
With RLS enabled, the database becomes the last line of defence. Even if someone queries the table with a valid key, the policies filter what comes back before any data leaves the database.
How policies work
You enable RLS on a table, then write policies in SQL:
-- Enable RLS on the table
ALTER TABLE bookings ENABLE ROW LEVEL SECURITY;
-- Allow the service role to read all rows
CREATE POLICY "service role can read all bookings"
ON bookings
FOR SELECT
TO service_role
USING (true);
-- Allow guests to read only their own booking by reference number
CREATE POLICY "guest can read own booking"
ON bookings
FOR SELECT
TO anon
USING (reference_number = current_setting('request.jwt.claim.ref', true));
Policies are per-operation (SELECT, INSERT, UPDATE, DELETE). You can have multiple policies on one table.
RLS in Supabase
Supabase builds its entire auth model around RLS. When a user logs in, Supabase sets their JWT in the request context. Policies can check that JWT to know who's asking:
-- Only the authenticated user can see their own data
USING (auth.uid() = user_id)
auth.uid() is a Supabase helper that reads the user's ID from their session JWT.
The two Supabase access keys map directly to RLS:
- Anon key: subject to all RLS policies
- Service role key: bypasses RLS entirely (use only server-side)
Common pitfalls
- Forgetting to enable RLS: tables are open by default. Always run
ALTER TABLE ... ENABLE ROW LEVEL SECURITY. - Writing policies that look correct but leak data: test directly against the Supabase REST API with the anon key, not just through the app.
SECURITY DEFINERfunctions bypass RLS: stored functions with this setting run as the DB owner, so they can read/write regardless of the caller's policies. Useful (see Supabase), but requires care.
Related notes
- Supabase — uses RLS as the primary access control model
- PostgreSQL — where RLS is defined