database/row-level-security

35 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Row Level Security protects frontend access with publishable key

When using the Data API with a publishable key from the frontend, Row Level Security (RLS) must be enabled on tables to protect data. Access permission is checked against RLS policies and the user's JSON Web Token (JWT), which is automatically sent by Supabase client libraries if the user is logged in using Supabase Auth. The publishable key is safe to expose when paired with RLS and least-privilege grants.

Service role and secret keys bypass RLS and must never be exposed frontend

Unlike the publishable key, service role keys and secret keys bypass Row Level Security entirely and are never safe to expose on the frontend. These keys must only be used on the backend, treated as secrets, and should be imported as sensitive environment variables rather than hardcoded.

Frontend security requirements for Data API access

To keep data secure when accessing the Data API from the frontend, you must: (1) Turn on Row Level Security for your tables and properly configure access policies to grant only necessary least privileges; (2) Use your Supabase publishable key when creating a Supabase client.

View security definer vs invoker

By default, views are accessed with their creator's permission (security_definer). To enforce row-level security policies, define the view with the security_invoker modifier. Use: `create view <view_name> with(security_invoker=true) as (select * from <table>);` or alter an existing view with: `alter view <view_name> set (security_invoker = true);`.

RLS must be enabled on tables in exposed schemas

Supabase allows secure data access from the browser only when RLS is enabled. 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, but if you create a table in raw SQL or with the SQL editor, you must enable RLS yourself.

Enable RLS syntax

To enable Row Level Security on a table, use the SQL command: ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Grant permissions when enabling RLS

When enabling RLS on a table, grant appropriate permissions to the anon, authenticated, and service_role Postgres roles. Example: 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;

RLS policies act as implicit WHERE clauses

Policies are Postgres's rule engine for RLS. Each policy is attached to a table and is executed every time the table is accessed. A policy can be thought of as adding a WHERE clause to every query. For example, a policy checking if auth.uid() equals user_id will implicitly filter query results to only rows where this condition is true.

CREATE POLICY syntax for SELECT

SELECT policies use the USING clause to specify which rows are visible. Syntax: CREATE POLICY "policy_name" ON table_name FOR SELECT TO role_name USING (condition);

CREATE POLICY syntax for INSERT

INSERT policies use the WITH CHECK clause to ensure new rows adhere to policy constraints. Syntax: CREATE POLICY "policy_name" ON table_name FOR INSERT TO role_name WITH CHECK (condition);

CREATE POLICY syntax for UPDATE

UPDATE policies require both USING and WITH CHECK clauses. USING checks if the existing row complies with the policy, and WITH CHECK ensures the new row complies with the policy. Syntax: CREATE POLICY "policy_name" ON table_name FOR UPDATE TO role_name USING (condition) WITH CHECK (condition);

UPDATE policies require a corresponding SELECT policy

To perform an UPDATE operation, a corresponding SELECT policy is required. Without a SELECT policy, the UPDATE operation will not work as expected.

CREATE POLICY syntax for DELETE

DELETE policies use the USING clause to specify which rows can be deleted. Syntax: CREATE POLICY "policy_name" ON table_name FOR DELETE TO role_name USING (condition);

auth.uid() returns user ID or null when unauthenticated

The auth.uid() helper function returns the ID of the user making the request. When a request is made without an authenticated user (no access token or expired session), auth.uid() returns null. This means a policy using USING (auth.uid() = user_id) will silently fail for unauthenticated users because null = user_id is always false in SQL.

Best practice: explicitly check for authentication in policies

To avoid confusion when auth.uid() returns null for unauthenticated users, explicitly check for authentication in policies: USING (auth.uid() IS NOT NULL AND auth.uid() = user_id) instead of just USING (auth.uid() = user_id).

Postgres roles mapped to requests

Supabase maps every request to one of two Postgres roles: anon (unauthenticated request, user not logged in) or authenticated (authenticated request, user is logged in). These roles are used in policies with the TO clause.

auth.jwt() helper function

The auth.jwt() function returns the JWT of the user making the request. Anything stored in the user's raw_app_meta_data column or raw_user_meta_data column is accessible using this function. raw_user_meta_data can be updated by the authenticated user and should not store authorization data. raw_app_meta_data cannot be updated by the user and is suitable for storing authorization data.

JWT is not always fresh in auth.jwt()

A JWT retrieved with auth.jwt() is not always fresh. If you remove a user from a team and update the app_metadata field, that change will not be reflected using auth.jwt() until the user's JWT is refreshed. Additionally, when using Cookies for Auth, be mindful of JWT size as some browsers are limited to 4096 bytes per cookie.

