Account Management and Role-Based Access Control

In this assignment, you will implement role-based access control in your application, transforming it from a system where all authenticated users have identical permissions to one where different users can perform different actions based on their assigned roles. You will create the database structure to support roles, modify your authentication system to include role information, build authorization middleware to protect routes, and create account management features that respect permission boundaries.

By the end of this assignment, you will have a working system where standard users can edit their own account information while administrators can manage all user accounts. You will use the flash messaging system you implemented previously to provide clear feedback when authorization checks succeed or fail.

Assignment Instructions

1. Create Database Structure for Roles

Open your src/models/sql/practice.sql file and add the following SQL statements at the end. These statements create a roles table, add a role reference to your users table, seed initial roles, and assign existing users to the default role.

        -- Roles table for role-based access control
        CREATE TABLE IF NOT EXISTS roles (
            id SERIAL PRIMARY KEY,
            role_name VARCHAR(50) UNIQUE NOT NULL,
            role_description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        -- Add role_id column to users table if it doesn't exist
        DO $$
        BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM information_schema.columns 
                WHERE table_name = 'users' AND column_name = 'role_id'
            ) THEN
                ALTER TABLE users 
                ADD COLUMN role_id INTEGER REFERENCES roles(id);
            END IF;
        END $$;

        -- Seed roles (idempotent - safe to run multiple times)
        INSERT INTO roles (role_name, role_description) 
        VALUES 
            ('user', 'Standard user with basic access'),
            ('admin', 'Administrator with full system access')
        ON CONFLICT (role_name) DO NOTHING;

        -- Set the default value of role_id to the 'user' role so new inserts without role_id are handled automatically
        DO $$
        DECLARE
            user_role_id INTEGER;
        BEGIN
            SELECT id INTO user_role_id FROM roles WHERE role_name = 'user';
            IF user_role_id IS NOT NULL THEN
                EXECUTE format(
                    'ALTER TABLE users ALTER COLUMN role_id SET DEFAULT %s',
                    user_role_id
                );
            END IF;
        END $$;

        -- Update existing users without a role to default 'user' role
        DO $$
        DECLARE
            user_role_id INTEGER;
        BEGIN
            SELECT id INTO user_role_id FROM roles WHERE role_name = 'user';
            IF user_role_id IS NOT NULL THEN
                UPDATE users 
                SET role_id = user_role_id 
                WHERE role_id IS NULL;
            END IF;
        END $$;
    

Restart your server and verify the changes in pgAdmin. You should see a roles table with two roles, and your users table should have a role_id column with all existing users assigned to the "user" role.

2. Create Test Accounts

You need test accounts to verify role-based access control works correctly. The simplest approach is to register accounts through your application, then promote one to admin using pgAdmin.

Use your registration form to create three test accounts with this password: Test1234!

• admin@example.com
• user1@example.com
• user2@example.com

After registering these accounts, open pgAdmin and run this SQL to promote the first account to admin:

        -- Promote admin@example.com to admin role
        UPDATE users 
        SET role_id = (SELECT id FROM roles WHERE role_name = 'admin')
        WHERE email = 'admin@example.com';
    

You now have one admin account and two standard user accounts, all with the same password for easy testing.

3. Include Role Information in Sessions

When users log in, your application needs to retrieve their role information and store it in the session. Open src/models/forms/login.js and update the findUserByEmail function to join with the roles table:

        const findUserByEmail = async (email) => {
            const query = `
                SELECT 
                    users.id, 
                    users.name, 
                    users.email, 
                    users.password, 
                    users.created_at,
                    roles.role_name AS "roleName"
                FROM users
                INNER JOIN roles ON users.role_id = roles.id
                WHERE LOWER(users.email) = LOWER($1)
                LIMIT 1
            `;
            const result = await db.query(query, [email]);
            return result.rows[0] || null;
        };
    

The login controller already removes the password before storing the user in the session, so roleName will automatically be included in req.session.user. Restart your server, log in, and verify the role is in the session by adding the following to your dashboard view between the email and created at lines:

        <div class="user-detail">
            <span class="label">Role:</span>
            <span class="value"><%= user.roleName %></span>
        </div>
    

You should see "user" or "admin" depending on which account you logged in with. In a production application, you would not typically display role information in the UI, but this is useful for testing.

