Implementing Roles and Permissions: RBAC for SaaS Databases

Previously in this course, we covered Handling Many-to-Many Relationships: Database Design Junction Tables, which provided the theoretical foundation for bridging entities. In this lesson, we apply those concepts to build a robust Role-Based Access Control (RBAC) system for our SaaS project.

RBAC is the industry standard for managing user permissions. Instead of assigning individual rights to every user, we group those rights into "roles" (like Admin, Editor, or Viewer) and assign those roles to users. Because one user can hold multiple roles and one role can be held by many users, this is a classic many-to-many scenario.

Designing the Roles Schema

To implement RBAC, we need three distinct entities:

1. Users: Our existing table created in Implementing the User Table: A Practical Guide to SQL DDL.
2. Roles: A new table defining the available access levels.
3. User_Roles: The junction table that links users to their assigned roles.

1. The Roles Table

This table acts as a lookup for your application’s authorization logic. Keep it simple: an ID and a human-readable name.

SQL
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

-- Seed basic roles
INSERT INTO roles (role_name, description) VALUES 
('admin', 'Full system access'),
('editor', 'Can modify content but not settings'),
('viewer', 'Read-only access');

2. The User-Role Junction Table

As discussed in our work on Primary Keys and Identifiers: Designing for Data Integrity, we use foreign keys to ensure referential integrity. This table acts as the bridge.

SQL
CREATE TABLE user_roles (
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    role_id INT REFERENCES roles(role_id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

Why a Junction Table is Essential

Using a junction table allows for extreme flexibility. If you want to promote a user to "Admin" without removing their existing "Editor" role, you simply insert a new row into user_roles.

The composite primary key (user_id, role_id) prevents the same role from being assigned to the same user twice, keeping your data clean and avoiding duplicate authorization logic in your application code.

Hands-on Exercise: Expand Your Roles

In your local database, execute the SQL above. Then, try to perform these three steps:

1. Insert a new role called 'billing_manager'.
2. Assign a user (from your existing users table) to both 'viewer' and 'billing_manager' by inserting two rows into user_roles.
3. Write a SELECT query that joins users, user_roles, and roles to list all users and their respective roles.

Common Pitfalls

• Hardcoding Roles in Application Logic: Never check for if (user.role == 'admin') in your code using string comparisons. Always reference the role_id or a specific permission flag to avoid breaking your app when you rename a role in the database.
• Ignoring ON DELETE CASCADE: If a user is deleted from your system, you don't want orphaned records sitting in your user_roles table. Always use ON DELETE CASCADE on your foreign keys to ensure that when a user is removed, their role assignments vanish automatically.
• Overcomplicating the Schema: Keep the roles table lean. If you find yourself needing to store "permission sets" (e.g., can_edit_posts, can_delete_users), consider creating a permissions table and a second junction table (role_permissions) rather than bloating the roles table with dozens of boolean flags.

FAQ

Q: Should I store permissions in the roles table? A: For small apps, adding a permissions column (like a JSONB array in PostgreSQL) can work. However, for a scalable SaaS, use a separate permissions table linked to roles via a junction table.

Q: Can I use this for multi-tenant SaaS? A: Yes, but you would likely add a tenant_id column to the user_roles table to ensure that a user’s role is only valid within the context of a specific account/organization.

Recap

We’ve implemented a standard RBAC system by moving from a simple user model to a flexible many-to-many structure. By decoupling users from their roles via a junction table, we’ve made our SaaS database architecture significantly more maintainable and secure.

Up next: We will dive into Advanced Subscription Modeling, where we apply these same junction table patterns to manage complex feature sets linked to user plans.