·Updated

The Ultimate Guide to Secure API Key Management in Supabase Projects

Build secure API key management in Supabase with PostgreSQL roles, Row Level Security, and scoped permissions. Production-ready code included.

supabase
postgresql
security
api

Every SaaS product eventually needs API keys. Whether you're building a public API for third-party integrations or an API-first product where keys track usage and authenticate requests, you'll need a system that actually works.

API key management in Supabase requires a private schema for key storage, bcrypt hashing so keys stay secure even if your database leaks, custom PostgreSQL roles for scoped access, and Row Level Security policies that check permissions before granting data access. This architecture protects keys at rest while giving each key granular control over which resources it can access.

API key management is the practice of generating, storing, validating, and revoking programmatic access tokens that authenticate machine-to-machine API requests. Unlike user sessions, API keys are long-lived, scoped to specific permissions, and don't require interactive login.

What we'll build:

  • Database Schema: Schema isolation to protect sensitive key data
  • Key Generation: Cryptographically sound key generation with bcrypt hashing
  • Permissions: Scope-based access control through controlled interfaces
  • API: Lifecycle management for creating, listing, and revoking keys

When to Use This Pattern

Use API keys when:

  • Third-party integrations need programmatic access to your API
  • You're building an API-first product where customers authenticate via keys
  • You need fine-grained, auditable access control per integration

Use Supabase Auth instead when:

  • You only need user authentication (sessions are simpler)
  • Access is always tied to a logged-in user, not a system

If unsure: Start with Supabase Auth. Add API keys when customers ask for programmatic access.

Prerequisites

Before starting, ensure you have:

  • A Supabase project with the pgcrypto extension enabled (Postgres 15+ recommended)
  • Basic familiarity with PostgreSQL Row Level Security and Supabase
  • A Next.js application for the TypeScript examples (tested with Next.js 15)
  • Access to your Supabase JWT secret and service role key

Tested with Supabase 2.x, PostgreSQL 15, @supabase/supabase-js 2.x, and jsonwebtoken 9.x.

Security Fundamentals

Before writing any code, let's establish the security principles that guide this implementation:

  1. Schema Isolation: Store sensitive API key data in a private schema with controlled access
  2. Least Privilege: Expose functionality through specific interfaces with explicit permission checks
  3. Never Store Raw Keys: Hash all API keys cryptographically—even if the database leaks, keys remain unusable
  4. Defense in Depth: Layer multiple security controls to protect against single points of failure
  5. Audit Everything: Log all key creations, usages, and revocations

Database Architecture

We start with proper schema isolation—the foundation of our security model.

The Private Schema

We create a dedicated api_keys_private schema that clients cannot access directly. All sensitive data lives here, and we expose functionality only through controlled public functions.

The API Key Role

We also create a custom PostgreSQL role called api_key. This role accesses data through RLS policies that check the scopes column, enabling granular, permission-based access to your application's entities.

Core Schema

-- Create a dedicated private schema for API key data
CREATE SCHEMA IF NOT EXISTS api_keys_private;
-- API keys table
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));
-- Usage logs table
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;
-- Performance indexes
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);
-- 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 access only to service_role
GRANT USAGE ON SCHEMA api_keys_private TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA api_keys_private TO service_role;
-- Create the API key role
CREATE ROLE api_key NOBYPASSRLS;
GRANT api_key TO authenticator;
GRANT anon TO api_key;

Key design decisions:

  • The private schema blocks direct client access entirely
  • Only service_role can touch the private tables directly
  • We store only hashed keys, so even a full database leak doesn't expose usable credentials
  • The key_prefix enables efficient lookups without exposing the full key (with ~500 keys, verification stays under 10ms)

Public Interface Functions

These functions provide controlled access to the API key system. They're the only way clients interact with key management.

Create API Key

Generates a new API key with specified scopes and optional expiration:

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;
    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 using bcrypt with cost factor 12
    v_key_hash := crypt(v_key, gen_salt('bf', 12));
    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 full key only once—it cannot be retrieved again
    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 function returns the full API key exactly once. Store it securely—it cannot be retrieved again.

Implementing has_role_on_account

The public.has_role_on_account function comes built into Makerkit. If you're not using Makerkit, implement your own version that checks whether the current user has permission to act on resources belonging to the given account_id.

List API Keys

Returns all API keys for an account, excluding sensitive hash data:

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
    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 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;

Revoke API Key

Deactivates an API key so it can no longer authenticate requests:

CREATE OR REPLACE FUNCTION public.revoke_api_key(p_api_key_id UUID)
    RETURNS BOOLEAN AS
$$
DECLARE
    v_account_id UUID;
BEGIN
    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;
    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;
    UPDATE api_keys_private.api_keys
    SET is_active = FALSE
    WHERE id = p_api_key_id;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Design notes:

  • Every function performs explicit permission checks before any operation
  • SECURITY DEFINER allows functions to access the private schema securely
  • Raw keys never leave the create_api_key function after initial generation

Authentication and Session Management

These functions handle API key validation and usage tracking.

Verify API Key

Validates an API key and returns its associated account:

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
    v_key_prefix := substring(p_api_key, 1, 7);
    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
        IF crypt(p_api_key, v_key_record.key_hash) = v_key_record.key_hash THEN
            v_found := TRUE;
            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 jsonb_build_object(
                'valid', TRUE,
                'api_key_id', v_key_record.id,
                'account_id', v_key_record.account_id
            );
        END IF;
    END LOOP;
    RETURN jsonb_build_object('valid', FALSE, 'error', 'Invalid API key');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Success response:

{ "valid": true, "api_key_id": "uuid-string", "account_id": "uuid-string" }

Failure response:

{ "valid": false, "error": "Invalid API key" }

Log API Key Usage

Records each API key use for auditing and updates the last_used_at timestamp:

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 api_keys_private.api_keys
  SET last_used_at = v_current_time
  WHERE id = p_api_key_id;
  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 jsonb_build_object(
    'success', TRUE,
    'log_id', v_log_id,
    'timestamp', v_current_time
  );
EXCEPTION WHEN OTHERS THEN
  RETURN jsonb_build_object(
    'success', FALSE,
    'error', SQLERRM,
    'error_code', SQLSTATE
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Authorization Functions for Row Level Security

These functions power RLS policies that control what data API keys can access.

Check Scope Permissions

Determines whether the current API key has permission for a specific action on an entity:

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
  v_api_key_id := auth.uid();
  IF v_api_key_id IS NULL THEN
    RETURN FALSE;
  END IF;
  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;
  -- Global wildcard
  IF v_scopes @> '[{"entity_type": "*", "entity_id": "*", "action": "*"}]' THEN
    RETURN TRUE;
  END IF;
  -- 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;
  -- Action wildcard for entity type
  IF v_scopes @> jsonb_build_array(jsonb_build_object(
    'entity_type', p_entity_type, 'entity_id', '*', 'action', p_action
  )) THEN
    RETURN TRUE;
  END IF;
  -- 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;

Get API Key Account ID

Retrieves the account associated with the current API key for RLS enforcement:

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;
    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;

Use this in RLS policies to verify the API key belongs to the correct account before granting access.

Granting Function Permissions

-- Authenticated users can manage their own keys
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;
-- Service role handles verification and logging
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;
-- API key role uses authorization functions
GRANT EXECUTE ON FUNCTION public.has_scope TO api_key;
GRANT EXECUTE ON FUNCTION public.get_api_key_account_id TO api_key;

Securing Application Data

Here's how to set up RLS policies for tables that API keys should access. This example uses a documents 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()
);
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
-- Grant access to api_key role
GRANT SELECT, INSERT, UPDATE, DELETE ON public.documents TO api_key;
-- Regular user access
CREATE POLICY "Account members can access documents"
    ON public.documents
    FOR ALL
    TO authenticated
    USING (public.has_role_on_account(account_id));
-- API key SELECT
CREATE POLICY "API keys can read documents with proper scope"
    ON public.documents
    FOR SELECT
    TO api_key
    USING (
        documents.account_id = public.get_api_key_account_id()
        AND public.has_scope('document', id::text, 'read')
    );
-- API key INSERT
CREATE POLICY "API keys can create documents with proper scope"
    ON public.documents
    FOR INSERT
    TO api_key
    WITH CHECK (
        documents.account_id = public.get_api_key_account_id()
        AND public.has_scope('document', '*', 'create')
    );
-- API key UPDATE
CREATE POLICY "API keys can update documents with proper scope"
    ON public.documents
    FOR UPDATE
    TO api_key
    USING (
        documents.account_id = public.get_api_key_account_id()
        AND public.has_scope('document', id::text, 'update')
    );
-- API key DELETE
CREATE POLICY "API keys can delete documents with proper scope"
    ON public.documents
    FOR DELETE
    TO api_key
    USING (
        documents.account_id = public.get_api_key_account_id()
        AND public.has_scope('document', id::text, 'delete')
    );

Policy pattern:

  1. Verify account ownership first (get_api_key_account_id)
  2. Then check specific permissions (has_scope)
  3. Separate policies for each operation type enable granular control

Apply this pattern to any table that needs API key access.

TypeScript Client Implementation

Create an authenticated Supabase client from an API key:

import { createClient } from '@supabase/supabase-js';
import jwt from 'jsonwebtoken';
import { Database } from '~/lib/database.types';
const JWT_SECRET = process.env.SUPABASE_JWT_SECRET!;
export async function getSupabaseClientWithApiKey(
  apiKey: string,
  request?: Request,
) {
  if (!apiKey) {
    throw new Error('API key is required');
  }
  // Use service role for verification and logging (SECURITY DEFINER functions)
  const serviceSupabase = createClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!,
  );
  try {
    const { data, error } = await serviceSupabase.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' },
    );
    const apiKeySupabaseClient = createClient<Database>(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
      {
        global: {
          headers: {
            Authorization: `Bearer ${token}`,
          },
        },
      },
    );
    // Log the authentication (requires service role)
    const { error: logError } = await serviceSupabase.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');
  }
}
function getClientIp(request?: Request) {
  if (!request) return 'unknown';
  const forwardedFor = request.headers.get('x-forwarded-for');
  if (forwardedFor) {
    return forwardedFor.split(',')[0]?.trim();
  }
  const realIp = request.headers.get('x-real-ip');
  if (realIp) return realIp;
  return 'unknown';
}
function getUserAgent(request?: Request) {
  if (!request) return 'unknown';
  return request.headers.get('user-agent');
}

Requirements:

  • JWT Secret: Found in your Supabase project settings. Keep it secure.
  • Service Role Key: Also in project settings. Never expose to clients.
  • jsonwebtoken: Install with npm install jsonwebtoken

API Key Management Utilities

These functions wrap the database RPCs for use in your application. They require an authenticated user context—call them from server actions or API routes where the user is already signed in.

import { createClient } from '@supabase/supabase-js';
// Note: In production, pass an authenticated Supabase client instead of
// creating a new one. These examples show the RPC signatures.
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!
  );
  const formattedScopes = scopes.map(scope => ({
    entity_type: scope.entityType,
    entity_id: scope.entityId || '*',
    action: scope.action
  }));
  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 new Error(`API key creation failed: ${error.message}`);
  }
  return data;
}
export async function listApiKeys(accountId: string) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
  const { data, error } = await supabase.rpc('list_api_keys', {
    p_account_id: accountId
  });
  if (error) {
    throw error;
  }
  return data;
}
export async function revokeApiKey(apiKeyId: string) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );
  const { data, error } = await supabase.rpc('revoke_api_key', {
    p_api_key_id: apiKeyId
  });
  if (error) {
    throw error;
  }
  return data;
}

These functions work for authenticated users who have the appropriate permissions on the account.

Testing with pgTAP

Verify the implementation with comprehensive database tests:

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
select makerkit.authenticate_as('apitest1');
select public.create_team_account('APITest');
-- 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 creation
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 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 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 revocation
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');
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 7: Create and verify a valid API key
select makerkit.authenticate_as('apitest1');
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;
    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 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 without API key context
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 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 log entry
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 12: Setup for RLS scope testing
select makerkit.authenticate_as('apitest1');
INSERT INTO public.documents (account_id, title, content)
VALUES ((select id from makerkit.get_account_by_slug('apitest')), 'Test Document', 'Content')
RETURNING id;
-- Create 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'
        ))
    );
    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 13: Verify has_scope 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 14: Verify RLS SELECT access
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 15: Verify RLS UPDATE restriction
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 16: Verify RLS DELETE restriction
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'
);
set local role postgres;
select * from finish();
rollback;

Example Next.js API Route

Here's a complete example showing API key creation and usage:

// app/api/api-keys/route.ts
import { NextResponse } from 'next/server';
import { createApiKey, getSupabaseClientWithApiKey } from '~/lib/server/api-keys';
export const POST = async () => {
  // Replace with actual account ID from your auth context
  const apiKey = await createApiKey({
    accountId: 'your-account-uuid-here',
    name: 'Production 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) {
    return NextResponse.json({ error: 'Missing API key' }, { status: 401 });
  }
  const client = await getSupabaseClientWithApiKey(apiKey, request);
  const { data } = await client.supabase.from('documents').select('*');
  return NextResponse.json(data);
}

Testing the flow:

  1. Sign in to your application
  2. Call POST /api/api-keys to create a key
  3. Copy the returned key
  4. Make a GET /api/api-keys request with the X-API-KEY header
  5. You should see the documents accessible to that key

Common Pitfalls

Watch out for these issues when implementing API key management:

  1. Don't use anon key for verification. The verify_api_key and log_api_key_usage functions require service role access. Using anon key will silently fail.
  2. Don't log full keys. Even in error messages, the 7-character prefix is enough for debugging. Full keys in logs are a security incident waiting to happen.
  3. Don't skip the key prefix index. Without idx_api_keys_key_prefix, verification scans the entire table. With ~500 keys in production, we saw verification jump from 8ms to 200ms+ without it.
  4. Don't forget RLS on the logs table. The api_key_logs table contains usage patterns. Enable RLS and restrict access to service role only.
  5. Don't expose scopes to clients. Return only key_prefix and metadata from list operations. Never expose the full scope definition to the frontend.
  6. Don't trust expiration alone. Always check is_active alongside expires_at. Revoked keys should fail immediately, not wait for expiration.

When to Consider Alternatives

This pattern works well for most SaaS products, but consider alternatives if:

  • You need rate limiting: Add a rate limiting layer (Redis, Upstash) in front of this system
  • You need key rotation workflows: Build a rotation UI that creates new keys before revoking old ones
  • You have >10k keys per account: The bcrypt verification loop may need optimization (consider key sharding by prefix)

For simpler use cases, Supabase Vault can store secrets without the full key management overhead.

Frequently Asked Questions

Can API keys be rotated without downtime?
Yes. Create a new key, update your integration to use it, then revoke the old key. Both keys work simultaneously during the transition. The scopes system means you can even create a new key with different permissions.
How do I add rate limiting to API keys?
This implementation doesn't include rate limiting. Add a Redis-based rate limiter (like Upstash) in your API middleware that checks limits before calling verify_api_key. Store rate limit config in the scopes JSONB field.
What happens if someone steals an API key?
Revoke it immediately using revoke_api_key(). The key becomes invalid instantly since verification checks is_active. Review the api_key_logs table to see what endpoints were accessed and when.
Can I use this with the anon key instead of service role?
No. The verify_api_key and log_api_key_usage functions are granted only to service_role for security. Client-side code should never verify keys directly. Always verify in your API routes using the service role client.
How do scopes work with wildcards?
Scopes support three wildcard patterns: global (*/*/* grants all access), entity type (document/*/* grants all document access), and action (document/*/read grants read on all documents). The has_scope function checks from most to least permissive.
Why use bcrypt instead of SHA-256 for hashing?
Bcrypt is designed for password/key hashing with a configurable cost factor that makes brute-force attacks expensive. SHA-256 is fast by design, which is bad for secrets. The cost factor 12 we use takes ~250ms per hash, making bulk attacks impractical.

Next Steps

You now have encrypted key storage, scoped permissions via RLS policies, and audit logging. For storing additional secrets alongside API keys, see Supabase Vault.

If you're building a SaaS with Makerkit, this pattern integrates with the existing account system. The has_role_on_account function already handles team membership checks.

Some other posts you might like...
Supabase RLS Best Practices: Production Patterns for Secure Multi-Tenant AppsLearn battle-tested Supabase Row Level Security patterns with performance optimizations, multi-tenant policies, and automated testing strategies.
Supabase Vault: Store Secrets Securely in PostgresSupabase Vault is a Postgres extension for storing encrypted secrets in your database. Learn how to store, retrieve, and delete API keys and tokens with production-ready SQL functions and TypeScript examples.
Next.js Server Actions Security: 5 Vulnerabilities You Must FixServer Actions are public HTTP endpoints. Learn to secure them with authentication, Zod validation, rate limiting, and authorization patterns. Updated for Next.js 16.
Build a Production Supabase Blog in Next.js with Supamode CMSLearn how to design a Supabase blog schema, build a Next.js UI, and wire up Supamode CMS so your team can publish fast without touching SQL
Claude Code Best Practices: Building SaaS Features with AI AgentsHow to get production-quality code from Claude Code. Covers PRD-driven development, MCP integration, and why reference code matters more than prompts.
Build a SaaS with Claude CodeThis is a step-by-step guide to building an AI Content Repurposer SaaS by vibe-coding with Claude Code and Makerkit.