Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/list_auth_users.ts:56-94 (handler)

  The execute function that performs the tool's core logic: extracts input parameters, checks for PG connection, constructs and executes SQL query on auth.users with LIMIT/OFFSET, validates response using Zod schema via handleSqlResponse, logs results, and returns the list of auth users.

  execute: async (input: ListAuthUsersInput, context: ToolContext): Promise<ListAuthUsersOutput> => {
      const client = context.selfhostedClient;
      const { limit, offset } = input;
  
      // Check if direct DB connection is available, as it's likely needed for auth.users
      if (!client.isPgAvailable()) {
          context.log('Direct database connection (DATABASE_URL) is required to list auth users.', 'error');
          throw new Error('Direct database connection (DATABASE_URL) is required to list auth users.');
      }
  
      // Construct SQL query - ensure schema name is correct
      const listUsersSql = `
          SELECT
              id,
              email,
              role,
              raw_app_meta_data,
              raw_user_meta_data,
              created_at::text, -- Cast timestamp to text for JSON
              last_sign_in_at::text -- Cast timestamp to text for JSON
          FROM
              auth.users
          ORDER BY
              created_at DESC
          LIMIT ${limit}
          OFFSET ${offset}
      `; // No semicolon needed here
  
      console.error('Attempting to list auth users using direct DB connection...');
      // Use direct connection (executeSqlWithPg) as it likely has necessary privileges
      const result = await client.executeSqlWithPg(listUsersSql);
  
      // Validate and return
      const validatedUsers = handleSqlResponse(result, ListAuthUsersOutputSchema);
  
      console.error(`Found ${validatedUsers.length} users.`);
      context.log(`Found ${validatedUsers.length} users.`);
      return validatedUsers;
  },

• src/tools/list_auth_users.ts:7-28 (schema)

  Zod schemas defining the input (limit/offset pagination) and output structure (array of AuthUser objects with id, email, role, metadata, timestamps) for the list_auth_users tool. Includes type inferences and AuthUserZodSchema.

  const ListAuthUsersInputSchema = z.object({
      limit: z.number().int().positive().optional().default(50).describe('Max number of users to return'),
      offset: z.number().int().nonnegative().optional().default(0).describe('Number of users to skip'),
      // Add filters later (e.g., by email pattern, role)
  });
  type ListAuthUsersInput = z.infer<typeof ListAuthUsersInputSchema>;
  
  // Output schema - Zod for validation
  const AuthUserZodSchema = z.object({
      id: z.string().uuid(),
      email: z.string().email().nullable(),
      role: z.string().nullable(),
      // Timestamps returned as text from DB might not strictly be ISO 8601 / Zod datetime compliant
      created_at: z.string().nullable(),
      last_sign_in_at: z.string().nullable(),
      raw_app_meta_data: z.record(z.unknown()).nullable(),
      raw_user_meta_data: z.record(z.unknown()).nullable(),
      // Add more fields as needed (e.g., email_confirmed_at, phone)
  });
  const ListAuthUsersOutputSchema = z.array(AuthUserZodSchema);
  // Use AuthUser[] for the output type hint
  type ListAuthUsersOutput = AuthUser[];

• src/index.ts:24-24 (registration)

  Import statement that brings the listAuthUsersTool into the main index file for registration in the MCP server.

  import { listAuthUsersTool } from './tools/list_auth_users.js';

• src/index.ts:113-113 (registration)

  Registers the listAuthUsersTool in the availableTools object, which is used to populate MCP server capabilities and handle tool calls.

  [listAuthUsersTool.name]: listAuthUsersTool as AppTool,