row-level security & policies

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

Tables without RLS exposed in Data API can be accessed by any matching role

Tables and views exposed through the Data API without RLS enabled can be accessed by any role with matching grants. Enable RLS or add equivalent controls to prevent unauthorized access.

Data API access control has two layers: Grants and RLS

The Data API works with two layers of Postgres access control. Grants determine which Postgres roles can reach a table, view, or function over the Data API (roles include anon, authenticated, and service_role). Row Level Security (RLS) policies determine which rows those roles can read or modify. Grants control whether a role can access an object, while RLS controls which rows the role can access. Use both controls for every exposed object.

Default privileges on existing projects expose new objects automatically

On existing Supabase projects, tables created in the public schema automatically receive SELECT, INSERT, UPDATE, and DELETE privileges for anon, authenticated, and service_role. Functions automatically receive EXECUTE privileges. These grants make new objects reachable through the Data API even when not intended to be exposed. Supabase is changing the platform default to revoke these automatic grants so exposure becomes opt-in. The internal supabase_admin role grants these permissions but cannot authenticate through the Data API.

Revoke default privileges SQL statements

To prevent automatic grants on new objects in the public schema, execute the following SQL statements: ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM anon, authenticated, service_role; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM anon, authenticated, service_role; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE USAGE, SELECT ON SEQUENCES FROM anon, authenticated, service_role; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM public. After running these statements, new tables, functions, and sequences require explicit grants before Data API roles can access them.

Grant specific privileges per role using GRANT statements

Grant the minimum privileges each role needs for Data API access. For tables: GRANT SELECT ON TABLE public.your_table TO anon for read-only access by anonymous clients; GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.your_table TO authenticated for full access by signed-in users (RLS still applies); GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.your_table TO service_role for full access by server-side code using the service role. For functions: GRANT EXECUTE ON FUNCTION public.your_function() TO anon, authenticated to specify which roles can call them. If a required grant is missing, PostgREST returns a 42501 error with a hint naming the exact GRANT statement needed.

Enable RLS on tables exposed through Data API

Enable Row Level Security on every table and view exposed through the Data API. Tables created through the Supabase Dashboard have RLS enabled by default. For tables created in the SQL Editor or through another tool, enable RLS explicitly with the SQL statement: ALTER TABLE your_table ENABLE ROW LEVEL SECURITY. After enabling RLS, create policies that control which data users can access and update based on their authentication token.

Pre-request checks for API security beyond RLS

RLS policies do not cover every API security requirement. Add pre-request checks for requirements such as enforcing per-IP or per-user rate limits, checking custom or additional API keys before allowing access, rejecting requests after exceeding a quota or requiring payment, and disallowing direct access to certain tables, views, or functions in exposed schemas. A Postgres pre-request function reads request information and performs these checks before serving a response, such as counting requests or verifying an API key.

Access request information in pre-request functions

Use the Postgres current_setting() function to access request information in pre-request checks. Available settings are: request.method (example values: GET, HEAD, POST, PUT, PATCH, DELETE), request.path (values like table, view, or rpc/function), request.headers (JSON object of request headers), request.cookies (JSON object of request cookies), request.jwt (JSON object of JWT payload). Use current_setting('request.headers', true)::json to get all headers as JSON, or current_setting('request.headers', true)::json->>'user-agent' to access individual headers. To access client IP address, look up the X-Forwarded-For header: split_part(current_setting('request.headers', true)::json->>'x-forwarded-for', ',', 1) takes the client IP before the first comma.

Pre-request function error response with custom HTTP status

A pre-request function can raise an exception to stop a request and return a custom HTTP response. Use: RAISE SQLSTATE 'PGRST' USING message = json_build_object('code', '123', 'message', 'Payment Required', 'details', 'Quota exceeded', 'hint', 'Upgrade your plan')::text, detail = json_build_object('status', 402, 'headers', json_build_object('X-Powered-By', 'Nerd Rage'))::text; This produces an HTTP 402 Payment Required response with the specified headers and JSON body. Use the status_text key in the detail clause when using a custom HTTP status code such as 419.

Create and register a pre-request function

