The Ultimate Guide to Secure API Key Management in Supabase Projects

Learn how to build a secure, production-grade API key system in Supabase with PostgreSQL roles, Row Level Security, and scope-based permissions. Complete with code examples.

supabase

In this comprehensive guide, we'll explore how to build a production-grade API key system in Supabase.

What's an API Key management system for? This sort of functionality is often required in modern applications, such as applications that offer a public API to grant programmatic access to their users - or API-based SaaS products where API Keys are essential to track usage and allow customers to use the product.

If you're building an application that offers an API to customers with Supabase/Postgres, this guide is for you!

We will cover the following topics:

  • Database Schema: Designing a secure database architecture with schema isolation
  • Key Generation: Implementing cryptographically sound key generation and validation
  • Permissions: Building fine-grained permissions with controlled public interfaces
  • API: Creating a complete API key lifecycle management system

1. Understanding API Key Security Fundamentals

Before we write our first line of code, let's understand the critical security principles that should guide our implementation:

Core Security Principles:

  1. Schema Isolation: Store sensitive API key data in a private schema with controlled access
  2. Least Privilege Access: Expose functionality through specific interfaces with permission checks
  3. Never Store Raw Keys: API keys should only be stored as cryptographic hashes
  4. Defense in Depth: Multiple security layers protect against single points of failure
  5. Audit Everything: All key creations, uses, and revocations should be logged

The core principles above are the foundation for our API key management system. Let's look at each one in more detail.

2. Creating the Schema Architecture

First, let's establish our database foundation with proper schema isolation.

Create a private schema "api_keys_private" for API key data

We will create a private schema for API key data named api_keys_private. We'll create tables in this schema to store API keys, logs, and other data.

To expose the functionality in the private schema api_keys_private, we will add some public functions to the public schema, so that the Supabase Client can access the private schema through the functions in the public schema.

Creating a new Postgres role "api_key"

In addition, we'll create a role api_key that will be used to access the private schema.

This role will be granted access to the public schema, so that it will be able to perform the operations that we grant it using the scopes column in the api_keys_private.api_keys table. In this way, we can grant granular access to the entities using RLS policies.

API Keys Core schema structure

Let's begin by creating the core schema structure:

-- Step 1: Create a dedicated private schema for API key data
CREATE SCHEMA IF NOT EXISTS api_keys_private;
-- Step 2: Create the tables in the private schema
CREATE TABLE api_keys_private.api_keys (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id UUID NOT NULL,
  name TEXT NOT NULL,
  key_prefix VARCHAR(7) NOT NULL,
  key_hash TEXT NOT NULL,
  scopes JSONB NOT NULL DEFAULT '[]',
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_used_at TIMESTAMPTZ,
  created_by UUID NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true
);
ALTER table api_keys_private.api_keys enable row level security;
create policy "Accounts can read API Keys" on api_keys_private.api_keys
    for select
    to authenticated
    using (
        public.has_role_on_account(account_id)
    );
CREATE TABLE api_keys_private.api_key_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  api_key_id UUID REFERENCES api_keys_private.api_keys(id) ON DELETE CASCADE,
  endpoint TEXT NOT NULL,
  method TEXT NOT NULL,
  status_code INTEGER,
  ip_address TEXT,
  user_agent TEXT,
  timestamp TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER table api_keys_private.api_key_logs enable row level security;
-- Step 3: Create indexes for performance optimization
CREATE INDEX idx_api_keys_account_id ON api_keys_private.api_keys(account_id);
CREATE INDEX idx_api_keys_key_prefix ON api_keys_private.api_keys(key_prefix);
CREATE INDEX idx_api_key_logs_api_key_id ON api_keys_private.api_key_logs(api_key_id);
CREATE INDEX idx_api_key_logs_timestamp ON api_keys_private.api_key_logs(timestamp);
-- Step 4: Lock down the private schema
REVOKE ALL ON SCHEMA api_keys_private FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA api_keys_private FROM PUBLIC;
-- Grant usage to roles that need it
GRANT USAGE ON SCHEMA api_keys_private TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA api_keys_private TO service_role;
-- Create our API key role
CREATE ROLE api_key NOBYPASSRLS;
GRANT api_key TO authenticator;
GRANT anon TO api_key;

Let's take a moment to review the core schema structure:

  • We create a dedicated private schema that can't be accessed directly by clients
  • All sensitive data (key hashes, logs) are stored in this protected area
  • We revoke all public access to the private schema to prevent unauthorized access
  • Only the service_role can directly access the private tables
  • We'll expose functionality through controlled public interfaces

We don't store keys in plain text: Keys are never stored in plain text, so that even if the row gets somehow leaked, it can't be used to perform actions using the API Key.

3. Implementing Public Interface Functions

Now, let's create the public interface functions that will provide controlled access to our API key system. This is so that the Supabase Client can access the private schema through the functions in the public schema.

1. Create API Key

