How to configure Supabase (https://supabase.com/) to generate and accept API tokens.

supabase_api_auth.sql

-- Token Based API Access for Supabase
--
-- How to configure Supabase (https://supabase.com/) to generate and accept API tokens.
--
-- (c) 2022 Felix Zedén Yverås
-- Provided under the MIT license (https://spdx.org/licenses/MIT.html)
--
-- Disclaimer: This file is formatted using pg_format. I'm not happy with the result but
-- prefer to follow a tool over going by personal taste.
--
-- ---
--
-- # Introduction
--
-- > Supabase is an open source Firebase alternative. Start your project with a Postgres
-- > Database, Authentication, instant APIs, Realtime subscriptions and Storage.
-- > (https://supabase.com/, 2022-08-08)
--
-- Supabase is a new, open source, BaaS (Backend-as-a-Service), providing developers
-- with a simple yet powerful way to power their frontend applications. While not even
-- out of [beta](https://supabase.com/beta), the service already demonstrates the power
-- of PostgreSQL and the open source ecosystem.
--
-- Recently, I have been working on an API service and thought to use Supabase as my
-- backend. The idea is this:
-- 1. "editors" sign in to an administration portal and publish events.
-- 2. "developers" sign in to a developer portal where they can generate API keys to
--    access these events
-- 3. "Developers" can then create third-party applications, websites, etc. using the
--    data they have retrieved from the API.
--
-- To achieve my goals and also prevent abuse, I established the following:
-- 1. API keys should be long-lived and not expire by default,
-- 2. API keys should be immediately revokable,
-- 3. API keys should work as naturally as possible with built-in Supabase functions,
--    such as `auth.uid()`, roles etc.,
-- 4. API access should be logged,
-- 5. it should be possible to limit the rate of requests from a given API key, and
-- 6. old entries in the access log should be purged
--
-- At the time of writing (August 2022), Supabase does not appear to have an out-of-the-box
-- solution for this (see https://github.com/supabase/supabase/issues/7186 and
-- https://github.com/supabase/supabase/discussions/4419). This article/script is
-- therefore designed to document my solution to the problem and to hopefully help you in
-- your own efforts. The article/script is also designed as a migration for the
-- Supabase CLI, meaning there will be some "weird" conditional statements or exception
-- handling in some places to work around running the script multiple times in the fashion
-- Supabase CLI does.
--
-- ## Theory
--
-- At its base, Supabase provides us with a [PostgreSQL](https://www.postgresql.org/)
-- database. This database can be accessed via an API powered by
-- [PostgREST](https://postgrest.org/en/stable/#). This API is in turned hosted behind
-- a [Kong](https://docs.konghq.com/gateway/latest/) API gateway. Supabase also provides
-- a [Gotrue](https://supabase.com/docs/learn/auth-deep-dive/auth-gotrue) auth server
-- which we can thankfully ignore for this article/script. To learn more about Gotrue and
-- Supabase's architecture in general, check out
-- [the official docs](https://supabase.com/docs/guides/hosting/overview#architecture)).
--
-- For now, it's enough to know this:
-- * To get past Kong, we need to set the `apikey` header of our API request to our anon key.
-- * To authenticate with PostgREST, we need to set the `Authorization` header of our API
--   request to `Bearer <our-api-key>`. PostgREST expects `<our-api-key>` to be a valid jwt,
--   meaning our API keys will, by necessity, be jwt tokens.
--
-- Some additional, useful, resources:
-- * https://postgrest.org/en/latest/tutorials/tut1.html#bonus-topic-immediate-revocation
-- * https://github.com/supabase/supabase/issues/3233#issuecomment-1088999663
-- * https://postgrest.org/en/stable/configuration.html#in-database-configuration
--
-- ## Environments
--
-- This script assumes 4 environments:
-- * `local`, a local development environment handled by Supabase CLI
--   (see https://supabase.com/docs/guides/local-development). Extra details in responses
--   are enabled.
-- * `development`, a development environment for testing. Extra details in responses
--   are enabled.
-- * `stage`, a mirror of your production environment for testing. Extra details in
--   responses are NOT enabled.
-- * `production`, your production environment. Extra details in responses are NOT enabled.
--
-- You can set the current environment by running the following query in the SQL Editor:
--
-- ```sql
-- ALTER DATABASE postgres SET config.environment = '<environment>';
-- ```
--
-- If no environment has been configured, `local` is assumed.
--
-- **Before you continue** you should go through this script and update all mentions of
-- these environments to match your setup. You may want to add/remove/edit one or more
-- environments. The script also needs to know e.g. the supabase url and anon key for
-- each environment to work.
--
-- ## Basic Setup
--
-- To start, we will set up an `tokenauthed` role to contrast `authenticated`. This will help
-- us separate users authenticated with API token from users authenticated with email & password.
-- See also https://github.com/supabase/supabase/blob/76e1254a91cd5c6b1eb80d3faf19cfd6a3735a95/docker/volumes/db/init/00-initial-schema.sql
DO $$
BEGIN
  CREATE ROLE tokenauthed NOLOGIN NOINHERIT;
EXCEPTION
  WHEN duplicate_object THEN
    RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;

-- Configure default grants for the tokenauthed role
-- to be similar to those for `authenticated`
GRANT tokenauthed TO authenticator;

ALTER DEFAULT privileges FOR USER supabase_admin IN SCHEMA public GRANT ALL ON tables TO tokenauthed;

ALTER DEFAULT privileges FOR USER supabase_admin IN SCHEMA public GRANT ALL ON functions TO tokenauthed;

ALTER DEFAULT privileges FOR USER supabase_admin IN SCHEMA public GRANT ALL ON sequences TO tokenauthed;

ALTER DEFAULT privileges IN SCHEMA public GRANT ALL ON tables TO tokenauthed;

ALTER DEFAULT privileges IN SCHEMA public GRANT ALL ON functions TO tokenauthed;

ALTER DEFAULT privileges IN SCHEMA public GRANT ALL ON sequences TO tokenauthed;

GRANT USAGE ON SCHEMA public TO tokenauthed;

GRANT ALL ON ALL TABLES IN SCHEMA public TO tokenauthed;

GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO tokenauthed;

GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO tokenauthed;

ALTER ROLE tokenauthed SET statement_timeout = '8s';

GRANT USAGE ON SCHEMA extensions TO tokenauthed;

GRANT USAGE ON SCHEMA auth TO tokenauthed;

GRANT USAGE ON SCHEMA storage TO tokenauthed;

ALTER DEFAULT privileges IN SCHEMA storage GRANT ALL ON tables TO tokenauthed;

ALTER DEFAULT privileges IN SCHEMA storage GRANT ALL ON functions TO tokenauthed;

ALTER DEFAULT privileges IN SCHEMA storage GRANT ALL ON sequences TO tokenauthed;

GRANT ALL ON ALL TABLES IN SCHEMA storage TO tokenauthed;

GRANT ALL ON ALL FUNCTIONS IN SCHEMA storage TO tokenauthed;

GRANT ALL ON ALL SEQUENCES IN SCHEMA storage TO tokenauthed;

-- We will also set up the `api_auth` schema for our tables and functions.
CREATE SCHEMA "api_auth" AUTHORIZATION supabase_admin;

COMMENT ON SCHEMA "api_auth" IS 'API keys and related configuration';

GRANT ALL PRIVILEGES ON SCHEMA api_auth TO supabase_auth_admin;

GRANT USAGE ON SCHEMA api_auth TO anon, authenticated, service_role, tokenauthed;

ALTER DEFAULT privileges IN SCHEMA api_auth GRANT ALL ON tables TO supabase_auth_admin;

ALTER DEFAULT privileges IN SCHEMA api_auth GRANT ALL ON functions TO supabase_auth_admin;

ALTER DEFAULT privileges IN SCHEMA api_auth GRANT ALL ON sequences TO supabase_auth_admin;

-- ## API tokens
--
-- API tokens will be stored in the `tokens` table. Since tokens are authentication
-- credentials, we do not store the tokens themselves, but instead a unique token id.
CREATE TABLE IF NOT EXISTS api_auth.tokens (
  id uuid NOT NULL DEFAULT gen_random_uuid (),
  user_id uuid NOT NULL,
  -- title, allowing users to name their API tokens if they have multiple.
  title text COLLATE pg_catalog."default" NOT NULL,
  -- When a token is revoked, there may yet be log entries relating to it
  -- that need to be kept around a bit longer. The `revoked` column allows
  -- us to mark a token as revoked until such time that we are ready to
  -- delete the row.
  revoked boolean NOT NULL DEFAULT FALSE,
  created_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  CONSTRAINT tokens_pkey PRIMARY KEY (id))
TABLESPACE pg_default;

ALTER TABLE IF EXISTS api_auth.tokens OWNER TO supabase_auth_admin;

ALTER TABLE IF EXISTS api_auth.tokens ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE api_auth.tokens IS 'Listing of known API tokens';

-- Since we want to keep token information around until we are sure we do not need
-- it anymore, we'll need to set up a trigger to handle user deletions.
DROP FUNCTION IF EXISTS api_auth.handle_deleted_user CASCADE;

CREATE FUNCTION api_auth.handle_deleted_user ()
  RETURNS TRIGGER
  AS $BODY$
BEGIN
  -- Revoke tokens when user accounts are deleted.
  UPDATE
    api_auth.tokens
  SET
    revoked = TRUE
  WHERE
    user_id = OLD.id;
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql
-- Execute this function with the privileges of
-- its owner to ensure proper access.
SECURITY DEFINER;

ALTER FUNCTION api_auth.handle_deleted_user () OWNER TO supabase_auth_admin;

CREATE TRIGGER api_auth_handle_deleted_user
  AFTER DELETE ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION api_auth.handle_deleted_user ();

-- To actually create the API tokens we need to mint our own jwt tokens.
-- We will use the `pgjwt` extension for this.
-- (To learn more about the extension, visit its [github](https://github.com/michelp/pgjwt)
-- page.)
CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";

COMMENT ON EXTENSION "pgjwt" IS 'JSON Web Token API For Postgresql';

-- We'll also use a function to help us with the generation.
-- Some notes:
-- * This function can generate valid JWTs for any user
-- * The token generated by this function is valid forever
-- * We only store the id of the generated token (out of security concerns).
--   The full token will only be provided once, as the return value of this
--   function.
CREATE OR REPLACE FUNCTION api_auth.new_api_token (user_id uuid, title text)
  RETURNS text
  AS $token$
DECLARE
  token_id uuid;
  jwt_secret text := coalesce(
    -- Live Supabase instances store the jwt_secret in the `app.settings.jwt_secret` variable.
    -- You can run `SHOW app.settings.jwt_secret;` in the supabase SQL editor for your project
    -- to prove this for yourself.
    -- https://github.com/supabase/supabase-js/issues/25#issuecomment-683239444
    -- https://postgrest.org/en/stable/configuration.html#list-of-parameters
    current_setting('app.settings.jwt_secret', TRUE),
    -- The JWT secret for local development with [Supabase CLI](https://supabase.com/docs/guides/local-development)
    -- has the constant value "super-secret-jwt-token-with-at-least-32-characters-long" as an undocumented "feature".
    -- https://github.com/supabase/supabase-js/issues/25#issuecomment-1019935888
    'super-secret-jwt-token-with-at-least-32-characters-long');
BEGIN
  INSERT INTO api_auth.tokens (
    user_id,
    title)
  VALUES (
    user_id,
    title)
RETURNING
  id INTO token_id;
  -- Since this is an API token to be given to third parties,
  -- we must be careful with the information we include in the payload.
  -- Learn more:
  -- * https://supabase.com/docs/learn/auth-deep-dive/auth-deep-dive-jwts
  -- * https://auth0.com/docs/secure/tokens/json-web-tokens/json-web-token-claims
  -- * https://github.com/supabase/gotrue/blob/master/api/token.go
  -- * https://jwt.io/
  RETURN extensions.sign(json_build_object(
      -- Id of the token to support immediate revocation
      'tid', token_id,
      -- Issuer
      'iss', 'supabase',
      -- Subject, i.e. the user to whom this token is tied.
      -- This field is read by `auth.uid()` and must therefore match the user
      -- who owns the token.
      'sub', user_id,
      -- This field is read by `auth.role()`. It is also used by postgrest to
      -- determine which role to use when accessing your database.
      -- Normally, this would be `authenticated`, but we'll use `tokenauthed`
      -- instead to allow for improved security.
      'role', 'tokenauthed',
      -- Issued At
      'iat', extract(epoch FROM now())), jwt_secret::text,
  -- Supabase tokens currently use the HS256 algorithm
  -- https://github.com/supabase/gotrue/blob/f9b28dd076450ba1d24db17e6c68f39f502dab4c/api/token.go#L548
  'HS256'::text);
END;
$token$
LANGUAGE plpgsql;

ALTER FUNCTION api_auth.new_api_token OWNER TO supabase_auth_admin;

-- Allow authenticated (but not `tokenauthed`!) users to create new
-- API tokens for themselves
GRANT EXECUTE ON FUNCTION api_auth.new_api_token TO authenticated;

-- To help extracting the token id from incoming requests, we'll add the following
-- helper function, based on `auth.uid`.
-- See also
-- https://github.com/supabase/supabase/blob/76e1254a91cd5c6b1eb80d3faf19cfd6a3735a95/docker/volumes/db/init/01-auth-schema.sql
CREATE OR REPLACE FUNCTION api_auth.tid ()
  RETURNS uuid
  LANGUAGE 'sql'
  STABLE
  AS $BODY$
  SELECT
    coalesce(nullif (current_setting('request.jwt.claim.tid', TRUE), ''), (nullif (current_setting('request.jwt.claims', TRUE), '')::jsonb ->> 'tid'))::uuid
$BODY$;

ALTER FUNCTION "api_auth"."tid" OWNER TO supabase_auth_admin;

GRANT EXECUTE ON FUNCTION api_auth.tid TO PUBLIC;

-- Helper function to check for token revocation
CREATE OR REPLACE FUNCTION api_auth.check_token_revocation ()
  RETURNS void
  LANGUAGE plpgsql
  -- Execute this function with the privileges of
  -- its owner to ensure proper access rights
  SECURITY DEFINER
  AS $BODY$
DECLARE
  revoked boolean;
BEGIN
  revoked = COALESCE((
    SELECT
      t.revoked
    FROM api_auth.tokens t
    WHERE
      id = api_auth.tid ()), TRUE);
  IF revoked THEN
    -- Show extra details in local and development environments
    IF COALESCE(current_setting('config.environment', TRUE), 'local') IN ('development', 'local') THEN
      RAISE sqlstate 'PT401'
      USING MESSAGE = 'Unauthorized', DETAIL = 'Token is invalid or revoked';
    ELSE
      RAISE sqlstate 'PT401'
      USING MESSAGE = 'Unauthorized';
    END IF;
    END IF;
END;
$BODY$;

ALTER FUNCTION api_auth.check_token_revocation () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.check_token_revocation () TO postgres;

-- ## Restricting API usage
--
-- To keep our API safe and prevent abuse, we want to be able to limit usage.
-- This could also be useful, e.g. to provide users with "free" and "premium"
-- tiers.
--
-- At this time, it does not appear possible to access the cloudflare edge logs
-- from Supabase (https://github.com/supabase/supabase/discussions/8175).
-- Even if it was, it does not appear possible to access the JWT payload from these
-- logs, making it impossible to attribute usage to individual tokens
-- (https://github.com/supabase/supabase/discussions/6380).
--
-- Instead, we'll have to do our own logging.
CREATE TABLE IF NOT EXISTS api_auth.log (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 START 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  CACHE 1),
  user_id uuid NOT NULL DEFAULT auth.uid (),
  token_id uuid NOT NULL DEFAULT api_auth.tid (),
  method text COLLATE pg_catalog."default" DEFAULT current_setting('request.method'::text, TRUE) ::text,
  path text COLLATE pg_catalog."default" DEFAULT current_setting('request.path'::text, TRUE) ::text,
  user_agent text COLLATE pg_catalog."default" DEFAULT ((current_setting('request.headers'::text, TRUE))::json ->> 'user-agent'::text),
  origin text COLLATE pg_catalog."default" DEFAULT ((current_setting('request.headers'::text, TRUE))::json ->> 'origin'::text),
  ip text COLLATE pg_catalog."default" DEFAULT ((current_setting('request.headers'::text, TRUE))::json ->> 'x_real_ip'::text),
  created_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  CONSTRAINT log_pkey PRIMARY KEY (id),
  CONSTRAINT log_token_id_fkey FOREIGN KEY (token_id) REFERENCES api_auth.tokens (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS api_auth.log OWNER TO supabase_auth_admin;

ALTER TABLE IF EXISTS api_auth.log ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE api_auth.log IS 'Log for API token usage';

-- We should also make sure to clean old entires from this log every now and then,
-- say every 30 days.
--
-- Note that there are some issues with cron for the local development CLI:
-- https://github.com/supabase/cli/issues/158. For this reason, we can only
-- perform the following for non-local environments.
CREATE OR REPLACE FUNCTION api_auth.clean_log ()
  RETURNS void
  LANGUAGE plpgsql
  AS $BODY$
BEGIN
  DELETE FROM api_auth.log
  WHERE created_at < (now() at time zone 'utc') - interval '30 days';
  DELETE FROM api_auth.tokens
  WHERE tokens.revoked
    AND NOT EXISTS (
      SELECT
        *
      FROM
        api_auth.log
      WHERE
        log.token_id = tokens.id);
END;
$BODY$;

DO $CONFIGURE_CRON$
BEGIN
  IF COALESCE(current_setting('config.environment', TRUE), 'local') <> 'local' THEN
    CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA extensions;
    COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';
    PERFORM
      cron.schedule ('nightly-api-auth-log-clean', '0 1 * * *', $$
        PERFORM
          api_auth.clean_log () $$);
  END IF;
END
$CONFIGURE_CRON$;

-- Here's the unfortunate part:
-- PostgREST will use a read only transaction for GET requests, and if we want to
-- make use of PostgREST's `db-pre-request` hook, we are also trapped in a read
-- only transaction.
--
-- There is some logic to this - the current host could potentially be a read only
-- replica - but it means we will not be able to make writes to the log table easily.
-- Our workaround will therefore involve making an additional API request to a
-- function which can perform the write.
--
-- Sidenote:
-- If you are only interested in logging write operations (i.e. non-GET requests),
-- you could call `api_auth.validate_token()` (defined further down) manually as part
-- of your RLS policy (instead of via the `db-pre-request` hook) for relevant tables.
CREATE EXTENSION IF NOT EXISTS "http" WITH SCHEMA "extensions";

COMMENT ON EXTENSION "http" IS 'HTTP Client For PostgreSQL, Allows Web Page Retrieval Inside The Database.';

-- Note the `public` schema!
CREATE OR REPLACE FUNCTION public.api_auth_log_request (method text, path text, user_agent text, origin text, ip text)
  RETURNS void
  LANGUAGE 'plpgsql'
  COST 100 VOLATILE PARALLEL UNSAFE
  AS $BODY$
BEGIN
  INSERT INTO api_auth.log(
    method, path, user_agent, origin, ip)
  VALUES (
    method,
    path,
    user_agent,
    origin,
    ip);
END;
$BODY$;

ALTER FUNCTION public.api_auth_log_request OWNER TO supabase_auth_admin;

-- Require callers to be authenticated via API token to increase security.
GRANT EXECUTE ON FUNCTION public.api_auth_log_request TO tokenauthed;

REVOKE EXECUTE ON FUNCTION public.api_auth_log_request FROM public, anon, authenticated;

-- Add OpenAPI description
COMMENT ON FUNCTION public.api_auth_log_request IS $$For internal use only.

For internal use only.

Logs a usage event for the current API token.$$;

-- Helper function to call `public.api_auth_log_request` via the PostgREST API.
CREATE OR REPLACE FUNCTION api_auth.send_log_api_request ()
  RETURNS void
  LANGUAGE plpgsql
  -- Execute this function with the privileges of
  -- its owner to ensure proper access rights
  SECURITY DEFINER
  AS $BODY$
DECLARE
  response_status integer;
  response_content text;
  -- TODO: Change these to match your environment!
  supabase_url text := CASE WHEN current_setting('config.environment', TRUE) = 'production' THEN
    '<production-url>'
  WHEN current_setting('config.environment', TRUE) = 'stage' THEN
    '<stage-url>'
  WHEN current_setting('config.environment', TRUE) = 'development' THEN
    '<development-url>'
    -- Else: Default for Supabase CLI local development.
    -- Note that Supabase CLI runs supabase locally in a docker environment.
  ELSE
    -- TODO: Replace with your project id
    -- 'http://supabase_kong_<replace_with_(supabase/config.toml#project_id)>:8000'
  END;
  -- NOTE: The anon key is meant to be usable in untrusted contexts.
  -- DO NOT use any other key than your anon key here!
  supabase_anon_key text := CASE WHEN current_setting('config.environment', TRUE) = 'production' THEN
    '<production-anon-key>'
  WHEN current_setting('config.environment', TRUE) = 'stage' THEN
    '<stage-anon-key>'
  WHEN current_setting('config.environment', TRUE) = 'development' THEN
    '<development-anon-key>'
    -- Else: Default for Supabase CLI local development
  ELSE
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24ifQ.625_WdcF3KHqz5amU0x2X5WWHP-OEs_4qj0ssLNHzTs'
  END;
BEGIN
  SELECT
    status,
    content
  FROM
    -- Post request to enable write access
    http ( --
      ( --
        'POST', --
        FORMAT('%s/rest/v1/rpc/api_auth_log_request', supabase_url), --
        ( --
          SELECT
            array_agg(http_header (key, value #>> '{}'))
            FROM jsonb_each( --
              jsonb_build_object(
                -- Request expects to find its endpoint in the "public" schema.
                'Content-Profile', 'public', --
                'apikey', supabase_anon_key, --
                -- Reuse current authorization to maintain security
                -- on the `api_auth.log` table
                'Authorization', current_setting('request.headers')::json ->> 'authorization' --
))), --
'application/json', --
jsonb_build_object( --
  'method', current_setting('request.method'::text, TRUE), --
  'path', current_setting('request.path'::text, TRUE), --
  'user_agent', current_setting('request.headers')::json ->> 'user-agent', --
  'origin', current_setting('request.headers')::json ->> 'origin', --
  'ip', current_setting('request.headers')::json ->> 'x-real-ip') --
)::http_request) INTO response_status,
  response_content;
  IF NOT (coalesce((response_status), -1) BETWEEN 200 AND 299) THEN
    -- Show extra details in local and development environments
    IF COALESCE(current_setting('config.environment', TRUE), 'local') IN ('development', 'local') THEN
      RAISE sqlstate 'PT500'
      USING MESSAGE = 'Failed to log API request', DETAIL = FORMAT('Failed to log API request (%s)', response_status), HINT = response_content;
    ELSE
      RAISE sqlstate 'PT500'
      USING MESSAGE = 'Failed to log API request', DETAIL = FORMAT('Failed to log API request (%s)', response_status);
    END IF;
    END IF;
END;
$BODY$;

ALTER FUNCTION api_auth.send_log_api_request () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.send_log_api_request () TO postgres;

-- Back to preventing abuse:
-- First, we want to limit the number of tokens a single user can request.
-- We also want to limit the number of requests a single user(!) can make
-- in a given amount of time.
--
-- The presets here are arbitrary and should be configured to match your
-- use case.
--
-- Note that it is likely reasonable to allow for short request bursts
-- while limiting usage over longer spans of time harder.
CREATE TABLE IF NOT EXISTS api_auth.user_config (
  user_id uuid NOT NULL,
  max_tokens integer NOT NULL DEFAULT 10,
  -- 4 requests/s
  max_req_5s integer NOT NULL DEFAULT 20,
  -- 1.5 request/s
  max_req_10m integer NOT NULL DEFAULT 900,
  -- 0.5 request/s
  max_req_24h integer NOT NULL DEFAULT 43200,
  -- 0.25 request/s
  max_req_7d integer NOT NULL DEFAULT 151200,
  -- 0.2 request/s
  max_req_30d integer NOT NULL DEFAULT 518400,
  updated_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  created_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  CONSTRAINT user_config_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_config_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS api_auth.user_config OWNER TO supabase_auth_admin;

ALTER TABLE IF EXISTS api_auth.user_config ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE api_auth.user_config IS 'API Key restrictions and other API related configuration';

-- We'll set up a trigger to automatically provide our users with a configuration
-- when they sign up (you'll need to add any existing users to the table manually).
CREATE OR REPLACE FUNCTION api_auth.handle_new_user ()
  RETURNS TRIGGER
  LANGUAGE 'plpgsql'
  COST 100 VOLATILE NOT LEAKPROOF
  SECURITY DEFINER
  SET search_path = api
  AS $BODY$
BEGIN
  INSERT INTO api_auth.user_config (
    user_id)
  VALUES (
    NEW.id);
  RETURN new;
END;
$BODY$;

ALTER FUNCTION api_auth.handle_new_user () OWNER TO supabase_auth_admin;

CREATE TRIGGER api_handle_new_user
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE api_auth.handle_new_user ();

-- We'll use the moddatetime extension to keep track of configuration changes
CREATE EXTENSION IF NOT EXISTS "moddatetime" WITH SCHEMA "extensions";

COMMENT ON EXTENSION "moddatetime" IS 'Functions For Tracking Last Modification Time';

CREATE TRIGGER api_auth_config_moddatetime
  BEFORE UPDATE ON api_auth.user_config
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime ('updated_at');

-- To make supervision easier, we add a helper view.
-- Note that RLS and views are not fully compatible at this time.
-- There appears to be some updates on the horizon in Postgres 15
-- to change this, however.
-- https://www.cybertec-postgresql.com/en/view-permissions-and-row-level-security-in-postgresql/
CREATE OR REPLACE VIEW api_auth.usage AS
SELECT
  coalesce(users.id, log.user_id) AS user_id,
  count(*) FILTER (WHERE log.created_at >= (now() - '00:00:05'::interval)) AS req_5s,
count(*) FILTER (WHERE log.created_at >= (now() - '00:10:00'::interval)) AS req_10m,
count(*) FILTER (WHERE log.created_at >= (now() - '24:00:00'::interval)) AS req_24h,
count(*) FILTER (WHERE log.created_at >= (now() - '7 days'::interval)) AS req_7d,
count(*) FILTER (WHERE log.created_at >= (now() - '30 days'::interval)) AS req_30d
FROM
  auth.users
  FULL OUTER JOIN api_auth.log ON users.id = log.user_id
GROUP BY
  users.id,
  log.user_id;

ALTER TABLE api_auth.usage OWNER TO supabase_auth_admin;

-- Helper function to validate request limits
CREATE OR REPLACE FUNCTION api_auth.check_usage_limit_rules ()
  RETURNS void
  LANGUAGE plpgsql
  -- Execute this function with the privileges of
  -- its owner to ensure proper access rights
  SECURITY DEFINER
  AS $BODY$
DECLARE
  under_usage_limit boolean;
BEGIN
  SELECT
    config.max_req_5s > coalesce(usage.req_5s, 0)
    AND config.max_req_10m > coalesce(usage.req_10m, 0)
    AND config.max_req_24h > coalesce(usage.req_24h, 0)
    AND config.max_req_7d > coalesce(usage.req_7d, 0)
    AND config.max_req_30d > coalesce(usage.req_30d, 0) INTO under_usage_limit
  FROM
    api_auth.user_config config
  LEFT JOIN api_auth.usage usage ON config.user_id = usage.user_id
WHERE
  config.user_id = auth.uid ();
  IF NOT under_usage_limit THEN
    RAISE sqlstate 'PT429'
    USING MESSAGE = 'Too many requests', DETAIL = 'Account request limit reached', HINT = 'Wait a few minutes and try again.';
  END IF;
END;
$BODY$;

ALTER FUNCTION api_auth.check_usage_limit_rules () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.check_usage_limit_rules () TO postgres;

-- For API tokens used on websites, it may be necessary to expose them in code.
-- An example of this is when implementing Google Maps. To keep the tokens
-- (somewhat) secure, we can limit the tokens to be used only by certain websites,
-- using the http
-- [`Origin`](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Origin)
-- header.
--
-- Note that this only prevents attackers from embedding the token in client code
-- on their own websites (since `Origin` is one of the
-- [forbidden header names](https://developer.mozilla.org/en-US/docs/Glossary/Forbidden_header_name)).
-- It does not prevent attackers from using the token e.g. with curl or Postman,
-- where the `Origin` header CAN be set.
--
-- Also note that some API testing tools such as [Hoppscotch](https://hoppscotch.io/)
-- may use your browser to send requests under the hood, in which case requests may
-- silently fail to set the `Origin` header.
CREATE TABLE IF NOT EXISTS api_auth.token_origin_rules (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 START 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  CACHE 1),
  token_id uuid NOT NULL,
  origin text COLLATE pg_catalog."default" NOT NULL,
  CONSTRAINT token_origin_rules_pkey PRIMARY KEY (id),
  CONSTRAINT token_origin_rules_token_id_fkey FOREIGN KEY (token_id) REFERENCES api_auth.tokens (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT token_origin_rules_token_id_origin_uniq UNIQUE (token_id, origin))
TABLESPACE pg_default;

ALTER TABLE IF EXISTS api_auth.token_origin_rules OWNER TO supabase_auth_admin;

ALTER TABLE IF EXISTS api_auth.token_origin_rules ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE api_auth.token_origin_rules IS 'Whitelist for origins from which an API key may be used. No entries means allowed from all.';

-- Helper function to validate origin whitelist
CREATE OR REPLACE FUNCTION api_auth.check_origin_rules ()
  RETURNS void
  LANGUAGE plpgsql
  -- Execute this function with the privileges of
  -- its owner to ensure proper access rights
  SECURITY DEFINER
  AS $BODY$
DECLARE
  valid_origin boolean;
BEGIN
  SELECT
    CASE WHEN NOT EXISTS (
      SELECT
        *
      FROM
        api_auth.token_origin_rules
      WHERE
        token_id = api_auth.tid ())
      OR EXISTS (
        SELECT
          *
        FROM
          api_auth.token_origin_rules
        WHERE
          token_id = api_auth.tid ()
          AND origin = (current_setting('request.headers')::json ->> 'origin')) THEN
      TRUE
    ELSE
      FALSE
    END INTO valid_origin;
  IF NOT valid_origin THEN
    -- Show extra details in local and development environments
    IF COALESCE(current_setting('config.environment', TRUE), 'local') IN ('development', 'local') THEN
      RAISE sqlstate 'PT403'
      USING MESSAGE = 'Illegal origin', DETAIL = FORMAT('Requests from origin ''%s'' may not use this token', COALESCE(current_setting('request.headers')::json ->> 'origin', ''));
    ELSE
      RAISE sqlstate 'PT403'
      USING MESSAGE = 'Forbidden';
    END IF;
    END IF;
END;
$BODY$;

ALTER FUNCTION api_auth.check_origin_rules () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.check_origin_rules () TO postgres;

-- There may also be instances where the caller's IP(s) is known beforehand.
-- In such cases, we can improve security by restricting the token's use to
-- a predefined set of IP addresses.
CREATE TABLE IF NOT EXISTS api_auth.token_ip_rules (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 START 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  CACHE 1),
  token_id uuid NOT NULL,
  ip text COLLATE pg_catalog."default" NOT NULL,
  CONSTRAINT token_ip_rules_pkey PRIMARY KEY (id),
  CONSTRAINT token_ip_rules_token_id_fkey FOREIGN KEY (token_id) REFERENCES api_auth.tokens (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT token_ip_rules_token_id_origin_uniq UNIQUE (token_id, ip))
TABLESPACE pg_default;

ALTER TABLE IF EXISTS api_auth.token_ip_rules OWNER TO supabase_auth_admin;

ALTER TABLE IF EXISTS api_auth.token_ip_rules ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE api_auth.token_ip_rules IS 'Whitelist for IP addresses from which an API key may be used. No entries means allowed from all.';

-- Helper function to validate ip whitelist
CREATE OR REPLACE FUNCTION api_auth.check_ip_rules ()
  RETURNS void
  LANGUAGE plpgsql
  -- Execute this function with the privileges of
  -- its owner to ensure proper access rights
  SECURITY DEFINER
  AS $BODY$
DECLARE
  valid_ip boolean;
BEGIN
  SELECT
    CASE WHEN NOT EXISTS (
      SELECT
        *
      FROM
        api_auth.token_ip_rules
      WHERE
        token_id = api_auth.tid ())
      OR EXISTS (
        SELECT
          *
        FROM
          api_auth.token_ip_rules
        WHERE
          token_id = api_auth.tid ()
          AND ip = (current_setting('request.headers')::json ->> 'x-real-ip')) THEN
      TRUE
    ELSE
      FALSE
    END INTO valid_ip;
  IF NOT valid_ip THEN
    -- Show extra details in local and development environments
    IF COALESCE(current_setting('config.environment', TRUE), 'local') IN ('development', 'local') THEN
      RAISE sqlstate 'PT403'
      USING MESSAGE = 'Illegal IP', DETAIL = FORMAT('Requests from IP ''%s'' may not use this token', COALESCE(current_setting('request.headers')::json ->> 'x-real-ip', ''));
    ELSE
      RAISE sqlstate 'PT403'
      USING MESSAGE = 'Forbidden';
    END IF;
    END IF;
END;
$BODY$;

ALTER FUNCTION api_auth.check_ip_rules () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.check_ip_rules () TO postgres;

-- ## Token Validation and Log Writing
--
-- So far, we have successfully created a token which can be used in calls to PostgREST
-- (using the `Authorization` header. Note that the `apikey` header used by Kong still
-- uses your anon key!). We have also created an `api_auth.log` table to keep track of
-- api token usage and set up some restrictions on API token usage.
--
-- Now we need a way to intercept usage of API tokens and perform our validation on them.
-- For such cases, PostgREST exposes the `db-pre-request` hook
-- (https://postgrest.org/en/latest/tutorials/tut1.html#bonus-topic-immediate-revocation).
--
-- NOTE: AFAIK, there can only be _one_ function associated with the `db-pre-request`
-- hook at a time. I have not seen any note of use by Supabase but this may change in
-- the future!
CREATE OR REPLACE FUNCTION api_auth.validate_token ()
  RETURNS void
  LANGUAGE 'plpgsql'
  -- Execute this function with the privileges of
  -- its owner to ensure proper access rights
  SECURITY DEFINER
  AS $BODY$
BEGIN
  -- 1. We should only verify api tokens.
  -- Skip check if auth is not done using an API token
  IF auth.role () <> 'tokenauthed' THEN
    RETURN;
  END IF;
  -- 2. Make sure the token has not been revoked
  PERFORM
    api_auth.check_token_revocation ();
  -- 3. We perform logging by making a new API call to `public.api_auth_log_request`.
  -- However, this call will cause `api_auth.validate_token` to be run, potentially
  -- causing an endless loop of requests. Therefore, validation of calls for
  -- `api_auth.validate_token` should be interrupted here.
  IF current_setting('request.path'::text, TRUE)
    LIKE '%/api_auth_log_request' THEN
    RETURN;
  END IF;
  -- 4. Make sure user is under their usage limit
  PERFORM
    api_auth.check_usage_limit_rules ();
  -- 5. Verify request origin
  PERFORM
    api_auth.check_origin_rules ();
  -- 6. Verify request IP
  PERFORM
    api_auth.check_ip_rules ();
  -- 7. Request a write to `api_auth.log`. This is relatively costly as it involves
  -- an network call. We have therefore waited with this request for as long as possible.
  PERFORM
    api_auth.send_log_api_request ();
END
$BODY$;

ALTER FUNCTION api_auth.validate_token () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.validate_token () TO postgres;

-- Set `pgrst.db_pre_request` to point to our validation function.
-- This causes PostgREST to execute the function for every valid request,
-- before executing the requested query.
ALTER ROLE postgres SET pgrst.db_pre_request TO 'api_auth.validate_token';

-- ## Configure Access Policies
GRANT ALL ON TABLE api_auth.user_config TO authenticated, tokenauthed;

CREATE POLICY "Allow users to see the API configuration applying to them" ON api_auth.user_config AS PERMISSIVE
  FOR SELECT TO authenticated, tokenauthed
    USING (auth.uid () = user_id);

GRANT ALL ON TABLE api_auth.tokens TO authenticated;

CREATE POLICY "Allow authenticated users to see API token entries they own" ON api_auth.tokens AS PERMISSIVE
  FOR SELECT TO authenticated
    USING (auth.uid () = user_id);

CREATE POLICY "Allow authenticated users to create new API tokens" ON api_auth.tokens AS PERMISSIVE
  FOR INSERT TO authenticated
    WITH CHECK (
auth.uid () = user_id
    AND (
    -- Prevent users from creating more tokens than their configured maximum
    SELECT (
      SELECT
        COUNT(*)
      FROM
        api_auth.tokens
      WHERE
        tokens.user_id = user_id AND revoked = FALSE) < (
          SELECT
            config.max_tokens
          FROM
            api_auth.user_config config
          WHERE
            config.user_id = user_id)));

GRANT ALL ON TABLE api_auth.log TO authenticated, tokenauthed;

CREATE POLICY "Allow authenticated users to see API log entries they own" ON api_auth.log AS PERMISSIVE
  FOR SELECT TO authenticated
    USING (auth.uid () = user_id);

CREATE POLICY "Allow creation of new API log entries" ON api_auth.log AS PERMISSIVE
  FOR INSERT TO tokenauthed
    WITH CHECK (
auth.uid () = user_id
    AND api_auth.tid () = token_id);

-- Depending on how you use this script, it may be necessary to let PostgREST know
-- about our changes:
NOTIFY pgrst,
'reload schema';

NOTIFY pgrst,
'reload config';

If I understeand this correctly it can be used by doing this:
curl '<SUPABASE_URL>/rest/v1/todos'
-H "apikey: <SUPABASE_ANON_KEY>"
-H "Authorization: Bearer <API_KEY_TO_GIVE_OUT>"

Yes, that's right.

I'm not sure this can work with their recent changes to remove superuser controls...

fyi - I had a use case where I needed to embed a token into app that didn't expire -
it's possible to do this by ripping out the exp(iry) when jwt is generated. I was originally thinking I needed some more complex like this but this hack was sufficient.

I made this simple for my business Capgo.

If my RLS:

is_allowed_apikey(((current_setting('request.headers'::text, true))::json ->> 'capgkey'::text), '{all,write}'::key_mode[])

My table apikey:

id	key (uuid)	user_id (uuid)	mode (all/write/read)
1	f83fd102-c21d-4984-b6a1-33c2cf108fd7	f83fd102-c21d-4984-b6a1-33c2cf108fd7	all

my postgress function

CREATE OR REPLACE FUNCTION public.is_allowed_apikey(apikey text, keymode key_mode[])
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
Begin
  RETURN (SELECT EXISTS (SELECT 1
  FROM apikeys
  WHERE key=apikey
  AND mode=ANY(keymode)));
End;  
$function$

And in the SDK 1 you can add your APIKEY like that

const supabase = createClient(hostSupa, supaAnon, {
    headers: {
        capgkey: apikey,
    }
})

In SDK v2

const supabase = createClient(hostSupa, supaAnon, {
    global: {
      headers: {
          capgkey: apikey,
      }
  }
})

I explained first this way there: supabase/storage#81 (comment)

Does this still work?

@motz0815 I ended up not using this in the end so I don't know, unfortunately. I would hope it still does 🙂

@motz0815 yes it's still used in production in https://capgo.app :)
i made it as db extension here:
https://database.dev/martindonadieu/supabase_auth_apikey

@riderx Nice! I can't seem to install your extension though, when I run

select dbdev.install('martindonadieu-supabase_auth_apikey');
create extension supabase_auth_apikey with schema extensions;

I just get

ERROR:  0A000: extension "supabase_auth_apikey" is not available

DETAIL:  Could not open extension control file "/usr/share/postgresql/15/extension/supabase_auth_apikey.control": No such file or directory.

HINT:  The extension must first be installed on the system where PostgreSQL is running.

Thanks @FelixZY this really helps :)

What did you end up going with in the end?

@ryan-walton TBH I haven't fully decided yet. I produced this for a personal side-project which I've had quite limited time to spend on. I'm currently exploring other solutions but it seems I'm slowly circling back to Supabase so maybe I'll come back to use this eventually :) .

I think that lines 831 and 833 should be:

ALTER FUNCTION api_auth.check_ip_rules () OWNER TO postgres;

GRANT EXECUTE ON FUNCTION api_auth.check_ip_rules () TO postgres;

@motz0815 i fixed the extension, sorry for the issue.

@jachinte yes, probably. I updated the gist. Thanks!

Hey, as @ericmillsio mentioned: there was the superuser privilege change, I assume this only works for projects that were created before the change, or does someone know a workaround for the issue:

Error: ERROR: 42501: permission denied to set parameter "config.environment"

If not, does a simple config table with variables seem like an acceptable alternative in performance etc?

I made this simple for my business Capgo.

If my RLS:

is_allowed_apikey(((current_setting('request.headers'::text, true))::json ->> 'capgkey'::text), '{all,write}'::key_mode[])

My table apikey:
id key (uuid) user_id (uuid) mode (all/write/read)
1 f83fd102-c21d-4984-b6a1-33c2cf108fd7 f83fd102-c21d-4984-b6a1-33c2cf108fd7 all

my postgress function

CREATE OR REPLACE FUNCTION public.is_allowed_apikey(apikey text, keymode key_mode[])
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
Begin
  RETURN (SELECT EXISTS (SELECT 1
  FROM apikeys
  WHERE key=apikey
  AND mode=ANY(keymode)));
End;  
$function$

And in the SDK 1 you can add your APIKEY like that

const supabase = createClient(hostSupa, supaAnon, {
    headers: {
        capgkey: apikey,
    }
})

In SDK v2

const supabase = createClient(hostSupa, supaAnon, {
    global: {
      headers: {
          capgkey: apikey,
      }
  }
})

I explained first this way there: supabase/storage#81 (comment)

Thank you for sharing this method @riderx!

One consideration that is worth paying attention to when using this method is that RLS policies are run for each row of the table/view that you are selecting from, and thus this can cause performance problems because the API key is being validated for each row.

This resulted in me not being able to utilize this method, however it may be a good option for other projects.