Lock Down Your Data: Implement Row-Level Security Policies in Supabase SQL

Data breaches are everywhere these days. The numbers aren't encouraging. The chart below (based on data from Statista) shows that data compromises since 2020 have almost tripled:

I realized that if I didn't want my product ending up on lists like this, I'd need multiple security measures and multiple layers. If one gets breached, there should be another protecting user data.

Row-level security (RLS) in Supabase lets me define security policies directly in my database. These policies restrict what users can access, so even if there's a vulnerability in my API or web app that could expose data to bad actors, the database itself acts as a final line of defense.

In this guide, I'll walk through how RLS policies work, how to construct them, some advanced use cases, and how to write tests to make sure your policies are locked down before going to production.

Understanding RLS

RLS is a feature of Supabase's underlying database provider, Postgres. It allows you to define access policies for different types of queries on a table. These policies are executed every time a query is executed against that table. Normally, you would restrict access to data in an API endpoint and add the restrictions to the query directly, but adding the policy to the database ensures that the policy will always run.

RLS can provide defense in depth, a security concept where there are multiple layers of security. It's also enforced on the database, which means it can limit the blast radius of a leaked access token, restricting access to only the data belonging to the user who originally received that token.

In addition to providing access control at a lower level than the API, RLS works by essentially adding a WHERE clause to queries on a specific table. This can greatly simplify queries for multitenant apps (where companies have multiple users that can access their data) as you can write queries without thinking about which tenant is logged in. You can also allow users to write custom queries for dashboards or their own internal tools, and the RLS policies will ensure they only get data that they're supposed to see.

RLS Example Database: Recipe-Sharing Web App

This article uses a recipe-sharing web application as an example to explore creating a variety of different policies. The comments and recipes tables both reference the users table by its user_id field. This field is used in policies to make sure the correct user is given access to modify or delete recipes and comments. The simplified schema diagram below will help you follow along:

Using RLS in Supabase

When you create a table through the Supabase dashboard, RLS is enabled by default. If you aren't building your application through ClickOps, though, you'll probably be creating your tables using an SQL query. The following example creates a table named recipes and enables RLS:

-- Create the recipes table
CREATE TABLE recipes (
    ...
);

-- Enable RLS for recipes table
ALTER TABLE recipes ENABLE ROW LEVEL SECURITY;

When you've enabled RLS but haven't created the policies that it will apply, it will give authenticated users full access to your database and block all access from anonymous users. To understand this, you need to know that Supabase maps every request to one of two roles:

• anon (anonymous): The user is not logged in.
• authenticated: The user is logged in.

When you have a table with RLS enabled but no policies created, Supabase will block all requests made using the anon role by default. Blocking anonymous requests by default will help prevent accidental data leaks by not giving public access to your whole table.

Basic Components of SQL Policies for RLS

If you want to move beyond blocking anonymous access and allowing any authenticated user to access any data, you'll need to define some policies.

There are five parts to an RLS policy that you'll use to configure access to your database. You can get more detailed information from the Postgres Create Policy documentation.

• Name
• Table
• Command
• Role
• Conditions

Name

The policy name is used to refer to the policy after it's created. It's a good idea to create a naming convention to be used by all policies and be descriptive about what the policy should do. Using a consistent naming policy will make it easier to maintain and test as your database grows.

The policy name is provided immediately after the CREATE POLICY statement. Below is an example:

CREATE POLICY "Allow anyone to view public recipes"

Table

You've told Postgres that you want to create a policy and what it's called, but it also needs to be associated with a table. Using the ON keyword assigns the policy to a specific table:

ON recipes

Command

You also need to define which operations this policy applies to. You can specify the standard CRUD operations: SELECT, INSERT, UPDATE, or DELETE.

By default, policies use ALL and apply the policy to every operation type, but writing a policy for each operation gives you more control over how users can access or modify data. For example, you might allow users to UPDATE their own profile information while restricting INSERT and DELETE operations to system-level processes only.

To specify which command your policy applies to, use the FOR keyword followed by a single command or the ALL keyword:

FOR SELECT

Role

As mentioned above, Supabase comes with two roles by default: anon and authenticated. Supabase uses Postgres as its database provider, which has its own PUBLIC role applied to all other roles. By default, new policies will be applied to the Postgres PUBLIC role, which, in turn, applies to both the anon and authenticated roles.

Most of the time, you'll want to use the authenticated role for your policies, which ensures that an authenticated user is making the request.

If you want to create a policy that applies only to authenticated users, specify it with the TO keyword:

TO authenticated

Conditions

Every part of the CREATE POLICY statement up to this point (besides giving it a name) has been to determine when to apply the policy. The next step is defining the actual policy with some conditions.

There are two different condition types that you can add to a policy. You can add a filter to restrict which row can be selected, updated, or deleted, and you add a check on data being passed to inserts and updates.

Filtering Results (USING):

Restricting which rows a command can read or update is done with the USING keyword followed by a Boolean expression. For example, to give authenticated users access to recipes they created, you can use the following expression.

USING (user_id = (SELECT auth.uid()))

