13. Chapter 13: Authentication and User Management

13.1. Learning Objectives

By the end of this chapter, you will understand:

• Implementing secure authentication systems for Web GIS applications

• Managing user permissions and role-based access control

• Securing geospatial APIs and protecting sensitive location data

• OAuth integration and social login implementation

• Session management and token-based authentication strategies

13.2. Authentication in Web GIS Applications

Authentication and authorization in Web GIS applications present unique challenges compared to traditional web applications. Geospatial data often contains sensitive location information, requires fine-grained access controls based on geographic boundaries, and must handle both human users and automated systems accessing spatial services.

13.2.1. Understanding GIS Security Requirements

Sensitive Location Data: Geospatial applications frequently handle sensitive location information including personal movement patterns, property boundaries, infrastructure locations, and classified geographical features. This data requires protection both at rest and in transit, with careful consideration of privacy regulations like GDPR and CCPA.

Multi-tenancy and Data Isolation: Many Web GIS applications serve multiple organizations or departments, each requiring complete isolation of their geospatial data. This necessitates robust tenant isolation mechanisms that prevent data leakage between organizations while maintaining system performance.

Geographic Access Controls: Traditional role-based access control (RBAC) must be extended to include geographic boundaries. Users might have different permissions based on their location, the geographic extent of the data they’re accessing, or administrative boundaries within which they have authority.

API Security for Spatial Services: Web GIS applications often expose numerous APIs for tile serving, spatial queries, and data manipulation. Each endpoint requires appropriate security measures while maintaining the performance needed for interactive mapping applications.

Compliance and Audit Requirements: Many organizations using GIS applications must comply with regulations requiring detailed audit trails of data access, modification tracking, and user activity logging. This is particularly important for government agencies and organizations handling critical infrastructure data.

13.2.2. Modern Authentication Patterns

JWT (JSON Web Tokens): JWTs provide a stateless authentication mechanism that scales well for distributed GIS applications. They can include geospatial context like authorized geographic extents or location-based permissions.

OAuth 2.0 and OpenID Connect: These standards enable integration with existing organizational identity providers and support secure API access for both interactive applications and automated GIS workflows.

Multi-factor Authentication (MFA): Given the sensitive nature of geospatial data, MFA is often required to ensure that location information is accessed only by authorized individuals.

Certificate-based Authentication: For high-security applications, client certificates provide strong authentication for both users and automated systems accessing geospatial services.

13.3. JWT-based Authentication Implementation

13.3.1. Core Authentication Service

// server/src/services/authService.ts
import jwt from 'jsonwebtoken';
import bcrypt from 'bcrypt';
import { DatabaseService } from './databaseService';
import { RedisService } from './redisService';
import { AuthenticationError, AuthorizationError } from '../utils/errors';

interface User {
  id: string;
  email: string;
  username: string;
  passwordHash: string;
  role: string;
  organizationId: string;
  isActive: boolean;
  lastLoginAt?: Date;
  createdAt: Date;
  updatedAt: Date;
  profile?: UserProfile;
  permissions: string[];
}

interface UserProfile {
  firstName: string;
  lastName: string;
  department?: string;
  phone?: string;
  timezone: string;
  preferences: Record<string, any>;
}

interface JWTPayload {
  userId: string;
  email: string;
  username: string;
  role: string;
  organizationId: string;
  permissions: string[];
  geographicExtent?: GeoJSON.Polygon;
  sessionId: string;
  iat: number;
  exp: number;
}

interface GeographicExtent {
  type: 'Polygon';
  coordinates: number[][][];
}

export class AuthService {
  private db = new DatabaseService();
  private redis = new RedisService();
  private jwtSecret = process.env.JWT_SECRET!;
  private jwtExpiry = process.env.JWT_EXPIRY || '24h';
  private refreshTokenExpiry = 7 * 24 * 60 * 60; // 7 days

  async authenticate(email: string, password: string): Promise<{
    user: Omit<User, 'passwordHash'>;
    accessToken: string;
    refreshToken: string;
  }> {
    try {
      // Get user from database
      const user = await this.getUserByEmail(email);
      
      if (!user || !user.isActive) {
        throw new AuthenticationError('Invalid credentials');
      }

      // Verify password
      const isValidPassword = await bcrypt.compare(password, user.passwordHash);
      if (!isValidPassword) {
        throw new AuthenticationError('Invalid credentials');
      }

      // Update last login
      await this.updateLastLogin(user.id);

      // Generate session ID
      const sessionId = this.generateSessionId();

      // Get user permissions
      const permissions = await this.getUserPermissions(user.id);

      // Get geographic extent for user
      const geographicExtent = await this.getUserGeographicExtent(user.id);

      // Generate tokens
      const accessToken = this.generateAccessToken({
        userId: user.id,
        email: user.email,
        username: user.username,
        role: user.role,
        organizationId: user.organizationId,
        permissions,
        geographicExtent,
        sessionId
      });

      const refreshToken = this.generateRefreshToken(user.id, sessionId);

      // Store session in Redis
      await this.storeSession(sessionId, user.id);

      // Remove password hash from response
      const { passwordHash, ...userResponse } = user;

      return {
        user: { ...userResponse, permissions },
        accessToken,
        refreshToken
      };
    } catch (error) {
      console.error('Authentication error:', error);
      throw error;
    }
  }

  async refreshToken(refreshToken: string): Promise<{
    accessToken: string;
    refreshToken: string;
  }> {
    try {
      // Verify refresh token
      const decoded = jwt.verify(refreshToken, this.jwtSecret) as any;
      
      if (decoded.type !== 'refresh') {
        throw new AuthenticationError('Invalid refresh token');
      }

      // Check if session exists
      const sessionExists = await this.redis.exists(`session:${decoded.sessionId}`);
      if (!sessionExists) {
        throw new AuthenticationError('Session expired');
      }

      // Get updated user data
      const user = await this.getUserById(decoded.userId);
      if (!user || !user.isActive) {
        throw new AuthenticationError('User not found or inactive');
      }

      // Get current permissions and geographic extent
      const permissions = await this.getUserPermissions(user.id);
      const geographicExtent = await this.getUserGeographicExtent(user.id);

      // Generate new tokens with same session ID
      const newAccessToken = this.generateAccessToken({
        userId: user.id,
        email: user.email,
        username: user.username,
        role: user.role,
        organizationId: user.organizationId,
        permissions,
        geographicExtent,
        sessionId: decoded.sessionId
      });

      const newRefreshToken = this.generateRefreshToken(user.id, decoded.sessionId);

      // Extend session
      await this.extendSession(decoded.sessionId);

      return {
        accessToken: newAccessToken,
        refreshToken: newRefreshToken
      };
    } catch (error) {
      console.error('Token refresh error:', error);
      throw new AuthenticationError('Failed to refresh token');
    }
  }

  async logout(sessionId: string): Promise<void> {
    try {
      await this.redis.del(`session:${sessionId}`);
    } catch (error) {
      console.error('Logout error:', error);
    }
  }

