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 ✅

1. 1. Do NOT store permissions as comma-separated values – it is not scalable.
   2. Use role_permissions and user_permissions for flexible access control.
   3. Maintain audit_logs for tracking changes.
   4. 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!