MFA enforcement using auth.jwt()

You can use auth.jwt() to check for Multi-Factor Authentication. For example, restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2): CREATE POLICY "Restrict updates." ON profiles AS RESTRICTIVE FOR UPDATE TO authenticated USING ((select auth.jwt()->>'aal') = 'aal2');

Service keys bypass RLS

Supabase provides special Service keys that can bypass RLS. These should never be used in the browser or exposed to customers, but are useful for administrative tasks.

Service key RLS exception

Even when a client library is initialized with a Service Key, Supabase will adhere to the RLS policy of the signed-in user.

Create Postgres role to bypass RLS

You can create a Postgres role with the bypassrls privilege to bypass Row Level Security: ALTER ROLE role_name WITH BYPASSRLS; This is useful for system-level access but credentials should never be shared.

RLS performance: add indexes on policy columns

Performance impact from RLS is important to consider. Add indexes on any columns used within policies that are not already indexed or primary keys. For a policy checking auth.uid() = user_id, create an index: CREATE INDEX userid ON test_table USING btree (user_id); This can provide 99%+ performance improvements.

RLS performance: wrap functions in SELECT

To improve policy performance with functions like auth.uid() or auth.jwt(), wrap them in a SELECT statement. Instead of: USING (auth.uid() = user_id), use: USING ((select auth.uid()) = user_id). This causes Postgres to run an initPlan which caches results per-statement rather than calling the function on each row. This technique only works if query/function results do not change based on row data. Performance improvement can be 95-99.99% depending on the function.

RLS performance: add filters to every query

Always add explicit filters to queries even though policies act as implicit WHERE clauses. Instead of: supabase.from('table').select(), use: supabase.from('table').select().eq('user_id', userId). This allows Postgres to construct a better query plan. Performance improvement is typically around 95%.

RLS performance: use security definer functions

A security definer function runs using the same role that created it. If created by a superuser, it has bypassrls privileges. This allows the function to scan tables without RLS penalties. Security definer functions should never be created in a schema listed in API settings' Exposed schemas.

RLS performance: minimize joins

Rewrite policies to avoid joins between source and target tables. Instead of joining, fetch relevant data from the target table into an array or set, then use IN or ANY operations. For example, instead of selecting user_id from team_user where team_user.team_id = test_table.team_id (joins to source), select team_id from team_user where user_id = auth.uid() (no join). This can provide 99%+ performance improvements.

RLS performance: specify roles in policies

Always use the TO operator to specify the role in policies. Instead of: CREATE POLICY "rls_test_select" ON rls_test USING (auth.uid() = user_id), use: CREATE POLICY "rls_test_select" ON rls_test TO authenticated USING ((select auth.uid()) = user_id). This prevents the policy from running for anon users. Performance improvement can be 99%+ when anon users attempt access.

Views bypass RLS by default with security definer

Views bypass RLS by default because they are usually created with the postgres user, which automatically creates views with security definer. In Postgres 15+, make a view obey RLS policies of underlying tables by setting security_invoker = true: CREATE VIEW view_name WITH(security_invoker = true) AS SELECT query;

Views in older Postgres: protect with schema or revoke access

In Postgres versions older than 15, protect views from bypassing RLS by either revoking access from anon and authenticated roles, or by putting views in an unexposed schema.

Auto-enable RLS for new tables with event trigger

Create an event trigger to automatically enable RLS on newly created tables. This uses a Postgres event trigger to call ALTER TABLE ... ENABLE ROW LEVEL SECURITY on each new table. The trigger only applies to tables created after the trigger is installed; existing tables need RLS enabled manually.

Event trigger for auto-enabling RLS checks schema

The RLS auto-enable event trigger includes logic to check if the schema is in an enforced list (like 'public') and excludes system schemas like pg_catalog, information_schema, pg_toast, and pg_temp before enabling RLS.

Do not use user_metadata in RLS policies

Not all information in the JWT should be used in RLS policies. Creating an RLS policy that relies on the user_metadata claim can create security issues because this information can be modified by authenticated end users.

Anonymous user versus anon Postgres role

Using the anon Postgres role is different from an anonymous user 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.

WITH CHECK expression behavior for UPDATE

For UPDATE policies, if no WITH CHECK expression is defined, then the USING expression will be used both to determine which rows are visible and which new rows will be allowed to be added.

Give your agent this brain