Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:845-910 (handler)

  Main handler function for the 'schemas' MCP tool. Dispatches to SchemaAPIClient methods based on action (list, info, create, drop, rename).

  private async handleSchemas(args: any) {
    const { action, schemaName, owner, options = {} } = args;
  
    switch (action) {
      case 'list':
        const schemas = await this.schemaClient.listSchemas(options.includeSystem);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(schemas, null, 2)
          }]
        };
  
      case 'info':
        ParameterValidator.validateRequired(schemaName, 'schemaName');
        const schemaInfo = await this.schemaClient.getSchemaInfo(schemaName);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(schemaInfo, null, 2)
          }]
        };
  
      case 'create':
        ParameterValidator.validateRequired(schemaName, 'schemaName');
        const createResult = await this.schemaClient.createSchema(schemaName, {
          ifNotExists: options.ifNotExists,
          owner,
          authorization: options.authorization
        });
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(createResult, null, 2)
          }]
        };
  
      case 'drop':
        ParameterValidator.validateRequired(schemaName, 'schemaName');
        const dropResult = await this.schemaClient.dropSchema(
          schemaName,
          options.cascade,
          options.ifExists
        );
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(dropResult, null, 2)
          }]
        };
  
      case 'rename':
        ParameterValidator.validateRequired(schemaName, 'schemaName');
        ParameterValidator.validateRequired(options.newName, 'newName');
        const renameResult = await this.schemaClient.renameSchema(schemaName, options.newName);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(renameResult, null, 2)
          }]
        };
  
      default:
        throw new Error(`Unknown schema action: ${action}`);
    }
  }

• src/index.ts:157-188 (schema)

  Input schema definition for the 'schemas' tool, defining parameters like action, schemaName, owner, and options.

  {
    name: 'schemas',
    description: 'Schema management: list, create, drop schemas and manage schema permissions',
    inputSchema: {
      type: 'object',
      properties: {
        action: {
          type: 'string',
          enum: ['list', 'create', 'drop', 'permissions'],
          description: 'Action: list (all schemas), create (new schema), drop (remove schema), permissions (schema permissions)'
        },
        schemaName: {
          type: 'string',
          description: 'Schema name (required for create, drop, permissions)'
        },
        owner: {
          type: 'string',
          description: 'Schema owner (for create action)'
        },
        options: {
          type: 'object',
          properties: {
            ifNotExists: { type: 'boolean', default: false },
            ifExists: { type: 'boolean', default: true },
            cascade: { type: 'boolean', default: false }
          },
          description: 'Action-specific options'
        }
      },
      required: ['action']
    }
  },

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

  Registration of all tools including 'schemas' via ListToolsRequestSchema handler, exposing toolDefinitions.

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

• src/index.ts:649-650 (registration)

  CallToolRequestSchema switch case that routes 'schemas' tool calls to handleSchemas method.

  case 'schemas':
    return await this.handleSchemas(args);

