Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

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

  The execute function that performs a paginated SQL query on the auth.users table using direct PostgreSQL connection, validates the response with Zod schema, 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 for input (limit/offset pagination) and output (array of AuthUser objects with fields like id, email, role, metadata, timestamps). Includes type inferences.

  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/tools/list_auth_users.ts:31-46 (schema)

  Static JSON schema for MCP tool capabilities, mirroring the Zod input schema.

  const mcpInputSchema = {
      type: 'object',
      properties: {
          limit: {
              type: 'number',
              description: 'Max number of users to return',
              default: 50,
          },
          offset: {
              type: 'number',
              description: 'Number of users to skip',
              default: 0,
          },
      },
      required: [],
  };

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

  Adds listAuthUsersTool to the availableTools registry object used by the MCP server.

  [listAuthUsersTool.name]: listAuthUsersTool as AppTool,

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

  Imports the listAuthUsersTool for registration in the MCP server.

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