4. Create Authorization Middleware

Open src/middleware/auth.js. Currently this file has requireLogin middleware that checks authentication. Add a new middleware factory function that checks authorization (role-based permissions).

Add this function after requireLogin:

        /**
         * Middleware factory to require specific role for route access
         * Returns middleware that checks if user has the required role
         * 
         * @param {string} roleName - The role name required (e.g., 'admin', 'user')
         * @returns {Function} Express middleware function
         */
        const requireRole = (roleName) => {
            return (req, res, next) => {
                // Check if user is logged in first
                if (!req.session || !req.session.user) {
                    req.flash('error', 'You must be logged in to access this page.');
                    return res.redirect('/login');
                }

                // Check if user's role matches the required role
                if (req.session.user.roleName !== roleName) {
                    req.flash('error', 'You do not have permission to access this page.');
                    return res.redirect('/');
                }

                // User has required role, continue
                next();
            };
        };
    

Update the export statement:

        export { requireLogin, requireRole };
    

This middleware factory returns a function that checks if the authenticated user has the required role. The comment in your current requireLogin mentioned "we can beef this up later with roles and permissions" - this is that enhancement.

5. Add Account Management Models

Open src/models/forms/registration.js and add three new functions for account management. Add these functions before the export statement:

        /**
         * Retrieve a single user by ID with role information
         */
        const getUserById = async (id) => {
            const query = `
                SELECT 
                    users.id,
                    users.name,
                    users.email,
                    users.created_at,
                    roles.role_name AS "roleName"
                FROM users
                INNER JOIN roles ON users.role_id = roles.id
                WHERE users.id = $1
            `;
            const result = await db.query(query, [id]);
            return result.rows[0] || null;
        };

        /**
         * Update a user's name and email
         */
        const updateUser = async (id, name, email) => {
            const query = `
                UPDATE users 
                SET name = $1, email = $2, updated_at = CURRENT_TIMESTAMP
                WHERE id = $3
                RETURNING id, name, email, updated_at
            `;
            const result = await db.query(query, [name, email, id]);
            return result.rows[0] || null;
        };

        /**
         * Delete a user account
         */
        const deleteUser = async (id) => {
            const query = 'DELETE FROM users WHERE id = $1';
            const result = await db.query(query, [id]);
            return result.rowCount > 0;
        };
    

Update the export statement to include these new functions:

        export { 
            emailExists, 
            saveUser, 
            getAllUsers, 
            getUserById, 
            updateUser, 
            deleteUser 
        };
    

6. Create Edit Account Controllers

Open src/controllers/forms/registration.js. First, update the import statement to include your new model functions:

        import { 
            emailExists, 
            saveUser, 
            getAllUsers,
            getUserById,
            updateUser,
            deleteUser
        } from '../../models/forms/registration.js';
    