  async verifyToken(token: string): Promise<JWTPayload> {
    try {
      const decoded = jwt.verify(token, this.jwtSecret) as JWTPayload;
      
      // Check if session is still valid
      const sessionExists = await this.redis.exists(`session:${decoded.sessionId}`);
      if (!sessionExists) {
        throw new AuthenticationError('Session expired');
      }

      return decoded;
    } catch (error) {
      if (error instanceof jwt.JsonWebTokenError) {
        throw new AuthenticationError('Invalid token');
      }
      throw error;
    }
  }

  private generateAccessToken(payload: Omit<JWTPayload, 'iat' | 'exp'>): string {
    return jwt.sign(
      {
        ...payload,
        type: 'access'
      },
      this.jwtSecret,
      {
        expiresIn: this.jwtExpiry,
        issuer: 'webgis-app',
        audience: 'webgis-api'
      }
    );
  }

  private generateRefreshToken(userId: string, sessionId: string): string {
    return jwt.sign(
      {
        userId,
        sessionId,
        type: 'refresh'
      },
      this.jwtSecret,
      {
        expiresIn: `${this.refreshTokenExpiry}s`,
        issuer: 'webgis-app',
        audience: 'webgis-api'
      }
    );
  }

  private generateSessionId(): string {
    return require('crypto').randomBytes(32).toString('hex');
  }

  private async storeSession(sessionId: string, userId: string): Promise<void> {
    await this.redis.setex(
      `session:${sessionId}`,
      this.refreshTokenExpiry,
      JSON.stringify({
        userId,
        createdAt: new Date().toISOString()
      })
    );
  }

  private async extendSession(sessionId: string): Promise<void> {
    await this.redis.expire(`session:${sessionId}`, this.refreshTokenExpiry);
  }

  private async getUserByEmail(email: string): Promise<User | null> {
    const query = `
      SELECT u.*, p.first_name, p.last_name, p.department, p.phone, p.timezone, p.preferences
      FROM auth.users u
      LEFT JOIN auth.user_profiles p ON u.id = p.user_id
      WHERE u.email = $1
    `;
    
    const result = await this.db.query(query, [email]);
    
    if (result.rows.length === 0) {
      return null;
    }

    const row = result.rows[0];
    return {
      id: row.id,
      email: row.email,
      username: row.username,
      passwordHash: row.password_hash,
      role: row.role,
      organizationId: row.organization_id,
      isActive: row.is_active,
      lastLoginAt: row.last_login_at,
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      profile: row.first_name ? {
        firstName: row.first_name,
        lastName: row.last_name,
        department: row.department,
        phone: row.phone,
        timezone: row.timezone,
        preferences: row.preferences || {}
      } : undefined,
      permissions: [] // Will be populated separately
    };
  }

  private async getUserById(id: string): Promise<User | null> {
    const query = `
      SELECT * FROM auth.users WHERE id = $1 AND is_active = true
    `;
    
    const result = await this.db.query(query, [id]);
    return result.rows.length > 0 ? result.rows[0] : null;
  }

  private async updateLastLogin(userId: string): Promise<void> {
    const query = `
      UPDATE auth.users 
      SET last_login_at = NOW() 
      WHERE id = $1
    `;
    
    await this.db.query(query, [userId]);
  }

  private async getUserPermissions(userId: string): Promise<string[]> {
    const query = `
      SELECT DISTINCT p.name
      FROM auth.permissions p
      JOIN auth.role_permissions rp ON p.id = rp.permission_id
      JOIN auth.user_roles ur ON rp.role_id = ur.role_id
      WHERE ur.user_id = $1
      UNION
      SELECT DISTINCT p.name
      FROM auth.permissions p
      JOIN auth.user_permissions up ON p.id = up.permission_id
      WHERE up.user_id = $1
    `;

    const result = await this.db.query(query, [userId]);
    return result.rows.map(row => row.name);
  }

  private async getUserGeographicExtent(userId: string): Promise<GeoJSON.Polygon | undefined> {
    const query = `
      SELECT ST_AsGeoJSON(geographic_extent) as extent
      FROM auth.user_geographic_extents
      WHERE user_id = $1
    `;

    const result = await this.db.query(query, [userId]);
    
    if (result.rows.length === 0) {
      return undefined;
    }

    return JSON.parse(result.rows[0].extent);
  }
}

13.3.2. Authentication Middleware

// server/src/middleware/auth.ts
import { Request, Response, NextFunction } from 'express';
import { AuthService } from '../services/authService';
import { AuthenticationError, AuthorizationError } from '../utils/errors';

interface AuthenticatedRequest extends Request {
  user: {
    userId: string;
    email: string;
    username: string;
    role: string;
    organizationId: string;
    permissions: string[];
    geographicExtent?: GeoJSON.Polygon;
    sessionId: string;
  };
}

export const authMiddleware = async (
  req: AuthenticatedRequest,
  res: Response,
  next: NextFunction
) => {
  try {
    const authHeader = req.headers.authorization;
    
    if (!authHeader || !authHeader.startsWith('Bearer ')) {
      throw new AuthenticationError('No valid authorization header');
    }

    const token = authHeader.substring(7);
    const authService = new AuthService();
    
    const payload = await authService.verifyToken(token);
    
    // Attach user information to request
    req.user = {
      userId: payload.userId,
      email: payload.email,
      username: payload.username,
      role: payload.role,
      organizationId: payload.organizationId,
      permissions: payload.permissions,
      geographicExtent: payload.geographicExtent,
      sessionId: payload.sessionId
    };

    next();
  } catch (error) {
    if (error instanceof AuthenticationError) {
      return res.status(401).json({ error: error.message });
    }
    
    console.error('Auth middleware error:', error);
    return res.status(500).json({ error: 'Internal server error' });
  }
};

// Permission-based authorization middleware
export const requirePermission = (requiredPermission: string) => {
  return (req: AuthenticatedRequest, res: Response, next: NextFunction) => {
    if (!req.user) {
      return res.status(401).json({ error: 'Not authenticated' });
    }

    if (!req.user.permissions.includes(requiredPermission)) {
      return res.status(403).json({ 
        error: 'Insufficient permissions',
        required: requiredPermission
      });
    }

    next();
  };
};

// Role-based authorization middleware
export const requireRole = (requiredRole: string | string[]) => {
  const roles = Array.isArray(requiredRole) ? requiredRole : [requiredRole];
  
  return (req: AuthenticatedRequest, res: Response, next: NextFunction) => {
    if (!req.user) {
      return res.status(401).json({ error: 'Not authenticated' });
    }

    if (!roles.includes(req.user.role)) {
      return res.status(403).json({ 
        error: 'Insufficient role',
        required: roles,
        current: req.user.role
      });
    }

    next();
  };
};

