Unit 4: Authentication and Database Integration

I. Orientation

Modern web applications must establish who a user is, maintain that identity across requests, decide what the user may do, and store application data reliably. Authentication and database integration therefore work together: credentials are checked against persistent records, sessions preserve login state, authorization protects resources, and database operations implement the application's data workflows.

• Identity: Authentication answers “Who is this user?”; authorization answers “What may this user do?”
• Trust boundary: The server, not the browser, must make final decisions about identity, permissions, and data access.
• Persistence: User accounts, roles, and application records normally reside in a database.
• Confidentiality: Passwords are stored as slow, salted hashes, never as recoverable plaintext.
• Request model: HTTP is stateless; sessions, tokens, or another mechanism connect separate requests to one identity.
• Validation principle: Untrusted input must be validated before database insertion, update, or use in application logic.
• Configuration principle: Secrets and deployment-specific settings belong in environment variables, not source code.

II. Authentication Concepts — Verifying User Identity

Authentication is the process of proving that a request belongs to a particular user. A typical web login combines an identifier, such as an email address, with a secret, such as a password, and produces an authenticated session after successful verification.

A. Authentication Concepts

Authentication depends on securely comparing supplied credentials with stored account data.

• Credential flow: The client submits email and password over HTTPS; the server looks up the account and verifies the password hash.
• Password hashing: A password such as River9! is transformed by a one-way algorithm such as Argon2id or bcrypt. The original password cannot be recovered from the hash.
• Salt: A unique random salt ensures two users with the same password do not receive the same stored hash.
• Verification: The server runs the submitted password through the hashing algorithm using the stored salt and compares the resulting value.
• Failure response: Invalid email and invalid password should normally produce the same public message, such as “Invalid credentials,” to reduce account enumeration.
• Additional factors: Multi-factor authentication may combine a password with a time-based code, security key, or biometric factor.
• Transport protection: HTTPS encrypts credentials in transit; hashing alone does not protect a password sent over an unencrypted connection.

III. Session Management — Maintaining Login State

Session management preserves an authenticated identity across HTTP requests. The server creates a session after login, and the browser presents a session identifier on later requests.

A. Session Management

A secure session uses an unpredictable identifier and carefully controlled cookie behavior.

• Session record: A database or server-side store may map session_id = 8f... to user_id = 42, creation time, and expiry time.
• Cookie transport: The browser stores a cookie such as session_id; it sends that cookie automatically to matching server requests.
• Cookie flags:
  • HttpOnly: Prevents JavaScript from reading the cookie, reducing direct theft through many XSS attacks.
  • Secure: Sends the cookie only over HTTPS.
  • SameSite=Lax or Strict: Limits cross-site cookie transmission and helps reduce CSRF.
• Session fixation defense: Regenerate the session identifier after successful login so an attacker cannot reuse an identifier established before authentication.
• Expiration: An idle timeout might invalidate a session after 30 minutes without activity; an absolute timeout can require reauthentication after a longer period.
• Logout: The server should invalidate the session record and clear the browser cookie rather than merely hiding the user interface.
• Session storage choice: In-memory storage is simple for development but loses data on restart and is unsuitable for multiple server instances without shared storage.

IV. Protected Routes — Restricting Unauthenticated Access

A protected route is an endpoint that requires a valid authenticated identity before it performs its normal operation or returns protected data.

A. Protected Routes

Route protection should be enforced in server-side middleware or an equivalent request guard.

• Guard sequence: The middleware reads the session, verifies its validity and expiry, loads the user, and only then calls the route handler.
• Pseudocode:

requireAuth(request, response, next):
    session = sessionStore.find(request.cookie.session_id)
    if session is missing or expired:
        return response.status(401).send("Authentication required")
    request.user = userStore.find(session.user_id)
    return next()

