Posted onApr 23, 2024

Build A Public API with Supabase in 10 Minutes

Building with Supabase means most likely you are not buildig your API yourself thanks to PostgREST and the GraphQL extension. But what if your customers want a public API to integrate with your product? Instead of building your own API server from scratch, you can leverage Postgres + PostgREST to build a public API in minutes. This blog post walks you through the required steps. The target architecture is very simple: A custom database role and Row-Level-Security, your PostgREST server, and a proxy that adds rate-limiting and observability, and a few additional security measures.

The Database Schema

First we create a sample schema. In this case, we create anorganisation table. Each organisation has employees. We also have acontact table that we want to expose in our public API.

createtableorganisation(iduuidprimarykeynotnulldefaultgen_random_uuid(),nametextuniquenotnull);createtableemployee(iduuidprimarykeynotnulldefaultgen_random_uuid(),organisation_iduuidnotnullreferencesorganisationonupdaterestrictondeletecascadedefaultprivate.organisation_id(),user_iduuiduniquereferencesauth.usersonupdaterestrictondeletecascade);-- we want to expose this in our public api!createtablecontact(iduuidprimarykeynotnulldefaultgen_random_uuid(),organisation_iduuidnotnullreferencesorganisationondeletecascadedefaultprivate.organisation_id(),full_nametext);

We use Row-Level-Security to secure our data and make it accessible only if the user is authenticated and employee of the organisation. Theprivate.organisation_id() function is a little helper function that returns theorganisation_id of the authenticated user, if any.

createorreplacefunctionprivate.organisation_id()returnsuuidas$sql$selectorganisation_idfromemployeewhereuser_id=auth.uid()$sql$languagesqlstablesecuritydefiner;createpolicyemployee_allonorganisationtoauthenticatedusing((selectprivate.organisation_id())=id);createpolicyemployee_allonemployeetoauthenticatedusing((selectprivate.organisation_id())=organisation_id);createpolicyemployee_alloncontacttoauthenticatedusing((selectprivate.organisation_id())=organisation_id);

Setup API Tokens

We will manage and create api tokens right within our database, and use Row-Level-Security to define what the api token can do. The first step is to create a roletokenauthed that can be used to authenticate with the database. It should behave like theauthenticated role.

createroletokenauthed;granttokenauthedtoauthenticator;grantanontotokenauthed;

Create the API Token Table

We store references to api tokens in a table. Note that the key itself is not stored. Employees should be able to manage tokens.

createtableifnotexistsapi_token(iduuidprimarykeynotnulldefaultgen_random_uuid(),organisation_iduuidnotnullreferencesorganisationondeletecascadedefaultprivate.organisation_id(),created_attimestampwithtimezonenotnulldefaultnow(),nametextnotnull);altertableapi_tokenenablerowlevelsecurity;createpolicyemployee_allonapi_tokentoauthenticatedusing((selectprivate.organisation_id())=organisation_id);

Allow users to actually create a token

To actually create the api tokens we need to mint our own jwt tokens. We will use thepgjwt extension for this. Luckily, its ready to install on our Supabase instance.

createextensionifnotexists"pgjwt"withschema"extensions";

Thecreate_api_token function inserts an api token into the database, and then signs a jwt token with id of the token and the organisation id in the payload. There are a few notable things here. First, live supabase instances store the jwt_secret in theapp.settings.jwt_secret variable. >ou can runshow app.settings.jwt_secret; in the Supabase SQL editor for your project to prove this for yourself. The jwt secret for local development with theSupabase CLI has the constant value "super-secret-jwt-token-with-at-least-32-characters-long" as an undocumented "feature". You might notice that the token is always valid forever for now. We will fix that at a later stage. And we useHS256, because its the same algorithm Supabase auth uses.

createorreplacefunctioncreate_api_token(organisation_iduuid,nametext)returnstextas$token$declaretoken_iduuid;jwt_secrettext:=coalesce(nullif(current_setting('app.settings.jwt_secret',true),''),'super-secret-jwt-token-with-at-least-32-characters-long');begininsertintoapi_token(organisation_id,name)values(organisation_id,name)returningidintotoken_id;returnextensions.sign(json_build_object('tid',token_id,'iss','supabase','sub',organisation_id,'role','tokenauthed','iat',trunc((extract(epochfromNow()))::numeric,0)),jwt_secret::text,'HS256'::text);end;$token$securityinvokerlanguageplpgsql;

