Postgres MCP Server

Instructions

Implementation Reference

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

  Input schema definition for the 'admin' tool, specifying parameters and validation rules for admin operations.
  { 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:1128-1298 (handler)

  Primary handler function for the 'admin' tool that implements all administrative operations by executing specific SQL queries based on the 'operation' parameter.
  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:634-636 (registration)

  Registration of tool list handler that includes the 'admin' tool schema from toolDefinitions array.
  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: toolDefinitions, }));
• src/index.ts:662-663 (registration)

  Dispatch registration in the main CallToolRequestSchema handler that routes 'admin' tool calls to handleAdmin function.
  return await this.handleAdmin(args);