• Symbol definitions: request is the incoming HTTP request, response is the server reply, next continues processing, and 401 means the request lacks valid authentication.
• API behavior: A JSON API commonly returns 401 Unauthorized with a structured error; a browser application may redirect an unauthenticated user to /login.
• Placement: Apply the guard before database queries or file access, preventing unauthorized work as well as unauthorized display.
• Client-side limits: Hiding a button in React or another frontend does not protect the endpoint; a user can call the URL directly.
• Public versus private data: A route such as GET /products may be public, while GET /users/42/orders must verify both authentication and ownership or permission.

V. Authorization — Controlling Permitted Actions

Authorization evaluates whether an authenticated user may perform a particular operation on a resource. It occurs after authentication because the server must know the requesting identity first.

A. Authorization

Authorization decisions should be explicit, resource-specific, and enforced for every sensitive action.

• Permission question: For user 42 requesting invoice 900, the server asks whether that user may read invoice 900, not merely whether the user is logged in.
• Ownership rule: A query can enforce ownership directly:

SELECT * FROM orders
WHERE id = 900 AND user_id = 42;

Here 900 is the resource identifier and 42 is the authenticated user identifier.

• Status codes: Return 403 Forbidden when identity is known but permission is absent; reserve 401 for missing or invalid authentication.
• Least privilege: Give each account only the permissions required for its work, such as orders:read without orders:delete.
• Server-side enforcement: Authorization checks belong in route handlers, service functions, or policy middleware, not only in navigation components.
• Sensitive operations: Deletion, account changes, financial actions, and access to another user's records require checks even if ordinary reads are allowed.
• Defense in depth: Combine policy checks with database constraints, ownership filters, and audit logging.

VI. Role-based Access Control — Assigning Permissions Through Roles

Role-based Access Control (RBAC) assigns permissions to roles and assigns roles to users. It simplifies administration when many users share the same responsibilities.

A. Role-based Access Control

RBAC is represented by a relationship such as user -> role -> permission.

• Role example: A support_agent may view tickets, while an admin may view, edit, and delete users.
• Database representation: A simple system may store role = 'admin' in a user row; a flexible system uses roles, permissions, and a junction table such as user_roles.
• Middleware example:

requireRole("admin"):
    if request.user.role != "admin":
        return response.status(403).send("Forbidden")
    return next()

• Role hierarchy: If admin includes all editor permissions, this inheritance must be explicitly implemented rather than assumed by role names.
• Granularity: Roles are useful for broad access; ownership and resource policies are still needed for rules such as “an editor may edit only documents in their department.”
• Maintenance: Centralize role and permission definitions to avoid contradictory checks scattered across routes.
• Default safety: New users should receive the least privileged role, commonly user, rather than administrative access.

VII. ORM Basics — Mapping Objects to Relational Data

An Object-Relational Mapper (ORM) connects programming-language objects with relational database tables. It provides models, query methods, relationships, and transaction support while hiding much SQL detail.

A. ORM Basics

An ORM maps a model such as User to a table such as users, usually mapping object fields to columns.

• Model mapping: A User object with id, email, and createdAt may correspond to columns id, email, and created_at.
• CRUD abstraction: User.findByPk(42) expresses a lookup without manually writing SELECT ... WHERE id = 42.
• Relationships: A one-to-many relationship means one user can own many orders; an orders.user_id foreign key represents that association.
• Migrations: A migration records schema changes, such as adding role VARCHAR(30) NOT NULL DEFAULT 'user', so environments can be updated consistently.
• Parameterization: ORM query parameters should be bound values, not string-concatenated input; this prevents SQL injection.
• Tradeoff: ORMs improve productivity and consistency but can produce inefficient queries, especially unnecessary repeated queries known as the N+1 problem.
• Transactions: Related changes should be grouped atomically so either all succeed or all roll back.

VIII. Database Connectivity — Linking the Application and Database

Database connectivity is the configuration and lifecycle management required for an application to communicate with a database server.

A. Database Connectivity

A reliable connection layer creates configured connections, reuses them efficiently, and reports failures safely.

