PostgreSQL MCP Server

Instructions

Implementation Reference

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

  Complete definition of the pg_manage_users tool handler, including schema, description, and the main execute function that handles different operations (create, drop, alter, grant, revoke, list, get_permissions) by delegating to internal helper functions.
  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)

  Comprehensive Zod schema for pg_manage_users input parameters supporting multiple operations with conditional fields.
  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:28-28 (registration)

  Import of the manageUsersTool (pg_manage_users) from users.ts module.
  import { manageUsersTool } from './tools/users.js';
• src/index.ts:225-257 (registration)

  Registration of pg_manage_users (as manageUsersTool) in the central allTools array used to initialize the MCP server with available tools.
  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/tools/users.ts:45-89 (helper)

  Helper function for creating a new PostgreSQL user, called by pg_manage_users when operation='create'.
  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(); } }