Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/create_auth_user.ts:52-136 (handler)

  The execute function implementing the core logic of the 'create_auth_user' tool. It performs input destructuring, checks for direct PG connection, warns about security, executes a transaction with pgcrypto check, inserts into auth.users with hashed password, parses output with Zod, and handles errors including unique email violations.

  execute: async (input: CreateAuthUserInput, context: ToolContext): Promise<CreateAuthUserOutput> => { // Use CreateAuthUserOutput
      const client = context.selfhostedClient;
      const { email, password, role, app_metadata, user_metadata } = input;
  
      // Direct DB connection is absolutely required for this direct insert
      if (!client.isPgAvailable()) {
           context.log('Direct database connection (DATABASE_URL) is required to create an auth user directly.', 'error');
          throw new Error('Direct database connection (DATABASE_URL) is required to create an auth user directly.');
      }
  
      console.warn(`SECURITY WARNING: Creating user ${email} with plain text password via direct DB insert.`);
      context.log(`Attempting to create user ${email}...`, 'warn');
  
      // Use transaction to ensure atomicity and get pg client
      const createdUser = await client.executeTransactionWithPg(async (pgClient: PoolClient) => {
          // Check if pgcrypto extension is available (needed for crypt)
          try {
              await pgClient.query("SELECT crypt('test', gen_salt('bf'))");
          } catch (err) {
               throw new Error('Failed to execute crypt function. Ensure pgcrypto extension is enabled in the database.');
          }
          
          // Construct the INSERT statement with parameterization
          const sql = `
              INSERT INTO auth.users (
                  instance_id, email, encrypted_password, role,
                  raw_app_meta_data, raw_user_meta_data, 
                  aud, email_confirmed_at, confirmation_sent_at -- Set required defaults
              )
              VALUES (
                  COALESCE(current_setting('app.instance_id', TRUE), '00000000-0000-0000-0000-000000000000')::uuid,
                  $1, crypt($2, gen_salt('bf')),
                  $3,
                  $4::jsonb,
                  $5::jsonb,
                  'authenticated', now(), now()
              )
              RETURNING id, email, role, raw_app_meta_data, raw_user_meta_data, created_at::text, last_sign_in_at::text;
          `;
  
          const params = [
              email,
              password,
              role || 'authenticated', // Default role
              JSON.stringify(app_metadata || {}),
              JSON.stringify(user_metadata || {})
          ];
  
          try {
              const result = await pgClient.query(sql, params);
              if (result.rows.length === 0) {
                   throw new Error('User creation failed, no user returned after insert.');
              }
              return CreatedAuthUserZodSchema.parse(result.rows[0]);
          } catch (dbError: unknown) {
              let errorMessage = 'Unknown database error during user creation';
              let isUniqueViolation = false;
  
              if (typeof dbError === 'object' && dbError !== null && 'code' in dbError) {
                  // Check PG error code for unique violation safely
                  if (dbError.code === '23505') {
                      isUniqueViolation = true;
                      errorMessage = `User creation failed: Email '${email}' likely already exists.`;
                  } else if ('message' in dbError && typeof dbError.message === 'string') {
                       errorMessage = `Database error (${dbError.code}): ${dbError.message}`;
                  } else {
                      errorMessage = `Database error code: ${dbError.code}`;
                  }
              } else if (dbError instanceof Error) {
                  errorMessage = `Database error during user creation: ${dbError.message}`;
              } else {
                   errorMessage = `Database error during user creation: ${String(dbError)}`;
              }
  
              console.error('Error creating user in DB:', dbError); // Log the original error
  
              // Throw a specific error message
              throw new Error(errorMessage);
          }
      });
  
      console.error(`Successfully created user ${email} with ID ${createdUser.id}.`);
      context.log(`Successfully created user ${email} with ID ${createdUser.id}.`);
      return createdUser; // Matches CreateAuthUserOutput (AuthUser)
  },

• src/tools/create_auth_user.ts:8-14 (schema)

  Zod input schema for validating the tool's input parameters including email, password, role, and metadata.

  const CreateAuthUserInputSchema = z.object({
      email: z.string().email().describe('The email address for the new user.'),
      password: z.string().min(6).describe('Plain text password (min 6 chars). WARNING: Insecure.'),
      role: z.string().optional().describe('User role.'),
      app_metadata: z.record(z.unknown()).optional().describe('Optional app metadata.'),
      user_metadata: z.record(z.unknown()).optional().describe('Optional user metadata.'),
  });

• src/tools/create_auth_user.ts:18-29 (schema)

  Zod output schema and type for the created AuthUser, matching the structure returned from the database.

  const CreatedAuthUserZodSchema = z.object({
      id: z.string().uuid(),
      email: z.string().email().nullable(),
      role: z.string().nullable(),
      created_at: z.string().nullable(),
      last_sign_in_at: z.string().nullable(), // Will likely be null on creation
      raw_app_meta_data: z.record(z.unknown()).nullable(),
      raw_user_meta_data: z.record(z.unknown()).nullable(),
      // Add other fields returned by the INSERT if necessary
  });
  // Use AuthUser for the output type hint
  type CreateAuthUserOutput = AuthUser;

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

  Registration of the createAuthUserTool in the availableTools object, making it available to the MCP server.

  [createAuthUserTool.name]: createAuthUserTool as AppTool,

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

  Import statement for the createAuthUserTool from its implementation file.

  import { createAuthUserTool } from './tools/create_auth_user.js';