Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:1128-1298 (handler)

  The primary handler function for the 'admin' tool. It parses the 'operation' parameter and executes corresponding SQL queries for database administration tasks such as getting database info, managing users, granting/revoking permissions, and maintenance operations like VACUUM, ANALYZE, and REINDEX.

  private async handleAdmin(args: any) {
    const { operation, username, password, permissions, tableName, options = {} } = args;
  
    switch (operation) {
      case 'database_info':
        const dbInfo = await this.queryClient.executeQuery(`
          SELECT 
            current_database() as database_name,
            current_user as current_user,
            session_user as session_user,
            current_setting('server_version') as postgres_version,
            current_setting('server_encoding') as encoding,
            current_setting('timezone') as timezone,
            pg_database_size(current_database()) as database_size_bytes,
            pg_size_pretty(pg_database_size(current_database())) as database_size,
            (SELECT count(*) FROM pg_stat_activity WHERE datname = current_database()) as active_connections,
            current_setting('max_connections') as max_connections,
            current_setting('shared_buffers') as shared_buffers,
            current_setting('effective_cache_size') as effective_cache_size
        `);
        
        const tableCount = await this.queryClient.executeQuery(`
          SELECT count(*) as table_count
          FROM information_schema.tables 
          WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
        `);
        
        const result = {
          ...dbInfo.rows[0],
          table_count: parseInt(tableCount.rows[0].table_count),
          uptime: await this.getDatabaseUptime()
        };
        
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(result, null, 2)
          }]
        };
  
      case 'list_users':
        const users = await this.queryClient.executeQuery(`
          SELECT 
            usename as username,
            usesysid as user_id,
            usecreatedb as can_create_db,
            usesuper as is_superuser,
            userepl as can_replicate,
            usebypassrls as bypass_rls,
            valuntil as password_expires,
            (SELECT string_agg(datname, ', ') 
             FROM pg_database 
             WHERE has_database_privilege(usename, datname, 'CONNECT')) as accessible_databases
          FROM pg_user
          ORDER BY usename
        `);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(users.rows, null, 2)
          }]
        };
  
      case 'create_user':
        if (!username || !password) {
          throw new Error('Username and password are required for user creation');
        }
        await this.queryClient.executeQuery(`CREATE USER ${username} WITH PASSWORD '${password}'`);
        return {
          content: [{
            type: 'text',
            text: `User '${username}' created successfully`
          }]
        };
  
      case 'drop_user':
        if (!username) {
          throw new Error('Username is required for user deletion');
        }
        await this.queryClient.executeQuery(`DROP USER ${username}`);
        return {
          content: [{
            type: 'text',
            text: `User '${username}' dropped successfully`
          }]
        };
  
      case 'grant_permissions':
        if (!username || !permissions || permissions.length === 0) {
          throw new Error('Username and permissions are required');
        }
        
        const target = tableName ? `TABLE ${tableName}` : 'ALL TABLES IN SCHEMA public';
        const grantSQL = `GRANT ${permissions.join(', ')} ON ${target} TO ${username}`;
        await this.queryClient.executeQuery(grantSQL);
        
        return {
          content: [{
            type: 'text',
            text: `Permissions ${permissions.join(', ')} granted to '${username}' on ${target}`
          }]
        };
  
      case 'revoke_permissions':
        if (!username || !permissions || permissions.length === 0) {
          throw new Error('Username and permissions are required');
        }
        
        const revokeTarget = tableName ? `TABLE ${tableName}` : 'ALL TABLES IN SCHEMA public';
        const revokeSQL = `REVOKE ${permissions.join(', ')} ON ${revokeTarget} FROM ${username}`;
        await this.queryClient.executeQuery(revokeSQL);
        
        return {
          content: [{
            type: 'text',
            text: `Permissions ${permissions.join(', ')} revoked from '${username}' on ${revokeTarget}`
          }]
        };
  
      case 'vacuum':
        if (tableName) {
          const vacuumSQL = `VACUUM${options.full ? ' FULL' : ''} ${tableName}`;
          await this.queryClient.executeQuery(vacuumSQL);
          return {
            content: [{
              type: 'text',
              text: `Vacuum completed for table '${tableName}'`
            }]
          };
        } else {
          await this.queryClient.executeQuery('VACUUM');
          return {
            content: [{
              type: 'text',
              text: 'Database vacuum completed'
            }]
          };
        }
  
      case 'analyze':
        if (tableName) {
          await this.queryClient.executeQuery(`ANALYZE ${tableName}`);
          return {
            content: [{
              type: 'text',
              text: `Analyze completed for table '${tableName}'`
            }]
          };
        } else {
          await this.queryClient.executeQuery('ANALYZE');
          return {
            content: [{
              type: 'text',
              text: 'Database analyze completed'
            }]
          };
        }
  
      case 'reindex_database':
        await this.queryClient.executeQuery('REINDEX DATABASE CONCURRENTLY');
        return {
          content: [{
            type: 'text',
            text: 'Database reindex completed'
          }]
        };
  
      default:
        throw new Error(`Unknown admin operation: ${operation}`);
    }
  }

• src/index.ts:320-360 (schema)

  The input schema definition for the 'admin' tool, specifying parameters like operation, username, password, permissions, etc., and validating inputs before execution.

  {
    name: 'admin',
    description: 'Database administration: users, permissions, database info, maintenance operations',
    inputSchema: {
      type: 'object',
      properties: {
        operation: {
          type: 'string',
          enum: ['database_info', 'list_users', 'create_user', 'drop_user', 'grant_permissions', 'revoke_permissions', 'vacuum', 'analyze', 'reindex_database'],
          description: 'Admin operation to perform'
        },
        username: {
          type: 'string',
          description: 'Username (required for user operations)'
        },
        password: {
          type: 'string',
          description: 'Password (required for create_user)'
        },
        permissions: {
          type: 'array',
          items: { type: 'string' },
          description: 'Permissions to grant/revoke'
        },
        tableName: {
          type: 'string',
          description: 'Table name (for permission operations)'
        },
        options: {
          type: 'object',
          properties: {
            full: { type: 'boolean', default: false },
            verbose: { type: 'boolean', default: false },
            analyze: { type: 'boolean', default: false }
          },
          description: 'Operation options'
        }
      },
      required: ['operation']
    }
  },

• src/index.ts:634-636 (registration)

  Registration of the 'admin' tool schema via the ListToolsRequestSchema handler, which returns all tool definitions including 'admin'.

  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
    tools: toolDefinitions,
  }));

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

  Dispatch registration in the CallToolRequestSchema handler switch statement that routes 'admin' tool calls to the handleAdmin function.

  case 'admin':

• src/common/security.ts:278-280 (helper)

  Helper function to detect potentially dangerous administrative SQL operations, used by security validator for admin tool executions.

   * Check if operation is a dangerous admin operation
   */
  private isDangerousAdminOperation(sql: string): boolean {