// Organization-based authorization middleware
export const requireSameOrganization = (
  req: AuthenticatedRequest,
  res: Response,
  next: NextFunction
) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Not authenticated' });
  }

  // Check if the requested resource belongs to the user's organization
  const resourceOrgId = req.params.organizationId || req.body.organizationId;
  
  if (resourceOrgId && resourceOrgId !== req.user.organizationId) {
    return res.status(403).json({ 
      error: 'Access denied to organization resource'
    });
  }

  next();
};

// Geographic extent authorization middleware
export const requireGeographicAccess = async (
  req: AuthenticatedRequest,
  res: Response,
  next: NextFunction
) => {
  try {
    if (!req.user || !req.user.geographicExtent) {
      return next(); // No geographic restrictions
    }

    // Extract geometry from request (bbox, geometry, etc.)
    const requestGeometry = extractGeometryFromRequest(req);
    
    if (requestGeometry) {
      const hasAccess = await checkGeographicAccess(
        req.user.geographicExtent,
        requestGeometry
      );
      
      if (!hasAccess) {
        return res.status(403).json({
          error: 'Access denied: outside authorized geographic extent'
        });
      }
    }

    next();
  } catch (error) {
    console.error('Geographic access check error:', error);
    return res.status(500).json({ error: 'Internal server error' });
  }
};

function extractGeometryFromRequest(req: Request): GeoJSON.Geometry | null {
  // Extract bbox from query parameters
  if (req.query.bbox) {
    const [minLng, minLat, maxLng, maxLat] = (req.query.bbox as string)
      .split(',')
      .map(Number);
    
    return {
      type: 'Polygon',
      coordinates: [[
        [minLng, minLat],
        [maxLng, minLat],
        [maxLng, maxLat],
        [minLng, maxLat],
        [minLng, minLat]
      ]]
    };
  }

  // Extract geometry from request body
  if (req.body.geometry) {
    return req.body.geometry;
  }

  // Extract from GeoJSON feature
  if (req.body.type === 'Feature' && req.body.geometry) {
    return req.body.geometry;
  }

  return null;
}

async function checkGeographicAccess(
  userExtent: GeoJSON.Polygon,
  requestGeometry: GeoJSON.Geometry
): Promise<boolean> {
  // This would typically use PostGIS or a spatial library
  // For now, we'll use a simplified check
  try {
    const turf = require('@turf/turf');
    const requestFeature = turf.feature(requestGeometry);
    const userExtentFeature = turf.feature(userExtent);
    
    // Check if the request geometry is within the user's extent
    return turf.booleanWithin(requestFeature, userExtentFeature) ||
           turf.booleanIntersects(requestFeature, userExtentFeature);
  } catch (error) {
    console.error('Spatial access check error:', error);
    return false; // Deny access on error
  }
}

13.3.3. Database Schema for Authentication

-- Authentication schema
CREATE SCHEMA IF NOT EXISTS auth;

-- Organizations table
CREATE TABLE auth.organizations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    domain VARCHAR(255),
    settings JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Users table
CREATE TABLE auth.users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES auth.organizations(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(100) NOT NULL DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE,
    last_login_at TIMESTAMP WITH TIME ZONE,
    password_changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT unique_email_per_org UNIQUE (organization_id, email),
    CONSTRAINT unique_username_per_org UNIQUE (organization_id, username)
);

