PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/users.ts:586-804 (handler)

  Primary handler implementation for the pg_manage_users tool. Defines the tool object with name, description, input schema, and execute function that routes to specific user management operations (create, drop, alter, grant, revoke permissions, list users).

  export const manageUsersTool: PostgresTool = {
    name: 'pg_manage_users',
    description: 'Manage PostgreSQL users and permissions - create, drop, alter users, grant/revoke permissions. Examples: operation="create" with username="testuser", operation="grant" with username, permissions, target, targetType',
    inputSchema: z.object({
      connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
      operation: z.enum(['create', 'drop', 'alter', 'grant', 'revoke', 'get_permissions', 'list']).describe('Operation: create (new user), drop (remove user), alter (modify user), grant (permissions), revoke (permissions), get_permissions (view permissions), list (all users)'),
      
      // Common parameters
      username: z.string().optional().describe('Username (required for create/drop/alter/grant/revoke/get_permissions, optional filter for list)'),
      
      // Create user parameters
      password: z.string().optional().describe('Password for the user (for create operation)'),
      superuser: z.boolean().optional().describe('Grant superuser privileges (for create/alter operations)'),
      createdb: z.boolean().optional().describe('Allow user to create databases (for create/alter operations)'),
      createrole: z.boolean().optional().describe('Allow user to create roles (for create/alter operations)'),
      login: z.boolean().optional().describe('Allow user to login (for create/alter operations)'),
      replication: z.boolean().optional().describe('Allow replication privileges (for create/alter operations)'),
      connectionLimit: z.number().optional().describe('Maximum number of connections (for create/alter operations)'),
      validUntil: z.string().optional().describe('Password expiration date YYYY-MM-DD (for create/alter operations)'),
      inherit: z.boolean().optional().describe('Inherit privileges from parent roles (for create/alter operations)'),
      
      // Drop user parameters
      ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop operation)'),
      cascade: z.boolean().optional().describe('Include CASCADE to drop owned objects (for drop/revoke operations)'),
      
      // Permission parameters  
      permissions: z.array(z.enum(['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER', 'ALL'])).optional().describe('Permissions to grant/revoke: ["SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", "ALL"]'),
      target: z.string().optional().describe('Target object name (for grant/revoke operations)'),
      targetType: z.enum(['table', 'schema', 'database', 'sequence', 'function']).optional().describe('Type of target object (for grant/revoke operations)'),
      withGrantOption: z.boolean().optional().describe('Allow user to grant these permissions to others (for grant operation)'),
      
      // Get permissions parameters
      schema: z.string().optional().describe('Filter by schema (for get_permissions operation)'),
      
      // List users parameters
      includeSystemRoles: z.boolean().optional().describe('Include system roles (for list operation)')
    }),
    // biome-ignore lint/suspicious/noExplicitAny: <explanation>
    execute: async (args: any, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
      const { 
        connectionString: connStringArg,
        operation,
        username,
        password,
        superuser,
        createdb,
        createrole,
        login,
        replication,
        connectionLimit,
        validUntil,
        inherit,
        ifExists,
        cascade,
        permissions,
        target,
        targetType,
        withGrantOption,
        schema,
        includeSystemRoles
      } = args as {
        connectionString?: string;
        operation: 'create' | 'drop' | 'alter' | 'grant' | 'revoke' | 'get_permissions' | 'list';
        username?: string;
        password?: string;
        superuser?: boolean;
        createdb?: boolean;
        createrole?: boolean;
        login?: boolean;
        replication?: boolean;
        connectionLimit?: number;
        validUntil?: string;
        inherit?: boolean;
        ifExists?: boolean;
        cascade?: boolean;
        permissions?: string[];
        target?: string;
        targetType?: 'table' | 'schema' | 'database' | 'sequence' | 'function';
        withGrantOption?: boolean;
        schema?: string;
        includeSystemRoles?: boolean;
      };
  
      try {
        switch (operation) {
          case 'create': {
            if (!username) {
              return { 
                content: [{ type: 'text', text: 'Error: username is required for create operation' }], 
                isError: true 
              };
            }
            const result = await executeCreateUser({
              connectionString: connStringArg,
              username,
              password,
              superuser: superuser ?? false,
              createdb: createdb ?? false,
              createrole: createrole ?? false,
              login: login ?? true,
              replication: replication ?? false,
              connectionLimit,
              validUntil,
              inherit: inherit ?? true
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `User ${result.username} created successfully. Details: ${JSON.stringify(result)}` }] };
          }
  
          case 'drop': {
            if (!username) {
              return { 
                content: [{ type: 'text', text: 'Error: username is required for drop operation' }], 
                isError: true 
              };
            }
            const result = await executeDropUser({
              connectionString: connStringArg,
              username,
              ifExists: ifExists ?? true,
              cascade: cascade ?? false
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `User ${result.username} dropped successfully. Details: ${JSON.stringify(result)}` }] };
          }
  
          case 'alter': {
            if (!username) {
              return { 
                content: [{ type: 'text', text: 'Error: username is required for alter operation' }], 
                isError: true 
              };
            }
            const result = await executeAlterUser({
              connectionString: connStringArg,
              username,
              password,
              superuser,
              createdb,
              createrole,
              login,
              replication,
              connectionLimit,
              validUntil,
              inherit
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `User ${result.username} altered successfully. Changes: ${result.changes.join(', ')}. Details: ${JSON.stringify(result)}` }] };
          }
  
          case 'grant': {
            if (!username || !permissions || !target || !targetType) {
              return { 
                content: [{ type: 'text', text: 'Error: username, permissions, target, and targetType are required for grant operation' }], 
                isError: true 
              };
            }
            const result = await executeGrantPermissions({
              connectionString: connStringArg,
              username,
              permissions: permissions as ('SELECT' | 'INSERT' | 'UPDATE' | 'DELETE' | 'TRUNCATE' | 'REFERENCES' | 'TRIGGER' | 'ALL')[],
              target,
              targetType,
              withGrantOption: withGrantOption ?? false,
              schema: schema ?? 'public'
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `Permissions granted to ${result.username} on ${result.target}. Details: ${JSON.stringify(result)}` }] };
          }
  
          case 'revoke': {
            if (!username || !permissions || !target || !targetType) {
              return { 
                content: [{ type: 'text', text: 'Error: username, permissions, target, and targetType are required for revoke operation' }], 
                isError: true 
              };
            }
            const result = await executeRevokePermissions({
              connectionString: connStringArg,
              username,
              permissions: permissions as ('SELECT' | 'INSERT' | 'UPDATE' | 'DELETE' | 'TRUNCATE' | 'REFERENCES' | 'TRIGGER' | 'ALL')[],
              target,
              targetType,
              cascade: cascade ?? false,
              schema: schema ?? 'public'
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: `Permissions revoked from ${result.username} on ${result.target}. Details: ${JSON.stringify(result)}` }] };
          }
  
          case 'get_permissions': {
            const result = await executeGetUserPermissions({
              connectionString: connStringArg,
              username,
              schema,
              targetType
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }] };
          }
  
          case 'list': {
            const result = await executeListUsers({
              connectionString: connStringArg,
              includeSystemRoles: includeSystemRoles ?? false
            }, getConnectionStringVal);
            return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }] };
          }
  
          default:
            return { 
              content: [{ type: 'text', text: `Error: Unknown operation "${operation}". Supported operations: create, drop, alter, grant, revoke, get_permissions, list` }], 
              isError: true 
            };
        }
  
      } catch (error) {
        const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
        return { 
          content: [{ type: 'text', text: `Error executing ${operation} operation: ${errorMessage}` }], 
          isError: true 
        };
      }
    }
  }; 

