Dynamic Permission Management in PostgreSQL: Scalable Role-Based Access Control

By Anand Lagad February 27, 2025 PostgreSQL

In modern applications, managing user permissions efficiently is crucial for security and scalability. Traditional methods like storing permissions as comma-separated values can lead to performance issues and data inconsistency. In this blog, we will explore a robust PostgreSQL-based table architecture for dynamic permission management, ensuring scalability, flexibility, and efficient querying.

Recommended Table Schema for Role-Based Access Control (RBAC)

Table Relationships

One-to-Many:

roles (id) -> users (role_id)
modules (id) -> permissions (module_id)

Many-to-Many:

roles (id) <-> permissions (id) (via role_permissions)
users (id) <-> permissions (id) (via user_permissions)

Database Schema Design

1. Users Table (No Comma-Separated Permissions)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    mobile_number VARCHAR(10) UNIQUE NOT NULL,
    first_name VARCHAR(100),
    middle_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    password_hash TEXT NOT NULL,
    role_id INT,  -- Role assigned to the user
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
);

Each user has a unique mobile number.
A user is assigned one role (foreign key reference to the roles table).
Additional permissions are assigned via the user_permissions table.

2. Roles Table

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Defines different roles within the system.
Connected to users and role_permissions.

3. Modules Table (Supports Hierarchical Structure)

CREATE TABLE modules (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    parent_id INT NULL,  -- Supports hierarchical module structure
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES modules(id) ON DELETE SET NULL
);

Stores system modules (e.g., Users, Orders, Reports).
Supports a hierarchical structure with parent_id.

4. Permissions Table (Includes Slug for Uniqueness)

CREATE TABLE permissions (
    id SERIAL PRIMARY KEY,
    module_id INT NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,  -- Unique slug (e.g., "user.create", "user.delete")
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
);

Stores specific permissions (e.g., user.create, user.delete).
Connected to modules to categorize permissions.

5. Role Permissions Table (Efficient Role-Based Permissions)

CREATE TABLE role_permissions (
    id SERIAL PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    UNIQUE (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Defines a many-to-many relationship between roles and permissions.

6. User Permissions Table (Extra User-Specific Permissions)

CREATE TABLE user_permissions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    permission_id INT NOT NULL,
    UNIQUE (user_id, permission_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Defines a many-to-many relationship between users and permissions.
Allows assigning extra permissions beyond role-based permissions.

7. Audit Logs Table (Tracks Permission Changes)

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    action TEXT NOT NULL,
    details JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

Tracks all permission changes made to users.

Key Benefits of This Implementation

Scalability: Uses efficient many-to-many relationships instead of comma-separated values.
Flexibility: Allows role-based permissions while granting additional user-specific permissions.
Data Integrity: Foreign keys enforce structured relationships, preventing orphaned records.
Efficient Querying: Indexed joins improve permission retrieval performance.
Security: Granular permission control ensures restricted access based on roles.
Auditability: Tracks permission changes for transparency and compliance.

Efficient Queries in This Architecture

✅ Get All Permissions for a User

SELECT p.slug, p.name
FROM permissions p
JOIN role_permissions rp ON p.id = rp.permission_id
JOIN users u ON rp.role_id = u.role_id
WHERE u.id = 5

UNION

SELECT p.slug, p.name
FROM permissions p
JOIN user_permissions up ON p.id = up.permission_id
WHERE up.user_id = 5;

Retrieves both role-based and extra user-specific permissions efficiently.

✅ Track All Changes for a User

SELECT * FROM audit_logs WHERE user_id = 5 ORDER BY created_at DESC;

See who modified permissions for user ID 5.

✅ Assign an Extra Permission to a User

INSERT INTO user_permissions (user_id, permission_id) VALUES (5, 10);

Grants additional permissions to a specific user without modifying their role.

Final Recommendations ✅

Do NOT store permissions as comma-separated values – it is not scalable.
Use role_permissions and user_permissions for flexible access control.
Maintain audit_logs for tracking changes.
Query permissions efficiently using JOINs instead of parsing text fields.

Conclusion

A well-designed permission management system in PostgreSQL ensures secure, scalable, and efficient user access control. By implementing the above architecture, you can streamline role-based and user-specific permissions while maintaining data integrity and audit trails. This approach provides a future-proof solution for applications with growing user bases and complex permission requirements.

✅ Implement this architecture in your PostgreSQL-based project today and ensure a secure, scalable permission management system!

Dynamic Permission Management in PostgreSQL: Scalable Role-Based Access Control