-- User profiles
CREATE TABLE auth.user_profiles (
    user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department VARCHAR(100),
    phone VARCHAR(20),
    timezone VARCHAR(50) DEFAULT 'UTC',
    preferences JSONB DEFAULT '{}',
    avatar_url VARCHAR(500),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Roles table
CREATE TABLE auth.roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_id UUID NOT NULL REFERENCES auth.organizations(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_system_role BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT unique_role_name_per_org UNIQUE (organization_id, name)
);

-- Permissions table
CREATE TABLE auth.permissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    category VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Role permissions junction table
CREATE TABLE auth.role_permissions (
    role_id UUID REFERENCES auth.roles(id) ON DELETE CASCADE,
    permission_id UUID REFERENCES auth.permissions(id) ON DELETE CASCADE,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    granted_by UUID REFERENCES auth.users(id),
    
    PRIMARY KEY (role_id, permission_id)
);

-- User roles junction table
CREATE TABLE auth.user_roles (
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    role_id UUID REFERENCES auth.roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    assigned_by UUID REFERENCES auth.users(id),
    expires_at TIMESTAMP WITH TIME ZONE,
    
    PRIMARY KEY (user_id, role_id)
);

-- Direct user permissions (for exceptions)
CREATE TABLE auth.user_permissions (
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    permission_id UUID REFERENCES auth.permissions(id) ON DELETE CASCADE,
    granted BOOLEAN DEFAULT TRUE,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    granted_by UUID REFERENCES auth.users(id),
    expires_at TIMESTAMP WITH TIME ZONE,
    
    PRIMARY KEY (user_id, permission_id)
);

-- Geographic extents for users
CREATE TABLE auth.user_geographic_extents (
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    geographic_extent GEOMETRY(Polygon, 4326) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    
    PRIMARY KEY (user_id)
);

-- API keys for programmatic access
CREATE TABLE auth.api_keys (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    key_hash VARCHAR(255) NOT NULL UNIQUE,
    permissions TEXT[] DEFAULT '{}',
    rate_limit INTEGER DEFAULT 1000, -- requests per hour
    is_active BOOLEAN DEFAULT TRUE,
    expires_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Audit log for security events
CREATE TABLE auth.audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES auth.users(id),
    organization_id UUID REFERENCES auth.organizations(id),
    event_type VARCHAR(100) NOT NULL,
    resource_type VARCHAR(100),
    resource_id VARCHAR(255),
    ip_address INET,
    user_agent TEXT,
    details JSONB DEFAULT '{}',
    success BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_users_organization_id ON auth.users(organization_id);
CREATE INDEX idx_users_email ON auth.users(email);
CREATE INDEX idx_users_username ON auth.users(username);
CREATE INDEX idx_users_is_active ON auth.users(is_active);
CREATE INDEX idx_user_roles_user_id ON auth.user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON auth.user_roles(role_id);
CREATE INDEX idx_user_permissions_user_id ON auth.user_permissions(user_id);
CREATE INDEX idx_api_keys_key_hash ON auth.api_keys(key_hash);
CREATE INDEX idx_api_keys_user_id ON auth.api_keys(user_id);
CREATE INDEX idx_audit_log_user_id ON auth.audit_log(user_id);
CREATE INDEX idx_audit_log_created_at ON auth.audit_log(created_at);
CREATE INDEX idx_audit_log_event_type ON auth.audit_log(event_type);

-- Spatial index for geographic extents
CREATE INDEX idx_user_geographic_extents_geom ON auth.user_geographic_extents USING GIST(geographic_extent);

-- Insert default permissions
INSERT INTO auth.permissions (name, description, category) VALUES
('datasets.read', 'Read access to datasets', 'data'),
('datasets.write', 'Write access to datasets', 'data'),
('datasets.delete', 'Delete access to datasets', 'data'),
('datasets.admin', 'Administrative access to datasets', 'data'),
('maps.read', 'Read access to maps', 'maps'),
('maps.write', 'Write access to maps', 'maps'),
('maps.delete', 'Delete access to maps', 'maps'),
('maps.share', 'Share maps with others', 'maps'),
('users.read', 'Read user information', 'admin'),
('users.write', 'Modify user information', 'admin'),
('users.admin', 'Administrative access to users', 'admin'),
('analytics.read', 'Read analytics data', 'analytics'),
('system.admin', 'System administration', 'system');

-- Triggers for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_organizations_updated_at 
    BEFORE UPDATE ON auth.organizations 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON auth.users 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_profiles_updated_at 
    BEFORE UPDATE ON auth.user_profiles 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

13.4. OAuth Integration and Social Login

13.4.1. OAuth 2.0 Implementation

// server/src/services/oauthService.ts
import { AuthService } from './authService';
import { UserService } from './userService';
import { randomBytes } from 'crypto';

interface OAuthProvider {
  name: string;
  clientId: string;
  clientSecret: string;
  authorizationUrl: string;
  tokenUrl: string;
  userInfoUrl: string;
  scope: string[];
}

interface OAuthState {
  provider: string;
  returnUrl: string;
  nonce: string;
  createdAt: Date;
}

export class OAuthService {
  private authService = new AuthService();
  private userService = new UserService();
  private states = new Map<string, OAuthState>();

  private providers: Record<string, OAuthProvider> = {
    google: {
      name: 'Google',
      clientId: process.env.GOOGLE_CLIENT_ID!,
      clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
      authorizationUrl: 'https://accounts.google.com/o/oauth2/v2/auth',
      tokenUrl: 'https://oauth2.googleapis.com/token',
      userInfoUrl: 'https://www.googleapis.com/oauth2/v2/userinfo',
      scope: ['openid', 'email', 'profile']
    },
    github: {
      name: 'GitHub',
      clientId: process.env.GITHUB_CLIENT_ID!,
      clientSecret: process.env.GITHUB_CLIENT_SECRET!,
      authorizationUrl: 'https://github.com/login/oauth/authorize',
      tokenUrl: 'https://github.com/login/oauth/access_token',
      userInfoUrl: 'https://api.github.com/user',
      scope: ['user:email']
    },
    microsoft: {
      name: 'Microsoft',
      clientId: process.env.MICROSOFT_CLIENT_ID!,
      clientSecret: process.env.MICROSOFT_CLIENT_SECRET!,
      authorizationUrl: 'https://login.microsoftonline.com/common/oauth2/v2.0/authorize',
      tokenUrl: 'https://login.microsoftonline.com/common/oauth2/v2.0/token',
      userInfoUrl: 'https://graph.microsoft.com/v1.0/me',
      scope: ['openid', 'email', 'profile']
    }
  };

  getAuthorizationUrl(provider: string, returnUrl: string = '/'): string {
    const oauthProvider = this.providers[provider];
    if (!oauthProvider) {
      throw new Error(`Unsupported OAuth provider: ${provider}`);
    }

    const state = this.generateState(provider, returnUrl);
    const nonce = randomBytes(16).toString('hex');

    // Store state for verification
    this.states.set(state, {
      provider,
      returnUrl,
      nonce,
      createdAt: new Date()
    });

    // Clean up old states (older than 10 minutes)
    this.cleanupOldStates();

    const params = new URLSearchParams({
      client_id: oauthProvider.clientId,
      redirect_uri: `${process.env.BASE_URL}/auth/oauth/${provider}/callback`,
      response_type: 'code',
      scope: oauthProvider.scope.join(' '),
      state,
      nonce
    });

    return `${oauthProvider.authorizationUrl}?${params.toString()}`;
  }

  async handleCallback(
    provider: string,
    code: string,
    state: string
  ): Promise<{
    user: any;
    accessToken: string;
    refreshToken: string;
    returnUrl: string;
  }> {
    // Verify state
    const stateData = this.states.get(state);
    if (!stateData || stateData.provider !== provider) {
      throw new Error('Invalid OAuth state');
    }

    // Remove used state
    this.states.delete(state);

    const oauthProvider = this.providers[provider];
    if (!oauthProvider) {
      throw new Error(`Unsupported OAuth provider: ${provider}`);
    }

    try {
      // Exchange code for access token
      const tokenResponse = await this.exchangeCodeForToken(oauthProvider, code);
      
      // Get user info from provider
      const userInfo = await this.getUserInfo(oauthProvider, tokenResponse.access_token);
      
      // Find or create user
      const user = await this.findOrCreateUser(provider, userInfo);
      
      // Generate application tokens
      const authResult = await this.authService.authenticateOAuthUser(user);
      
      return {
        ...authResult,
        returnUrl: stateData.returnUrl
      };
    } catch (error) {
      console.error(`OAuth ${provider} callback error:`, error);
      throw new Error('OAuth authentication failed');
    }
  }

  private async exchangeCodeForToken(
    provider: OAuthProvider,
    code: string
  ): Promise<any> {
    const params = new URLSearchParams({
      client_id: provider.clientId,
      client_secret: provider.clientSecret,
      code,
      grant_type: 'authorization_code',
      redirect_uri: `${process.env.BASE_URL}/auth/oauth/${provider.name.toLowerCase()}/callback`
    });

    const response = await fetch(provider.tokenUrl, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/x-www-form-urlencoded',
        'Accept': 'application/json'
      },
      body: params.toString()
    });

    if (!response.ok) {
      throw new Error(`Token exchange failed: ${response.statusText}`);
    }

    return response.json();
  }

  private async getUserInfo(provider: OAuthProvider, accessToken: string): Promise<any> {
    const response = await fetch(provider.userInfoUrl, {
      headers: {
        'Authorization': `Bearer ${accessToken}`,
        'Accept': 'application/json'
      }
    });

    if (!response.ok) {
      throw new Error(`User info request failed: ${response.statusText}`);
    }

    return response.json();
  }

  private async findOrCreateUser(provider: string, userInfo: any): Promise<any> {
    const email = this.extractEmail(provider, userInfo);
    const username = this.extractUsername(provider, userInfo);
    const profile = this.extractProfile(provider, userInfo);

    // Try to find existing user by email
    let user = await this.userService.getUserByEmail(email);

    if (!user) {
      // Create new user
      user = await this.userService.createOAuthUser({
        email,
        username,
        provider,
        providerId: userInfo.id || userInfo.sub,
        profile
      });
    } else {
      // Link OAuth provider if not already linked
      await this.userService.linkOAuthProvider(user.id, provider, userInfo.id || userInfo.sub);
    }

    return user;
  }

  private extractEmail(provider: string, userInfo: any): string {
    switch (provider) {
      case 'google':
        return userInfo.email;
      case 'github':
        return userInfo.email;
      case 'microsoft':
        return userInfo.mail || userInfo.userPrincipalName;
      default:
        return userInfo.email;
    }
  }

  private extractUsername(provider: string, userInfo: any): string {
    switch (provider) {
      case 'google':
        return userInfo.email.split('@')[0];
      case 'github':
        return userInfo.login;
      case 'microsoft':
        return userInfo.displayName || userInfo.mail?.split('@')[0];
      default:
        return userInfo.email.split('@')[0];
    }
  }

  private extractProfile(provider: string, userInfo: any): any {
    switch (provider) {
      case 'google':
        return {
          firstName: userInfo.given_name,
          lastName: userInfo.family_name,
          avatarUrl: userInfo.picture
        };
      case 'github':
        return {
          firstName: userInfo.name?.split(' ')[0],
          lastName: userInfo.name?.split(' ').slice(1).join(' '),
          avatarUrl: userInfo.avatar_url
        };
      case 'microsoft':
        return {
          firstName: userInfo.givenName,
          lastName: userInfo.surname,
          avatarUrl: null
        };
      default:
        return {};
    }
  }

  private generateState(provider: string, returnUrl: string): string {
    const data = JSON.stringify({ provider, returnUrl, timestamp: Date.now() });
    return Buffer.from(data).toString('base64url');
  }

  private cleanupOldStates(): void {
    const tenMinutesAgo = new Date(Date.now() - 10 * 60 * 1000);
    
    for (const [state, data] of this.states.entries()) {
      if (data.createdAt < tenMinutesAgo) {
        this.states.delete(state);
      }
    }
  }
}