The create_api_key function will create a new API key for a given account.

We will pass the following inputs to this function:

  • account_id: The ID of the account to create the API key for
  • name: The name of the API key
  • scopes: The scopes for the API key
  • expires_at: The expiration date for the API key
-- Function 1: Create API Key (accessible to authenticated users)
CREATE OR REPLACE FUNCTION public.create_api_key(
    p_account_id UUID,
    p_name TEXT,
    p_scopes JSONB,
    p_expires_at TIMESTAMPTZ DEFAULT NULL
)
    RETURNS JSONB AS
$$
DECLARE
    v_is_authorized BOOLEAN;
    v_key           TEXT;
    v_key_prefix    TEXT;
    v_key_hash      TEXT;
    v_id            UUID;
    v_created_at    TIMESTAMPTZ;
BEGIN
    select public.has_role_on_account(p_account_id) into v_is_authorized;
    -- Security check: Verify user has permission to create API keys for this account
    IF NOT (v_is_authorized) THEN
        RAISE EXCEPTION 'Unauthorized: You do not have permission to create API keys for this account';
    END IF;
    -- Generate a secure random API key
    v_key := 'sk_' || encode(gen_random_bytes(32), 'base64');
    v_key := replace(replace(replace(v_key, '/', ''), '+', ''), '=', '');
    v_key_prefix := substring(v_key, 1, 7);
    -- Hash the key using pgcrypto's crypt function with a strong algorithm
    v_key_hash := crypt(v_key, gen_salt('bf', 12));
    -- Insert the new API key record
    INSERT INTO api_keys_private.api_keys (account_id,
                                           name,
                                           key_prefix,
                                           key_hash,
                                           scopes,
                                           expires_at,
                                           created_by)
    VALUES (p_account_id,
            p_name,
            v_key_prefix,
            v_key_hash,
            p_scopes,
            p_expires_at,
            auth.uid())
    RETURNING id, created_at INTO v_id, v_created_at;
    -- Return the API key details including the full key (only shown once)
    RETURN jsonb_build_object(
            'id', v_id,
            'name', p_name,
            'key', v_key,
            'key_prefix', v_key_prefix,
            'account_id', p_account_id,
            'scopes', p_scopes,
            'expires_at', p_expires_at,
            'created_at', v_created_at
           );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The create_api_key function will return the full API key, which is only shown once, so it can be safely stored by the consumer.

Implement your own "has_role_on_account" function

The public.has_role_on_account is a function we use in the Next.js Supabase SaaS Starter Kit - so this will work out of the box if you use Makerkit. However - if you don't use Makerkit, you'll need to implement your own version of this function. Basically, given an account_id property, you need to ensure if the current user can action the API Key whose account_id property is passed in.

2. List API Keys

The list_api_keys function will list all API keys for a given account. This function will only take an account ID as input, and will return a list of API keys:

-- Function 2: List API Keys for an account
CREATE OR REPLACE FUNCTION public.list_api_keys(
    p_account_id UUID
)
    RETURNS TABLE
            (
                id           UUID,
                name         TEXT,
                key_prefix   TEXT,
                scopes       JSONB,
                expires_at   TIMESTAMPTZ,
                created_at   TIMESTAMPTZ,
                last_used_at TIMESTAMPTZ,
                is_active    BOOLEAN,
                created_by   UUID
            )
AS
$$
BEGIN
    -- Security check: Verify user has permission to view API keys for this account
    IF NOT (
        public.has_role_on_account(p_account_id)
        ) THEN
        RAISE EXCEPTION 'Unauthorized: You do not have permission to view API keys for this account';
    END IF;
    -- Return API keys (without sensitive hash information)
    RETURN QUERY
        SELECT k.id,
               k.name,
               k.key_prefix,
               k.scopes,
               k.expires_at,
               k.created_at,
               k.last_used_at,
               k.is_active,
               k.created_by
        FROM api_keys_private.api_keys k
        WHERE k.account_id = p_account_id
        ORDER BY k.created_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

3. Revoke API Key

The revoke_api_key function will revoke an API key, so that it cannot be used to authenticate requests. This function will take an API key ID as input, and will return a boolean indicating whether the operation was successful:

-- Function 3: Revoke API Key
CREATE OR REPLACE FUNCTION public.revoke_api_key(
    p_api_key_id UUID
)
    RETURNS BOOLEAN AS
$$
DECLARE
    v_account_id UUID;
BEGIN
    -- Get the account ID for this API key
    SELECT account_id
    INTO v_account_id
    FROM api_keys_private.api_keys
    WHERE id = p_api_key_id;
    IF v_account_id IS NULL THEN
        RAISE EXCEPTION 'API key not found';
    END IF;
    -- Verify user is authorized to revoke API keys
    IF NOT (
        public.has_role_on_account(
                v_account_id
        )
        ) THEN
        RAISE EXCEPTION 'Unauthorized: You do not have permission to revoke API keys for this account';
    END IF;
    -- Revoke the API key
    UPDATE api_keys_private.api_keys
    SET is_active = FALSE
    WHERE id = p_api_key_id;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Understanding the public API Key Functions

  • Each function implements explicit permission checks before performing any operation
  • We use SECURITY DEFINER to allow functions to access the private schema securely
  • The functions expose only necessary operations with controlled inputs and outputs
  • Raw API keys are never stored - we generate, hash, and only return the full key once
  • These public functions can be called directly from client applications

We've now implemented all the core functionality for API key management.

4. Implementing Session Management and Authentication

Let's create the functions needed for API key authentication and session management.

1. Verify API Key

The verify_api_key function will verify an API key and return details about the key:

-- Function 5: Verify API Key (for authentication)
CREATE OR REPLACE FUNCTION public.verify_api_key(
    p_api_key TEXT
)
    RETURNS JSONB AS
$$
DECLARE
    v_key_prefix TEXT;
    v_key_record RECORD;
    v_found      BOOLEAN := FALSE;
BEGIN
    -- Extract key prefix
    v_key_prefix := substring(p_api_key, 1, 7);
    -- Find potential matching keys by prefix
    FOR v_key_record IN
        SELECT id, key_hash, account_id, is_active, expires_at
        FROM api_keys_private.api_keys
        WHERE key_prefix = v_key_prefix
          AND is_active = TRUE
        LOOP
            -- Check if the key matches
            IF crypt(p_api_key, v_key_record.key_hash) = v_key_record.key_hash THEN
                v_found := TRUE;
                -- Check if expired
                IF v_key_record.expires_at IS NOT NULL AND v_key_record.expires_at < now() THEN
                    RETURN jsonb_build_object(
                            'valid', FALSE,
                            'error', 'Invalid API key'
                           );
                END IF;
                -- Return success with key details
                RETURN jsonb_build_object(
                        'valid', TRUE,
                        'api_key_id', v_key_record.id,
                        'account_id', v_key_record.account_id
                       );
            END IF;
        END LOOP;
    -- If we get here, no matching key was found
    RETURN jsonb_build_object(
            'valid', FALSE,
            'error', 'Invalid API key'
           );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The verify_api_key function will return a JSON object with the following fields:

{
  valid: true;
  api_key_id: string;
  account_id: string;
}

If it's invalid, the error field will contain an error message.

{
  valid: false;
  error: string;
}

2. Log API Key Usage

The log_api_key_usage functions is used to log API key usage. This function will take the following inputs:

  • p_api_key_id: The ID of the API key
  • p_endpoint: The endpoint that was accessed
  • p_method: The HTTP method used to access the endpoint
  • p_status_code: The HTTP status code returned by the endpoint
  • p_ip_address: The IP address of the client
  • p_user_agent: The user agent of the client
-- Function 6: Log API Key Usage
CREATE OR REPLACE FUNCTION public.log_api_key_usage(
  p_api_key_id UUID,
  p_endpoint TEXT,
  p_method TEXT,
  p_status_code INTEGER,
  p_ip_address TEXT,
  p_user_agent TEXT
)
RETURNS JSONB AS $$
DECLARE
  v_current_time TIMESTAMPTZ := now();
  v_log_id UUID;
BEGIN
  -- Update last_used_at timestamp
  UPDATE api_keys_private.api_keys
  SET last_used_at = v_current_time
  WHERE id = p_api_key_id;
  -- Insert usage log
  INSERT INTO api_keys_private.api_key_logs (
    api_key_id,
    endpoint,
    method,
    status_code,
    ip_address,
    user_agent,
    timestamp
  ) VALUES (
    p_api_key_id,
    p_endpoint,
    p_method,
    p_status_code,
    p_ip_address,
    p_user_agent,
    v_current_time
  )
  RETURNING id INTO v_log_id;
  -- Return success status with ids
  RETURN jsonb_build_object(
    'success', TRUE,
    'log_id', v_log_id,
    'timestamp', v_current_time
  );
