MCP Server Supabase — Postgres, auth, and realtime in a TypeScript MCP server

Supabase client setup in an MCP server

Supabase provides two credential types with fundamentally different access levels. Choosing the wrong one is a common security mistake when building MCP servers.

import { createClient, SupabaseClient } from '@supabase/supabase-js';

const SUPABASE_URL = process.env.SUPABASE_URL!;
const SUPABASE_SERVICE_ROLE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY!;

// Admin client — bypasses RLS. Safe in a Node.js MCP server process.
// NEVER expose service_role key to client-side code or put it in a browser bundle.
export const adminClient = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, {
  auth: {
    autoRefreshToken: false,
    persistSession: false
  }
});

// Create a per-request client that respects RLS via the caller's JWT
export function userClient(userJwt: string): SupabaseClient {
  const client = createClient(SUPABASE_URL, process.env.SUPABASE_ANON_KEY!, {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    },
    global: {
      headers: {
        Authorization: `Bearer ${userJwt}`
      }
    }
  });
  return client;
}

// Utility: throw McpError from Supabase error response
import { McpError, ErrorCode } from '@modelcontextprotocol/sdk/types.js';

export function assertNoError<T>(
  result: { data: T | null; error: { message: string } | null }
): T {
  if (result.error) {
    throw new McpError(ErrorCode.InternalError, result.error.message);
  }
  return result.data!;
}

Basic CRUD tools

Supabase's JavaScript client returns { data, error } pairs rather than throwing on failure. Every tool handler must check .error before using .data. Forgetting this check is the most common source of silent null data bugs in Supabase integrations.

import { z } from 'zod';
import { adminClient, assertNoError } from './supabase.js';

// ---- list_rows ----
server.tool(
  'list_rows',
  {
    table: z.enum(['products', 'orders', 'customers']),
    filter_column: z.string().optional(),
    filter_value: z.string().optional(),
    limit: z.number().int().min(1).max(200).default(50),
    order_by: z.string().optional(),
    ascending: z.boolean().default(true)
  },
  async ({ table, filter_column, filter_value, limit, order_by, ascending }) => {
    let query = adminClient.from(table).select('*').limit(limit);
    if (filter_column && filter_value !== undefined) {
      query = query.eq(filter_column, filter_value);
    }
    if (order_by) {
      query = query.order(order_by, { ascending });
    }
    const rows = assertNoError(await query);
    return { content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }] };
  }
);

// ---- get_row ----
server.tool(
  'get_row',
  {
    table: z.enum(['products', 'orders', 'customers']),
    id: z.union([z.string(), z.number()])
  },
  async ({ table, id }) => {
    const result = assertNoError(
      await adminClient.from(table).select('*').eq('id', id).maybeSingle()
    );
    if (!result) {
      throw new McpError(ErrorCode.InvalidParams, `Row with id=${id} not found in ${table}`);
    }
    return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }] };
  }
);

// ---- insert_row ----
server.tool(
  'insert_row',
  {
    table: z.enum(['products', 'orders', 'customers']),
    data: z.record(z.unknown())
  },
  async ({ table, data }) => {
    const inserted = assertNoError(
      await adminClient.from(table).insert(data).select().single()
    );
    return { content: [{ type: 'text', text: JSON.stringify(inserted, null, 2) }] };
  }
);

// ---- update_row ----
server.tool(
  'update_row',
  {
    table: z.enum(['products', 'orders', 'customers']),
    id: z.union([z.string(), z.number()]),
    data: z.record(z.unknown())
  },
  async ({ table, id, data }) => {
    const updated = assertNoError(
      await adminClient.from(table).update(data).eq('id', id).select().single()
    );
    return { content: [{ type: 'text', text: JSON.stringify(updated, null, 2) }] };
  }
);

// ---- delete_row ----
server.tool(
  'delete_row',
  {
    table: z.enum(['products', 'orders', 'customers']),
    id: z.union([z.string(), z.number()])
  },
  async ({ table, id }) => {
    assertNoError(await adminClient.from(table).delete().eq('id', id));
    return { content: [{ type: 'text', text: JSON.stringify({ deleted: true, id }) }] };
  }
);

Row-level security in tools

RLS policies in Supabase are enforced by the Postgres database based on the current role and session variables. When you use the service_role key, RLS is bypassed entirely. To have RLS apply, you must pass the user's JWT to Supabase so it can set auth.uid() in the Postgres session.

