OAuth2 PostgreSQL Storage Adapter

A TypeORM-based PostgreSQL storage adapter for @dainprotocol/oauth2-token-manager.

Installation

npm install @dainprotocol/oauth2-storage-postgres

Quick Start

import { OAuth2Client } from '@dainprotocol/oauth2-token-manager';
import { PostgresStorageFactory } from '@dainprotocol/oauth2-storage-postgres';

// Create storage adapter
const storage = await PostgresStorageFactory.create({
  host: 'localhost',
  port: 5432,
  username: 'oauth2_user',
  password: 'secure_password',
  database: 'oauth2_db',
  ssl: process.env.NODE_ENV === 'production',
});

// Use with OAuth2Client
const oauth = new OAuth2Client({ storage });

Configuration

Basic Configuration

const storage = await PostgresStorageFactory.create({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: {
    rejectUnauthorized: false, // For self-signed certificates
  },
  synchronize: false, // Don't auto-sync in production
  logging: process.env.NODE_ENV === 'development',
  poolSize: 20,
});

Connection Pooling

// Create named connections for multi-tenant scenarios
const tenantStorage = await PostgresStorageFactory.getOrCreate('tenant-123', tenantDbConfig);

// Close when done
await PostgresStorageFactory.close('tenant-123');

// Or close all connections
await PostgresStorageFactory.closeAll();

Database Migrations

Generate Migration

npm run typeorm migration:generate -- -n YourMigrationName

Run Migrations

npm run typeorm migration:run

Revert Migration

npm run typeorm migration:revert

Database Schema

The adapter creates the following tables:

• oauth2_systems - OAuth2 systems/applications
• oauth2_scopes - Permission scopes
• oauth2_users - Users within systems
• oauth2_user_tokens - Stored OAuth2 tokens
• oauth2_authorization_states - Temporary authorization states

Advanced Usage

Custom Profile Fetchers

The PostgreSQL adapter supports registering custom profile fetchers for OAuth providers:

import { BaseProfileFetcher, UserProfile } from '@dainprotocol/oauth2-token-manager';
import { PostgresStorageFactory } from '@dainprotocol/oauth2-storage-postgres';

// Create a custom profile fetcher
class EnterpriseProfileFetcher extends BaseProfileFetcher {
  constructor() {
    super('https://enterprise.example.com/api/v2/userinfo');
  }

  protected mapToUserProfile(rawData: any): UserProfile {
    return {
      email: rawData.corporate_email,
      name: rawData.display_name,
      id: rawData.employee_id,
      avatar: rawData.photo_url,
      username: rawData.username,
      raw: rawData,
    };
  }
}

// Create storage with custom fetchers
const storage = await PostgresStorageFactory.create({
  host: 'localhost',
  port: 5432,
  username: 'oauth2_user',
  password: 'secure_password',
  database: 'oauth2_db',
});

// Register custom profile fetchers
storage.registerProfileFetcher('enterprise', new EnterpriseProfileFetcher());
storage.registerProfileFetcher('github', new CustomGitHubFetcher()); // Override default

const oauth = new OAuth2Client({ storage });

Custom Connection Options

import { DataSource } from 'typeorm';
import { PostgresStorageAdapter } from '@your-org/oauth2-storage-postgres';

const dataSource = new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  entities: ['dist/entities/*.js'],
  migrations: ['dist/migrations/*.js'],
  extra: {
    max: 30, // connection pool size
    idleTimeoutMillis: 30000,
  },
});

await dataSource.initialize();
const storage = new PostgresStorageAdapter(dataSource);

Transaction Support

await dataSource.transaction(async (manager) => {
  const storage = new PostgresStorageAdapter(manager);

  // All operations within transaction
  const system = await storage.createSystem({ name: 'System' });
  const user = await storage.createUser({ systemId: system.id });
  // If any operation fails, all are rolled back
});

Performance Optimization

Indexes

The adapter includes indexes for common queries:

• System name lookups
• User metadata searches (JSONB GIN index)
• Token lookups by user/system/scope/provider
• Authorization state cleanup by timestamp

Query Optimization

// Use pagination for large datasets
const systems = await dataSource.getRepository(SystemEntity).find({
  skip: 0,
  take: 100,
  order: { createdAt: 'DESC' },
});

Testing

# Run tests
npm test

# Run tests with coverage
npm run test:coverage

# Run tests in watch mode
npm run test:watch

Token Encryption

The adapter supports AES-256-CBC encryption for tokens at rest:

const storage = await PostgresStorageFactory.create({
  host: 'localhost',
  port: 5432,
  username: 'oauth2_user',
  password: 'secure_password',
  database: 'oauth2_db',
  encryption: {
    encryptionKey: process.env.TOKEN_ENCRYPTION_KEY, // Min 32 characters
  },
});

Generate a secure key:

openssl rand -base64 32

What Gets Encrypted

| Field | Encrypted | | ----------------------------- | --------- | | accessToken | Yes | | refreshToken | Yes | | provider, email, userId | No |

This implementation meets Google CASA (Cloud Application Security Assessment) requirements.

Security Best Practices

1. Enable token encryption - Use the encryption option with a strong key
2. Use SSL/TLS in production
3. Store encryption keys securely - Use environment variables or a secrets manager
4. Use read replicas for read-heavy operations
5. Regular backups of OAuth2 data
6. Monitor slow queries and optimize as needed

License

MIT