Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:1408-1553 (handler)

  Main execution handler for the 'permissions' tool. Handles operations like listing users/roles, creating users/roles, granting/revoking privileges, and checking permissions using direct PostgreSQL queries.
  private async handlePermissions(args: any) { const { operation, username, rolename, password, database, schema, table, privileges, attributes, grantOption } = args; switch (operation) { case 'list_users': const users = await this.queryClient.executeQuery(` SELECT r.rolname as username, r.oid as user_id, r.rolcreatedb as can_create_db, r.rolsuper as is_superuser, r.rolreplication as can_replicate, r.rolbypassrls as bypass_rls, r.rolvaliduntil as password_expires, ARRAY( SELECT m.rolname FROM pg_roles m JOIN pg_auth_members am ON m.oid = am.roleid WHERE am.member = r.oid ) as member_of FROM pg_roles r WHERE r.rolcanlogin = true ORDER BY r.rolname `); return { content: [{ type: 'text', text: JSON.stringify(users.rows, null, 2) }] }; case 'list_roles': const roles = await this.queryClient.executeQuery(` SELECT rolname as role_name, rolsuper as is_superuser, rolinherit as inherits, rolcreaterole as can_create_role, rolcreatedb as can_create_db, rolcanlogin as can_login, rolreplication as can_replicate, rolconnlimit as connection_limit, rolvaliduntil as valid_until, rolbypassrls as bypass_rls FROM pg_roles ORDER BY rolname `); return { content: [{ type: 'text', text: JSON.stringify(roles.rows, null, 2) }] }; case 'create_user': if (!username || !password) { throw new Error('Username and password are required for user creation'); } const createUserSQL = `CREATE USER ${username} WITH PASSWORD '${password}'`; if (attributes) { const attrSQL = Object.entries(attributes) .filter(([, value]) => value === true) .map(([key]) => key.toUpperCase()) .join(' '); if (attrSQL) { await this.queryClient.executeQuery(`${createUserSQL} ${attrSQL}`); } else { await this.queryClient.executeQuery(createUserSQL); } } else { await this.queryClient.executeQuery(createUserSQL); } return { content: [{ type: 'text', text: `User '${username}' created successfully` }] }; case 'grant_all_privileges': if (!username || !database) { throw new Error('Username and database are required for granting all privileges'); } const grantAllSQL = [ `GRANT ALL PRIVILEGES ON DATABASE ${database} TO ${username}`, `GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${username}`, `GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${username}`, `GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO ${username}` ]; for (const sql of grantAllSQL) { await this.queryClient.executeQuery(sql); } return { content: [{ type: 'text', text: `All privileges granted to '${username}' on database '${database}'` }] }; case 'grant_privilege': if (!username || !privileges || privileges.length === 0) { throw new Error('Username and privileges are required'); } const target = table ? `TABLE ${schema ? schema + '.' : ''}${table}` : schema ? `SCHEMA ${schema}` : database ? `DATABASE ${database}` : 'ALL TABLES IN SCHEMA public'; const grantSQL = `GRANT ${privileges.join(', ')} ON ${target} TO ${username}${grantOption ? ' WITH GRANT OPTION' : ''}`; await this.queryClient.executeQuery(grantSQL); return { content: [{ type: 'text', text: `Privileges ${privileges.join(', ')} granted to '${username}' on ${target}` }] }; case 'check_permissions': if (!username) { throw new Error('Username is required for permission check'); } const permissionsQuery = ` SELECT t.schemaname, t.tablename, p.privilege_type FROM information_schema.table_privileges p JOIN information_schema.tables t ON p.table_name = t.table_name AND p.table_schema = t.table_schema WHERE p.grantee = $1 ORDER BY t.schemaname, t.tablename, p.privilege_type `; const permissions = await this.queryClient.executeQuery(permissionsQuery, [username]); return { content: [{ type: 'text', text: JSON.stringify(permissions.rows, null, 2) }] }; default: throw new Error(`Unknown permissions operation: ${operation}`); } }
• src/index.ts:408-477 (schema)

  Input schema and metadata definition for the 'permissions' tool, specifying supported operations and parameter structures for permissions management.
  { name: 'permissions', description: 'Database permissions management: users, roles, grants, privileges', inputSchema: { type: 'object', properties: { operation: { type: 'string', enum: [ 'list_users', 'list_roles', 'list_grants', 'list_privileges', 'create_user', 'create_role', 'drop_user', 'drop_role', 'grant_role', 'revoke_role', 'grant_privilege', 'revoke_privilege', 'alter_user', 'alter_role', 'check_permissions', 'grant_all_privileges' ], description: 'Permission operation to perform' }, username: { type: 'string', description: 'Username for user operations' }, rolename: { type: 'string', description: 'Role name for role operations' }, password: { type: 'string', description: 'Password for user creation/modification' }, database: { type: 'string', description: 'Database name for grants' }, schema: { type: 'string', description: 'Schema name for grants' }, table: { type: 'string', description: 'Table name for grants' }, privileges: { type: 'array', items: { type: 'string', enum: ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER', 'CREATE', 'CONNECT', 'TEMPORARY', 'EXECUTE', 'USAGE', 'ALL'] }, description: 'Privileges to grant/revoke' }, attributes: { type: 'object', properties: { superuser: { type: 'boolean', description: 'Superuser privilege' }, createdb: { type: 'boolean', description: 'Create database privilege' }, createrole: { type: 'boolean', description: 'Create role privilege' }, replication: { type: 'boolean', description: 'Replication privilege' }, login: { type: 'boolean', description: 'Login privilege' }, inherit: { type: 'boolean', description: 'Inherit privileges' }, bypassrls: { type: 'boolean', description: 'Bypass row level security' } }, description: 'User/role attributes' }, grantOption: { type: 'boolean', description: 'Grant with GRANT OPTION', default: false } }, required: ['operation'] } },
• src/index.ts:670-671 (registration)

  Tool handler registration in the central MCP CallToolRequestSchema dispatch switch statement.
  case 'permissions': return await this.handlePermissions(args);
• src/index.ts:634-636 (registration)

  Registration of all tools (including 'permissions') for the ListToolsRequestSchema handler.
  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: toolDefinitions, }));