Row Level Security (RLS) Policies Explained for Beginners

Quick answer: Row-Level Security (RLS) is a PostgreSQL feature that makes the database itself enforce which rows each user can read or modify. Review table privileges, RLS state, applicable policies, and bypass roles. Test server-side authentication and authorization for each protected operation.

By Paula C · Kraftwire Software

Row-Level Security (RLS) is a PostgreSQL security feature that allows you to define rules (policies) directly in your database to control which rows a user can see, insert, update, or delete. With RLS disabled, table privileges determine access. With RLS enabled and no applicable policies, roles subject to RLS are denied access. Permissive policies can expose rows. Roles with BYPASSRLS, such as service_role, ignore RLS policies.

What Is Row-Level Security?

Row-Level Security (RLS) is a granular access control system built into PostgreSQL. Unlike traditional application-level security, where you write if statements in your backend code to check permissions, RLS moves that logic into the database engine itself. For Supabase’s Data API, RLS can filter rows for roles subject to it. It supplements table privileges and does not replace authentication, application authorization, or protection of bypass credentials.

Think of a standard database table like a shared spreadsheet. Without RLS, table privileges determine which database operations a role can perform. With RLS enabled, applicable policies determine which rows a role subject to RLS can access; ownership restrictions must be expressed in those policies. A request made with curl is still subject to the request role’s privileges and applicable policies. RLS does not automatically impose ownership: write that condition explicitly when private user-owned rows are intended. PostgreSQL can also apply privileges at table or column level.

Why RLS is Critical for AI-Built Apps (Windsurf, Cursor, Lovable)

If your application uses Supabase from browser code, inspect the permissions associated with its public anon key and user sessions. This applies regardless of whether you used Lovable, Bolt.new, Windsurf, or another development tool.

The Exposed API Risk

With a browser-accessible Data API, users can send requests independently of your UI. Inspect table privileges, applicable RLS policies, and bypass privileges. For example, supabase.from('profiles').select('*') requests data from profiles; its results depend on the effective role and policies, not the component that issues it. With RLS disabled, privileges still determine access; it is not automatically public.

The "Vibe Coding" Security Gap

Review generated code and deployment configuration independently of whether visible features work. For a new orders table, verify the intended roles, privileges, policies, and permitted operations before exposing it through an API.

These live figures describe all scanned sites. They do not establish prevalence for an AI-built subgroup, compare development methods, or attribute findings to AI generation.

30% of the 119 live sites SimplyScan has scanned (36 sites) have at least one high or critical security issue.

13% of the 119 live sites SimplyScan has scanned (15 sites) have at least one high-severity web-security issue, such as a missing Content-Security-Policy or HSTS header.

Use a vibe coding security checklist to organize application-specific checks; these aggregate figures do not establish which controls a particular app lacks.

How RLS Works: The Three-Step Process

Review table privileges, RLS state, applicable policies, and bypass roles. Test both permitted and denied access for each relevant role after configuration changes, including Supabase’s anon and authenticated roles.

1. Enable RLS on the Table

By default, PostgreSQL tables have RLS disabled. You must explicitly turn it on. When you do this, PostgreSQL shifts to a "default deny" posture.

If no applicable policy exists, roles subject to RLS are denied row access. Existing policies take effect when RLS is enabled. Superusers and BYPASSRLS roles bypass RLS; table owners normally do too unless FORCE ROW LEVEL SECURITY applies. All cases still require the relevant privileges. An empty response alone does not identify its cause.

2. Define the Policy Scope

Policy expressions determine which rows an operation may use or write, subject to the request role’s privileges. Applicable permissive policies combine with OR; restrictive policies combine with AND and require an applicable permissive policy to allow access.

A policy consists of:

• Command: Which action is being controlled (SELECT, INSERT, UPDATE, DELETE, or ALL).
• Target: Which roles the policy applies to (usually authenticated or anon).
• USING clause: A check for existing rows (used for SELECT, UPDATE, DELETE).
• WITH CHECK clause: A check for new data being added (used for INSERT, UPDATE).

3. Apply Authentication Context

In Supabase, auth.uid() makes the authenticated request’s user ID available to policies. The API layer validates the supplied token and establishes the database request context. For an unauthenticated request, the function returns null.

Common RLS Policy Examples

Basic Ownership Policy

This example illustrates an owner-based policy; test it with the relevant roles and privileges. Adapt its column comparison to your schema: a table using user_id for ownership needs that column in the predicate. A correctly scoped ownership policy can restrict rows by user ID for roles subject to RLS. Use it where each row has a single owner and sharing is not intended.

Public Read / Private Write

For intentionally public profiles, a read policy can allow matching rows while an update policy checks ownership. The examples assume RLS is enabled and the intended roles have the required table privileges. USING selects existing rows; WITH CHECK checks proposed rows, including the explicit update check below.

The "Admin" Policy (Security Definer)

An is_admin helper may read a role column from a profiles table. A policy that queries the same protected table can recurse: checking the role invokes the policy, which queries the table again. Inspect the complete policy dependency path.

A carefully scoped Security Definer function can query with its owner’s privileges. Avoiding recursion requires that this role’s read does not re-enter the same policy. Keep role assignments protected from user edits, use qualified names and a trusted search path, and restrict function execution. Review those conditions before adapting this example.