• src/api/domains/schema-api.ts:31-525 (helper)

  Helper class SchemaAPIClient implementing core schema operations (list, create, drop, etc.) used by the 'schemas' tool handler.

  export class SchemaAPIClient {
    constructor(
      private dbManager: DatabaseConnectionManager,
      private cache?: QueryResultCache
    ) {}
  
    /**
     * List all schemas with detailed information
     */
    async listSchemas(includeSystem: boolean = false): Promise<SchemaInfo[]> {
      const cacheKey = `schemas_list_${includeSystem}`;
      
      // Check cache first
      if (this.cache) {
        const cached = this.cache.get<SchemaInfo[]>(cacheKey);
        if (cached) return cached;
      }
  
      // Simplified query to avoid potential issues with complex joins
      let sql = `
        SELECT 
          n.nspname as schema_name,
          pg_catalog.pg_get_userbyid(n.nspowner) as owner,
          CASE 
            WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast', 'pg_temp_1', 'pg_toast_temp_1') 
            THEN 'system'
            ELSE 'user'
          END as schema_type
        FROM pg_catalog.pg_namespace n
        WHERE 1=1
      `;
  
      if (!includeSystem) {
        sql += ` AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                 AND n.nspname NOT LIKE 'pg_temp_%'
                 AND n.nspname NOT LIKE 'pg_toast_temp_%'`;
      }
  
      sql += ` ORDER BY 
        CASE WHEN n.nspname = 'public' THEN 1 ELSE 2 END,
        CASE 
          WHEN n.nspname IN ('information_schema', 'pg_catalog', 'pg_toast', 'pg_temp_1', 'pg_toast_temp_1') 
          THEN 'system'
          ELSE 'user'
        END,
        n.nspname`;
  
      try {
        const result = await this.dbManager.query(sql, [], { readOnly: true });
        
        // Get additional stats for each schema separately to avoid complex joins
        const schemas: SchemaInfo[] = await Promise.all(
          result.rows.map(async (row: any) => {
            const schemaName = row.schema_name;
            
            // Get table count
            let tableCount = 0;
            try {
              const tableResult = await this.dbManager.query(
                'SELECT COUNT(*) as count FROM pg_tables WHERE schemaname = $1',
                [schemaName],
                { readOnly: true }
              );
              tableCount = parseInt(tableResult.rows[0]?.count) || 0;
            } catch (error) {
              logger.warn('Failed to get table count for schema', { schema: schemaName, error });
            }
  
            // Get view count
            let viewCount = 0;
            try {
              const viewResult = await this.dbManager.query(
                'SELECT COUNT(*) as count FROM pg_views WHERE schemaname = $1',
                [schemaName],
                { readOnly: true }
              );
              viewCount = parseInt(viewResult.rows[0]?.count) || 0;
            } catch (error) {
              logger.warn('Failed to get view count for schema', { schema: schemaName, error });
            }
  
            // Get function count
            let functionCount = 0;
            try {
              const functionResult = await this.dbManager.query(
                `SELECT COUNT(*) as count 
                 FROM pg_proc p
                 JOIN pg_namespace n ON p.pronamespace = n.oid
                 WHERE n.nspname = $1`,
                [schemaName],
                { readOnly: true }
              );
              functionCount = parseInt(functionResult.rows[0]?.count) || 0;
            } catch (error) {
              logger.warn('Failed to get function count for schema', { schema: schemaName, error });
            }
  
            // Get permissions (simplified - don't fail if this doesn't work)
            let permissions: string[] = [];
            try {
              const schemaPermissions = await this.getSchemaPermissions(schemaName);
              permissions = schemaPermissions.map(p => `${p.grantee}:${p.privilege}`);
            } catch (error) {
              logger.warn('Failed to get permissions for schema', { schema: schemaName, error });
            }
            
            return {
              schemaName: row.schema_name,
              owner: row.owner,
              schemaType: row.schema_type,
              tableCount,
              viewCount,
              functionCount,
              sizeBytes: 0, // Calculate separately if needed
              permissions
            };
          })
        );
  
        // Cache the result
        if (this.cache) {
          this.cache.set(cacheKey, schemas);
        }
  
        return schemas;
      } catch (error) {
        logger.error('Failed to list schemas', { error: error instanceof Error ? error.message : error });
        throw new DatabaseError(
          ErrorCode.QUERY_FAILED,
          'Failed to retrieve schema information',
          { operation: 'listSchemas' },
          error as Error
        );
      }
    }
  
    /**
     * Get detailed information about a specific schema
     */
    async getSchemaInfo(schemaName: string): Promise<{
      schema: SchemaInfo;
      tables: any[];
      views: any[];
      functions: any[];
      permissions: SchemaPermission[];
      dependencies: any[];
    }> {
      const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
  
      try {
        // Get basic schema info
        const schemas = await this.listSchemas(true);
        const schema = schemas.find(s => s.schemaName === validatedSchema);
        
        if (!schema) {
          throw new DatabaseError(
            ErrorCode.SCHEMA_NOT_FOUND,
            `Schema '${validatedSchema}' not found`
          );
        }
  
        // Get detailed information in parallel
        const [tables, views, functions, permissions, dependencies] = await Promise.all([
          this.getSchemaTables(validatedSchema),
          this.getSchemaViews(validatedSchema),
          this.getSchemaFunctions(validatedSchema),
          this.getSchemaPermissions(validatedSchema),
          this.getSchemaDependencies(validatedSchema)
        ]);
  
        return {
          schema,
          tables,
          views,
          functions,
          permissions,
          dependencies
        };
      } catch (error) {
        if (error instanceof DatabaseError) throw error;
        
        logger.error('Failed to get schema info', { 
          schema: validatedSchema,
          error: error instanceof Error ? error.message : error 
        });
        throw new DatabaseError(
          ErrorCode.QUERY_FAILED,
          `Failed to retrieve information for schema '${validatedSchema}'`,
          { schema: validatedSchema },
          error as Error
        );
      }
    }
  
    /**
     * Create a new schema
     */
    async createSchema(
      schemaName: string,
      options: CreateSchemaOptions = {}
    ): Promise<{ success: boolean; message: string }> {
      const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
  
      // Build CREATE SCHEMA statement
      let sql = 'CREATE SCHEMA';
      
      if (options.ifNotExists) {
        sql += ' IF NOT EXISTS';
      }
      
      sql += ` ${validatedSchema}`;
      
      if (options.authorization) {
        sql += ` AUTHORIZATION ${options.authorization}`;
      } else if (options.owner) {
        sql += ` AUTHORIZATION ${options.owner}`;
      }
  
      try {
        await this.dbManager.query(sql);
        
        // Invalidate cache
        if (this.cache) {
          this.cache.invalidate('schemas_list');
        }
        
        logger.info('Schema created successfully', { 
          schema: validatedSchema,
          owner: options.owner || options.authorization 
        });
        
        return {
          success: true,
          message: `Schema '${validatedSchema}' created successfully`
        };
      } catch (error) {
        logger.error('Failed to create schema', { 
          schema: validatedSchema,
          error: error instanceof Error ? error.message : error 
        });
        throw new DatabaseError(
          ErrorCode.QUERY_FAILED,
          `Failed to create schema '${validatedSchema}'`,
          { schema: validatedSchema, options },
          error as Error
        );
      }
    }
  
    /**
     * Drop a schema
     */
    async dropSchema(
      schemaName: string,
      cascade: boolean = false,
      ifExists: boolean = true
    ): Promise<{ success: boolean; message: string }> {
      const validatedSchema = ParameterValidator.validateSchemaName(schemaName);
  
      // Security check - prevent dropping system schemas
      const systemSchemas = ['information_schema', 'pg_catalog', 'pg_toast', 'public'];
      if (systemSchemas.includes(validatedSchema)) {
        throw new DatabaseError(
          ErrorCode.PERMISSION_DENIED,
          `Cannot drop system schema '${validatedSchema}'`
        );
      }
  
      let sql = 'DROP SCHEMA';
      if (ifExists) sql += ' IF EXISTS';
      sql += ` ${validatedSchema}`;
      if (cascade) sql += ' CASCADE';
  
      try {
        await this.dbManager.query(sql);
        
        // Invalidate cache
        if (this.cache) {
          this.cache.invalidate('schemas_list');
        }
        
        logger.info('Schema dropped successfully', { 
          schema: validatedSchema,
          cascade 
        });
        
        return {
          success: true,
          message: `Schema '${validatedSchema}' dropped successfully`
        };
      } catch (error) {
        logger.error('Failed to drop schema', { 
          schema: validatedSchema,
          error: error instanceof Error ? error.message : error 
        });
        throw new DatabaseError(
          ErrorCode.QUERY_FAILED,
          `Failed to drop schema '${validatedSchema}'`,
          { schema: validatedSchema, cascade, ifExists },
          error as Error
        );
      }
    }
  
    /**
     * Rename a schema
     */
    async renameSchema(
      oldName: string,
      newName: string
    ): Promise<{ success: boolean; message: string }> {
      const validatedOldName = ParameterValidator.validateSchemaName(oldName);
      const validatedNewName = ParameterValidator.validateSchemaName(newName);
  
      // Security check - prevent renaming system schemas
      const systemSchemas = ['information_schema', 'pg_catalog', 'pg_toast', 'public'];
      if (systemSchemas.includes(validatedOldName)) {
        throw new DatabaseError(
          ErrorCode.PERMISSION_DENIED,
          `Cannot rename system schema '${validatedOldName}'`
        );
      }
  
      const sql = `ALTER SCHEMA ${validatedOldName} RENAME TO ${validatedNewName}`;
  
      try {
        await this.dbManager.query(sql);
        
        // Invalidate cache
        if (this.cache) {
          this.cache.invalidate('schemas_list');
        }
        
        logger.info('Schema renamed successfully', { 
          oldName: validatedOldName,
          newName: validatedNewName 
        });
        
        return {
          success: true,
          message: `Schema '${validatedOldName}' renamed to '${validatedNewName}' successfully`
        };
      } catch (error) {
        logger.error('Failed to rename schema', { 
          oldName: validatedOldName,
          newName: validatedNewName,
          error: error instanceof Error ? error.message : error 
        });
        throw new DatabaseError(
          ErrorCode.QUERY_FAILED,
          `Failed to rename schema '${validatedOldName}' to '${validatedNewName}'`,
          { oldName: validatedOldName, newName: validatedNewName },
          error as Error
        );
      }
    }
  
    /**
     * Get schema permissions
     */
    private async getSchemaPermissions(schemaName: string): Promise<SchemaPermission[]> {
      // Use a simpler approach that's more compatible across PostgreSQL versions
      const sql = `
        SELECT 
          r.rolname as grantee,
          'USAGE' as privilege_type,
          false as is_grantable,
          'postgres' as grantor
        FROM pg_namespace n
        JOIN pg_roles r ON r.oid = n.nspowner
        WHERE n.nspname = $1
        
        UNION ALL
        
        SELECT 
          'public' as grantee,
          'USAGE' as privilege_type,
          false as is_grantable,
          'postgres' as grantor
        WHERE $1 = 'public'
        
        ORDER BY grantee, privilege_type
      `;
  
      try {
        const result = await this.dbManager.query(sql, [schemaName], { readOnly: true });
        
        return result.rows.map((row: any) => ({
          grantee: row.grantee,
          privilege: row.privilege_type,
          isGrantable: row.is_grantable === true || row.is_grantable === 'YES',
          grantor: row.grantor
        }));
      } catch (error) {
        logger.warn('Failed to get schema permissions', { 
          schema: schemaName,
          error: error instanceof Error ? error.message : error 
        });
        // Return empty array instead of failing
        return [];
      }
    }
  
    /**
     * Get tables in schema
     */
    private async getSchemaTables(schemaName: string): Promise<any[]> {
      try {
        const sql = `
          SELECT 
            tablename,
            tableowner
          FROM pg_tables 
          WHERE schemaname = $1
          ORDER BY tablename
        `;
  
        const result = await this.dbManager.query(sql, [schemaName], { readOnly: true });
        return result.rows;
      } catch (error) {
        logger.warn('Failed to get schema tables', { schema: schemaName, error });
        return [];
      }
    }
  
    /**
     * Get views in schema
     */
    private async getSchemaViews(schemaName: string): Promise<any[]> {
      try {
        const sql = `
          SELECT 
            viewname,
            viewowner
          FROM pg_views 
          WHERE schemaname = $1
          ORDER BY viewname
        `;
  
        const result = await this.dbManager.query(sql, [schemaName], { readOnly: true });
        return result.rows;
      } catch (error) {
        logger.warn('Failed to get schema views', { schema: schemaName, error });
        return [];
      }
    }
  
    /**
     * Get functions in schema
     */
    private async getSchemaFunctions(schemaName: string): Promise<any[]> {
      try {
        const sql = `
          SELECT 
            p.proname as function_name,
            pg_catalog.pg_get_userbyid(p.proowner) as owner
          FROM pg_catalog.pg_proc p
          JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
          WHERE n.nspname = $1
          ORDER BY p.proname
        `;
  
        const result = await this.dbManager.query(sql, [schemaName], { readOnly: true });
        return result.rows;
      } catch (error) {
        logger.warn('Failed to get schema functions', { schema: schemaName, error });
        return [];
      }
    }
  
    /**
     * Get schema dependencies
     */
    private async getSchemaDependencies(schemaName: string): Promise<any[]> {
      try {
        // Simplified dependency query
        const sql = `
          SELECT 
            'dependency' as type,
            c.relname as object_name
          FROM pg_class c
          JOIN pg_namespace n ON c.relnamespace = n.oid
          WHERE n.nspname = $1
            AND c.relkind IN ('r', 'v', 'f')  -- tables, views, foreign tables
          ORDER BY c.relname
        `;
  
        const result = await this.dbManager.query(sql, [schemaName], { readOnly: true });
        return result.rows;
      } catch (error) {
        logger.warn('Failed to get schema dependencies', { schema: schemaName, error });
        return [];
      }
    }
  }