Building Enterprise Role-Based Access Control (RBAC) in Full-Stack Node.js and PostgreSQL
How to build secure permission systems for enterprise applications: database schemas, JWT token payload validation, role inheritance, and row-level access rules.
By Uttam Thapa · · Security
⚡ Executive Summary (TL;DR)
if (user.role === 'admin') is the line that eventually forces a rewrite. This is the RBAC design behind an
Employee Management System on Node.js, Express and PostgreSQL: roles mapped to granular permission keys through a join
table, a single composable Express middleware that enforces them, and an append-only audit trail over every sensitive action.
Figure 1: Roles, departments and salary visibility — three different permission surfaces over the same records.
Why Role Strings Do Not Survive Contact With Requirements
Almost every system starts with a role column and a handful of string comparisons. It works until the first request that does not fit the ladder — and that
request always arrives.
🚨 The requests that break hardcoded roles
- "HR should see salaries, but not delete accounts." — Not a rung on the admin/manager/staff ladder.
- "This one manager needs export access for the audit." — Now you are adding a role for a single person.
- "Contractors see their own department only." — Scope, not seniority.
- "Who changed this salary in March?" — Impossible to answer if permissions were never recorded as events.
Each of these adds another branch to another if statement scattered across route handlers. The number of places to check grows with the number of
endpoints, and eventually one of them is missed. The fix is to stop asking who is this and start asking may they do this specific thing.
A Schema That Separates Identity From Capability
users (id, name, email, role_id)
roles (id, role_name) -- Admin, HR_Manager, Staff
permissions (id, permission_key) -- employees:read, salary:edit
role_permissions (role_id, permission_id) -- the join that makes it dynamic
The join table is the whole point. Granting HR the ability to edit salaries becomes one row, applied to everyone in that role, with no deployment. Permission
keys follow a resource:action convention so they stay greppable and self-describing — salary:edit tells a reviewer exactly what it
guards, where level_3 tells them nothing.
One Middleware, Every Route
With permissions in the data model, enforcement collapses to a single higher-order middleware. Every protected route declares the key it requires, right at the
point of definition, where a reviewer can see it.
export const requirePermission = (requiredPermission: string) => {
return (req: any, res: any, next: any) => {
const userPermissions: string[] = req.user?.permissions || [];
if (!userPermissions.includes(requiredPermission)) {
return res.status(403).json({
error: 'Forbidden: Insufficient authorization permissions.',
});
}
next();
};
};
// The requirement is visible in the route definition itself.
router.put('/api/salary/:id', requirePermission('salary:edit'), updateSalaryHandler);
router.get('/api/employees', requirePermission('employees:read'), listEmployeesHandler);
💡 Deny by default
Add a catch-all that rejects any route reaching a handler without having passed a permission check, and assert it in tests. Without that, a new endpoint added
on a busy afternoon is unprotected by omission — and omission is how most authorisation bugs actually happen, not by writing the wrong check.
Authorisation Is a Backend Concern, Full Stop
Hiding a button is a courtesy to the user, not a security control. The API endpoint behind it is reachable with curl regardless of what the
interface renders. Frontend permission checks exist to avoid showing people actions that will fail; the server decides whether they are allowed.
🖥️ Frontend checks
Improve usability. Hide controls the user cannot use. Never the enforcement point.
🛡️ Backend middleware
The only enforcement point. Runs on every request, including the ones no interface ever sends.
Audit Logs: Append-Only, and Written in the Same Transaction
Sensitive actions — changing compensation, altering department access, deleting an account — append an immutable record capturing
actor_user_id, target_resource, action_type, ip_address, and timestamp.
Two properties make the difference between a log and evidence. It must be append-only, with no UPDATE or DELETE grant for the
application role — otherwise the log is only as trustworthy as the account that was compromised. And the log write must share a transaction with the change it
records, so a partial failure can never leave a modified salary with no audit row.
✅ Key takeaways
- ✓Decouple roles from permissions. A join table turns a policy change into a row, not a deployment.
- ✓Name keys
resource:action. Self-describing and greppable beats numbered privilege levels.
- ✓Declare the requirement in the route. One middleware, visible at the point of definition.
- ✓Deny by default and test it. Unprotected endpoints are added by omission, not by mistake.
- ✓Write audit rows in the same transaction. An append-only log is evidence; an editable one is a rumour.
For the authentication layer underneath all of this, JWT vs HttpOnly session cookies
covers how req.user gets populated safely in the first place.
Frequently asked questions
What is wrong with storing a role as a string on the user?
It cannot express requirements that are not a strict hierarchy — HR seeing salaries but not deleting accounts, for example. Each exception becomes another branch in another route handler until one is missed.
How should permissions be modelled in a relational database?
Users reference a role, roles link to permission keys through a join table, and keys are named resource:action. Granting a capability then becomes a row rather than a deployment.
Is hiding a button enough to restrict access?
No. The endpoint behind it is reachable with curl regardless of what the interface renders. Frontend checks improve usability; the server middleware is the only enforcement point.
Home · Projects · Blog · Services · Résumé · Contact