// server/src/routes/auth.ts
import { Router } from 'express';
import { AuthService } from '../services/authService';
import { OAuthService } from '../services/oauthService';
import { body, validationResult } from 'express-validator';
import { authMiddleware } from '../middleware/auth';

const router = Router();
const authService = new AuthService();
const oauthService = new OAuthService();

// Login endpoint
router.post('/login', [
  body('email').isEmail().normalizeEmail(),
  body('password').isLength({ min: 1 })
], async (req, res, next) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({ errors: errors.array() });
    }

    const { email, password } = req.body;
    const result = await authService.authenticate(email, password);
    
    res.json(result);
  } catch (error) {
    next(error);
  }
});

// Refresh token endpoint
router.post('/refresh', [
  body('refreshToken').isString()
], async (req, res, next) => {
  try {
    const { refreshToken } = req.body;
    const result = await authService.refreshToken(refreshToken);
    
    res.json(result);
  } catch (error) {
    next(error);
  }
});

// Logout endpoint
router.post('/logout', authMiddleware, async (req: any, res, next) => {
  try {
    await authService.logout(req.user.sessionId);
    res.status(204).send();
  } catch (error) {
    next(error);
  }
});

// OAuth authorization URL
router.get('/oauth/:provider', (req, res, next) => {
  try {
    const { provider } = req.params;
    const { returnUrl } = req.query;
    
    const authUrl = oauthService.getAuthorizationUrl(
      provider, 
      returnUrl as string
    );
    
    res.json({ authUrl });
  } catch (error) {
    next(error);
  }
});

// OAuth callback
router.get('/oauth/:provider/callback', async (req, res, next) => {
  try {
    const { provider } = req.params;
    const { code, state } = req.query;
    
    if (!code || !state) {
      return res.status(400).json({ error: 'Missing code or state' });
    }
    
    const result = await oauthService.handleCallback(
      provider,
      code as string,
      state as string
    );
    
    // Redirect to frontend with tokens
    const redirectUrl = new URL(result.returnUrl, process.env.FRONTEND_URL);
    redirectUrl.searchParams.set('accessToken', result.accessToken);
    redirectUrl.searchParams.set('refreshToken', result.refreshToken);
    
    res.redirect(redirectUrl.toString());
  } catch (error) {
    next(error);
  }
});

// Get current user
router.get('/me', authMiddleware, async (req: any, res, next) => {
  try {
    const userService = new (require('../services/userService')).UserService();
    const user = await userService.getUserById(req.user.userId);
    
    res.json(user);
  } catch (error) {
    next(error);
  }
});

export { router as authRouter };

13.5. Role-Based Access Control (RBAC)

13.5.1. Permission Management System

// server/src/services/permissionService.ts
interface Permission {
  id: string;
  name: string;
  description: string;
  category: string;
}

interface Role {
  id: string;
  organizationId: string;
  name: string;
  description: string;
  permissions: Permission[];
  isSystemRole: boolean;
}

export class PermissionService {
  private db = new DatabaseService();
  private cache = new CacheService();

  async getUserPermissions(userId: string): Promise<Permission[]> {
    const cacheKey = `user_permissions:${userId}`;
    
    return this.cache.getOrSet(cacheKey, async () => {
      const query = `
        WITH user_role_permissions AS (
          SELECT DISTINCT p.id, p.name, p.description, p.category
          FROM auth.permissions p
          JOIN auth.role_permissions rp ON p.id = rp.permission_id
          JOIN auth.user_roles ur ON rp.role_id = ur.role_id
          WHERE ur.user_id = $1
          AND (ur.expires_at IS NULL OR ur.expires_at > NOW())
        ),
        user_direct_permissions AS (
          SELECT DISTINCT p.id, p.name, p.description, p.category
          FROM auth.permissions p
          JOIN auth.user_permissions up ON p.id = up.permission_id
          WHERE up.user_id = $1
          AND up.granted = true
          AND (up.expires_at IS NULL OR up.expires_at > NOW())
        ),
        user_denied_permissions AS (
          SELECT permission_id
          FROM auth.user_permissions
          WHERE user_id = $1
          AND granted = false
          AND (expires_at IS NULL OR expires_at > NOW())
        )
        SELECT DISTINCT id, name, description, category
        FROM (
          SELECT * FROM user_role_permissions
          UNION
          SELECT * FROM user_direct_permissions
        ) all_permissions
        WHERE id NOT IN (SELECT permission_id FROM user_denied_permissions)
        ORDER BY category, name
      `;

      const result = await this.db.query(query, [userId]);
      return result.rows;
    }, 300); // Cache for 5 minutes
  }

  async hasPermission(userId: string, permissionName: string): Promise<boolean> {
    const permissions = await this.getUserPermissions(userId);
    return permissions.some(p => p.name === permissionName);
  }

  async hasAnyPermission(userId: string, permissionNames: string[]): Promise<boolean> {
    const permissions = await this.getUserPermissions(userId);
    const userPermissionNames = permissions.map(p => p.name);
    return permissionNames.some(name => userPermissionNames.includes(name));
  }

  async hasAllPermissions(userId: string, permissionNames: string[]): Promise<boolean> {
    const permissions = await this.getUserPermissions(userId);
    const userPermissionNames = permissions.map(p => p.name);
    return permissionNames.every(name => userPermissionNames.includes(name));
  }

