Enabling and Using Row‑Level Security in Supabase: A Practical Guide

Why Row‑Level Security Matters in Supabase

Supabase tables are created without Row‑Level Security (RLS) turned on, meaning every authenticated user can see and modify all rows unless you explicitly restrict them. Enabling RLS and writing policies is the most reliable way to enforce per‑row access control, especially for multi‑tenant applications where each user should only see their own data.

Enabling RLS on a Table

RLS is a Postgres feature that must be turned on explicitly. In Supabase, run the following SQL in the dashboard’s SQL editor or via psql:

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

Permissions required: the table owner or a superuser. After execution, verify with:

SELECT relname, relrowsecurity FROM pg_class WHERE relname = 'profiles';

The relrowsecurity column should be true. You can also use \dp+ profiles in psql to list attached policies.

Writing a Simple Policy: One Row Per User

Suppose you have a profiles table with a primary key id that stores the Supabase Auth UID. The following policy allows a user to SELECT, INSERT, UPDATE, and DELETE only the row that matches their UID.

CREATE POLICY user_is_owner
  ON profiles
  FOR ALL
  USING (id = auth.uid());

Key points:

• auth.uid() is a Postgres function that returns the UID of the authenticated Supabase session.
• The USING clause determines which rows a user can read.
• Because the policy applies to all operations, INSERT and UPDATE are also restricted.
• If you want to allow only SELECT and UPDATE, replace FOR ALL with FOR SELECT, UPDATE and add a separate INSERT policy.

Testing the Policy

1. Log in as a test user via the Supabase client or REST API. 2. Attempt to SELECT * FROM profiles; – only the row where id equals the user’s UID should return. 3. Try inserting a row with a different id; the request should fail with a permission denied error.

Use the Supabase Dashboard’s RLS tab to view the policy visually, but remember that the SQL definition is the source of truth.

Common Pitfalls and How to Avoid Them

• RLS Disabled but Policies Defined: Policies are inert until RLS is enabled. Always run the ALTER TABLE ... ENABLE ROW LEVEL SECURITY command before creating policies.
• Owner Bypass: The table owner (the user who created the table) can bypass all policies unless FORCE ROW LEVEL SECURITY is set. To enforce RLS for the owner, run:

  ALTER TABLE profiles FORCE ROW LEVEL SECURITY;

• Unauthenticated Requests: Policies that only check auth.uid() will still allow unauthenticated users if the function returns NULL. Add a role check:

  USING (auth.role() = 'authenticated' AND id = auth.uid());

• WITH CHECK Misunderstanding: The WITH CHECK clause validates new or updated rows but does not filter existing data. If you need to clean up old data, run a manual DELETE or UPDATE outside of the policy.
• Column Privileges: RLS does not replace column‑level privileges. If you need to hide specific columns, grant column permissions separately.
• Performance Overheads: Complex policies that involve subqueries or joins can slow down queries on large tables. Keep policies as simple as possible and test on representative data sizes.

Practical Checklist Before Deploying RLS

1. Enable RLS on each table that needs row‑level protection.
2. Create policies with explicit USING and WITH CHECK clauses.
3. Set FORCE ROW LEVEL SECURITY if the table owner should also be restricted.
4. Test each policy by logging in as different users and verifying that unauthorized queries return a 403 permission denied error.
5. Compare the SQL definitions with the dashboard’s visual editor to catch drift.
6. Document the policy logic in your codebase so future developers understand the access rules.

Conclusion

Row‑Level Security is the cornerstone of secure multi‑tenant applications in Supabase. By enabling RLS, writing clear policies, and rigorously testing, you can prevent accidental data leaks and enforce strict per‑row access control.