Row-Level Security Best Practices: Lessons From 150K+ Healthcare Users

Row-Level Security is the most underutilized security feature in PostgreSQL. At OpenMyPro, RLS policies are the last line of defense protecting 150K+ patients' health information. After two years of production experience, here are the best practices, performance patterns, and critical mistakes to avoid.

RLS as Defense in Depth

RLS is not a replacement for application-level authorization. It is an additional layer that catches bugs your application code misses. Consider this scenario: a developer writes a provider dashboard query that accidentally omits the WHERE provider_id = :current_provider clause. Without RLS, that query returns every patient's data. With RLS, it returns only the current provider's patients regardless of the application code error.

At OpenMyPro, every table containing protected health information has RLS enabled. Even tables that "should" only be accessed through carefully controlled application code. The cost is minimal — a few milliseconds per query — and the protection is absolute.

Policy Design Patterns

We follow three policy patterns depending on the table's access requirements. First, owner-only tables where only the record creator can read and modify. Patient profiles and health conditions use this pattern with a simple auth.uid() = user_id check.

Second, relational access tables where access depends on a relationship. Bookings are accessible to both the patient who booked and the provider who was booked. The policy checks auth.uid() = patient_id OR auth.uid() = provider_id_from_user_mapping. This lookup joins the providers table to map the Supabase auth user ID to the provider record.

Third, public-read tables where anyone can read but only owners can modify. Provider profiles, specialty listings, and public reviews use this pattern. The SELECT policy has no conditions (allowing public reads), while INSERT, UPDATE, and DELETE policies check ownership.

Performance Optimization

RLS policies execute on every row returned by a query. A poorly written policy can turn a 5ms query into a 500ms query. The primary optimization technique is ensuring that every column referenced in a policy has an index. If your policy checks auth.uid() = user_id, the user_id column must be indexed.

Avoid subqueries in RLS policies when possible. A policy that runs SELECT provider_id FROM providers WHERE user_id = auth.uid() on every row evaluation creates N+1 query behavior. Instead, use a helper function that caches the result: create a function get_current_provider_id() that runs the subquery once per transaction and returns the cached result.

We benchmark RLS overhead by comparing query execution times with and without policies using SET LOCAL role TO authenticated. If the RLS overhead exceeds 20% of query time, we optimize the policy — typically by adding an index or refactoring a subquery into a cached function.

Testing RLS Policies

Testing RLS is non-negotiable. We test every policy with multiple user contexts: the record owner, a related user (e.g., the provider for a patient's booking), an unrelated authenticated user, and an unauthenticated request.

Each test sets the Supabase auth context to a specific user, runs a query, and asserts that only the expected rows are returned. If a test user sees a row they should not, that is a critical security failure that blocks deployment.

We also test edge cases: what happens when a provider account is deactivated? When a patient deletes their profile? When a booking is cancelled? Each state change should properly restrict access according to our business rules.

Common Mistakes

The most dangerous RLS mistake is enabling RLS on a table but forgetting to create policies. PostgreSQL's default behavior when RLS is enabled with no policies is to deny all access, which seems safe. But if a developer adds a permissive policy without thinking about all operations, they might create a SELECT policy while leaving INSERT, UPDATE, and DELETE wide open.

Another common mistake is using the wrong policy type. PostgreSQL has PERMISSIVE and RESTRICTIVE policies. Permissive policies are OR'd together — any passing policy grants access. Restrictive policies are AND'd — all must pass. We use permissive policies for granting access and restrictive policies for adding global constraints like "cannot access soft-deleted records."

RLS and Supabase Auth

Supabase Auth integrates seamlessly with RLS through the auth.uid() and auth.jwt() functions. The current user's ID is available in every policy without any application-level setup. Custom JWT claims are available through auth.jwt() for role-based policies.

One nuance: Supabase's anon key bypasses RLS for tables in the public schema by default. We always set the table's RLS to force mode using ALTER TABLE tablename FORCE ROW LEVEL SECURITY, which applies RLS even to the table owner. This prevents any authentication bypass from exposing data.