@blureffect/oauth2-storage-postgres

OAuth2 PostgreSQL Storage Adapter

A TypeORM-based PostgreSQL storage adapter for the OAuth2 Manager library.

Installation

npm install @your-org/oauth2-storage-postgres

Quick Start

import { OAuth2Client } from '@your-org/oauth2-manager';
import { PostgresStorageFactory } from '@your-org/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 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

Security Best Practices

1. Use SSL/TLS in production
2. Encrypt sensitive data in JSONB columns
3. Use read replicas for read-heavy operations
4. Regular backups of OAuth2 data
5. Monitor slow queries and optimize as needed

License

MIT