Role-Based Access Control in a Custom ERP: NestJS Guards, PostgreSQL Permission Tables, and UU PDP Compliance

Access control is one of the most critical and most often under-designed aspects of a custom ERP. Get it wrong and you have employees approving their own expense claims, finance staff seeing each other's salaries, or — in the worst case — unauthorized users exporting the entire customer database. When I built the ERP for Commsult Indonesia, access control was designed from day one as a first-class concern, not bolted on after the fact.

Designing the Permission Model

We use a standard RBAC model: users are assigned one or more roles, roles have permissions, and permissions are (resource, action) pairs. A permission to create an invoice is resource='invoice', action='create'. A permission to approve a payment voucher is resource='ap_voucher', action='approve'. The key design decisions are: what counts as a resource, what actions exist per resource, and how roles map to job functions.

Resources and Actions

Resources map to major ERP entities: 'invoice', 'leave_request', 'ap_voucher', 'employee', 'customer', 'vendor', 'report'. Actions are: 'create', 'read', 'update', 'delete', 'approve', 'reject', 'void', 'export'. Not all actions apply to all resources — 'approve' only applies to resources with an approval workflow, 'export' applies to resources that can be downloaded as Excel or PDF.

Role Hierarchy Design

We defined four role levels: SUPER_ADMIN (full access, IT admin only), FINANCE_MANAGER (full AR/AP access, approve all amounts), FINANCE_STAFF (create and read invoices, no approve), and EMPLOYEE (create leave requests, read own records only). We chose not to implement role inheritance to keep the permission model explicit and auditable — every permission is explicitly assigned to a role.

ERP RBAC Data Model (PostgreSQL)

  ┌─────────────┐       ┌──────────────────┐       ┌──────────────┐
  │    users    │──────►│   user_roles     │◄──────│    roles     │
  │─────────────│  N:M  │──────────────────│  N:M  │──────────────│
  │ id          │       │ user_id (FK)     │       │ id           │
  │ name        │       │ role_id (FK)     │       │ name         │
  │ email       │       │ granted_by       │       │ description  │
  │ dept_id     │       │ granted_at       │       └──────┬───────┘
  │ is_active   │       └──────────────────┘              │
  └─────────────┘                                         │ N:M
                                                          ▼
                                               ┌────────────────────┐
                                               │  role_permissions  │
                                               │────────────────────│
                                               │ role_id (FK)       │
                                               │ permission_id (FK) │
                                               └──────────┬─────────┘
                                                          │
                                                          ▼
                                               ┌────────────────────┐
                                               │   permissions      │
                                               │────────────────────│
                                               │ id                 │
                                               │ resource  (e.g.    │
                                               │   'invoice')       │
                                               │ action    (e.g.    │
                                               │   'create','read', │
                                               │   'approve','void')│
                                               └────────────────────┘

  Role Hierarchy Example:
  SUPER_ADMIN > FINANCE_MANAGER > FINANCE_STAFF > EMPLOYEE

PostgreSQL Schema for RBAC

The RBAC schema consists of four tables: users (existing), roles, permissions, and two junction tables — user_roles and role_permissions. The junction tables use composite primary keys for efficiency. We add a granted_by and granted_at column to user_roles so there's always an audit trail of who assigned which role and when.

NestJS Guard Implementation

The RBAC guard uses NestJS's CanActivate interface. It reads a @RequirePermission(resource, action) decorator set on the controller method, extracts the user ID from the JWT, and checks the PermissionService. If the user doesn't have the permission, it throws a ForbiddenException with a message that includes the specific missing permission.

// NestJS: Custom RBAC guard with PostgreSQL permission lookup

// 1. Permission decorator
export const RequirePermission = (resource: string, action: string) =>
  SetMetadata('permission', { resource, action });

// 2. Guard implementation
@Injectable()
export class RbacGuard implements CanActivate {
  constructor(
    private reflector: Reflector,
    private permissionService: PermissionService,
  ) {}

  async canActivate(context: ExecutionContext): Promise<boolean> {
    const permission = this.reflector.get<{ resource: string; action: string }>(
      'permission',
      context.getHandler(),
    );
    if (!permission) return true; // No permission required

    const request = context.switchToHttp().getRequest();
    const userId = request.user?.id;
    if (!userId) throw new UnauthorizedException();

    const hasAccess = await this.permissionService.userHasPermission(
      userId,
      permission.resource,
      permission.action,
    );

    if (!hasAccess) {
      throw new ForbiddenException(
        `Missing permission: ${permission.resource}:${permission.action}`
      );
    }
    return true;
  }
}

// 3. Permission service with cached lookups
@Injectable()
export class PermissionService {
  constructor(
    @InjectRepository(UserRole) private userRoleRepo: Repository<UserRole>,
    private cacheManager: Cache,
  ) {}

  async userHasPermission(
    userId: string,
    resource: string,
    action: string,
  ): Promise<boolean> {
    const cacheKey = `perm:${userId}:${resource}:${action}`;
    const cached = await this.cacheManager.get<boolean>(cacheKey);
    if (cached !== undefined) return cached;

    const result = await this.userRoleRepo.query(`
      SELECT 1
      FROM user_roles ur
      JOIN role_permissions rp ON rp.role_id = ur.role_id
      JOIN permissions p ON p.id = rp.permission_id
      WHERE ur.user_id = $1
        AND p.resource = $2
        AND p.action = $3
      LIMIT 1
    `, [userId, resource, action]);

    const hasAccess = result.length > 0;
    await this.cacheManager.set(cacheKey, hasAccess, 300); // 5 min TTL
    return hasAccess;
  }
}

// 4. Usage on controller
@Controller('invoices')
@UseGuards(JwtAuthGuard, RbacGuard)
export class InvoiceController {
  @Post()
  @RequirePermission('invoice', 'create')
  create(@Body() dto: CreateInvoiceDto) { ... }

  @Post(':id/approve')
  @RequirePermission('invoice', 'approve')
  approve(@Param('id') id: string) { ... }

  @Post(':id/void')
  @RequirePermission('invoice', 'void')
  void(@Param('id') id: string) { ... }
}

UU PDP Compliance Considerations

Indonesia's UU PDP (Law No. 27 of 2022 on Personal Data Protection) imposes obligations on organizations that process personal data. In an ERP context, personal data includes employee records, salary information, customer contact details, and vendor identity information. RBAC is a key control for UU PDP compliance — you must be able to demonstrate that access to personal data is restricted to those with a legitimate need.

Row-Level Security for Multi-Tenant or Multi-Branch ERPs

If the ERP serves multiple branches that should not see each other's data (e.g., Jakarta branch invoices are not visible to Surabaya branch staff), you need row-level security in addition to RBAC. PostgreSQL supports RLS policies at the database level — these are enforced even if the application layer has a bug that bypasses the API guard.

Testing Access Control

Access control logic must have comprehensive test coverage. For each protected endpoint, write tests for: a user with the correct role can access it, a user with an adjacent but insufficient role is rejected with 403, an unauthenticated request is rejected with 401, and a SUPER_ADMIN can always access it. Pay special attention to testing approval flows — the system should prevent a user from approving their own submitted document.