In an MCP server, the user's JWT typically arrives via a custom HTTP header (if you're running an HTTP MCP transport) or as a parameter in the session initialization metadata. The pattern below reads it from an Express request header:

import { userClient, assertNoError } from './supabase.js';

// Tool that enforces RLS — user can only see their own orders
server.tool(
  'list_my_orders',
  {
    status: z.enum(['pending', 'shipped', 'delivered', 'cancelled']).optional(),
    limit: z.number().int().min(1).max(50).default(20)
  },
  async ({ status, limit }, { meta }) => {
    // Extract user JWT from tool call metadata (passed by MCP client)
    const userJwt = meta?.userJwt as string | undefined;
    if (!userJwt) {
      throw new McpError(ErrorCode.InvalidRequest, 'Authentication required');
    }

    // Build a client using the user's JWT — RLS policies apply
    const client = userClient(userJwt);
    let query = client.from('orders').select('*').limit(limit);
    if (status) query = query.eq('status', status);

    // RLS policy: "auth.uid() = user_id" on the orders table
    // ensures this only returns the authenticated user's orders
    const orders = assertNoError(await query);
    return { content: [{ type: 'text', text: JSON.stringify(orders, null, 2) }] };
  }
);

// Alternative: use the admin client but add explicit user_id filter
// (useful when you don't have a JWT but do have a trusted user identifier)
server.tool(
  'list_orders_for_user',
  {
    user_id: z.string().uuid(),
    limit: z.number().int().min(1).max(200).default(50)
  },
  async ({ user_id, limit }) => {
    // Only safe because this is an admin-context tool — not exposed to end users
    const orders = assertNoError(
      await adminClient.from('orders').select('*').eq('user_id', user_id).limit(limit)
    );
    return { content: [{ type: 'text', text: JSON.stringify(orders, null, 2) }] };
  }
);

A critical RLS pitfall: when RLS policies are misconfigured (e.g., a policy that always evaluates to false), queries succeed and return empty result sets rather than errors. This is indistinguishable from "no matching rows" at the API layer. Test RLS policies explicitly by querying with a known user JWT and verifying row counts against expected data.

Realtime events as MCP notifications

Supabase Realtime delivers Postgres CDC (change data capture) events over a WebSocket. You can listen for INSERT, UPDATE, and DELETE events on any table and forward them as MCP resource list or resource update notifications. The key architectural point: Supabase Realtime uses its own WebSocket connection, independent of whatever MCP transport (SSE or stdio) your server is using. You must manage both lifecycles separately.

import { RealtimeChannel } from '@supabase/supabase-js';
import { adminClient } from './supabase.js';

let realtimeChannel: RealtimeChannel | null = null;

function startRealtimeSubscription(server: import('@modelcontextprotocol/sdk/server/index.js').Server): void {
  realtimeChannel = adminClient
    .channel('db-changes')
    .on(
      'postgres_changes',
      { event: 'INSERT', schema: 'public', table: 'products' },
      async (payload) => {
        console.log('New product inserted:', payload.new);
        // Notify MCP clients that the resource list has changed
        try {
          await server.sendResourceListChanged();
        } catch (e) {
          console.error('Failed to send resource list changed:', e);
        }
      }
    )
    .on(
      'postgres_changes',
      { event: 'UPDATE', schema: 'public', table: 'products' },
      async (payload) => {
        const productId = (payload.new as { id: string }).id;
        const uri = `supabase://products/${productId}`;
        try {
          await server.sendResourceUpdated({ uri });
        } catch (e) {
          console.error('Failed to send resource updated:', e);
        }
      }
    )
    .on(
      'postgres_changes',
      { event: 'DELETE', schema: 'public', table: 'products' },
      async () => {
        try {
          await server.sendResourceListChanged();
        } catch (e) {
          console.error('Failed to send resource list changed on delete:', e);
        }
      }
    )
    .subscribe((status, err) => {
      if (status === 'SUBSCRIBED') {
        console.log('Supabase Realtime subscribed');
      } else if (status === 'CHANNEL_ERROR') {
        console.error('Supabase Realtime channel error:', err);
      }
    });
}

// Clean up on MCP server shutdown
async function stopRealtimeSubscription(): Promise<void> {
  if (realtimeChannel) {
    await adminClient.removeChannel(realtimeChannel);
    realtimeChannel = null;
  }
}