Update Row-Level-Security Policies

First, we add a function to extract thetid from the jwt token, similar toauth.uid().

createorreplacefunctionprivate.tid()returnsuuidlanguage'sql'stableas$body$selectcoalesce(nullif(current_setting('request.jwt.claim.tid',true),''),(nullif(current_setting('request.jwt.claims',true),'')::jsonb->>'tid'))::uuid$body$;

We then updateprivate.organisation_id() to return theorganisation_id from the jwt payload when the role istokenauthed.

createorreplacefunctionprivate.organisation_id()returnsuuidas$sql$select(casewhenauth.role()='authenticated'then(selectorganisation_idfromemployeewhereuser_id=auth.uid())whenauth.role()='tokenauthed'then(selectcoalesce(nullif(current_setting('request.jwt.claim.sub',true),''),(nullif(current_setting('request.jwt.claims',true),'')::jsonb->>'sub'))::uuid)end);$sql$languagesqlstablesecuritydefiner;

Finally, we re-create the Row-Level-Security policy oncontact to includetokenauthed.

droppolicyemployee_alloncontact;createpolicyemployee_tokenauthed_alloncontacttoauthenticated,tokenauthedusing((selectprivate.organisation_id())=organisation_id);

Create the API Schema

To control what tables and columns should be exposed, we create a separateapi schema. This separates our internal data model from the data model exposed in our public api.

createschemaapi;grantusageonschemaapitopostgres,anon,authenticated,service_role,tokenauthed;alterdefaultprivilegesinschemaapigrantallontablestopostgres,anon,authenticated,service_role,tokenauthed;alterdefaultprivilegesinschemaapigrantallonfunctionstopostgres,anon,authenticated,service_role,tokenauthed;alterdefaultprivilegesinschemaapigrantallonsequencestopostgres,anon,authenticated,service_role,tokenauthed;

We now use"updateable views" and thesecurity_invoker config to expose thecontact. An updatable view in postgres is a view which allows mutations, because the underlying table can be derived. Thanks to thesecurity_invoker config, all operations will respect the Row-Level-Security policy we set on thecontact table. For better performance, we always filter on theorganisation_id and therefore mimick the security policy clause. With the view, we can also define what columns should be exposed. This is pretty neat, since most apps will have columns that are internal to the app logic.

createviewapi.contactwith(security_invoker)asselectid,full_namefromcontactwhereorganisation_id=private.organisation_id();

And thats it for the database part. As of now, we have:

a custom database role
a function to generate tokens
helper functions to easily define Row-Level-Security Policies
a table that is exposed via a customapi schema

We could just share our Supabase Rest API endpoint and be done. I would not recommend that. Instead, lets build a little proxy that will give us the possibility to add required security measures such as rate-limiting and observability.

The Proxy

Our little proxy will be a very simple Cloudflare Worker using Hono. You can choose any framework or platform you want for this though. First, we set up the project.

bunx create-hono api-proxy

First, setup a JWT middleware to verify the incoming request. You can find the JWT secret in your Supabase Dashboard.

constapp=newHono<Env>();app.use((c,next)=>{constjwtMiddleware=jwt({secret:c.env.SUPABASE_JWT_SECRET,alg:"HS256",});returnjwtMiddleware(c,next);});

Now, add a single "catch-all" route that forwards the request to the upstream PostgREST api.

app.all("/:table",async(c)=>{constsearchParams=c.req.url.indexOf("?")>-1?c.req.url.slice(c.req.url.indexOf("?")):"";constupstreamUrl=`${c.env.SUPABASE_URL}/rest/v1/${c.req.param("table")}${searchParams}`;constnewRequest=newRequest(upstreamUrl,c.req.raw);constres=awaitfetch(newRequest);constnewResponse=newResponse(res.body,res);returnnewResponse;});

To fail early, you might want to validate the path parameter.