Add validation rules for editing accounts. Place this after your existing registrationValidation array:

        /**
         * Validation rules for editing user accounts
         */
        const editValidation = [
            body('name')
                .trim()
                .isLength({ min: 2, max: 100 })
                .withMessage('Name must be between 2 and 100 characters')
                .matches(/^[a-zA-Z\s'-]+$/)
                .withMessage('Name can only contain letters, spaces, hyphens, and apostrophes'),
            body('email')
                .trim()
                .isEmail()
                .normalizeEmail()
                .withMessage('Must be a valid email address')
                .isLength({ max: 255 })
                .withMessage('Email address is too long')
        ];
    

Add these two controller functions before the route definitions:

        /**
         * Display the edit account form
         * Users can edit their own account, admins can edit any account
         */
        const showEditAccountForm = async (req, res) => {
            const targetUserId = parseInt(req.params.id);
            const currentUser = req.session.user;

            const targetUser = await getUserById(targetUserId);
            
            if (!targetUser) {
                req.flash('error', 'User not found.');
                return res.redirect('/register/list');
            }

            // Check permissions: users can edit themselves, admins can edit anyone
            const canEdit = currentUser.id === targetUserId || currentUser.roleName === 'admin';
            
            if (!canEdit) {
                req.flash('error', 'You do not have permission to edit this account.');
                return res.redirect('/register/list');
            }

            res.render('forms/registration/edit', {
                title: 'Edit Account',
                user: targetUser
            });
        };

        /**
         * Process account edit form submission
         */
        const processEditAccount = async (req, res) => {
            const errors = validationResult(req);

            if (!errors.isEmpty()) {
                errors.array().forEach(error => {
                    req.flash('error', error.msg);
                });
                return res.redirect(`/register/${req.params.id}/edit`);
            }

            const targetUserId = parseInt(req.params.id);
            const currentUser = req.session.user;
            const { name, email } = req.body;

            try {
                const targetUser = await getUserById(targetUserId);
                
                if (!targetUser) {
                    req.flash('error', 'User not found.');
                    return res.redirect('/register/list');
                }

                // Check permissions
                const canEdit = currentUser.id === targetUserId || currentUser.roleName === 'admin';
                
                if (!canEdit) {
                    req.flash('error', 'You do not have permission to edit this account.');
                    return res.redirect('/register/list');
                }

                // Check if new email already exists (and belongs to different user)
                const emailTaken = await emailExists(email);
                if (emailTaken && targetUser.email !== email) {
                    req.flash('error', 'An account with this email already exists.');
                    return res.redirect(`/register/${targetUserId}/edit`);
                }

                // Update the user
                await updateUser(targetUserId, name, email);

                // If user edited their own account, update session
                if (currentUser.id === targetUserId) {
                    req.session.user.name = name;
                    req.session.user.email = email;
                }

                req.flash('success', 'Account updated successfully.');
                res.redirect('/register/list');
            } catch (error) {
                console.error('Error updating account:', error);
                req.flash('error', 'An error occurred while updating the account.');
                res.redirect(`/register/${targetUserId}/edit`);
            }
        };
    

Add these routes before the export statement:

        /**
         * GET /register/:id/edit - Display edit account form
         */
        router.get('/:id/edit', requireLogin, showEditAccountForm);

        /**
         * POST /register/:id/edit - Process account edit
         */
        router.post('/:id/edit', requireLogin, editValidation, processEditAccount);
    

Add the requireLogin import at the top of the file:

        import { requireLogin } from '../../middleware/auth.js';
    

7. Create Delete Account Controller

In the same src/controllers/forms/registration.js file, add the delete controller function:

        /**
         * Process account deletion
         * Only admins can delete accounts, and they cannot delete themselves
         */
        const processDeleteAccount = async (req, res) => {
            const targetUserId = parseInt(req.params.id);
            const currentUser = req.session.user;

            // Only admins can delete accounts
            if (currentUser.roleName !== 'admin') {
                req.flash('error', 'You do not have permission to delete accounts.');
                return res.redirect('/register/list');
            }

            // Prevent admins from deleting their own account
            if (currentUser.id === targetUserId) {
                req.flash('error', 'You cannot delete your own account.');
                return res.redirect('/register/list');
            }

            try {
                const deleted = await deleteUser(targetUserId);
                
                if (deleted) {
                    req.flash('success', 'User account deleted successfully.');
                } else {
                    req.flash('error', 'User not found or already deleted.');
                }
            } catch (error) {
                console.error('Error deleting user:', error);
                req.flash('error', 'An error occurred while deleting the account.');
            }

            res.redirect('/register/list');
        };
    

Add the delete route:

        /**
         * POST /register/:id/delete - Delete user account
         */
        router.post('/:id/delete', requireLogin, processDeleteAccount);
    

8. Create Edit Account View

Create a new file at src/views/forms/registration/edit.ejs with this content:

        <%- include('../../partials/header') %>

        <main>
            <h1><%= title %></h1>
            
            <form method="POST" action="/register/<%= user.id %>/edit">
                <div>
                    <label for="name">Name:</label>
                    <input type="text" id="name" name="name" value="<%= user.name %>" required>
                </div>
                
                <div>
                    <label for="email">Email:</label>
                    <input type="email" id="email" name="email" value="<%= user.email %>" required>
                </div>
                
                <button type="submit">Update Account</button>
                <a href="/register/list">Cancel</a>
            </form>
        </main>

        <%- include('../../partials/footer') %>
    

9. Update Users List Controller

Before adding buttons to the users list view, you need to ensure the current logged-in user's data is available to the view. Open src/controllers/forms/registration.js and update the showAllUsers function:

        const showAllUsers = async (req, res) => {
            let users = [];
            
            try {
                users = await getAllUsers();
            } catch (error) {
                console.error('Error retrieving users:', error);
            }

            res.render('forms/registration/list', {
                title: 'Registered Users',
                users,
                user: req.session && req.session.user ? req.session.user : null
            });
        };
    

This passes the current logged-in user to the view as user, which is separate from the users array. The view will use this to determine what permissions the current user has.

10. Add Edit and Delete Buttons to Users List

Open src/views/forms/registration/list.ejs. Locate where you display each user card and update it to include permission checks and buttons:

        <% if (users && users.length > 0) { %>
            <div class="users-list">
                <% users.forEach(listUser => { %>
                    <% 
                        // Calculate permissions for this user card
                        // 'user' variable contains the current logged-in user (from session)
                        // 'listUser' is the user from the users array we're currently rendering
                        const currentUser = typeof isLoggedIn !== 'undefined' && isLoggedIn ? user : null;
                        const canEdit = currentUser && (currentUser.id === listUser.id || currentUser.roleName === 'admin');
                        const canDelete = currentUser && currentUser.roleName === 'admin' && currentUser.id !== listUser.id;
                    %>
                    <div class="user-card">
                        <h3><%= listUser.name %></h3>
                        <p><%= listUser.email %></p>
                        
                        <% if (canEdit || canDelete) { %>
                            <div class="user-actions">
                                <% if (canEdit) { %>
                                    <a href="/register/<%= listUser.id %>/edit">Edit</a>
                                <% } %>
                                
                                <% if (canDelete) { %>
                                    <form method="POST" action="/register/<%= listUser.id %>/delete" style="display: inline;">
                                        <button type="submit" onclick="return confirm('Are you sure you want to delete this user?')">Delete</button>
                                    </form>
                                <% } %>
                            </div>
                        <% } %>
                    </div>
                <% }); %>
            </div>
        <% } else { %>
    

This code calculates permissions once per card, then conditionally renders the Edit link and Delete button. The Delete button includes a confirmation dialog to prevent accidental deletions.

11. Test Your Implementation

Test your role-based access control system thoroughly with different user types:

As a standard user (user1@example.com / Test1234!):

• Visit /register/list and verify you see an Edit button only on your own user card
• Click Edit on your card and change your name or email
• Try to access /register/2/edit (another user's edit page) and verify you are redirected with an error message

As an admin (admin@example.com / Test1234!):

• Visit /register/list and verify you see Edit buttons on all cards and Delete buttons on all cards except your own
• Edit another user's account and verify the changes save
• Delete one of the test user accounts and verify it disappears from the list
• Verify you cannot delete your own account (no Delete button appears on your card)

As an unauthenticated visitor:

• Visit /register/list and verify you see the list but no Edit or Delete buttons
• Try to access /register/1/edit directly and verify you are redirected to login

Key Concepts Summary

This assignment demonstrated role-based access control in a working application. You created database structures to store roles and associate them with users using foreign key relationships. Your setup logic is idempotent, meaning it can safely run multiple times without creating duplicate data.

You modified your authentication system to retrieve and store role information in sessions, making authorization decisions efficient. The authorization middleware factory pattern (requireRole) provides reusable role checking for routes. Controller-level authorization handles resource-specific permission checks, such as allowing users to edit only their own accounts.

Your views now display different capabilities based on user context. This improves user experience by hiding actions users cannot perform, while server-side authorization ensures actual security. The combination of middleware protection, controller logic, and conditional UI creates a robust access control system.

Security Principles Applied

Server-Side Authorization Is Mandatory: Every route that modifies data must check permissions on the server. Your middleware and controller logic enforce who can edit and delete accounts. Hiding buttons in the UI prevents confusion but does not provide security.

Defense in Depth: Your application checks permissions in multiple places. Middleware blocks routes for unauthorized users. Controllers verify permissions before operations. Views hide buttons for unavailable actions. If one layer fails, other layers still provide protection.

Preventing Self-Harm: Your delete controller prevents administrators from deleting their own accounts. Without this check, an administrator could accidentally lock themselves out of the system.

Foreign Key Constraints: The foreign key relationship between users and roles prevents orphaned data. You cannot assign a user to a role that does not exist, and you cannot delete a role that users currently have. This database-level protection prevents data inconsistencies.