IAM Schema - Identity & Access Management Microservice

A comprehensive, production-ready PostgreSQL database schema for Identity and Access Management (IAM) designed as a universal microservice for businesses of all sizes across different countries.

Features

Core Capabilities

• Multiple Authentication Methods

  • Email and password authentication
  • OAuth 2.0 integration (Google, Microsoft)
  • Support for multiple OAuth providers per user

• Email Verification

  • Token-based verification with secure links
  • 6-digit verification codes
  • Configurable expiration periods

• Password Management

  • Secure password hashing using bcrypt
  • Password reset with token and code
  • Password history tracking to prevent reuse
  • OAuth users can set passwords via reset flow

• User Profile Management

  • Profile updates (first name, last name, username)
  • Profile image URL storage
  • Optional usernames with validation
  • Email change with verification

• Account Management

  • Soft delete for account removal
  • Account status tracking (pending, active, suspended, deleted)
  • JWT token blacklist for logout and security events
  • Comprehensive audit logging

• Security Features

  • JWT-based authentication (stateless)
  • JWT token blacklist for logout and revocation
  • Failed login attempt logging
  • Token invalidation on password reset and account deletion
  • Automatic cleanup of expired data

Database Schema

Core Tables

users

Primary user account information

• id - Unique user identifier (UUID)
• email - User email address (unique, validated format)
• username - Optional username (unique, min 3 chars)
• password_hash - Bcrypt hashed password
• first_name, last_name - User profile names
• profile_image_url - URL to profile image
• status - Account status: pending, active, suspended, deleted
• email_verified - Email verification flag
• created_at, updated_at, deleted_at - Timestamps
• last_login_at - Last successful login timestamp

oauth_providers

OAuth authentication data

• Links users to external OAuth providers
• Stores access tokens and refresh tokens
• Tracks token expiration
• Unique constraint per provider per user

verification_tokens

Multi-purpose verification system

• Email verification
• Password reset
• Email change verification
• Contains both secure tokens (for links) and codes (for manual entry)

jwt_blacklist

JWT token blacklist for logout and revocation

• JWT ID (jti) storage for invalidated tokens
• Token type tracking (access, refresh)
• Reason for blacklisting
• Automatic expiration cleanup

audit_logs

Comprehensive audit trail

• All security-relevant actions
• JSONB metadata for flexible logging
• Resource tracking (type and ID)
• Network information (IP, user agent)

password_history

Password reuse prevention

• Historical password hash storage
• Enables password policy enforcement

Custom Types

auth_provider: 'email', 'google', 'microsoft'
verification_type: 'email_verification', 'password_reset', 'email_change'
user_status: 'pending', 'active', 'suspended', 'deleted'

Core Functions

User Registration

register_user_with_email()

Register a new user with email and password.

Parameters:

• p_email - User email address
• p_password - Plain text password (will be hashed)
• p_first_name - Optional first name
• p_last_name - Optional last name
• p_username - Optional username

Returns:

• user_id - Created user UUID
• verification_token - Email verification token
• verification_code - 6-digit verification code

Example:

SELECT * FROM register_user_with_email(
    'user@example.com',
    'SecurePassword123!',
    'John',
    'Doe',
    'johndoe'
);

register_user_with_oauth()

Register or authenticate a user via OAuth provider.

Parameters:

• p_provider - OAuth provider (google, microsoft)
• p_provider_user_id - Provider's unique user ID
• p_email - User email from provider
• p_first_name - Optional first name
• p_last_name - Optional last name
• p_access_token - OAuth access token
• p_refresh_token - OAuth refresh token
• p_token_expires_at - Token expiration timestamp

Returns:

• user_id - User UUID (existing or newly created)

Example:

SELECT register_user_with_oauth(
    'google'::auth_provider,
    'google_user_123456',
    'user@gmail.com',
    'Jane',
    'Smith',
    'ya29.a0AfH6SMB...',
    '1//0gH8_K9Z...',
    CURRENT_TIMESTAMP + INTERVAL '1 hour'
);

Email Verification

verify_email()

Verify user email with token or code.

Parameters:

• p_token - Verification token (from email link)
• p_code - 6-digit verification code

Returns:

• BOOLEAN - Success status

Example:

-- Verify with token
SELECT verify_email('abc123...', NULL);

-- Verify with code
SELECT verify_email(NULL, '123456');

Authentication

authenticate_user()

Authenticate user with email and password.

Parameters:

• p_email - User email
• p_password - Plain text password

Returns:

• user_id - User UUID (NULL on failure)
• success - Authentication success flag
• message - Result message