  async grantPermissionToUser(
    userId: string,
    permissionId: string,
    grantedBy: string,
    expiresAt?: Date
  ): Promise<void> {
    const query = `
      INSERT INTO auth.user_permissions (user_id, permission_id, granted, granted_by, expires_at)
      VALUES ($1, $2, true, $3, $4)
      ON CONFLICT (user_id, permission_id) 
      DO UPDATE SET 
        granted = true,
        granted_by = $3,
        expires_at = $4,
        granted_at = NOW()
    `;

    await this.db.query(query, [userId, permissionId, grantedBy, expiresAt]);
    
    // Invalidate cache
    await this.cache.del(`user_permissions:${userId}`);
  }

  async revokePermissionFromUser(
    userId: string,
    permissionId: string,
    revokedBy: string
  ): Promise<void> {
    const query = `
      INSERT INTO auth.user_permissions (user_id, permission_id, granted, granted_by)
      VALUES ($1, $2, false, $3)
      ON CONFLICT (user_id, permission_id) 
      DO UPDATE SET 
        granted = false,
        granted_by = $3,
        granted_at = NOW()
    `;

    await this.db.query(query, [userId, permissionId, revokedBy]);
    
    // Invalidate cache
    await this.cache.del(`user_permissions:${userId}`);
  }

  async assignRoleToUser(
    userId: string,
    roleId: string,
    assignedBy: string,
    expiresAt?: Date
  ): Promise<void> {
    const query = `
      INSERT INTO auth.user_roles (user_id, role_id, assigned_by, expires_at)
      VALUES ($1, $2, $3, $4)
      ON CONFLICT (user_id, role_id) 
      DO UPDATE SET 
        assigned_by = $3,
        expires_at = $4,
        assigned_at = NOW()
    `;

    await this.db.query(query, [userId, roleId, assignedBy, expiresAt]);
    
    // Invalidate cache
    await this.cache.del(`user_permissions:${userId}`);
  }

  async removeRoleFromUser(userId: string, roleId: string): Promise<void> {
    const query = `
      DELETE FROM auth.user_roles 
      WHERE user_id = $1 AND role_id = $2
    `;

    await this.db.query(query, [userId, roleId]);
    
    // Invalidate cache
    await this.cache.del(`user_permissions:${userId}`);
  }

  async createRole(
    organizationId: string,
    name: string,
    description: string,
    permissionIds: string[],
    createdBy: string
  ): Promise<Role> {
    return this.db.transaction(async (client) => {
      // Create role
      const roleQuery = `
        INSERT INTO auth.roles (organization_id, name, description)
        VALUES ($1, $2, $3)
        RETURNING id, organization_id, name, description, is_system_role, created_at
      `;

      const roleResult = await client.query(roleQuery, [organizationId, name, description]);
      const role = roleResult.rows[0];

      // Assign permissions to role
      if (permissionIds.length > 0) {
        const permissionValues = permissionIds.map((permId, index) => 
          `($1, $${index + 2}, $${permissionIds.length + 2})`
        ).join(', ');

        const permissionQuery = `
          INSERT INTO auth.role_permissions (role_id, permission_id, granted_by)
          VALUES ${permissionValues}
        `;

        await client.query(permissionQuery, [role.id, ...permissionIds, createdBy]);
      }

      // Get role with permissions
      return this.getRoleById(role.id);
    });
  }

  async getRoleById(roleId: string): Promise<Role | null> {
    const query = `
      SELECT 
        r.id,
        r.organization_id,
        r.name,
        r.description,
        r.is_system_role,
        array_agg(
          json_build_object(
            'id', p.id,
            'name', p.name,
            'description', p.description,
            'category', p.category
          )
        ) FILTER (WHERE p.id IS NOT NULL) as permissions
      FROM auth.roles r
      LEFT JOIN auth.role_permissions rp ON r.id = rp.role_id
      LEFT JOIN auth.permissions p ON rp.permission_id = p.id
      WHERE r.id = $1
      GROUP BY r.id, r.organization_id, r.name, r.description, r.is_system_role
    `;

    const result = await this.db.query(query, [roleId]);
    
    if (result.rows.length === 0) {
      return null;
    }

    const row = result.rows[0];
    return {
      id: row.id,
      organizationId: row.organization_id,
      name: row.name,
      description: row.description,
      isSystemRole: row.is_system_role,
      permissions: row.permissions || []
    };
  }

  async getOrganizationRoles(organizationId: string): Promise<Role[]> {
    const query = `
      SELECT 
        r.id,
        r.organization_id,
        r.name,
        r.description,
        r.is_system_role,
        count(ur.user_id) as user_count,
        array_agg(
          json_build_object(
            'id', p.id,
            'name', p.name,
            'description', p.description,
            'category', p.category
          )
        ) FILTER (WHERE p.id IS NOT NULL) as permissions
      FROM auth.roles r
      LEFT JOIN auth.role_permissions rp ON r.id = rp.role_id
      LEFT JOIN auth.permissions p ON rp.permission_id = p.id
      LEFT JOIN auth.user_roles ur ON r.id = ur.role_id
      WHERE r.organization_id = $1
      GROUP BY r.id, r.organization_id, r.name, r.description, r.is_system_role
      ORDER BY r.name
    `;

    const result = await this.db.query(query, [organizationId]);
    
    return result.rows.map(row => ({
      id: row.id,
      organizationId: row.organization_id,
      name: row.name,
      description: row.description,
      isSystemRole: row.is_system_role,
      permissions: row.permissions || []
    }));
  }

  async getAllPermissions(): Promise<Permission[]> {
    const cacheKey = 'all_permissions';
    
    return this.cache.getOrSet(cacheKey, async () => {
      const query = `
        SELECT id, name, description, category
        FROM auth.permissions
        ORDER BY category, name
      `;

      const result = await this.db.query(query);
      return result.rows;
    }, 3600); // Cache for 1 hour
  }
}

13.6. Security Best Practices

13.6.1. Input Validation and Sanitization

// server/src/middleware/validation.ts
import { body, param, query, ValidationChain } from 'express-validator';
import { Request, Response, NextFunction } from 'express';
import { validationResult } from 'express-validator';

// GeoJSON geometry validation
export const validateGeometry: ValidationChain = body('geometry').custom((value) => {
  if (!value || typeof value !== 'object') {
    throw new Error('Geometry must be a valid GeoJSON geometry object');
  }

  const validTypes = ['Point', 'LineString', 'Polygon', 'MultiPoint', 'MultiLineString', 'MultiPolygon'];
  
  if (!validTypes.includes(value.type)) {
    throw new Error('Invalid geometry type');
  }

  if (!Array.isArray(value.coordinates)) {
    throw new Error('Geometry coordinates must be an array');
  }

  // Additional validation based on geometry type
  switch (value.type) {
    case 'Point':
      if (!Array.isArray(value.coordinates) || value.coordinates.length !== 2) {
        throw new Error('Point coordinates must be [longitude, latitude]');
      }
      break;
    case 'LineString':
      if (!Array.isArray(value.coordinates) || value.coordinates.length < 2) {
        throw new Error('LineString must have at least 2 coordinates');
      }
      break;
    case 'Polygon':
      if (!Array.isArray(value.coordinates) || value.coordinates.length === 0) {
        throw new Error('Polygon must have coordinate rings');
      }
      break;
  }

  return true;
});

