Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/update_auth_user.ts:57-154 (handler)

  The execute handler function that implements the core logic for updating an auth user in the Supabase auth.users table using direct PostgreSQL queries.

      execute: async (input: UpdateAuthUserInput, context: ToolContext): Promise<UpdateAuthUserOutput> => { // Use UpdateAuthUserOutput
          const client = context.selfhostedClient;
          const { user_id, email, password, role, app_metadata, user_metadata } = input;
  
          if (!client.isPgAvailable()) {
              context.log('Direct database connection (DATABASE_URL) is required to update auth user details.', 'error');
              throw new Error('Direct database connection (DATABASE_URL) is required to update auth user details.');
          }
  
          const updates: string[] = [];
          const params: (string | object | null)[] = [];
          let paramIndex = 1;
  
          // Dynamically build SET clauses and params array
          if (email !== undefined) {
              updates.push(`email = $${paramIndex++}`);
              params.push(email);
          }
          if (password !== undefined) {
              updates.push(`encrypted_password = crypt($${paramIndex++}, gen_salt('bf'))`);
              params.push(password);
              console.warn(`SECURITY WARNING: Updating password for user ${user_id} with plain text password via direct DB update.`);
          }
          if (role !== undefined) {
              updates.push(`role = $${paramIndex++}`);
              params.push(role);
          }
          if (app_metadata !== undefined) {
              updates.push(`raw_app_meta_data = $${paramIndex++}::jsonb`);
              params.push(JSON.stringify(app_metadata));
          }
          if (user_metadata !== undefined) {
              updates.push(`raw_user_meta_data = $${paramIndex++}::jsonb`);
              params.push(JSON.stringify(user_metadata));
          }
  
          // Add user_id as the final parameter for the WHERE clause
          params.push(user_id);
          const userIdParamIndex = paramIndex;
  
          const sql = `
              UPDATE auth.users
              SET ${updates.join(', ')}, updated_at = NOW()
              WHERE id = $${userIdParamIndex}
              RETURNING id, email, role, raw_app_meta_data, raw_user_meta_data, created_at::text, updated_at::text, last_sign_in_at::text;
          `;
  
          console.error(`Attempting to update auth user ${user_id}...`);
          context.log(`Attempting to update auth user ${user_id}...`);
  
          const updatedUser = await client.executeTransactionWithPg(async (pgClient: PoolClient) => {
               // Check pgcrypto if password is being updated
               if (password !== undefined) {
                  try {
                      await pgClient.query("SELECT crypt('test', gen_salt('bf'))");
                  } catch (err) {
                      throw new Error('Failed to execute crypt function for password update. Ensure pgcrypto extension is enabled.');
                  }
               }
  
              try {
                  const result = await pgClient.query(sql, params);
                  if (result.rows.length === 0) {
                      throw new Error(`User update failed: User with ID ${user_id} not found or no rows affected.`);
                  }
                  return UpdatedAuthUserZodSchema.parse(result.rows[0]);
              } catch (dbError: unknown) {
                  let errorMessage = 'Unknown database error during user update';
                  let isUniqueViolation = false;
  
                  // Check for potential email unique constraint violation if email was updated
                  if (typeof dbError === 'object' && dbError !== null && 'code' in dbError) {
                      if (email !== undefined && dbError.code === '23505') {
                          isUniqueViolation = true;
                          errorMessage = `User update failed: Email '${email}' likely already exists for another user.`;
                      } 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 update: ${dbError.message}`;
                  } else {
                       errorMessage = `Database error during user update: ${String(dbError)}`;
                  }
  
                  console.error('Error updating user in DB:', dbError);
                  
                  // Throw the specific error message
                  throw new Error(errorMessage);
              }
          });
  
          console.error(`Successfully updated user ${user_id}.`);
          context.log(`Successfully updated user ${user_id}.`);
          return updatedUser; // Matches UpdateAuthUserOutput (AuthUser)
      },
  }; 

• src/tools/update_auth_user.ts:8-18 (schema)

  Zod schema for input validation of the update_auth_user tool, ensuring required fields and at least one update field is provided.

  const UpdateAuthUserInputSchema = z.object({
      user_id: z.string().uuid().describe('The UUID of the user to update.'),
      email: z.string().email().optional().describe('New email address.'),
      password: z.string().min(6).optional().describe('New plain text password (min 6 chars). WARNING: Insecure.'),
      role: z.string().optional().describe('New role.'),
      app_metadata: z.record(z.unknown()).optional().describe('New app metadata (will overwrite existing).'),
      user_metadata: z.record(z.unknown()).optional().describe('New user metadata (will overwrite existing).'),
  }).refine(data => 
      data.email || data.password || data.role || data.app_metadata || data.user_metadata,
      { message: "At least one field to update (email, password, role, app_metadata, user_metadata) must be provided." }
  );

• src/tools/update_auth_user.ts:22-31 (schema)

  Zod schema for output validation of the updated auth user object.

  const UpdatedAuthUserZodSchema = z.object({
      id: z.string().uuid(),
      email: z.string().email().nullable(),
      role: z.string().nullable(),
      created_at: z.string().nullable(),
      updated_at: z.string().nullable(), // Expect this to be updated
      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(),
  });

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

  Registration of the update_auth_user tool in the availableTools object, which is used to populate the MCP server's tool capabilities.

  [updateAuthUserTool.name]: updateAuthUserTool as AppTool,

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

  Import statement for the update_auth_user tool in the main index file.

  import { updateAuthUserTool } from './tools/update_auth_user.js';