To add pre-request checks: First, create a function with CREATE FUNCTION public.check_request() RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN -- your logic here END; $$;. Then register it to run on every Data API request with ALTER ROLE authenticator SET pgrst.db_pre_request = 'public.check_request';. Finally, reload the PostgREST configuration with NOTIFY pgrst, 'reload config';. The function will then run before every Data API request.

Rate limiting per IP using pre-request function

To rate-limit write requests (POST, PUT, PATCH, DELETE) by IP address: First create table private.rate_limits (ip inet, request_at timestamp) with index CREATE INDEX rate_limits_ip_request_at_idx ON private.rate_limits (ip, request_at DESC). Then create a pre-request function that extracts the IP from X-Forwarded-For header, counts requests from that IP in the last 5 minutes, and raises SQLSTATE 'PGRST' with HTTP 420 status if count exceeds 100. Insert the IP and current timestamp into private.rate_limits. Note: GET and HEAD requests cannot be rate-limited as they run in read-only mode and may be served by Read Replicas. Register with ALTER ROLE authenticator SET pgrst.db_pre_request = 'public.check_request' and NOTIFY pgrst, 'reload config'.

Additional API keys validation in pre-request function

To require additional application-managed API keys for anon role access: Create table private.anon_api_keys (id uuid primary key, --other fields). In the pre-request function, check if jwt_role is 'anon' by reading current_setting('request.jwt.claims', true)::json->>'role'. If it is anon, extract the x-app-api-key header with current_setting('request.headers', true)::json->>'x-app-api-key' and verify it exists in private.anon_api_keys. If not found or not anon role, raise SQLSTATE 'PGRST' with HTTP 403 status. Register with ALTER ROLE authenticator SET pgrst.db_pre_request = 'public.check_request' and NOTIFY pgrst, 'reload config'.

Disable Data API completely

If the application never uses Supabase client libraries, REST, or GraphQL data endpoints, the Data API can be turned off in the Dashboard. Navigate to Data API integration overview and turn Enable Data API off. With the Data API disabled, none of the auto-generated REST endpoints will respond, regardless of grants or RLS policies.

Using dedicated API schemas for security boundaries

A dedicated schema such as 'api' adds another boundary around the Data API. Objects in a schema define the API surface, while internal tables and helper functions remain in schemas that aren't exposed. Grants can be used to control access in any schema. A dedicated schema makes the exposed surface easier to identify and audit.

RLS does not apply to functions - use SECURITY DEFINER carefully

Row Level Security does not apply to functions. Grant EXECUTE only to the roles that need to call functions. Review every SECURITY DEFINER function carefully before deploying, as these functions run with the privileges of the user who defined them rather than the user who calls them.

Authorization via Row Level Security

Supabase Auth integrates with Postgres Policies for Row Level Security to control the data each user can access.

Network restrictions for database access

Supabase allows you to restrict IP ranges that can connect to your database for enhanced security.

SSL enforcement for Postgres connections

Supabase allows you to enforce Postgres clients to connect via SSL for secure communication.

Anonymous sign-ins in Hono require authenticated role access

Anonymous sign-ins use the authenticated role. The database setup grants read access to the anon role only. To allow authenticated users to query data, grant the authenticated role select privileges and create a matching RLS policy. Example: `grant select on public.instruments to authenticated;` followed by a policy like `create policy "authenticated can read instruments" on public.instruments for select to authenticated using (true);`

Grant write permissions for instruments table in Refine quickstart

Run the following SQL to grant insert, update, and delete privileges and create policies for the instruments table: `grant insert, update, delete on public.instruments to anon;` followed by three create policy statements for insert, update, and delete operations for the anon role.

SQL policies for public instruments table writes

Create the following policies: 1. INSERT policy: `create policy "public can insert instruments" on public.instruments for insert to anon with check (true);` 2. UPDATE policy: `create policy "public can update instruments" on public.instruments for update to anon using (true) with check (true);` 3. DELETE policy: `create policy "public can delete instruments" on public.instruments for delete to anon using (true);`

Warning about permissive policies in sample data

The policies that allow anyone with your publishable key to modify the instruments table are for trying the scaffolded UI against sample data only. Scope writes to authenticated users before putting real data in the table.

Give your agent this brain