mirror of
https://github.com/supabase/supabase.git
synced 2026-05-07 01:10:15 -04:00
Chris Chinchilla
0e736457c8
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Documentation** * Instructed granting least-privilege table permissions for anon, authenticated, and service roles prior to enabling Row Level Security across multiple guides and quickstarts. * Clarified SQL examples and inline comments, added explicit GRANT steps and RLS SELECT policies, rephrased policy guidance, and adjusted example ordering and section numbering for clearer setup and testing. <!-- end of auto-generated comment: release notes by coderabbit.ai --> --------- Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
587 lines
25 KiB
Plaintext
587 lines
25 KiB
Plaintext
---
|
|
id: 'row-level-security'
|
|
title: 'Row Level Security'
|
|
description: 'Secure your data using Postgres Row Level Security.'
|
|
subtitle: 'Secure your data using Postgres Row Level Security.'
|
|
---
|
|
|
|
When you need granular authorization rules, nothing beats Postgres's [Row Level Security (RLS)](https://www.postgresql.org/docs/current/ddl-rowsecurity.html).
|
|
|
|
## Row Level Security in Supabase
|
|
|
|
<Admonition type="danger">
|
|
|
|
Supabase allows convenient and secure data access from the browser, as long as you enable RLS.
|
|
|
|
RLS _must_ always be enabled on any tables stored in an exposed schema. By default, this is the `public` schema.
|
|
|
|
RLS is enabled by default on tables created with the Table Editor in the dashboard. If you create one in raw SQL or with the SQL editor, remember to enable RLS yourself and grant only the permissions each Postgres role needs.
|
|
|
|
```sql
|
|
GRANT SELECT ON <schema_name>.<table_name> TO anon;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON <schema_name>.<table_name> TO authenticated;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON <schema_name>.<table_name> TO service_role;
|
|
|
|
alter table <schema_name>.<table_name>
|
|
enable row level security;
|
|
```
|
|
|
|
</Admonition>
|
|
|
|
RLS is incredibly powerful and flexible, allowing you to write complex SQL rules that fit your unique business needs. RLS can be combined with [Supabase Auth](/docs/guides/auth) for end-to-end user security from the browser to the database.
|
|
|
|
RLS is a Postgres primitive and can provide "[defense in depth](<https://en.wikipedia.org/wiki/Defense_in_depth_(computing)>)" to protect your data from malicious actors even when accessed through third-party tooling.
|
|
|
|
## Policies
|
|
|
|
[Policies](https://www.postgresql.org/docs/current/sql-createpolicy.html) are Postgres's rule engine. Policies are easy to understand once you get the hang of them. Each policy is attached to a table, and the policy is executed every time a table is accessed.
|
|
|
|
You can just think of them as adding a `WHERE` clause to every query. For example a policy like this ...
|
|
|
|
```sql
|
|
create policy "Individuals can view their own todos."
|
|
on todos for select
|
|
using ( (select auth.uid()) = user_id );
|
|
```
|
|
|
|
.. would translate to this whenever a user tries to select from the todos table:
|
|
|
|
```sql
|
|
select *
|
|
from todos
|
|
where auth.uid() = todos.user_id;
|
|
-- Policy is implicitly added.
|
|
```
|
|
|
|
## Enabling Row Level Security
|
|
|
|
You can enable RLS for any table using the `enable row level security` clause:
|
|
|
|
```sql
|
|
alter table "table_name" enable row level security;
|
|
```
|
|
|
|
Once you have enabled RLS, no data will be accessible via the [API](/docs/guides/api) when using a publishable key, until you create policies.
|
|
|
|
## Auto-enable RLS for new tables
|
|
|
|
If you want RLS enabled automatically for new tables, you can create an event trigger that runs after table creation. This uses a Postgres [event trigger](/docs/guides/database/postgres/event-triggers) to call `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` on each newly created table.
|
|
|
|
```sql
|
|
CREATE OR REPLACE FUNCTION rls_auto_enable()
|
|
RETURNS EVENT_TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = pg_catalog
|
|
AS $$
|
|
DECLARE
|
|
cmd record;
|
|
BEGIN
|
|
FOR cmd IN
|
|
SELECT *
|
|
FROM pg_event_trigger_ddl_commands()
|
|
WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
|
|
AND object_type IN ('table','partitioned table')
|
|
LOOP
|
|
IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
|
|
BEGIN
|
|
EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
|
|
RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
|
|
END;
|
|
ELSE
|
|
RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
|
|
END IF;
|
|
END LOOP;
|
|
END;
|
|
$$;
|
|
|
|
DROP EVENT TRIGGER IF EXISTS ensure_rls;
|
|
CREATE EVENT TRIGGER ensure_rls
|
|
ON ddl_command_end
|
|
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
|
|
EXECUTE FUNCTION rls_auto_enable();
|
|
```
|
|
|
|
Note that this applies to tables created after the trigger is installed. Existing tables still need RLS enabled manually.
|
|
|
|
<Admonition type="caution" label="`auth.uid()` Returns `null` When Unauthenticated">
|
|
|
|
When a request is made without an authenticated user (e.g., no access token is provided or the session has expired), `auth.uid()` returns `null`.
|
|
|
|
This means that a policy like:
|
|
|
|
```sql
|
|
USING (auth.uid() = user_id)
|
|
```
|
|
|
|
will silently fail for unauthenticated users, because:
|
|
|
|
```sql
|
|
null = user_id
|
|
```
|
|
|
|
is always false in SQL.
|
|
|
|
To avoid confusion and make your intention clear, we recommend explicitly checking for authentication:
|
|
|
|
```sql
|
|
USING (auth.uid() IS NOT NULL AND auth.uid() = user_id)
|
|
```
|
|
|
|
</Admonition>
|
|
|
|
## Authenticated and unauthenticated roles
|
|
|
|
Supabase maps every request to one of the roles:
|
|
|
|
- `anon`: an unauthenticated request (the user is not logged in)
|
|
- `authenticated`: an authenticated request (the user is logged in)
|
|
|
|
These are actually [Postgres Roles](/docs/guides/database/postgres/roles). You can use these roles within your Policies using the `TO` clause:
|
|
|
|
```sql
|
|
create policy "Profiles are viewable by everyone"
|
|
on profiles for select
|
|
to authenticated, anon
|
|
using ( true );
|
|
|
|
-- OR
|
|
|
|
create policy "Public profiles are viewable only by authenticated users"
|
|
on profiles for select
|
|
to authenticated
|
|
using ( true );
|
|
```
|
|
|
|
<Admonition type="note" label="Anonymous user vs the anon key">
|
|
|
|
Using the `anon` Postgres role is different from an [anonymous user](/docs/guides/auth/auth-anonymous) in Supabase Auth. An anonymous user assumes the `authenticated` role to access the database and can be differentiated from a permanent user by checking the `is_anonymous` claim in the JWT.
|
|
|
|
</Admonition>
|
|
|
|
## Creating policies
|
|
|
|
Policies are SQL logic that you attach to a Postgres table. You can attach as many policies as you want to each table.
|
|
|
|
Supabase provides some [helpers](#helper-functions) that simplify RLS if you're using Supabase Auth. We'll use these helpers to illustrate some basic policies:
|
|
|
|
### SELECT policies
|
|
|
|
You can specify select policies with the `using` clause.
|
|
|
|
Let's say you have a table called `profiles` in the public schema and you want to enable read access to everyone.
|
|
|
|
```sql
|
|
-- 1. Create table
|
|
create table profiles (
|
|
id uuid primary key,
|
|
user_id uuid references auth.users,
|
|
avatar_url text
|
|
);
|
|
|
|
-- 2. Enable RLS
|
|
alter table profiles enable row level security;
|
|
|
|
-- 3. Create Policy
|
|
create policy "Public profiles are visible to everyone."
|
|
on profiles for select
|
|
to anon -- the Postgres Role (recommended)
|
|
using ( true ); -- the actual Policy
|
|
```
|
|
|
|
Alternatively, if you only wanted users to be able to see their own profiles:
|
|
|
|
```sql
|
|
create policy "User can see their own profile only."
|
|
on profiles
|
|
for select using ( (select auth.uid()) = user_id );
|
|
```
|
|
|
|
### INSERT policies
|
|
|
|
You can specify insert policies with the `with check` clause. The `with check` expression ensures that any new row data adheres to the policy constraints.
|
|
|
|
Let's say you have a table called `profiles` in the public schema and you only want users to be able to create a profile for themselves. In that case, we want to check their User ID matches the value that they are trying to insert:
|
|
|
|
```sql
|
|
-- 1. Create table
|
|
create table profiles (
|
|
id uuid primary key,
|
|
user_id uuid references auth.users,
|
|
avatar_url text
|
|
);
|
|
|
|
-- 2. Enable RLS
|
|
alter table profiles enable row level security;
|
|
|
|
-- 3. Create Policy
|
|
create policy "Users can create a profile."
|
|
on profiles for insert
|
|
to authenticated -- the Postgres Role (recommended)
|
|
with check ( (select auth.uid()) = user_id ); -- the actual Policy
|
|
```
|
|
|
|
### UPDATE policies
|
|
|
|
You can specify update policies by combining both the `using` and `with check` expressions.
|
|
|
|
The `using` clause represents the condition that must be true for the update to be allowed, and `with check` clause ensures that the updates made adhere to the policy constraints.
|
|
|
|
Let's say you have a table called `profiles` in the public schema and you only want users to be able to update their own profile.
|
|
|
|
You can create a policy where the `using` clause checks if the user owns the profile being updated. And the `with check` clause ensures that, in the resultant row, users do not change the `user_id` to a value that is not equal to their User ID, maintaining that the modified profile still meets the ownership condition.
|
|
|
|
```sql
|
|
-- 1. Create table
|
|
create table profiles (
|
|
id uuid primary key,
|
|
user_id uuid references auth.users,
|
|
avatar_url text
|
|
);
|
|
|
|
-- 2. Enable RLS
|
|
alter table profiles enable row level security;
|
|
|
|
-- 3. Create Policy
|
|
create policy "Users can update their own profile."
|
|
on profiles for update
|
|
to authenticated -- the Postgres Role (recommended)
|
|
using ( (select auth.uid()) = user_id ) -- checks if the existing row complies with the policy expression
|
|
with check ( (select auth.uid()) = user_id ); -- checks if the new row complies with the policy expression
|
|
```
|
|
|
|
If no `with check` expression is defined, then the `using` expression will be used both to determine which rows are visible (normal USING case) and which new rows will be allowed to be added (WITH CHECK case).
|
|
|
|
<Admonition type="caution">
|
|
|
|
To perform an `UPDATE` operation, a corresponding [`SELECT` policy](#select-policies) is required. Without a `SELECT` policy, the `UPDATE` operation will not work as expected.
|
|
|
|
</Admonition>
|
|
|
|
### DELETE policies
|
|
|
|
You can specify delete policies with the `using` clause.
|
|
|
|
Let's say you have a table called `profiles` in the public schema and you only want users to be able to delete their own profile:
|
|
|
|
```sql
|
|
-- 1. Create table
|
|
create table profiles (
|
|
id uuid primary key,
|
|
user_id uuid references auth.users,
|
|
avatar_url text
|
|
);
|
|
|
|
-- 2. Enable RLS
|
|
alter table profiles enable row level security;
|
|
|
|
-- 3. Create Policy
|
|
create policy "Users can delete a profile."
|
|
on profiles for delete
|
|
to authenticated -- the Postgres Role (recommended)
|
|
using ( (select auth.uid()) = user_id ); -- the actual Policy
|
|
```
|
|
|
|
### Views
|
|
|
|
Views bypass RLS by default because they are usually created with the `postgres` user. This is a feature of Postgres, which automatically creates views with `security definer`.
|
|
|
|
In Postgres 15 and above, you can make a view obey the RLS policies of the underlying tables when invoked by `anon` and `authenticated` roles by setting `security_invoker = true`.
|
|
|
|
```sql
|
|
create view <VIEW_NAME>
|
|
with(security_invoker = true)
|
|
as select <QUERY>
|
|
```
|
|
|
|
In older versions of Postgres, protect your views by revoking access from the `anon` and `authenticated` roles, or by putting them in an unexposed schema.
|
|
|
|
## Helper functions
|
|
|
|
Supabase provides some helper functions that make it easier to write Policies.
|
|
|
|
### `auth.uid()`
|
|
|
|
Returns the ID of the user making the request.
|
|
|
|
### `auth.jwt()`
|
|
|
|
<Admonition type="caution">
|
|
|
|
Not all information present in the JWT should be used in RLS policies. For instance, creating an RLS policy that relies on the `user_metadata` claim can create security issues in your application as this information can be modified by authenticated end users.
|
|
|
|
</Admonition>
|
|
|
|
Returns the JWT of the user making the request. Anything that you store in the user's `raw_app_meta_data` column or the `raw_user_meta_data` column will be accessible using this function. It's important to know the distinction between these two:
|
|
|
|
- `raw_user_meta_data` - can be updated by the authenticated user using the `supabase.auth.update()` function. It is not a good place to store authorization data.
|
|
- `raw_app_meta_data` - cannot be updated by the user, so it's a good place to store authorization data.
|
|
|
|
The `auth.jwt()` function is extremely versatile. For example, if you store some team data inside `app_metadata`, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs:
|
|
|
|
```sql
|
|
create policy "User is in team"
|
|
on my_table
|
|
to authenticated
|
|
using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams'));
|
|
```
|
|
|
|
<Admonition type="caution">
|
|
|
|
Keep in mind that a JWT is not always "fresh". In the example above, even if you remove a user from a team and update the `app_metadata` field, that will not be reflected using `auth.jwt()` until the user's JWT is refreshed.
|
|
|
|
Also, if you are using Cookies for Auth, then you must be mindful of the JWT size. Some browsers are limited to 4096 bytes for each cookie, and so the total size of your JWT should be small enough to fit inside this limitation.
|
|
|
|
</Admonition>
|
|
|
|
### MFA
|
|
|
|
The `auth.jwt()` function can be used to check for [Multi-Factor Authentication](/docs/guides/auth/auth-mfa#enforce-rules-for-mfa-logins). For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2):
|
|
|
|
```sql
|
|
create policy "Restrict updates."
|
|
on profiles
|
|
as restrictive
|
|
for update
|
|
to authenticated using (
|
|
(select auth.jwt()->>'aal') = 'aal2'
|
|
);
|
|
```
|
|
|
|
## Bypassing Row Level Security
|
|
|
|
Supabase provides special "Service" keys, which can be used to bypass RLS. These should never be used in the browser or exposed to customers, but they are useful for administrative tasks.
|
|
|
|
<Admonition type="note">
|
|
|
|
Supabase will adhere to the RLS policy of the signed-in user, even if the client library is initialized with a Service Key.
|
|
|
|
</Admonition>
|
|
|
|
You can also create new [Postgres Roles](/docs/guides/database/postgres/roles) which can bypass Row Level Security using the "bypass RLS" privilege:
|
|
|
|
```sql
|
|
alter role "role_name" with bypassrls;
|
|
```
|
|
|
|
This can be useful for system-level access. You should _never_ share login credentials for any Postgres Role with this privilege.
|
|
|
|
## RLS performance recommendations
|
|
|
|
Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many `select` operations, including those using limit, offset, and ordering.
|
|
|
|
Based on a series of [tests](https://github.com/GaryAustin1/RLS-Performance), we have a few recommendations for RLS:
|
|
|
|
### Add indexes
|
|
|
|
Make sure you've added [indexes](/docs/guides/database/postgres/indexes) on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on test_table
|
|
to authenticated
|
|
using ( (select auth.uid()) = user_id );
|
|
```
|
|
|
|
You can add an index like:
|
|
|
|
```sql
|
|
create index userid
|
|
on test_table
|
|
using btree (user_id);
|
|
```
|
|
|
|
#### Benchmarks
|
|
|
|
| Test | Before (ms) | After (ms) | % Improvement | Change |
|
|
| --------------------------------------------------------------------------------------------- | ----------- | ---------- | ------------- | -------------------------------------------------------------------------------------------------------- |
|
|
| [test1-indexed](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test1-indexed) | 171 | < 0.1 | 99.94% | <details className="cursor-pointer">Before:<br/>No index<br/><br/>After:<br/>`user_id` indexed</details> |
|
|
|
|
### Call functions with `select`
|
|
|
|
You can use `select` statement to improve policies that use functions. For example, instead of this:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on test_table
|
|
to authenticated
|
|
using ( auth.uid() = user_id );
|
|
```
|
|
|
|
You can do:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on test_table
|
|
to authenticated
|
|
using ( (select auth.uid()) = user_id );
|
|
```
|
|
|
|
This method works well for JWT functions like `auth.uid()` and `auth.jwt()` as well as `security definer` Functions. Wrapping the function causes an `initPlan` to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row.
|
|
|
|
<Admonition type="caution">
|
|
|
|
You can only use this technique if the results of the query or function do not change based on the row data.
|
|
|
|
</Admonition>
|
|
|
|
#### Benchmarks
|
|
|
|
| Test | Before (ms) | After (ms) | % Improvement | Change |
|
|
| --------------------------------------------------------------------------------------------------------------------------------- | ----------- | ---------- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| [test2a-wrappedSQL-uid](<https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test2a-wrappedSQL-uid()>) | 179 | 9 | 94.97% | <details className="cursor-pointer">Before:<br/>`auth.uid() = user_id` <br/><br/>After:<br/> `(select auth.uid()) = user_id`</details> |
|
|
| [test2b-wrappedSQL-isadmin](<https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test2b-wrappedSQL-isadmin()>) | 11,000 | 7 | 99.94% | <details className="cursor-pointer">Before:<br/>`is_admin()` _table join_<br/><br/>After:<br/>`(select is_admin())` _table join_</details> |
|
|
| [test2c-wrappedSQL-two-functions](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test2c-wrappedSQL-two-functions) | 11,000 | 10 | 99.91% | <details className="cursor-pointer">Before:<br/>`is_admin() OR auth.uid() = user_id`<br/><br/>After:<br/>`(select is_admin()) OR (select auth.uid() = user_id)`</details> |
|
|
| [test2d-wrappedSQL-sd-fun](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test2d-wrappedSQL-sd-fun) | 178,000 | 12 | 99.993% | <details className="cursor-pointer">Before:<br/>`has_role() = role` <br/><br/>After:<br/>(select has_role()) = role</details> |
|
|
| [test2e-wrappedSQL-sd-fun-array](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test2e-wrappedSQL-sd-fun-array) | 173000 | 16 | 99.991% | <details className="cursor-pointer">Before:<br/>`team_id=any(user_teams())` <br/><br/>After:<br/>team_id=any(array(select user_teams()))</details> |
|
|
|
|
### Add filters to every query
|
|
|
|
Policies are "implicit where clauses," so it's common to run `select` statements without any filters. This is a bad pattern for performance. Instead of doing this (JS client example):
|
|
|
|
{/* prettier-ignore */}
|
|
```js
|
|
const { data } = supabase
|
|
.from('table')
|
|
.select()
|
|
```
|
|
|
|
You should always add a filter:
|
|
|
|
{/* prettier-ignore */}
|
|
```js
|
|
const { data } = supabase
|
|
.from('table')
|
|
.select()
|
|
.eq('user_id', userId)
|
|
```
|
|
|
|
Even though this duplicates the contents of the Policy, Postgres can use the filter to construct a better query plan.
|
|
|
|
#### Benchmarks
|
|
|
|
| Test | Before (ms) | After (ms) | % Improvement | Change |
|
|
| ------------------------------------------------------------------------------------------------- | ----------- | ---------- | ------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| [test3-addfilter](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test3-addfilter) | 171 | 9 | 94.74% | <details className="cursor-pointer">Before:<br/>`auth.uid() = user_id`<br/><br/>After:<br/>add `.eq` or `where` on `user_id`</details> |
|
|
|
|
### Use security definer functions
|
|
|
|
A "security definer" function runs using the same role that _created_ the function. This means that if you create a role with a superuser (like `postgres`), then that function will have `bypassrls` privileges. For example, if you had a policy like this:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on test_table
|
|
to authenticated
|
|
using (
|
|
exists (
|
|
select 1 from roles_table
|
|
where (select auth.uid()) = user_id and role = 'good_role'
|
|
)
|
|
);
|
|
```
|
|
|
|
We can instead create a `security definer` function which can scan `roles_table` without any RLS penalties:
|
|
|
|
```sql
|
|
create function private.has_good_role()
|
|
returns boolean
|
|
language plpgsql
|
|
security definer -- will run as the creator
|
|
as $$
|
|
begin
|
|
return exists (
|
|
select 1 from roles_table
|
|
where (select auth.uid()) = user_id and role = 'good_role'
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
-- Update our policy to use this function:
|
|
create policy "rls_test_select"
|
|
on test_table
|
|
to authenticated
|
|
using ( (select private.has_good_role()) );
|
|
```
|
|
|
|
<Admonition type="caution">
|
|
|
|
Security-definer functions should never be created in a schema in the "Exposed schemas" inside your [API settings](/dashboard/project/_/settings/api)`.
|
|
|
|
</Admonition>
|
|
|
|
### Minimize joins
|
|
|
|
You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an `IN` or `ANY` operation in your filter.
|
|
|
|
For example, this is an example of a slow policy which joins the source `test_table` to the target `team_user`:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on test_table
|
|
to authenticated
|
|
using (
|
|
(select auth.uid()) in (
|
|
select user_id
|
|
from team_user
|
|
where team_user.team_id = team_id -- joins to the source "test_table.team_id"
|
|
)
|
|
);
|
|
```
|
|
|
|
We can rewrite this to avoid this join, and instead select the filter criteria into a set:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on test_table
|
|
to authenticated
|
|
using (
|
|
team_id in (
|
|
select team_id
|
|
from team_user
|
|
where user_id = (select auth.uid()) -- no join
|
|
)
|
|
);
|
|
```
|
|
|
|
In this case you can also consider [using a `security definer` function](#use-security-definer-functions) to bypass RLS on the join table:
|
|
|
|
<Admonition type="note">
|
|
|
|
If the list exceeds 1000 items, a different approach may be needed or you may need to analyze the approach to ensure that the performance is acceptable.
|
|
|
|
</Admonition>
|
|
|
|
#### Benchmarks
|
|
|
|
| Test | Before (ms) | After (ms) | % Improvement | Change |
|
|
| --------------------------------------------------------------------------------------------------- | ----------- | ---------- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
|
|
| [test5-fixed-join](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test5-fixed-join) | 9,000 | 20 | 99.78% | <details className="cursor-pointer">Before:<br/>`auth.uid()` in table join on col<br/><br/>After:<br/>col in table join on `auth.uid()`</details> |
|
|
|
|
### Specify roles in your policies
|
|
|
|
Always use the Role of inside your policies, specified by the `TO` operator. For example, instead of this query:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on rls_test
|
|
using ( auth.uid() = user_id );
|
|
```
|
|
|
|
Use:
|
|
|
|
```sql
|
|
create policy "rls_test_select" on rls_test
|
|
to authenticated
|
|
using ( (select auth.uid()) = user_id );
|
|
```
|
|
|
|
This prevents the policy `( (select auth.uid()) = user_id )` from running for any `anon` users, since the execution stops at the `to authenticated` step.
|
|
|
|
#### Benchmarks
|
|
|
|
| Test | Before (ms) | After (ms) | % Improvement | Change |
|
|
| --------------------------------------------------------------------------------------------- | ----------- | ---------- | ------------- | -------------------------------------------------------------------------------------------------------------------------------- |
|
|
| [test6-To-role](https://github.com/GaryAustin1/RLS-Performance/tree/main/tests/test6-To-role) | 170 | < 0.1 | 99.78% | <details className="cursor-pointer">Before:<br/>No `TO` policy<br/><br/>After:<br/>`TO authenticated` (anon accessing)</details> |
|
|
|
|
## More resources
|
|
|
|
- [Testing your database](/docs/guides/database/testing)
|
|
- [RLS Guide and Best Practices](https://github.com/orgs/supabase/discussions/14576)
|
|
- Community repo on testing RLS using [pgTAP and dbdev](https://github.com/usebasejump/supabase-test-helpers/tree/main)
|