REST API

Securing your API

The data APIs are designed to work with Postgres Row Level Security (RLS). If you use Supabase Auth, you can restrict data based on the logged-in user.

To control access to your data, you can use Policies.

Enabling row level security

Any table you create in the public schema will be accessible via the Supabase Data API.

To restrict access, enable Row Level Security (RLS) on all tables, views, and functions in the public schema. You can then write RLS policies to grant users access to specific database rows or functions based on their authentication token.

Any table created through the Supabase Dashboard will have RLS enabled by default. If you created the tables via the SQL editor or via another way, enable RLS like so:

  1. Go to the Authentication > Policies page in the Dashboard.
  2. Select Enable RLS to enable Row Level Security.

With RLS enabled, you can create Policies that allow or disallow users to access and update data. We provide a detailed guide for creating Row Level Security Policies in our Authorization documentation.

Safeguards towards accidental deletes and updates

By default, all projects have the safeupdate Postgres extension enabled for API queries. This ensures that delete() and update() requests will fail if there are no filters provided.

To confirm that safeupdate is enabled for API queries, run the following query:


_10
select
_10
usename,
_10
useconfig
_10
from pg_shadow
_10
where usename = 'authenticator';

The expected value for useconfig should be:


_10
;['session_preload_libraries=supautils, safeupdate']

Enforce additional rules on each request

Using Row Level Security policies may not always be adequate or sufficient to protect APIs.

Here are some common situations where additional protections are necessary:

  • Enforcing per-IP or per-user rate limits.
  • Checking custom or additional API keys before allowing further access.
  • Rejecting requests after exceeding a quota or requiring payment.
  • Disallowing direct access to certain tables, views or functions in the public schema.

You can build these cases in your application by creating a Postgres function that will read information from the request and perform additional checks, such as counting the number of requests received or checking that an API key is already registered in your database before serving the response.

Define a function like so:


_10
create function public.check_request()
_10
returns void
_10
language plpgsql
_10
security definer
_10
as $$
_10
begin
_10
-- your logic here
_10
end;
_10
$$;

And register it to run on every Data API request using:


_10
alter role authenticator
_10
set pgrst.db_pre_request = 'public.check_request';

This configures the public.check_request function to run on every Data API request. To have the changes take effect, you should run:


_10
notify pgrst, 'reload config';

Inside the function you can perform any additional checks on the request headers or JWT and raise an exception to prevent the request from completing. For example, this exception raises a HTTP 402 Payment Required response with a hint and additional X-Powered-By header:


_10
raise sqlstate 'PGRST' using
_10
message = json_build_object(
_10
'code', '123',
_10
'message', 'Payment Required',
_10
'details', 'Quota exceeded',
_10
'hint', 'Upgrade your plan')::text,
_10
detail = json_build_object(
_10
'status', 402,
_10
'headers', json_build_object(
_10
'X-Powered-By', 'Nerd Rage'))::text;

When raised within the public.check_request function, the resulting HTTP response will look like:


_10
HTTP/1.1 402 Payment Required
_10
Content-Type: application/json; charset=utf-8
_10
X-Powered-By: Nerd Rage
_10
_10
{
_10
"message": "Payment Required",
_10
"details": "Quota exceeded",
_10
"hint": "Upgrade your plan",
_10
"code": "123"
_10
}

Use the JSON operator functions to build rich and dynamic responses from exceptions.

If you use a custom HTTP status code like 419, you can supply the status_text key in the detail clause of the exception to describe the HTTP status.

If you're using PostgREST version 11 or lower (find out your PostgREST version) a different and less powerful syntax needs to be used.

Accessing request information

Like with RLS policies, you can access information about the request by using the current_setting() Postgres function. Here are some examples on how this works:


_10
-- To get all the headers sent in the request
_10
SELECT current_setting('request.headers', true)::json;
_10
_10
-- To get a single header, you can use JSON arrow operators
_10
SELECT current_setting('request.headers', true)::json->>'user-agent';
_10
_10
-- Access Cookies
_10
SELECT current_setting('request.cookies', true)::json;

current_setting()ExampleDescription
request.methodGET, HEAD, POST, PUT, PATCH, DELETERequest's method
request.pathtableTable's path
request.pathviewView's path
request.pathrpc/functionFunctions's path
request.headers{ "User-Agent": "...", ... }JSON object of the request's headers
request.cookies{ "cookieA": "...", "cookieB": "..." }JSON object of the request's cookies
request.jwt{ "sub": "a7194ea3-...", ... }JSON object of the JWT payload

To access the IP address of the client look up the X-Forwarded-For header in the request.headers setting. For example:


_10
SELECT split_part(
_10
current_setting('request.headers', true)::json->>'x-forwarded-for',
_10
',', 1); -- takes the client IP before the first comma (,)

Read more about PostgREST's pre-request function.

Examples

You can only rate-limit POST, PUT, PATCH and DELETE requests. This is because GET and HEAD requests run in read-only mode, and will be served by Read Replicas which do not support writing to the database.

Outline:

  • A new row is added to a private.rate_limits table each time a modifying action is done to the database containing the IP address and the timestamp of the action.
  • If there are over 100 requests from the same IP address in the last 5 minutes, the request is rejected with a HTTP 420 code.

Create the table:


_10
create table private.rate_limits (
_10
ip inet,
_10
request_at timestamp
_10
);
_10
_10
-- add an index so that lookups are fast
_10
create index rate_limits_ip_request_at_idx on private.rate_limits (ip, request_at desc);

The private schema is used as it cannot be accessed over the API!

Create the public.check_request function:


_35
create function public.check_request()
_35
returns void
_35
language plpgsql
_35
security definer
_35
as $$
_35
declare
_35
req_method text := current_setting('request.method', true);
_35
req_ip inet := split_part(
_35
current_setting('request.headers', true)::json->>'x-forwarded-for',
_35
',', 1)::inet;
_35
count_in_five_mins integer;
_35
begin
_35
if req_method = 'GET' or req_method = 'HEAD' or req_method is null then
_35
-- rate limiting can't be done on GET and HEAD requests
_35
return;
_35
end if;
_35
_35
select
_35
count(*) into count_in_five_mins
_35
from private.rate_limits
_35
where
_35
ip = req_ip and request_at between now() - interval '5 minutes' and now();
_35
_35
if count_in_five_mins > 100 then
_35
raise sqlstate 'PGRST' using
_35
message = json_build_object(
_35
'message', 'Rate limit exceeded, try again after a while')::text,
_35
detail = json_build_object(
_35
'status', 420,
_35
'status_text', 'Enhance Your Calm')::text;
_35
end if;
_35
_35
insert into private.rate_limits (ip, request_at) values (req_ip, now());
_35
end;
_35
$$;

Finally, configure the public.check_request() function to run on every Data API request:


_10
alter role authenticator
_10
set pgrst.db_pre_request = 'public.check_request';
_10
_10
notify pgrst, 'reload config';

To clear old entries in the private.rate_limits table, set up a pg_cron job to clean them up.