Adding custom API keys with selectable Postgres role for backend components

[LeonardoDiCappuccino]

Problem

When using a secret key for backend components, we grant them all privileges of the service_role so in most cases they have access to parts of the db they do not need to have or actually shouldn't. In my understanding this violates the principle of least privilege even if security is properly enforced in the backend components.

Suggestion

Adding the possibility in Project Settings -> API Keys to add a 'Service Key' (legacy service_role API key is not meant) where you can select a Postgres role. Custom roles can be selected there so we can use other roles then anon, authenticated and service_role (from the publishable/secret key) in a backend components. With the custom Postgres role we have fine control over what the backend components can access in the db.

Current solution

For people interested, this is my current approach in detail:

1. Generating new signing keys via supabase cli (be careful with exposing the key in shell history):

supabase gen signing-key --algorithm ES256

2. Minting a long-lived JWT with the wanted role (be careful with exposing the JWT in shell history). It will ask you to paste the previously generated signing key:

supabase gen bearer-jwt --role <your_service_role> --exp 2147483647

3. Paste this JWT in the backend components .env.
4. To make your supabase platform accept this JWT follow these steps:
   a. Dashboard -> Settings -> JWT Signing Keys -> Create Standby Key
   b. Select Import an existing key, paste the previously generated and create.
   c. Three-dot menu -> Move to previously used (Supabase will use this key just to verify, not for signing).
5. Using it in backend components:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_PUBLISHABLE_KEY,
  {
    accessToken: () => Promise.resolve(process.env.YOUR_SERVICE_JWT),
  }
)

If the JWT gets compromised, just revoke the signing key from step 4 and all JWTs signed with it will be invalid.

While this works, it doesn't feel "official" and is extra work to setup