• src/tools/users.ts:589-622 (schema)

  Zod input schema defining parameters for pg_manage_users tool, including operation type and all user management options.

  inputSchema: z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    operation: z.enum(['create', 'drop', 'alter', 'grant', 'revoke', 'get_permissions', 'list']).describe('Operation: create (new user), drop (remove user), alter (modify user), grant (permissions), revoke (permissions), get_permissions (view permissions), list (all users)'),
    
    // Common parameters
    username: z.string().optional().describe('Username (required for create/drop/alter/grant/revoke/get_permissions, optional filter for list)'),
    
    // Create user parameters
    password: z.string().optional().describe('Password for the user (for create operation)'),
    superuser: z.boolean().optional().describe('Grant superuser privileges (for create/alter operations)'),
    createdb: z.boolean().optional().describe('Allow user to create databases (for create/alter operations)'),
    createrole: z.boolean().optional().describe('Allow user to create roles (for create/alter operations)'),
    login: z.boolean().optional().describe('Allow user to login (for create/alter operations)'),
    replication: z.boolean().optional().describe('Allow replication privileges (for create/alter operations)'),
    connectionLimit: z.number().optional().describe('Maximum number of connections (for create/alter operations)'),
    validUntil: z.string().optional().describe('Password expiration date YYYY-MM-DD (for create/alter operations)'),
    inherit: z.boolean().optional().describe('Inherit privileges from parent roles (for create/alter operations)'),
    
    // Drop user parameters
    ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop operation)'),
    cascade: z.boolean().optional().describe('Include CASCADE to drop owned objects (for drop/revoke operations)'),
    
    // Permission parameters  
    permissions: z.array(z.enum(['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER', 'ALL'])).optional().describe('Permissions to grant/revoke: ["SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", "ALL"]'),
    target: z.string().optional().describe('Target object name (for grant/revoke operations)'),
    targetType: z.enum(['table', 'schema', 'database', 'sequence', 'function']).optional().describe('Type of target object (for grant/revoke operations)'),
    withGrantOption: z.boolean().optional().describe('Allow user to grant these permissions to others (for grant operation)'),
    
    // Get permissions parameters
    schema: z.string().optional().describe('Filter by schema (for get_permissions operation)'),
    
    // List users parameters
    includeSystemRoles: z.boolean().optional().describe('Include system roles (for list operation)')
  }),