EXCEPTION WHEN OTHERS THEN
  -- Return error information
  RETURN jsonb_build_object(
    'success', FALSE,
    'error', SQLERRM,
    'error_code', SQLSTATE
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Summary of API Key Functions:

  • The create_api_key function creates a new API key and returns the full key
  • The list_api_keys function lists all API keys for an account
  • The revoke_api_key function revokes an API key, so that it cannot be used to authenticate requests
  • The verify_api_key function handles all key validation logic securely
  • We use cryptographic comparison to validate keys, not simple string matching
  • The log_api_key_usage function consolidates updating and logging in one transaction
  • All functions include proper error handling and security checks

5. Implementing Authorization Functions for Row Level Security

Next, let's implement the functions that will power our Row Level Security policies:

1. Checking the scope of an API Key

The has_scope function will check if a user has permission to perform an action on a specific entity:

-- Function 7: Has Scope (for RLS policies)
CREATE OR REPLACE FUNCTION public.has_scope(
  p_entity_type TEXT,
  p_entity_id TEXT,
  p_action TEXT
)
RETURNS BOOLEAN AS $$
DECLARE
  v_scopes JSONB;
  v_api_key_id UUID;
BEGIN
  -- Get API key ID from session variable
  v_api_key_id := auth.uid();
  IF v_api_key_id IS NULL THEN
    RETURN FALSE;
  END IF;
  -- Get scopes for the current API key
  SELECT scopes INTO v_scopes
  FROM api_keys_private.api_keys
  WHERE id = v_api_key_id
    AND is_active = TRUE
    AND (expires_at IS NULL OR expires_at > now());
  IF v_scopes IS NULL THEN
    RETURN FALSE;
  END IF;
  -- Check for global wildcard
  IF v_scopes @> '[{"entity_type": "*", "entity_id": "*", "action": "*"}]' THEN
    RETURN TRUE;
  END IF;
  -- Check for entity type wildcard
  IF v_scopes @> jsonb_build_array(jsonb_build_object(
    'entity_type', p_entity_type,
    'entity_id', '*',
    'action', '*'
  )) THEN
    RETURN TRUE;
  END IF;
  -- Check for specific action wildcard
  IF v_scopes @> jsonb_build_array(jsonb_build_object(
    'entity_type', p_entity_type,
    'entity_id', '*',
    'action', p_action
  )) THEN
    RETURN TRUE;
  END IF;
  -- Check for specific entity permission
  IF v_scopes @> jsonb_build_array(jsonb_build_object(
    'entity_type', p_entity_type,
    'entity_id', p_entity_id,
    'action', p_action
  )) OR v_scopes @> jsonb_build_array(jsonb_build_object(
    'entity_type', p_entity_type,
    'entity_id', p_entity_id,
    'action', '*'
  )) THEN
    RETURN TRUE;
  END IF;
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The has_scope function takes the following inputs:

  • p_entity_type: The type of entity that the action is being performed on
  • p_entity_id: The ID of the entity that the action is being performed on
  • p_action: The action being performed on the entity

The function will return true if the user has permission to perform the action on the entity and false otherwise.

2. Get API Key Account ID

The get_api_key_account_id function will retrieve the account ID for an API key. This will be used to enforce RLS policies:

-- Function 8: Get API Key Account ID (for RLS policies)
CREATE OR REPLACE FUNCTION public.get_api_key_account_id()
    RETURNS UUID AS
$$
DECLARE
    v_api_key_id UUID;
    v_account_id UUID;
BEGIN
    v_api_key_id := auth.uid();
    IF v_api_key_id IS NULL THEN
        RETURN NULL;
    END IF;
    -- Get account_id for this API key
    SELECT account_id
    INTO v_account_id
    FROM api_keys_private.api_keys
    WHERE id = v_api_key_id
      AND is_active = TRUE
      AND (expires_at IS NULL OR expires_at > now());
    RETURN v_account_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

How to use the get_api_key_account_id function

When applying RLS policies, we need to know the account ID for the API key that is making the request. This function will retrieve the account ID from the session variable if it exists, otherwise it will look up the account ID from the API key ID.

Once retrieved the account ID, we can verify who the API key belongs to and enforce RLS policies accordingly:

  • if account_id is the ID of the authenticated user, you can check account_id against the auth.uid() function to verify that the API key belongs to the authenticated user
  • if account_id is the ID of row (ex. public.teams), you can check account_id against the primary key of the row to verify that the API key belongs to the row

4. Granting Permissions to Functions

Let's grant permissions to the functions we just created so that they can be executed by the roles who are authorized to use them:

-- Grant execute permissions on all functions
GRANT EXECUTE ON FUNCTION public.create_api_key TO authenticated;
GRANT EXECUTE ON FUNCTION public.list_api_keys TO authenticated;
GRANT EXECUTE ON FUNCTION public.revoke_api_key TO authenticated;
GRANT EXECUTE ON FUNCTION public.log_api_key_usage TO service_role;
GRANT EXECUTE ON FUNCTION public.verify_api_key TO service_role, api_key;
GRANT EXECUTE ON FUNCTION public.has_scope TO api_key;
GRANT EXECUTE ON FUNCTION public.get_api_key_account_id TO api_key;

Authenticated users have the following permissions:

  • create_api_key: Create a new API key
  • list_api_keys: List all API keys for an account
  • revoke_api_key: Revoke an API key

Service roles (e.g. clients created using a service role key) have the following permissions:

  • log_api_key_usage: Log API key usage
  • verify_api_key: Verify API key

API keys have the following permissions:

  • has_scope: Check if a user has permission to perform an action on a specific entity
  • get_api_key_account_id: Get the account ID for an API key

5. Securing Application Data with API Key Access

Now, let's set up Row Level Security for your application data tables.

In the example below, we'll use the documents table as an example. The documents table belongs to a row public.accounts(id). In your case, this may differ.

-- Example entity table
CREATE TABLE if not exists public.documents
(
    id         UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    account_id UUID        NOT NULL REFERENCES public.accounts(id),
    title      TEXT        NOT NULL,
    content    TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Enable RLS
ALTER TABLE public.documents
    ENABLE ROW LEVEL SECURITY;
-- Grant specific access to api_key role (principle of least privilege)
GRANT SELECT, INSERT, UPDATE, DELETE ON public.documents TO api_key;
-- Regular user access policy
CREATE POLICY "Account members can access documents"
    ON public.documents
    FOR ALL
    TO authenticated
    USING (
        public.has_role_on_account(account_id)
    );
-- API key access policy for SELECT operations
CREATE POLICY "API keys can read documents with proper scope"
    ON public.documents
    FOR SELECT
    TO api_key
    USING (
    -- Check account ownership first
    documents.account_id = public.get_api_key_account_id()
        AND
        -- Check for read permission
    public.has_scope('document', id, 'read')
    );
-- API key access policy for INSERT operations
CREATE POLICY "API keys can create documents with proper scope"
    ON public.documents
    FOR INSERT
    TO api_key
    WITH CHECK (
    -- Check account ownership first
    documents.account_id = public.get_api_key_account_id()
        AND
        -- Check for create permission
    public.has_scope('document', '*', 'create')
    );
-- API key access policy for UPDATE operations
CREATE POLICY "API keys can update documents with proper scope"
    ON public.documents
    FOR UPDATE
    TO api_key
    USING (
    -- Check account ownership first
    documents.account_id = public.get_api_key_account_id()
        AND
        -- Check for update permission
    public.has_scope('document', id, 'update')
    );
-- API key access policy for DELETE operations
CREATE POLICY "API keys can delete documents with proper scope"
    ON public.documents
    FOR DELETE
    TO api_key
    USING (
    -- Check account ownership first
    documents.account_id = public.get_api_key_account_id()
        AND
        -- Check for delete permission
    public.has_scope('document', id, 'delete')
    );

Let's break down the policies we just created:

  • RLS policies use our public authorization functions to enforce permissions
  • Each operation type (SELECT, INSERT, etc.) has its own policy for fine-grained control
  • We first verify account ownership before checking specific permissions
  • The has_scope function evaluates permission based on entity type, ID, and action
  • This pattern can be repeated for all your application tables that need API key access

6. Implementing A Supabase Client for API Key Authentication

Let's update our TypeScript authentication function to use our new schema architecture.

We will need:

  • JWT Secret - This is a secret that will be used to sign our JWT tokens. It's in your Supabase project settings. Keep it secret!
  • Json web Token - A library that will be used to sign and verify JWT tokens. Install it in your project to use it.
import { createClient } from '@supabase/supabase-js';
import jwt from 'jsonwebtoken';
import { Database } from '~/lib/database.types';
const JWT_SECRET = process.env.SUPABASE_JWT_SECRET!;
// Authenticate and get a connection with the API key
export async function getSupabaseClientWithApiKey(
  apiKey: string,
  request?: Request,
) {
  if (!apiKey) {
    throw new Error('API key is required');
  }
  const adminSupabase = createClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
  );
  try {
    // Use our verify_api_key function
    const { data, error } = await adminSupabase.rpc('verify_api_key', {
      p_api_key: apiKey,
    });
    if (error) {
      throw new Error(error.message || 'Failed to verify API key');
    }
    const result = data as {
      account_id: string;
      api_key_id: string;
      valid: boolean;
    };
    if (!result.valid) {
      throw new Error('Invalid API key');
    }
    const token = jwt.sign(
      { role: 'api_key', sub: result.api_key_id },
      JWT_SECRET,
      {
        expiresIn: '1h',
      },
    );
    // a Supabase client that uses the API key to authenticate
    const apiKeySupabaseClient = createClient<Database>(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
      {
        global: {
          headers: {
            Authorization: `Bearer ${token}`,
          },
        },
      },
    );
    // Log API key usage and update last_used_at
    const { error: logError } = await adminSupabase.rpc('log_api_key_usage', {
      p_api_key_id: result.api_key_id,
      p_endpoint: 'authentication',
      p_method: 'AUTH',
      p_status_code: 200,
      p_ip_address: getClientIp(request) ?? 'unknown',
      p_user_agent: getUserAgent(request) ?? 'unknown',
    });
    if (logError) {
      console.error('Failed to log API key usage:', logError);
    }
    return {
      supabase: apiKeySupabaseClient,
      accountId: result.account_id,
    };
  } catch (error) {
    console.error('API key authentication error:', error);
    if (error instanceof Error) {
      throw new Error(`Authentication failed: ${error.message}`);
    }
    throw new Error('Unknown error occurred during authentication');
  }
}
// Helper functions to get client IP and user agent
function getClientIp(request?: Request) {
  if (!request) return 'unknown';
  // Check for forwarded IP (if behind proxy)
  const forwardedFor = request.headers.get('x-forwarded-for');
  if (forwardedFor) {
    // Extract the first IP if multiple are present
    return forwardedFor.split(',')[0]?.trim();
  }
  // Try to get from other common headers
  const realIp = request.headers.get('x-real-ip');
  if (realIp) return realIp;
  // Default fallback
  return 'unknown';
}
function getUserAgent(request?: Request) {
  if (!request) return 'unknown';
  return request.headers.get('user-agent');
}

7. API Key Management

Now we can create a set of utility functions to manage API keys.

We will create the following functions:

  • createApiKey: Create a new API key
  • listApiKeys: List all API keys for an account
  • revokeApiKey: Revoke an API key

These functions can be called directly by authed users (either client or server side) if they have permissions to perform the action (we assigned these permissions when we created the RLS policies).

// Function to create a new API key
export async function createApiKey({
  accountId,
  name,
  scopes,
  expiresAt
}: {
  accountId: string;
  name: string;
  scopes: Array<{entityType: string; entityId?: string; action: string}>;
  expiresAt?: Date;
}) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
  try {
    // Format scopes for storage
    const formattedScopes = scopes.map(scope => ({
      entity_type: scope.entityType,
      entity_id: scope.entityId || '*',
      action: scope.action
    }));
    // Call our database function to create the API key
    const { data, error } = await supabase.rpc('create_api_key', {
      p_account_id: accountId,
      p_name: name,
      p_scopes: formattedScopes,
      p_expires_at: expiresAt ? expiresAt.toISOString() : undefined
    });
    if (error) {
      throw error;
    }
    return data;
  } catch (error) {
    console.error('API key creation error:', error);
    if (error instanceof Error) {
      throw new Error(`API key creation failed: ${error.message}`);
    }
    throw new Error('Unknown error occurred during API key creation');
  }
}
// Function to list all API keys for an account
export async function listApiKeys(accountId: string) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
  try {
    const { data, error } = await supabase.rpc('list_api_keys', {
      p_account_id: accountId
    });
    if (error) {
      throw error;
    }
    return data;
  } catch (error) {
    console.error('Failed to list API keys:', error);
    throw error;
  }
}
// Function to revoke an API key
export async function revokeApiKey(apiKeyId: string) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
  try {
    const { data, error } = await supabase.rpc('revoke_api_key', {
      p_api_key_id: apiKeyId
    });
    if (error) {
      throw error;
    }
    return data;
  } catch (error) {
    console.error('Failed to revoke API key:', error);
    throw error;
  }
}