// Bounding box validation
export const validateBbox: ValidationChain = query('bbox').optional().custom((value) => {
  if (typeof value !== 'string') {
    throw new Error('Bbox must be a string');
  }

  const coords = value.split(',').map(Number);
  
  if (coords.length !== 4) {
    throw new Error('Bbox must have 4 coordinates: minLng,minLat,maxLng,maxLat');
  }

  const [minLng, minLat, maxLng, maxLat] = coords;

  if (isNaN(minLng) || isNaN(minLat) || isNaN(maxLng) || isNaN(maxLat)) {
    throw new Error('All bbox coordinates must be valid numbers');
  }

  if (minLng >= maxLng || minLat >= maxLat) {
    throw new Error('Invalid bbox: min values must be less than max values');
  }

  if (minLng < -180 || maxLng > 180 || minLat < -90 || maxLat > 90) {
    throw new Error('Bbox coordinates must be within valid geographic bounds');
  }

  return true;
});

// Sanitize HTML content
export const sanitizeHtml = (allowedTags: string[] = []) => {
  return body('*').customSanitizer((value) => {
    if (typeof value === 'string') {
      // Remove script tags and dangerous attributes
      return value
        .replace(/<script\b[^<]*(?:(?!<\/script>)<[^<]*)*<\/script>/gi, '')
        .replace(/javascript:/gi, '')
        .replace(/on\w+="[^"]*"/gi, '')
        .replace(/on\w+='[^']*'/gi, '');
    }
    return value;
  });
};

// Rate limiting by user
export const createUserRateLimit = (windowMs: number, max: number) => {
  const userRequests = new Map<string, { count: number; resetTime: number }>();

  return (req: any, res: Response, next: NextFunction) => {
    const userId = req.user?.userId;
    
    if (!userId) {
      return next();
    }

    const now = Date.now();
    const resetTime = now + windowMs;
    
    const userRecord = userRequests.get(userId);
    
    if (!userRecord || now > userRecord.resetTime) {
      userRequests.set(userId, { count: 1, resetTime });
      return next();
    }

    if (userRecord.count >= max) {
      return res.status(429).json({
        error: 'Too many requests',
        retryAfter: Math.ceil((userRecord.resetTime - now) / 1000)
      });
    }

    userRecord.count++;
    next();
  };
};

// Validation error handler
export const handleValidationErrors = (
  req: Request,
  res: Response,
  next: NextFunction
) => {
  const errors = validationResult(req);
  
  if (!errors.isEmpty()) {
    return res.status(400).json({
      error: 'Validation failed',
      details: errors.array()
    });
  }
  
  next();
};

// File upload validation
export const validateFileUpload = (allowedTypes: string[], maxSize: number) => {
  return (req: Request, res: Response, next: NextFunction) => {
    if (!req.file) {
      return res.status(400).json({ error: 'No file provided' });
    }

    // Check file type
    if (!allowedTypes.includes(req.file.mimetype)) {
      return res.status(400).json({
        error: 'Invalid file type',
        allowed: allowedTypes
      });
    }

    // Check file size
    if (req.file.size > maxSize) {
      return res.status(400).json({
        error: 'File too large',
        maxSize: `${maxSize} bytes`
      });
    }

    next();
  };
};

13.6.2. Security Middleware

// server/src/middleware/security.ts
import helmet from 'helmet';
import { Request, Response, NextFunction } from 'express';
import { createHash } from 'crypto';

// Enhanced security headers
export const securityHeaders = helmet({
  contentSecurityPolicy: {
    directives: {
      defaultSrc: ["'self'"],
      styleSrc: ["'self'", "'unsafe-inline'", "https://unpkg.com"],
      scriptSrc: ["'self'", "https://unpkg.com"],
      imgSrc: ["'self'", "data:", "https:"],
      connectSrc: ["'self'", "wss:", "https:"],
      fontSrc: ["'self'", "https:"],
      objectSrc: ["'none'"],
      mediaSrc: ["'self'"],
      frameSrc: ["'none'"],
    },
  },
  crossOriginEmbedderPolicy: false, // Allow embedding for maps
  hsts: {
    maxAge: 31536000,
    includeSubDomains: true,
    preload: true
  }
});

// Request fingerprinting for abuse detection
export const fingerprinting = (req: Request, res: Response, next: NextFunction) => {
  const fingerprint = createHash('sha256')
    .update(req.ip + req.get('User-Agent') + req.get('Accept-Language'))
    .digest('hex');
  
  (req as any).fingerprint = fingerprint;
  next();
};

// Audit logging middleware
export const auditLog = (eventType: string) => {
  return async (req: any, res: Response, next: NextFunction) => {
    const startTime = Date.now();
    
    // Store original json method
    const originalJson = res.json;
    let responseData: any;
    
    res.json = function(data: any) {
      responseData = data;
      return originalJson.call(this, data);
    };

    res.on('finish', async () => {
      try {
        const duration = Date.now() - startTime;
        const success = res.statusCode < 400;
        
        const logEntry = {
          eventType,
          userId: req.user?.userId,
          organizationId: req.user?.organizationId,
          resourceType: req.baseUrl.split('/').pop(),
          resourceId: req.params.id,
          method: req.method,
          path: req.path,
          query: req.query,
          ip: req.ip,
          userAgent: req.get('User-Agent'),
          fingerprint: req.fingerprint,
          statusCode: res.statusCode,
          duration,
          success,
          timestamp: new Date().toISOString()
        };

        // Log to database (implement as needed)
        await logAuditEvent(logEntry);
      } catch (error) {
        console.error('Audit logging error:', error);
      }
    });

    next();
  };
};

async function logAuditEvent(logEntry: any): Promise<void> {
  // Implement database logging
  const db = new (require('../services/databaseService')).DatabaseService();
  
  const query = `
    INSERT INTO auth.audit_log (
      event_type, user_id, organization_id, resource_type, resource_id,
      ip_address, user_agent, details, success, created_at
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
  `;

  const params = [
    logEntry.eventType,
    logEntry.userId,
    logEntry.organizationId,
    logEntry.resourceType,
    logEntry.resourceId,
    logEntry.ip,
    logEntry.userAgent,
    JSON.stringify({
      method: logEntry.method,
      path: logEntry.path,
      query: logEntry.query,
      statusCode: logEntry.statusCode,
      duration: logEntry.duration,
      fingerprint: logEntry.fingerprint
    }),
    logEntry.success,
    logEntry.timestamp
  ];

  await db.query(query, params);
}

