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.

Always enable Row Level Security on tables, views, and functions in the public schema to protect your data.

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:

<Tabs scrollable size="small" type="underlined" defaultActiveId="dashboard" queryGroup="database-method"

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

alter table
  todos 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.

Any table without RLS enabled in the public schema will be accessible to the public, using the anon role. Always make sure that RLS is enabled or that you've got other security measures in place to avoid unauthorized access to your project's data!

Disable the API or restrict to custom schema

If you don't use the Data API, or if you don't want to expose the public schema, you can either disable it entirely or change the automatically exposed schema to one of your choice. See Hardening the Data API for instructions.

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:

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

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

alter role authenticator
  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:

notify pgrst, 'reload config';

<$Partial path="db_pre_request_warning.mdx" />

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 an HTTP 402 Payment Required response with a hint and additional X-Powered-By header:

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;

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

HTTP/1.1 402 Payment Required
Content-Type: application/json; charset=utf-8
X-Powered-By: Nerd Rage

{
  "message": "Payment Required",
  "details": "Quota exceeded",
  "hint": "Upgrade your plan",
  "code": "123"
}

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:

-- To get all the headers sent in the request
SELECT current_setting('request.headers', true)::json;

-- To get a single header, you can use JSON arrow operators
SELECT current_setting('request.headers', true)::json->>'user-agent';

-- Access Cookies
SELECT current_setting('request.cookies', true)::json;

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

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

Read more about PostgREST's pre-request function.

Examples

<Tabs scrollable size="small" type="underlined" defaultActiveId="rate-limit-per-ip" queryGroup="pre-request"

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 an HTTP 420 code.

Create the table:

create table private.rate_limits (
  ip inet,
  request_at timestamp
);

-- add an index so that lookups are fast
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:

create function public.check_request()
  returns void
  language plpgsql
  security definer
  as $$
declare
  req_method text := current_setting('request.method', true);
  req_ip inet := split_part(
    current_setting('request.headers', true)::json->>'x-forwarded-for',
    ',', 1)::inet;
  count_in_five_mins integer;
begin
  if req_method = 'GET' or req_method = 'HEAD' or req_method is null then
    -- rate limiting can't be done on GET and HEAD requests
    return;
  end if;

  select
    count(*) into count_in_five_mins
  from private.rate_limits
  where
    ip = req_ip and request_at between now() - interval '5 minutes' and now();

  if count_in_five_mins > 100 then
    raise sqlstate 'PGRST' using
      message = json_build_object(
        'message', 'Rate limit exceeded, try again after a while')::text,
      detail = json_build_object(
        'status',  420,
        'status_text', 'Enhance Your Calm')::text;
  end if;

  insert into private.rate_limits (ip, request_at) values (req_ip, now());
end;
  $$;

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

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

notify pgrst, 'reload config';

<$Partial path="db_pre_request_warning.mdx" />

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

Some applications can benefit from using additional API keys managed by the application in addition to the Supabase API keys. This is commonly necessary in cases like:

• Applications that use the Data API without RLS policies.
• Applications that do not use Supabase Auth or any other authentication system and rely on the anon role.

Using the apikey header with the Supabase API keys is mandatory and not configurable. If you use additional API keys, you have to distribute both the anon API key and your application's custom API key.

Outline:

• Your application requires the presence of the x-app-api-key header when the anon role is used to prevent abuse of your API.
• These API keys are stored in the private.anon_api_keys table, and are distributed independently.
• Each request using the anon role will be blocked with HTTP 403 if the x-app-api-key header is not registered in the table.

Set up the table:

create table private.anon_api_keys (
  id uuid primary key,
  -- other relevant fields
);

Create the public.check_request function:

create function public.check_request()
  returns void
  language plpgsql
  security definer
  as $$
declare
  req_app_api_key text := current_setting('request.headers', true)::json->>'x-app-api-key';
  is_app_api_key_registered boolean;
  jwt_role text := current_setting('request.jwt.claims', true)::json->>'role';
begin
  if jwt_role <> 'anon' then
    -- not `anon` role, allow the request to pass
    return;
  end if;

  select
    true into is_app_api_key_registered
  from private.anon_api_keys
  where
    id = req_app_api_key::uuid
  limit 1;

  if is_app_api_key_registered is true then
    -- api key is registered, allow the request to pass
    return;
  end if;

  raise sqlstate 'PGRST' using
    message = json_build_object(
      'message', 'No registered API key found in x-app-api-key header.')::text,
    detail = json_build_object(
      'status', 403)::text;
end;
  $$;

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

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

notify pgrst, 'reload config';

<$Partial path="db_pre_request_warning.mdx" />