8. PgTap Tests

Now, let's write some PgTap tests to verify our API key functionality.

begin;
create extension "basejump-supabase_test_helpers" version '0.0.6';
select no_plan();
create or replace function tests.authenticate_as_api_key(api_key_id uuid)
    returns void
as
$$
begin
    perform set_config('role', 'api_key', true);
    perform set_config('request.jwt.claims', json_build_object('sub', api_key_id)::text, true);
end;
$$ language plpgsql;
set local role postgres;
-- Create test users
select tests.create_supabase_user('apitest1', 'apitest1@test.com');
select tests.create_supabase_user('apitest2', 'apitest2@test.com');
-- Create a team account for API key tests
select makerkit.authenticate_as('apitest1');
select public.create_team_account('APITest');
-- Test API Key Creation
-- Test 1: Owner can create API key
select lives_ok(
               $$ select public.create_api_key(
        (select id from makerkit.get_account_by_slug('apitest')),
        'Test API Key',
        '[{"entity_type": "document", "entity_id": "*", "action": "read"}]'::jsonb
    ) $$,
               'Account owner should be able to create an API key'
       );
-- Verify the API key was created with correct properties
select results_eq(
               $$ select name, is_active from api_keys_private.api_keys
       where account_id = (select id from makerkit.get_account_by_slug('apitest'))
       and name = 'Test API Key' $$,
               $$ values ('Test API Key'::text, true::boolean) $$,
               'API key should be created with correct name and active status'
       );