• Connection string: A PostgreSQL URL may contain postgresql://user:password@host:5432/appdb; 5432 is the conventional PostgreSQL port.
• Connection pool: A pool maintains reusable connections, for example a maximum of 10, avoiding the overhead of opening a new connection for every request.
• Startup check: The application can execute a lightweight query such as SELECT 1 during startup to detect invalid credentials or unavailable databases.
• Async handling: Database operations return promises or futures; route code must await completion and handle rejected operations.
• Failure handling: Log diagnostic server details, but return a generic response so database credentials and topology are not exposed.
• Resource release: Transactions and manually acquired connections must be committed, rolled back, and released in all paths.
• Integrity controls: Foreign keys, unique constraints, and indexes enforce relationships, prevent duplicates, and improve lookup performance.

IX. CRUD Operations — Managing Persistent Records

CRUD describes the four basic data operations: Create, Read, Update, and Delete. Web endpoints commonly map these operations to HTTP methods.

A. CRUD Operations

CRUD should combine validated input, authorization, parameterized queries, and appropriate response codes.

• Create: POST /users inserts a new record; successful creation commonly returns 201 Created and the new identifier.
• Read: GET /users/42 retrieves record 42; list endpoints should support pagination rather than returning unlimited rows.
• Update: PATCH /users/42 changes selected fields, while PUT conventionally replaces a complete representation.
• Delete: DELETE /users/42 removes or deactivates a record; soft deletion may set deleted_at instead of physically removing data.
• Atomicity: Creating an order and its line items should use one transaction so an order is never stored without its required items.
• Concurrency: An update can use an updated_at value or version number to detect overwrites from simultaneous users.
• Result handling: A missing record should normally produce 404 Not Found, not a successful empty response that hides an error.

X. Data Validation — Checking Untrusted Input

Data validation determines whether incoming values satisfy the application's type, format, range, and business rules before they are processed or stored.

A. Data Validation

Validation should occur at the request boundary and again through database constraints where possible.

• Shape validation: Require an object containing email as a string and age as an integer; reject unexpected or missing fields according to the API contract.
• Format validation: Check that an email has an acceptable structure and that a date such as 2025-04-15 is parseable.
• Range validation: Reject quantity = 0 when the business rule requires a positive integer, for example 1 <= quantity <= 100.
• Normalization: Trim surrounding whitespace and canonicalize fields such as email carefully before comparison or storage.
• Business rules: Schema validation may accept a value that business logic rejects, such as a booking whose end date precedes its start date.
• Database constraints: A NOT NULL, UNIQUE, or CHECK (quantity > 0) constraint protects data even if another code path bypasses request validation.
• Error response: Return 400 Bad Request or 422 Unprocessable Content with field-specific messages, without revealing SQL statements or internal stack traces.

XI. Environment Variables — Separating Configuration from Code

Environment variables provide configuration at runtime, allowing the same application code to run in development, testing, and production with different settings.

A. Environment Variables

Environment variables are especially important for database credentials, session secrets, ports, and external service keys.

• Typical values: DATABASE_URL, SESSION_SECRET, PORT, and NODE_ENV describe connection, cryptographic, network, and runtime configuration.
• Reading configuration:

databaseUrl = env("DATABASE_URL")
sessionSecret = env("SESSION_SECRET")
if databaseUrl is missing or sessionSecret is missing:
    stop startup with a configuration error

env(name) retrieves the variable identified by name; startup failure prevents an insecure partially configured server.

• Secret handling: Do not commit .env files containing real passwords or keys; add them to .gitignore and provide a sanitized example file.
• Validation: Check required variables, accepted formats, and minimum secret length before opening the application to traffic.
• Scope: Server-only secrets must never be embedded in frontend bundles, where browser users can inspect them.
• Environment separation: Development and production should use different databases and credentials to prevent test data or destructive migrations affecting live records.
• Operational security: Secret rotation requires updating the deployment configuration and, where relevant, invalidating sessions signed with an old secret.