Troubleshooting AI-Generated RLS Issues

Inspect generated code and deployed configuration directly instead of assuming that development settings suit production. See Cursor, Lovable. Review table privileges, RLS state, applicable policies, and bypass roles.

The "Empty Array" Bug

An empty array [] can result from query filters, missing data, or an RLS policy that exposes no rows. Inspect the request identity, privileges, query, and applicable SELECT policies. Check whether the USING expression matches the intended records before changing permissions.

The "Infinite Recursion" Error

A policy can recurse when it queries the table it protects: for example, a policy on teams that checks teams.owner_id through a SELECT on teams. Review indirect dependencies too. A separate user_roles table helps only if its policies do not form another cycle. A SECURITY DEFINER helper also needs the owner and execution safeguards described above.

The "Update Fails Silently" Issue

If an update changes no rows, inspect the query filter, privileges, and applicable row-selection policies. If it fails a new-row check, inspect the effective WITH CHECK condition. To forbid ownership transfers, test a change to owner_id and verify that the effective policy rejects it.

5 Common RLS Mistakes to Avoid

1. Misunderstanding the WITH CHECK Clause

For a FOR UPDATE policy, PostgreSQL uses the USING expression as the new-row check when WITH CHECK is omitted. An ownership condition on user_id therefore also checks the resulting row by default. Write an explicit WITH CHECK when the new-row condition should differ or when it helps readability, and test the combined effect of all applicable policies.

2. Relying on "Hidden" IDs

Some developers think that if they use a long, random UUID for a record, they don't need RLS because "nobody can guess the ID." This is "security by obscurity" and is not a valid defense. If access relies only on possession of that ID, disclosure can expose the resource. Test applicable RLS policies and privileges; knowing a record ID should not authorize access to it.

3. Not Testing as an Anonymous User

Always test your API while logged out. If an unauthenticated request returns records intended to be private, investigate the endpoint, request role, privileges, and RLS policies. You can use our API security best practices guide to set up a robust testing workflow that includes both authenticated and unauthenticated states.

4. Ignoring Junction Tables

For many-to-many relationships (such as team_members linking users to teams), review the link table as well as the entities it connects. For example, a policy on team_members must distinguish permitted membership changes from self-granted access. Unintended reads can disclose membership information; unintended writes can alter access. Enable RLS on exposed tables where row-level restrictions are required, then add only the policies needed for intended access.

5. Over-reliance on TO authenticated

A permissive policy using TO authenticated USING (true) allows matching rows for authenticated roles with the required privileges, subject to applicable restrictive policies. For private user-owned records, compare ownership with auth.uid(); shared or role-based data needs its own access model. Membership in authenticated alone does not establish a right to another user’s private row.

The Ultimate Vibe Coding Security Checklist

Before you ship your AI-built application, run through this checklist to review your database security. Don't assume the AI handled it correctly; verify every step.

• Enable RLS where needed: For Data API tables needing row filtering, including those in the public schema, apply ALTER TABLE ... ENABLE ROW LEVEL SECURITY with the intended policies and privileges.
• Review Permissive Conditions: If a policy uses USING (true), inspect its roles, commands, privileges, and any restrictive policies to establish its actual access.
• Service Role Protection: Are you sure you aren't accidentally using the service_role key in your frontend? This key bypasses RLS entirely and should only be used in secure backend environments or Edge Functions.
• Delete Policies: Verify who has deletion privileges and which policies apply. Test permitted and denied deletes with disposable records.
• Audit Functions: Are your security definer functions set to search_path = '' with schema-qualified table names? Review operators and other referenced objects too; a search-path setting alone is not a complete function-security review.
• Automated Scanning: Have you run a SimplyScan report to check for exposed keys or missing headers? Investigate the supported findings alongside code review and permission tests.

How to Audit Your RLS Policies Automatically

Maintain a table and policy inventory as the schema changes. Record intended access and test cases so new tables and policy interactions are included in the review.

SimplyScan’s free security scanner checks supported public signals, including exposed secrets and selected backend responses. Anonymous URL checks do not verify cross-user access or server-only protections. They do not enumerate or prove the correctness of every database policy.

79% of the 119 live sites SimplyScan has scanned (94 sites) have at least one medium-severity speed issue, such as render-blocking scripts or no route-based code splitting.

The speed statistic above is a separate site-health signal; it does not measure RLS correctness.

If you are building with Cursor or Windsurf, you can also use our MCP server to integrate security scanning directly into your AI coding workflow. Configured checks can help identify detectable RLS regressions; review newly generated schemas and privileges directly as well. Use IDE findings to guide fixes and verify them before committing; a clean check does not establish that the code has no vulnerabilities.

Conclusion

Use RLS where the data-access architecture needs row filtering, and review it alongside privileges and server-side controls. Test both expected access and rejected access after policy changes. A working UI or a passing scan does not establish that private data is protected.

Before deployment, review how protected operations behave under unintended requests. Use a free scan for supported site-health signals, including speed, SEO, and security, and complete the application-specific checks it cannot perform.

Scan your app for RLS issues →