Example:

SELECT * FROM authenticate_user('user@example.com', 'password123');

Password Reset

request_password_reset()

Request a password reset for a user.

Parameters:

• p_email - User email address

Returns:

• user_id - User UUID
• reset_token - Password reset token
• reset_code - 6-digit reset code

Example:

SELECT * FROM request_password_reset('user@example.com');

reset_password()

Reset password using reset token.

Parameters:

• p_token - Password reset token
• p_new_password - New plain text password

Returns:

• BOOLEAN - Success status

Side Effects:

• Invalidates all user sessions
• Saves old password to history

Example:

SELECT reset_password('reset_token_xyz', 'NewSecurePassword123!');

Profile Management

update_user_profile()

Update user profile information.

Parameters:

• p_user_id - User UUID
• p_first_name - Optional first name
• p_last_name - Optional last name
• p_username - Optional username
• p_profile_image_url - Optional profile image URL

Returns:

• BOOLEAN - Success status

Example:

SELECT update_user_profile(
    'user-uuid',
    p_first_name := 'John',
    p_last_name := 'Updated',
    p_profile_image_url := 'https://example.com/avatar.jpg'
);

get_user_profile()

Retrieve complete user profile.

Parameters:

• p_user_id - User UUID

Returns:

• Complete user profile with OAuth providers

Example:

SELECT * FROM get_user_profile('user-uuid');

Email Change

request_email_change()

Request to change user email address.

Parameters:

• p_user_id - User UUID
• p_new_email - New email address

Returns:

• change_token - Email change verification token
• change_code - 6-digit verification code

Example:

SELECT * FROM request_email_change('user-uuid', 'newemail@example.com');

verify_email_change()

Verify and complete email change.

Parameters:

• p_token - Email change token

Returns:

• BOOLEAN - Success status

Example:

SELECT verify_email_change('change_token_xyz');

Account Deletion

delete_user_account()

Soft delete a user account.

Parameters:

• p_user_id - User UUID

Returns:

• BOOLEAN - Success status

Side Effects:

• Sets status to 'deleted'
• Sets deleted_at timestamp
• Invalidates all sessions
• Invalidates all tokens

Example:

SELECT delete_user_account('user-uuid');

JWT Token Management

blacklist_jwt_token()

Add a JWT token to the blacklist (for logout, security events).

Parameters:

• p_user_id - User UUID
• p_jti - JWT ID (jti claim from token)
• p_expires_at - Token expiration timestamp
• p_token_type - Token type (default: 'access')
• p_reason - Optional reason for blacklisting

Returns:

• BOOLEAN - Success status

Example:

SELECT blacklist_jwt_token(
    'user-uuid',
    'jwt-id-from-token',
    CURRENT_TIMESTAMP + INTERVAL '15 minutes',
    'access',
    'user_logout'
);

is_jwt_blacklisted()

Check if a JWT token is blacklisted.

Parameters:

• p_jti - JWT ID (jti claim from token)

Returns:

• BOOLEAN - TRUE if blacklisted and not expired

Example:

SELECT is_jwt_blacklisted('jwt-id-from-token');

blacklist_all_user_tokens()

Invalidate all tokens for a user (logout from all devices).

Parameters:

• p_user_id - User UUID
• p_reason - Optional reason (default: 'logout_all')

Returns:

• INTEGER - Success indicator

Side Effects:

• Updates user's updated_at timestamp
• Application should check JWT iat claim against user's updated_at

Example:

SELECT blacklist_all_user_tokens('user-uuid', 'security_breach');

Note: For "logout all devices" functionality, your application should:

1. Call this function to update the user's updated_at timestamp
2. When validating JWTs, compare the token's iat (issued at) claim with the user's updated_at
3. Reject tokens where iat < updated_at

Maintenance

cleanup_expired_data()

Clean up expired tokens, sessions, and old audit logs.

Returns:

• INTEGER - Count of deleted records

Cleanup Rules:

• Verification tokens: > 7 days past expiration
• JWT blacklist entries: Past expiration
• Audit logs: > 1 year old

Example:

SELECT cleanup_expired_data();

Installation & Setup

Prerequisites

• Docker and Docker Compose
• Atlas CLI (for migrations)
• pgTAP (for testing)

Quick Start

1. Clone the repository

git clone <repository-url>
cd iam-schema

2. Start the database

docker-compose up -d db

3. Run migrations

docker-compose up migrate

4. Access pgAdmin (optional)

docker-compose --profile manual up pgadmin

Navigate to http://localhost:5050 and login with:

• Email: admin@example.com
• Password: admin

Configuration

Edit .env file to customize:

# Database
PGHOST=db
POSTGRES_USER=postgres
PGPASSWORD=postgres
PGDATABASE=iam
PGPORT=5432

# pgAdmin
PGADMIN_DEFAULT_EMAIL=admin@example.com
PGADMIN_DEFAULT_PASSWORD=admin
PGADMIN_PORT=5050

Testing

The project includes comprehensive pgTAP tests covering all functionality.

Run All Tests

docker-compose --profile manual up pgtap

Test Coverage

• Schema Tests (001_schema_tests.sql)

  • Table structure validation
  • Column existence and types
  • Constraints and indexes
  • Foreign key relationships

• Registration Tests (002_user_registration_tests.sql)

  • Email registration
  • OAuth registration (Google, Microsoft)
  • Duplicate handling
  • Validation constraints

• Authentication Tests (003_authentication_tests.sql)

  • Valid/invalid credentials
  • Unverified accounts
  • Suspended/deleted accounts
  • OAuth-only users

• Email Verification Tests (004_email_verification_tests.sql)

  • Token verification
  • Code verification
  • Expiration handling
  • Duplicate usage prevention

• Password Reset Tests (005_password_reset_tests.sql)

  • Reset request
  • Token validation
  • Password history
  • Token invalidation logging
  • OAuth user password setting

• Profile Management Tests (006_profile_management_tests.sql)

  • Profile updates
  • Username changes
  • Profile image updates
  • Data retrieval

• Email Change Tests (007_email_change_tests.sql)

  • Change request
  • Verification
  • Duplicate email prevention
  • Token expiration

• Account Deletion Tests (008_account_deletion_tests.sql)

  • Soft delete
  • Token invalidation logging
  • Verification token cleanup
  • Data preservation

• JWT Management Tests (009_jwt_management_tests.sql)

  • Token blacklisting
  • Blacklist validation
  • Expired entries handling
  • Logout all devices
  • Token types and reasons

• Cleanup Tests (010_cleanup_tests.sql)

  • Expired token cleanup
  • JWT blacklist cleanup
  • Audit log retention

Database Migrations

This project uses Atlas for database migrations.

Create a Migration Hash

docker-compose --profile manual up hash

Apply Migrations

docker-compose up migrate

Migration Files

All SQL migration files are stored in the schema/ directory:

• 001_initial_schema.sql - Core tables and types
• 002_functions.sql - Stored procedures and functions
• atlas.sum - Atlas migration checksums

Architecture & Design Decisions

Security

• Password Hashing: Bcrypt with salt factor 10
• Token Generation: Cryptographically secure random bytes (32 bytes for tokens)
• Soft Delete: Preserves data for audit and recovery
• JWT Authentication: Stateless authentication with blacklist support
• Token Revocation: Granular (per-token) and bulk (all devices) invalidation
• Audit Trail: Comprehensive logging of security events

Scalability

• UUID Primary Keys: Enables distributed systems
• Indexed Columns: Performance optimization for common queries
• JSONB Metadata: Flexible audit log data without schema changes
• Soft Delete: Maintains referential integrity

Flexibility

• Multiple OAuth Providers: Users can link multiple authentication methods
• Optional Fields: Minimal required data (email only for OAuth)
• Extensible Audit Logs: JSONB metadata for custom tracking
• Configurable Expiration: Flexible JWT and verification token lifetimes
• Stateless Design: JWT-based auth reduces database load

Data Integrity

• Foreign Keys with Cascade: Automatic cleanup on user deletion
• Check Constraints: Email format, username length validation
• Unique Constraints: Prevent duplicate emails, usernames, tokens
• NOT NULL Constraints: Ensure critical data is present

Use Cases

Small Business

• Simple email/password authentication
• Optional OAuth for user convenience
• Basic profile management

Medium Business

• Multiple OAuth providers
• Session management across devices
• Audit logging for compliance

Large Enterprise

• Multi-region support via UUID keys
• Comprehensive audit trails
• Advanced session management
• Password policies via history

International Applications

• No country-specific constraints
• UTC timestamps for global coordination
• Flexible username requirements
• Multi-provider OAuth support

JWT Implementation Guide

This IAM system is designed to work with JWT (JSON Web Token) based authentication. Here's how to integrate it with your application:

JWT Structure

Your JWTs should include these claims:

{
  "sub": "user-uuid",           // Subject (user ID)
  "jti": "unique-token-id",     // JWT ID (for blacklist)
  "iat": 1234567890,            // Issued at (Unix timestamp)
  "exp": 1234571490,            // Expiration (Unix timestamp)
  "email": "user@example.com",  // Optional: user email
  "role": "user"                // Optional: user role
}