• src/index.ts:225-257 (registration)

  Registration of manageUsersTool (pg_manage_users) in the central allTools array used to initialize the MCP server's available tools list.

  const allTools: PostgresTool[] = [
    // Core Analysis & Debugging
    analyzeDatabaseTool,
    debugDatabaseTool,
    
    // Schema & Structure Management (Meta-Tools)
    manageSchemaTools,
    manageFunctionsTool,
    manageTriggersTools,
    manageIndexesTool,
    manageConstraintsTool,
    manageRLSTool,
    
    // User & Security Management
    manageUsersTool,
    
    // Query & Performance Management
    manageQueryTool,
    
    // Data Operations (Enhancement Tools)
    executeQueryTool,
    executeMutationTool,
    executeSqlTool,
    
    // Documentation & Metadata
    manageCommentsTool,
    
    // Data Migration & Monitoring
    exportTableDataTool,
    importTableDataTool,
    copyBetweenDatabasesTool,
    monitorDatabaseTool
  ];

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

  Import of the manageUsersTool from users.ts for registration in the MCP server.

  import { manageUsersTool } from './tools/users.js';

• src/tools/users.ts:45-89 (helper)

  Helper function for creating a new PostgreSQL user, called by the main handler for 'create' operation.

  async function executeCreateUser(
    input: CreateUserInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ username: string; created: true }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { 
      username, 
      password, 
      superuser, 
      createdb, 
      createrole, 
      login, 
      replication, 
      connectionLimit, 
      validUntil,
      inherit 
    } = input;
  
    try {
      await db.connect(resolvedConnectionString);
      
      const options = [];
      
      if (password) options.push(`PASSWORD '${password.replace(/'/g, "''")}'`);
      if (superuser) options.push('SUPERUSER');
      if (createdb) options.push('CREATEDB');
      if (createrole) options.push('CREATEROLE');
      if (login) options.push('LOGIN');
      if (replication) options.push('REPLICATION');
      if (!inherit) options.push('NOINHERIT');
      if (connectionLimit !== undefined) options.push(`CONNECTION LIMIT ${connectionLimit}`);
      if (validUntil) options.push(`VALID UNTIL '${validUntil}'`);
      
      const createUserSQL = `CREATE USER "${username}"${options.length > 0 ? ` ${options.join(' ')}` : ''}`;
      
      await db.query(createUserSQL);
      
      return { username, created: true };
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to create user: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }