Postgres MCP Server

Instructions

Implementation Reference

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

  The primary handler function for the 'schemas' MCP tool. It parses the input arguments, validates parameters, and dispatches to appropriate SchemaAPIClient methods based on the 'action' parameter (list, info, create, drop, rename). Returns JSON-formatted results.
  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:158-188 (schema)

  The input schema definition for the 'schemas' tool, specifying the expected parameters including action (list/create/drop/permissions), 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:649-651 (registration)

  Registration of the 'schemas' tool handler in the MCP CallToolRequestSchema dispatcher switch statement.
  case 'schemas': return await this.handleSchemas(args);
• src/index.ts:634-636 (registration)

  Registration of tool list handler that includes the 'schemas' tool definition and schema in the response.
  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: toolDefinitions, }));
• src/api/domains/schema-api.ts:31-525 (helper)

  The SchemaAPIClient class providing the core database operations (listSchemas, createSchema, 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 []; } } }