JWT Validation Flow

1. Verify JWT signature and expiration (standard JWT validation)
2. Check if token is blacklisted:

   SELECT is_jwt_blacklisted('jti-from-token');

3. Check for "logout all devices" scenario:

   SELECT updated_at FROM users WHERE id = 'user-id-from-token';
   -- Reject if JWT iat < user updated_at

Logout Scenarios

Single Device Logout:

-- When user logs out from one device
SELECT blacklist_jwt_token(
    user_id,
    jti_from_token,
    exp_from_token,
    'access',
    'user_logout'
);

All Devices Logout:

-- When user clicks "logout from all devices"
SELECT blacklist_all_user_tokens(user_id, 'logout_all');

-- Then in JWT validation, reject tokens where:
-- token.iat < user.updated_at

Security Event (Password Reset, Account Deletion):

• Password reset and account deletion automatically log invalidate_tokens: true in audit_logs
• Your application should:
  1. Monitor these audit log events
  2. Call blacklist_all_user_tokens() or reject tokens based on updated_at

Performance Considerations

• Blacklist Growth: Run cleanup_expired_data() regularly (e.g., daily cron job)
• Database Queries: Only 2 queries needed per request:
  1. Check JWT blacklist (indexed on jti)
  2. Get user updated_at (primary key lookup)
• Caching: Consider caching user updated_at with short TTL (e.g., 5 minutes)

API Integration Example

Registration Flow

-- 1. Register user
SELECT * FROM register_user_with_email('user@example.com', 'password123', 'John', 'Doe');
-- Returns: user_id, verification_token, verification_code

-- 2. Send verification email with token/code

-- 3. Verify email
SELECT verify_email('token_from_email', NULL);
-- Returns: true

-- 4. User can now login
SELECT * FROM authenticate_user('user@example.com', 'password123');
-- Returns: user_id, success=true, message='Authentication successful'

OAuth Flow with JWT

-- 1. Register/Login with OAuth
SELECT register_user_with_oauth(
    'google'::auth_provider,
    'google_id_123',
    'user@gmail.com',
    'John',
    'Doe',
    'access_token',
    'refresh_token',
    CURRENT_TIMESTAMP + INTERVAL '1 hour'
);
-- Returns: user_id

-- 2. Application generates JWT with user_id
-- Include 'jti' (JWT ID) claim in your JWT for blacklist support
-- Include 'iat' (issued at) claim for logout-all-devices support

-- 3. On each request, validate JWT and check blacklist
SELECT is_jwt_blacklisted('jti-from-jwt-token');
-- Returns: false (if valid)

-- 4. Compare JWT iat with user's updated_at for "logout all" support
SELECT updated_at FROM users WHERE id = 'user_id_from_jwt';
-- Reject if JWT iat < user updated_at

JWT Logout Flow

-- Single device logout - blacklist specific token
SELECT blacklist_jwt_token(
    'user-uuid',
    'jti-from-token',
    'token-expiration-time',
    'access',
    'user_logout'
);

-- Logout from all devices - invalidate all user tokens
SELECT blacklist_all_user_tokens('user-uuid', 'logout_all');
-- This updates user's updated_at timestamp
-- Application should reject JWTs where iat < updated_at

Password Reset Flow

-- 1. Request reset
SELECT * FROM request_password_reset('user@example.com');
-- Returns: user_id, reset_token, reset_code

-- 2. Send reset email

-- 3. Reset password
SELECT reset_password('token_from_email', 'NewPassword123!');
-- Returns: true

Maintenance

Regular Cleanup

Run cleanup regularly (e.g., via cron job):

SELECT cleanup_expired_data();

This removes:

• Verification tokens expired > 7 days ago
• Expired JWT blacklist entries
• Audit logs > 1 year old

Backup Recommendations

• Regular PostgreSQL backups (pg_dump)
• Point-in-time recovery (WAL archiving)
• Encrypt backups containing user data
• Test restore procedures regularly

Monitoring

Key metrics to monitor:

• JWT blacklist size
• Failed login attempts
• Token expiration rates
• Audit log growth
• Query performance on indexed columns
• User updated_at changes (for logout-all tracking)

License

MIT License - See LICENSE file for details

Contributing

1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Ensure all tests pass
5. Submit a pull request

Support

For issues, questions, or contributions:

• GitHub Issues: [repository-url]/issues
• Documentation: This README
• Tests: See PGTap/ directory for examples