You may have noticed the auth.uid() function in the snippet above. This is a helper function provided by Supabase that, as you probably guessed, returns the ID of the user making the request. Supabase also provides another helper ,auth.jwt(), that provides more details about the user.

Validating Inputs (WITH CHECK):

To restrict the data being saved to the database through INSERT and UPDATE operations, use WITH CHECK followed by a Boolean expression.

In the recipe app example, you would want to add a check to validate whether the user_id field matches the ID of the user sending the request.

WITH CHECK (user_id = (SELECT auth.uid()))

To clarify when you can use each type of condition, here's a table showing when they apply:

Command	USING	WITH CHECK
SELECT	Yes	No
DELETE	Yes	No
INSERT	No	Yes
UPDATE	Yes	Yes

Creating a SELECT Policy

Now that you've seen the basic components of an RLS policy, you can put it all together to create an actual policy. The example application needs to allow authenticated users to view recipes that they've created.

On a SELECT policy, there's no data to validate, so you don't need a WITH CHECK, just a filter with the USING statement:

CREATE POLICY "Allow authenticated users to view their own recipes" ON recipes
    FOR SELECT
    TO authenticated
    USING (user_id = (SELECT auth.uid()))

Using auth.uid() inside a SELECT statement like this ensures the function is only executed once and cached instead of being executed for every row.

Creating INSERT/UPDATE Policies

Your users can now see all of their own recipes, but you'll also need a policy to stop them from accessing or altering data they shouldn't—for example, creating a recipe with a different user's ID or a null user ID.

INSERT and UPDATE policies use the WITH CHECK to verify the data being sent is valid. In this case, you just need to make sure the user_id matches the ID of the currently authenticated user.

CREATE POLICY "Allow authenticated users to create recipes" ON recipes
    FOR INSERT
    TO authenticated
    WITH CHECK (user_id = (SELECT auth.uid()))

Advanced RLS Policies for Real-World Use Cases

Beyond restricting a user to viewing items that they created, you may need more advanced policies—for example, if you're using a multitenant application or require role-based access or conditional policies.

Multitenant Applications

If you have a multitenant application, you need to restrict access to the user's tenant so they can't browse your other customers' data. To prevent this, you'll create records for the tenants and tie each user to a specific tenant. To set up a multitenant schema, create a tenant table and then use Supabase's updateUserById to set tenant_id in the user's app_metadata.

Since the tenant_id is a restricted field that users can't modify, you should store it in the app_metadata. This ensures that users can't insert some other tenant_id in their user metadata and start browsing other tenants' data.

Next, similar to adding a user_id to recipe records to restrict access, you need to add a tenant_id to the tables instead. This will allow you to connect records to a tenant instead of a specific user. In the policy check, add the tenant_id in app_metadata. Ideally, you can create an auth.tenant_id() function that queries the user's JSON Web Token (JWT) claims to get the tenant_id. This function can be reused in your policies:

CREATE OR REPLACE FUNCTION auth.tenant_id()
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (SELECT auth.jwt() -> 'app_metadata' ->> 'tenant_id');
END;
$$;

A more detailed guide was published by Ryan O'Neill on his Substack.

Role-Based Access Control (RBAC)

If you have an application where a lot of users will be accessing the same data but some users will have more access than others, then you'll want to set up role-based access. While Supabase doesn't have support for this built in.

To set up RBAC with Supabase, you first create a user_roles and a role_permissions table. The role_permissions table defines roles and their permissions using an enum type, and the user_roles table ties a user_id to an app_role enum. Once these tables are created, you can define your roles and permissions and assign roles to users.

To enforce these roles, you need to ensure they're available when a user runs a query. To do that, create a custom-access token hook. This hook will be executed when Supabase generates a user access token. Overriding this hook allows you to query user_roles and add a claim to the token.

Now that you have the user's role in the JWT, you can access it when writing RLS policies:

select (auth.jwt() ->> 'user_role') into user_role

Conditional Policies

There may be times when you want to restrict data access in other ways besides simply assigning which user/role has access to which records. For example, you can make records noneditable after a certain period of time or use pg_catalog.inet_client_addr() to restrict access to your company's network. In the recipe example above, public recipes have a published_at timestamp. You can create a conditional policy to keep these recipes from being visible until after the timestamp has passed:

CREATE POLICY "Allow anonymous users to view public recipes" ON recipes
    FOR SELECT
    TO anon
    USING (is_public = true AND (published_at IS NULL OR published_at <= (SELECT NOW())))

Testing and Validating RLS Implementation

One of the trade-offs of using RLS policies is that you're adding complexity to every query on a table that isn't obvious, which can impact performance. Also, if you're writing queries using the default postgres role in Supabase's query editor, the RLS policies won't be applied. To troubleshoot these issues, use the Postgres EXPLAIN ANALYZE command to see how the RLS policies are being executed. If you want to actually see the policy being executed, you'll need to run the query as an application user.

While you're writing your RLS policies, you can use Supabase's impersonate feature in the dashboard to run queries as specific users. To do this, open the SQL Editor in the Supabase dashboard and click on the Role drop-down option next to the green Run button. A dialog will pop up that allows you to use the anon role or select any user for the authenticated role:

Now you can run any query and it'll use a JWT for the user you selected. Try it out using SELECT auth.jwt();.

Validating with Unit Tests

Once you've written your policies, you can dive into setting up tests using pgTAP and running the supabase test db command. This combination can be used to write unit tests so you can make sure your policies work as expected before deploying them to production.

The most basic test that you may want to add is to verify which policies are applied to a certain table. To verify the RLS policies that are applied to the recipes table, create a new test file using the command below:

supabase test new recipes_rls

This will create the file supabase/tests/recipes_rls_test.sql, with some boilerplate code to roll back any changes made during testing. Next, add a call to the policies_are function. It takes the schema (public), the table name (recipes), and an array of policy names that should be applied to the table. Add the following SELECT statement to recipes_rls_test.sql:

SELECT policies_are(
  'public',
  'recipes',
  ARRAY [
    'Allow anonymous users to view published public recipes',
    'Allow authenticated users to create recipes',
    'Allow authenticated users to delete their own recipes',
    'Allow authenticated users to update their own recipes',
    'Allow authenticated users to view accessible recipes',
    'Allow moderators to delete public published recipes',
    'Allow moderators to update public published recipes'
  ]
);

Now, to run your database tests, execute supabase test db and you'll see all of your tests pass successfully:

$ supabase test db
Connecting to local database...
./recipes_rls_test.sql .. ok
All tests successful.
Files=1, Tests=1,  0 wallclock secs ( 0.02 usr  0.01 sys +  0.01 cusr  0.00 csys =  0.04 CPU)
Result: PASS

You can write additional tests to verify that your policies work correctly for different user roles and database operations. You can also write tests that try different CRUD operations to test your policies. For more details, see Supabase's documentation.

Troubleshooting Common Issues

If you get your policies set up and you start running into issues it can be difficult to troubleshoot. It's especially frustrating when you run a query in the Supabase console and verify that it's working, but it just returns nothing (or an error) when your app tries to run the same query. This is a typical RLS misconfiguration.

This section covers some of the more common RLS configuration issues and how to fix them.

Anonymous Data Access Issues

If you have an app that lets users view some data anonymously, like publicly shared recipes, there are a few things that may cause your anonymous queries to return empty.

The first thing you should check is to make sure you created a role TO anon. Without this, Supabase will use its default policy which will deny all access.

If you do have a policy that allows anon to access the data that you're after, make sure it's not using auth.uid(). When auth.uid() is executed for an anonymous user, it will return NULL. So, if your USING query is checking that auth.uid() equals a table column, then you won't get any results back from your query.

Server-Side Rendering

If you're using a server-side rendering framework like Next.js, you may run into an issue where you get empty results for logged in users. This is a common issue that shows up when you don't forward the user's authentication headers to Supabase.

When this happens, Supabase will see an unauthenticated request and apply the anon policies to the request.

The solution is to make sure you're making requests to Supabase on behalf of the user. This can be easily done using Supabase's SSR library.

Recursive RLS Policies

When a policy's query looks up another table, that table's policies will be applied to the USING query of the first policy. This can go several levels deep, and will cause the policy to fail if this chain of policies references a policy that's already being executed. This will result in the error ERROR: 42P17: infinite recursion detected in policy for relation "teams".

To illustrate this issue I'll use a simple database schema:

erDiagram
    users ||--o{ team_membership : "is member of"
    teams ||--o{ team_membership : "has member"

The teams table has a policy that ensures a user can only see teams they are a member of, by querying the team_membership table. Meanwhile, team_membership has a policy that checks the referenced team by querying the teams table again, creating a loop.

graph TD;
    QT(Query teams)
    QTM[Query team_membership]

    QT--Is User a Team Member?-->QTM
    QTM--Does Team Exist?-->QT

Fortunately, this will cause an error that explicitly notifies you of the infinite recursion. Once you see this error, you'll want to refactor your RLS policy to call a security definer function.

Security definer functions are special functions that run with the permissions of the user that created the function. This means you can refactor your RLS policies to call a function, and that function will avoid any future RLS policy checks.

Here's an example function that would resolve the recursive issue in the above example:

CREATE OR REPLACE FUNCTION is_member_of_team(team_id_to_check bigint)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER -- Run as function creator
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM team_memberships
    WHERE team_id = team_id_to_check
    AND user_id = auth.uid()
  );
END
$$;

CREATE POLICY "Users can see teams they are members of"
ON teams FOR SELECT
TO authenticated
USING ( is_member_of_team(id) ); -- No more recursion!

For more details, see the Supabase documentation.

Conclusion

Well-written RLS policies can prevent data leaks, ensuring your customers are safe. After reading this article, you should now know how these policies are built, some advanced use cases, and how to write tests to ensure they're working as expected.

Remember that RLS policies add an extra layer of security but shouldn't be your only defense. They work best as part of a wider security strategy that includes proper authentication, input validation, and regular security audits. As your application grows and evolves, make sure to regularly review and update your policies to ensure they continue to protect your users' data effectively.