// IP-based rate limiting with whitelist
export const ipRateLimit = (
  windowMs: number,
  max: number,
  whitelist: string[] = []
) => {
  const requests = new Map<string, { count: number; resetTime: number }>();

  return (req: Request, res: Response, next: NextFunction) => {
    const ip = req.ip;
    
    // Skip rate limiting for whitelisted IPs
    if (whitelist.includes(ip)) {
      return next();
    }

    const now = Date.now();
    const resetTime = now + windowMs;
    
    const record = requests.get(ip);
    
    if (!record || now > record.resetTime) {
      requests.set(ip, { count: 1, resetTime });
      return next();
    }

    if (record.count >= max) {
      return res.status(429).json({
        error: 'Too many requests from this IP',
        retryAfter: Math.ceil((record.resetTime - now) / 1000)
      });
    }

    record.count++;
    next();
  };
};

13.7. Summary

Authentication and authorization form the critical security foundation of Web GIS applications. The unique requirements of geospatial applications—including location data sensitivity, geographic access controls, and multi-tenant isolation—require specialized security implementations beyond traditional web applications.

Key implementation elements include JWT-based authentication with geospatial context, OAuth integration for enterprise identity providers, comprehensive role-based access control with geographic boundaries, and robust audit logging for compliance requirements. Security best practices encompass input validation, rate limiting, proper error handling, and comprehensive monitoring.

The authentication system provides the foundation for the advanced deployment and operational topics covered in the final part of this book. Understanding these security patterns is essential for building production-ready Web GIS applications that protect sensitive location data while enabling collaborative workflows.

13.8. Exercises

13.8.1. Exercise 13.1: Complete Authentication System

Objective: Implement a full-featured authentication system with JWT tokens, refresh mechanisms, and session management.

Instructions:

1. Build core authentication service:

   • User registration with email verification

   • Password-based login with bcrypt hashing

   • JWT token generation with proper expiration

   • Refresh token mechanism with rotation

2. Add security features:

   • Password complexity requirements

   • Account lockout after failed attempts

   • Rate limiting for authentication endpoints

   • Audit logging for security events

3. Implement session management:

   • Redis-based session storage

   • Session invalidation and cleanup

   • Concurrent session limits

   • Device tracking and management

Deliverable: A complete authentication system with comprehensive security features and audit capabilities.

13.8.2. Exercise 13.2: OAuth Integration

Objective: Implement OAuth 2.0 integration with multiple identity providers for enterprise single sign-on.

Instructions:

1. Multi-provider OAuth support:

   • Google OAuth integration

   • Microsoft Azure AD integration

   • GitHub OAuth integration

   • Generic OIDC provider support

2. Account linking and management:

   • Link multiple OAuth providers to single account

   • Account creation from OAuth profiles

   • Profile synchronization and updates

   • OAuth token refresh and management

3. Enterprise features:

   • Domain-based organization assignment

   • SAML integration for enterprise SSO

   • JIT (Just-In-Time) user provisioning

   • Group and role mapping from identity providers

Deliverable: A comprehensive OAuth integration supporting multiple enterprise identity providers.

13.8.3. Exercise 13.3: Role-Based Access Control System

Objective: Implement a sophisticated RBAC system with geographic access controls and dynamic permissions.

Instructions:

1. Core RBAC implementation:

   • Hierarchical role system with inheritance

   • Fine-grained permission management

   • Dynamic permission evaluation

   • Role assignment with expiration

2. Geographic access controls:

   • Geographic extent-based permissions

   • Location-based role activation

   • Spatial query authorization

   • Administrative boundary enforcement

3. Advanced permission features:

   • Conditional permissions based on context

   • Time-based access controls

   • Resource-specific permissions

   • Permission delegation and approval workflows

Deliverable: A complete RBAC system with geographic access controls and advanced permission management.

13.8.4. Exercise 13.4: API Security Implementation

Objective: Secure Web GIS APIs with comprehensive protection against common attack vectors.

Instructions:

1. Input validation and sanitization:

   • GeoJSON validation and sanitization

   • SQL injection prevention

   • XSS protection for user content

   • File upload security controls

2. Rate limiting and abuse prevention:

   • User-based and IP-based rate limiting

   • API key management and quotas

   • DDoS protection strategies

   • Abuse detection and blocking

3. Security monitoring and alerting:

   • Failed authentication monitoring

   • Suspicious activity detection

   • Security event alerting

   • Automated threat response

Deliverable: A comprehensive API security implementation with monitoring and threat detection capabilities.

13.8.5. Exercise 13.5: Multi-tenant Security Architecture

Objective: Design and implement a secure multi-tenant architecture with complete data isolation.

Instructions:

1. Tenant isolation design:

   • Database-level tenant separation

   • Application-level access controls

   • API endpoint isolation

   • Resource quota management

2. Tenant administration:

   • Tenant onboarding and configuration

   • Cross-tenant user management

   • Tenant-specific customizations

   • Billing and usage tracking

3. Security compliance features:

   • Data residency controls

   • Encryption key management per tenant

   • Compliance reporting and auditing

   • Data export and deletion capabilities

Deliverable: A complete multi-tenant security architecture with proper isolation and compliance features.

13.8.6. Exercise 13.6: Security Monitoring and Incident Response

Objective: Implement comprehensive security monitoring with automated incident response capabilities.

Instructions:

1. Security monitoring infrastructure:

   • Real-time security event collection

   • Anomaly detection algorithms

   • Threat intelligence integration

   • Security metrics and dashboards

2. Incident response automation:

   • Automated threat detection and blocking

   • Security incident workflow management

   • Forensic data collection and preservation

   • Communication and notification systems

3. Compliance and reporting:

   • Regulatory compliance monitoring

   • Security audit trail management

   • Compliance reporting automation

   • Security posture assessment

Deliverable: A comprehensive security monitoring and incident response system with automated threat detection.

13.8.7. Exercise 13.7: Advanced Authentication Features

Objective: Implement advanced authentication features for high-security environments.

Instructions:

1. Multi-factor authentication:

   • TOTP (Time-based One-Time Password) support

   • SMS and email-based MFA

   • Hardware token integration

   • Biometric authentication support

2. Advanced security features:

   • Certificate-based authentication

   • Risk-based authentication

   • Adaptive authentication based on context

   • Zero-trust security principles

3. Recovery and backup authentication:

   • Account recovery workflows

   • Backup authentication methods

   • Emergency access procedures

   • Security key management

Deliverable: An advanced authentication system with MFA and enterprise-grade security features.

Reflection Questions:

• How do geospatial applications require different security considerations than traditional web applications?

• What are the trade-offs between security and usability in Web GIS authentication systems?

• How can geographic access controls be implemented without impacting application performance?

• What are the key compliance requirements for applications handling location data?

13.9. Further Reading

• OWASP Web Application Security Guide

• JWT Best Practices

• OAuth 2.0 Security Best Practices

• NIST Cybersecurity Framework