import{object,picklist}from"valibot";import{vValidator}from"@hono/valibot-validator";constTABLES=["contact"];constparamsSchema=object({table:picklist(TABLES),});app.all("/:table",vValidator<typeofparamsSchema,"param",Env,any>("param",paramsSchema),async(c)=>{// ...},);

For Kong, the API gateway that Supabase uses, to accept your proxied request, we need to set theapiKey header to theanon key. To target theapi schema, set theAccept-Profile andContent-Profile headers.

app.all("/:table",vValidator<typeofparamsSchema,"param",Env,any>("param",paramsSchema),async(c)=>{//  ...constnewRequest=newRequest(upstreamUrl,c.req.raw);newRequest.headers.set("apiKey",c.env.SUPABASE_ANON_KEY);if(["GET","HEAD"].includes(c.req.method)){newRequest.headers.set("Accept-Profile","api");}else{newRequest.headers.set("Content-Profile","api");}// ...},);

This is also a great opportunity to change the default behavior of PostgREST. For example, your users might expect that any mutation always returns the mutated row. This can be enabled by default by always addingreturn=representation to thePrefer header.

app.all("/:table",vValidator<typeofparamsSchema,"param",Env,any>("param",paramsSchema),async(c)=>{//  ...constnewRequest=newRequest(upstreamUrl,c.req.raw);// ...if(c.req.query("select")){newRequest.headers.set("Prefer",[c.req.header("Prefer"),"return=representation"].filter(Boolean).join(","),);}// ...},);

And thats it! From here, you should be able to complete this on your own. To be ready for production, you should add rate-limiting as well as logging and monitoring to your worker.

Recommendations

Control the statement timeout for yourtokenauthed role. You are giving your users a lot of power with all the query features that PostgREST offers. To make sure they spend some time optimizing their requests, set the limit to a relatively low value depending on your use case.

alterroletokenauthedsetstatement_timeout='2s';

Next, you should make sure that we can invalidate tokens. For now, a token can be invalidated if its reference in theapi_token table is deleted. We can leverage thedb_pre_request hook that PostgREST exposes for this. To read more about the Pre-Request feature check out theirdocumentation. Its basically a middleware within Postgres which we can use to ensure that the token reference in our database still exists.

createorreplacefunctionprivate.validate_token()returnsvoidlanguage'plpgsql'securitydefineras$body$begin-- 1. we should only verify api tokens.-- skip check if auth is not done using an api tokenifauth.role()<>'tokenauthed'thenreturn;endif;-- 2. make sure the token has not been revokedif(casewhen(selectidfromapi_tokentwhereid=private.tid())isnotnullthenfalseelsetrueend)istruethenraisesqlstate'pt401'usingmessage='unauthorized';endif;end$body$;alterroleauthenticatorsetpgrst.db_pre_requestto'private.validate_token';notifypgrst,'reload config';

Finally, we only want to allowtokenauthed requests if they were routed through our proxy, so that rate-limiting applies. The simplest way to achieve this is to add a custom secret value to the header in the request.

app.all("/:table",vValidator<typeofparamsSchema,"param",Env,any>("param",paramsSchema),async(c)=>{//  ...constnewRequest=newRequest(upstreamUrl,c.req.raw);// ...if(c.req.query("select")){newRequest.headers.set("x-proxy-secret",c.env.PROXY_SECRET);}// ...},);

We can check for the existence of this token within thevalidate_token function. Luckily, our Supabase project comes with a built-in Vault, so we do not have to worry about storing our secret key in the database.

createorreplacefunctionprivate.validate_token()returnsvoidlanguage'plpgsql'securitydefineras$body$begin-- 1. we should only verify api tokens.-- skip check if auth is not done using an api tokenifauth.role()<>'tokenauthed'thenreturn;endif;-- 2. Make sure that the request is going through our proxyif(selectcurrent_setting('request.headers',true)::json->>'x-proxy-secret')<>(selectdecrypted_secretfromvault.decrypted_secretswherename='proxy_secret')thenraisesqlstate'pt401'usingmessage='unauthorized';endif;-- 3. make sure the token has not been revokedif(casewhen(selectidfromapi_tokentwhereid=private.tid())isnotnullthenfalseelsetrueend)istruethenraisesqlstate'pt401'usingmessage='unauthorized';endif;end$body$;