-- Test 2: Non-member cannot create API key
select makerkit.authenticate_as('apitest2');
select throws_ok(
               $$ select public.create_api_key(
        (select id from makerkit.get_account_by_slug('apitest')),
        'Unauthorized Key',
        '[]'::jsonb
    ) $$,
               'Unauthorized: You do not have permission to create API keys for this account',
               'Non-member should not be able to create API keys'
       );
-- Test API Key Listing
-- Test 3: Owner can list API keys
select makerkit.authenticate_as('apitest1');
select isnt_empty(
               $$ select * from public.list_api_keys(
        (select id from makerkit.get_account_by_slug('apitest'))
    ) $$,
               'Owner should be able to list API keys'
       );
-- Test 4: Non-member cannot list API keys
select makerkit.authenticate_as('apitest2');
select throws_ok(
               $$ select * from public.list_api_keys(
        (select id from makerkit.get_account_by_slug('apitest'))
    ) $$,
               'Unauthorized: You do not have permission to view API keys for this account',
               'Non-member should not be able to list API keys'
       );
-- Test API Key Revocation
-- Test 5: Owner can revoke API key
select makerkit.authenticate_as('apitest1');
select lives_ok(
               $$ select public.revoke_api_key(
        (select id from api_keys_private.api_keys
         where account_id = (select id from makerkit.get_account_by_slug('apitest'))
         limit 1)
    ) $$,
               'Owner should be able to revoke an API key'
       );
-- Verify the API key was revoked
select results_eq(
               $$ select is_active from api_keys_private.api_keys
       where account_id = (select id from makerkit.get_account_by_slug('apitest'))
       limit 1 $$,
               $$ values (false::boolean) $$,
               'API key should be marked as inactive after revocation'
       );
-- Test 6: Non-member cannot revoke API key
select makerkit.authenticate_as('apitest2');
-- Test fails because the user cannot even read the account
select throws_ok(
               $$ select public.revoke_api_key(
        (select id from api_keys_private.api_keys
         where account_id = (select id from makerkit.get_account_by_slug('apitest'))
         limit 1)
    ) $$,
               'Account ID not found',
               'Non-member should not be able to revoke API keys'
       );
-- Test API Key Verification
-- Test 7: Create and verify a valid API key
select makerkit.authenticate_as('apitest1');
-- Store the API key for verification
DO
$$
    DECLARE
        api_key_result JSONB;
    BEGIN
        SELECT public.create_api_key(
                       (select id from makerkit.get_account_by_slug('apitest')),
                       'Verification Test Key',
                       '[
                         {
                           "entity_type": "document",
                           "entity_id": "*",
                           "action": "read"
                         }
                       ]'::jsonb
               )
        INTO api_key_result;
        -- Verify the created key
        ASSERT (SELECT (public.verify_api_key(api_key_result ->> 'key') ->> 'valid')::boolean),
            'API key verification should succeed for valid key';
    END
$$;
-- Test 8: Invalid API key verification
select row_eq(
               $$ select (public.verify_api_key('sk_invalid_key')->>'valid')::boolean $$,
               row (false::boolean),
               'Invalid API key should fail verification'
       );
-- Test API Key Scopes
-- Test 9: Create API key with specific scopes
select lives_ok(
               $$ select public.create_api_key(
        (select id from makerkit.get_account_by_slug('apitest')),
        'Scoped Key',
        '[
            {"entity_type": "document", "entity_id": "123", "action": "read"},
            {"entity_type": "document", "entity_id": "123", "action": "write"}
        ]'::jsonb
    ) $$,
               'Should be able to create API key with specific scopes'
       );
-- Test 10: Verify scope checking function
select is(
               public.has_scope('document', '123', 'read'),
               false::boolean,
               'has_scope should return false when not in API key context'
       );
set local role service_role;
-- Test API Key Usage Logging
-- Test 11: Log API key usage
select lives_ok(
               $$ select public.log_api_key_usage(
        (select id from api_keys_private.api_keys
         where account_id = (select id from makerkit.get_account_by_slug('apitest'))
         limit 1),
        '/api/test',
        'GET',
        200,
        '127.0.0.1',
        'Test User Agent'
    ) $$,
               'Should be able to log API key usage'
       );
-- Verify the log entry was created
select isnt_empty(
               $$ select * from api_keys_private.api_key_logs
       where api_key_id = (
           select id from api_keys_private.api_keys
           where account_id = (select id from makerkit.get_account_by_slug('apitest'))
           limit 1
       ) $$,
               'API key usage log entry should be created'
       );
-- Test API Key Scopes & RLS
-- Test 12: Setup for scope testing
select makerkit.authenticate_as('apitest1');
-- Create a document for testing RLS
INSERT INTO public.documents (account_id, title, content)
VALUES ((select id from makerkit.get_account_by_slug('apitest')), 'Test Document', 'Content')
RETURNING id;
-- Create an API key with specific document read scope
DO
$$
    DECLARE
        v_account_id   UUID := (select id
                                from makerkit.get_account_by_slug('apitest'));
        v_document_id  UUID := (select id
                                from public.documents
                                where title = 'Test Document'
                                limit 1);
        api_key_result JSONB;
    BEGIN
        api_key_result := public.create_api_key(
                v_account_id,
                'Document Read Key',
                jsonb_build_array(jsonb_build_object(
                        'entity_type', 'document',
                        'entity_id', v_document_id::text,
                        'action', 'read'
                                  ))
                          );
        -- Store key_id and account_id in temporary config for later use
        PERFORM set_config('tests.api_key_id', (api_key_result ->> 'id')::text, false);
        PERFORM set_config('tests.account_id', v_account_id::text, false);
        PERFORM set_config('tests.document_id', v_document_id::text, false);
    END
$$;
set local role postgres;
-- Test 14: Verify has_scope function as api_key role
select tests.authenticate_as_api_key(
               current_setting('tests.api_key_id')::uuid
       );
select is(
               (public.has_scope('document', current_setting('tests.document_id')::text, 'read')),
               true,
               'API Key should have read scope for the specific document'
       );
select is(
               (public.has_scope('document', current_setting('tests.document_id')::text, 'update')),
               false,
               'API Key should NOT have update scope for the specific document'
       );
select is(
               (public.has_scope('document', uuid_generate_v4()::text, 'read')),
               false,
               'API Key should NOT have read scope for a different document'
       );
select is(
               (public.has_scope('other_entity', '*', 'read')),
               false,
               'API Key should NOT have scope for other entity types'
       );
-- Test 15: Verify RLS SELECT access as api_key role
select isnt_empty(
               $$ SELECT * FROM public.documents WHERE id = current_setting('tests.document_id')::uuid $$,
               'API Key with read scope should be able to SELECT the document'
       );
-- Test 16: Verify RLS UPDATE restriction as api_key role
UPDATE public.documents
SET title = 'Updated Title'
WHERE id = current_setting('tests.document_id')::uuid;
select row_eq(
               $$ SELECT title FROM public.documents WHERE id = current_setting('tests.document_id')::uuid $$,
               row ('Test Document'::text),
               'API Key without write scope should NOT be able to UPDATE the document'
       );
-- Test 17: Verify RLS DELETE restriction as api_key role
select results_eq(
               $$ DELETE FROM public.documents WHERE id = current_setting('tests.document_id')::uuid RETURNING 1 $$,
               $$ SELECT 1 LIMIT 0 $$,
               'API Key without delete scope should NOT be able to DELETE the document'
       );
-- Reset role for cleanup
set local role postgres;
select *
from finish();
rollback;

9. Example API with Next.js

For the sake of testing the system quickly, let's create an example API with Next.js that uses our API key functionality.

We create a route at api/api-keys:

import { NextResponse } from 'next/server';
import { createApiKey, getSupabaseClientWithApiKey } from '~/lib/server/api-keys';
export const POST = async () => {
  const apiKey = await createApiKey({
    accountId: '<account-id>',
    name: 'API KEY',
    scopes: [
      {
        action: 'read',
        entityType: 'documents'
      }
    ]
  });
  return NextResponse.json(apiKey);
};
export const GET = async (request: Request) => {
  const apiKey = request.headers.get('X-API-KEY');
  if (!apiKey) {
    throw new Error('Missing API key');
  }
  const client = await getSupabaseClientWithApiKey(apiKey, request);
  const { data } = await client.supabase.from('documents').select('*');
  return NextResponse.json(data);
}

Sign in to your web application and launch an API request at api/api-keys using:

fetch(`/api/api-keys`).then(console.log);

The console will log the API key details. Grab the API Key, and launch another request perhaps from an HTTP Client like Postman:

GET http://localhost:3001/api/api-keys
X-API-KEY: <api-key>

Use the header X-API-KEY to pass the API key to the API and launch the request. Make sure the documents table has at least one row. You should now see all the documents that the API key has access to! Yay!

10. Security Best Practices for API Key Management

Follow these essential security practices for your API key system:

  1. Schema Isolation: Store sensitive API key data in a private schema with controlled access
  2. Never Store Raw Keys: Always hash API keys before storing them
  3. Implement Key Rotation: Encourage regular rotation of API keys
  4. Set Expiration Dates: Consider making keys expire automatically
  5. Use HTTPS Only: Never transmit API keys over unencrypted connections
  6. Limit Key Permissions: Apply the principle of least privilege
  7. Log All Activities: Keep comprehensive logs for security auditing

Conclusion: Building a Production-Grade API Key System

By implementing the patterns in this guide, you've created a production-grade API key system in Supabase that rivals commercial authentication providers while maintaining complete control over your infrastructure.

This approach strikes the perfect balance between security, usability, and maintainability, providing your users with a robust API key system that they can trust with their most sensitive operations.

Some other posts you might like...
Best Practices for Building a SaaS with Windsurf and MakerkitWindsurf is a new AI-powered editor taking the developer experience to the next level. With the new optimized rules for Makerkit, building a SaaS just got a lot easier!
Best Practices for Building a SaaS with Cursor and MakerkitCursor is the hottest AI editor in the market. With the new optimized rules for Makerkit, building a SaaS just got a lot easier!
Building a Real-time Notification System with Supabase and Next.jsLearn how to build a complete real-time notification system using Supabase and Next.js. Includes live updates, dismissible notifications, and Row Level Security.
Using Drizzle ORM with Supabase in Next.js: A Complete GuideIn this comprehensive guide, we'll walk through integrating Drizzle ORM with your Next.js Supabase project, offering you the best of both worlds: Supabase's powerful features and Drizzle's type-safe query builder.