Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:408-477 (schema)

  Tool definition and input schema for the 'permissions' MCP tool, defining all supported operations and parameters

  {
    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-672 (registration)

  Registration and dispatch for the 'permissions' tool in the CallToolRequestSchema handler switch statement

  case 'permissions':
    return await this.handlePermissions(args);

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

  Core handler implementation for the 'permissions' tool. Handles all operations (list_users, create_user, grant_privilege, etc.) by executing specific PostgreSQL queries via queryClient.

  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}`);
    }
  }