Storage tools

Supabase Storage provides S3-compatible object storage with RLS-style bucket policies. MCP tools can upload, download, and list files using supabase.storage. For binary content in MCP tool responses, use base64 encoding with MIME type detection.

import { z } from 'zod';
import { adminClient } from './supabase.js';

server.tool(
  'upload_file',
  {
    bucket: z.string(),
    path: z.string(),
    content_base64: z.string(),
    content_type: z.string().default('application/octet-stream')
  },
  async ({ bucket, path, content_base64, content_type }) => {
    const buffer = Buffer.from(content_base64, 'base64');
    const { data, error } = await adminClient.storage
      .from(bucket)
      .upload(path, buffer, { contentType: content_type, upsert: true });

    if (error) throw new McpError(ErrorCode.InternalError, error.message);
    return { content: [{ type: 'text', text: JSON.stringify({ path: data.path }) }] };
  }
);

server.tool(
  'download_file',
  {
    bucket: z.string(),
    path: z.string()
  },
  async ({ bucket, path }) => {
    const { data, error } = await adminClient.storage.from(bucket).download(path);
    if (error) throw new McpError(ErrorCode.InternalError, error.message);

    const buffer = Buffer.from(await data.arrayBuffer());
    const mimeType = data.type || 'application/octet-stream';
    const isImage = mimeType.startsWith('image/');

    return {
      content: [{
        type: isImage ? 'image' : 'text',
        ...(isImage
          ? { data: buffer.toString('base64'), mimeType }
          : { text: buffer.toString('base64') })
      }]
    };
  }
);

server.tool(
  'list_files',
  {
    bucket: z.string(),
    prefix: z.string().default(''),
    limit: z.number().int().min(1).max(200).default(50)
  },
  async ({ bucket, prefix, limit }) => {
    const { data, error } = await adminClient.storage
      .from(bucket)
      .list(prefix, { limit });
    if (error) throw new McpError(ErrorCode.InternalError, error.message);
    return { content: [{ type: 'text', text: JSON.stringify(data, null, 2) }] };
  }
);

Health endpoint: /health/supabase

The most important thing to check in a Supabase health endpoint is whether the project is paused. Supabase free tier projects pause after 7 days of inactivity — the MCP server process stays alive, but every Supabase query returns a 503. Without a health endpoint, this failure is invisible to uptime monitors.

import express from 'express';
import { adminClient } from './supabase.js';

const app = express();

app.get('/health/supabase', async (_req, res) => {
  const start = Date.now();
  const checks: Record<string, unknown> = {};

  // 1. Database connectivity — lightweight SELECT 1 via RPC or any small table
  try {
    const dbStart = Date.now();
    const { error } = await adminClient
      .from('_health_probe')   // create this table: CREATE TABLE _health_probe (id int);
      .select('id')
      .limit(1);
    checks.database = {
      status: error ? 'error' : 'ok',
      latency_ms: Date.now() - dbStart,
      error: error?.message ?? null
    };
  } catch (e) {
    checks.database = { status: 'error', error: (e as Error).message };
  }

  // 2. Realtime subscription status
  checks.realtime = {
    status: realtimeChannel?.state === 'joined' ? 'ok' : 'disconnected',
    channel_state: realtimeChannel?.state ?? 'none'
  };

  // 3. Storage accessibility
  try {
    const storageStart = Date.now();
    const { error } = await adminClient.storage.listBuckets();
    checks.storage = {
      status: error ? 'error' : 'ok',
      latency_ms: Date.now() - storageStart,
      error: error?.message ?? null
    };
  } catch (e) {
    checks.storage = { status: 'error', error: (e as Error).message };
  }

  const allOk = Object.values(checks).every(
    (c) => (c as { status: string }).status === 'ok'
  );

  res.status(allOk ? 200 : 503).json({
    status: allOk ? 'ok' : 'degraded',
    elapsed_ms: Date.now() - start,
    checks
  });
});

Silent failure modes

Frequently asked questions

Further reading

• MCP Server Postgres — direct pg driver connection and query tools
• MCP Server Row-Level Security — per-user data isolation in MCP tools
• MCP Server Authentication — passing user JWTs through MCP tool calls
• MCP Server Health Check — designing endpoints for uptime monitors
• MCP Server Notifications — resource update and list-changed patterns