PostgreSQL Web Authentication

BoilStream provides a built-in web-based authentication system specifically for PostgreSQL access. This system is separate from FlightRPC JWT authentication and enables self-service user onboarding with OAuth providers or email/password.

Overview

The PostgreSQL authentication system provides:

• Web UI for user login and session management (https://your-domain/auth)
• OAuth Integration with GitHub, Google, and SAML providers
• Email/Password authentication with SCRAM-SHA-256 hashing
• Multi-Factor Authentication (TOTP, passkeys, backup codes)
• Role-Based Access Control via GitHub team mappings
• Temporary Credentials for PostgreSQL connections (configurable TTL)
• Encrypted Storage for all user data and credentials

Two Authentication Systems

Important Distinction

BoilStream has two separate authentication systems:

System	Interface	Method	Use Case
FlightRPC Auth	DuckDB Airport, Arrow Flight	JWT tokens from enterprise SSO	Data ingestion, programmatic access
PostgreSQL Web Auth	PostgreSQL protocol, BI tools	OAuth web UI + email/password	SQL queries, BI tools, data exploration

These systems are independently configured and can be enabled separately.

Architecture

Database Separation

PostgreSQL connections route to different databases based on username:

• Superadmin (boilstream) → Auth database with users.duckdb attached
  • Full access to user management
  • Direct SQL access to users table
• Regular Users → Global database (isolated from auth data)
  • Query topics and views only
  • No access to user database

Initial Setup

First Startup

When you first enable the auth server, BoilStream prompts for two critical secrets:

1. Encryption Key Setup

The encryption key secures the users.duckdb and superadmin.duckdb databases.

Three methods to provide the key:

Method A: Save to file (recommended for production)

# config.yaml
auth_server:
  enabled: true
  encryption_key_path: "encryption.key"

On first run:

./boilstream
# Prompt: "Enter encryption key (press Enter to generate random): "
# Press Enter to generate
# Key saved to encryption.key
# Future starts: automatic (no prompt)

Method B: Manual entry every startup (maximum security)

# config.yaml
auth_server:
  enabled: true
  # No encryption_key_path specified

On every run:

./boilstream
# Prompt: "Enter encryption key: "
# Must enter key manually each time
# Key never saved to disk

Method C: Pipe from secrets manager (automated deployments)

# Kubernetes secret
kubectl get secret boilstream-key -o jsonpath='{.data.key}' | base64 -d | ./boilstream

# AWS Secrets Manager
aws secretsmanager get-secret-value --secret-id boilstream-key --query SecretString --output text | ./boilstream

# HashiCorp Vault
vault kv get -field=key secret/boilstream | ./boilstream

Critical Warning

If you lose the encryption key, all user data is permanently lost. There is no recovery mechanism.

Store the encryption key securely:

• Use a secrets manager (AWS Secrets Manager, HashiCorp Vault)
• Backup to multiple secure locations
• Document key location in runbooks

2. Superadmin Password Setup

After the encryption key, BoilStream prompts for the superadmin password:

# First run after encryption key setup
./boilstream
# Prompt: "Set superadmin password (min 12 characters): "
# Enter password (not echoed)
# Prompt: "Confirm password: "
# Password stored encrypted in superadmin.duckdb

The superadmin account:

• Username: boilstream (hardcoded)
• Purpose: Database administration, user management
• Access: Auth database with full privileges
• Connection: psql -h localhost -p 5432 -U boilstream

Password Recovery

There is no password reset mechanism for the superadmin account. If forgotten:

1. Stop the server
2. Delete data/superadmin.duckdb
3. Restart and set a new password
4. Note: User database remains intact

Automated Startup Requirements

For production deployments without manual intervention:

# config.yaml
auth_server:
  enabled: true
  encryption_key_path: "/etc/boilstream/encryption.key"

Prerequisites:

1. Encryption key file must exist at specified path
2. Superadmin password already set (from first run)
3. File permissions: chmod 600 /etc/boilstream/encryption.key

Server will start automatically on subsequent runs.

User Access Flow

1. Access Web UI

Navigate to the authentication portal:

https://your-domain/auth

For local development (default):

https://localhost/auth

2. Login Options

The web UI provides multiple authentication methods:

GitHub OAuth

• Click "Sign in with GitHub"
• Authorize BoilStream application
• Automatic role assignment via GitHub teams (if configured)

Google OAuth

• Click "Sign in with Google"
• Select Google account
• Domain restrictions apply if configured

Email/Password

1. Click "Sign Up" tab
2. Enter email and password (min 12 characters)
3. Receive verification email
4. Click verification link
5. Login with credentials

3. Multi-Factor Authentication (Optional)

After login, users can enable MFA for enhanced security:

TOTP (Authenticator Apps)

1. Navigate to dashboard → Security Settings
2. Click "Enable TOTP"
3. Scan QR code with authenticator app (Google Authenticator, Authy, 1Password)
4. Enter verification code
5. Save backup codes

Passkeys (WebAuthn)

1. Navigate to dashboard → Security Settings
2. Click "Add Passkey"
3. Follow browser prompts (Face ID, Touch ID, Windows Hello, YubiKey)
4. Name the passkey
5. Future logins: Biometric authentication

Backup Codes

• Automatically generated when enabling MFA
• 10 one-time-use codes
• Store securely (password manager, encrypted file)
• Use if authenticator unavailable

4. Get PostgreSQL Credentials

After successful login:

1. Dashboard displays PostgreSQL connection details:

   Host: localhost
   Port: 5432
   Database: boilstream
   Username: your.email@company.com
   Password: [Auto-generated session password]

2. Credentials are temporary (default: 8 hours TTL)

3. Password rotates on each login session

5. Connect with PostgreSQL Clients

psql (command-line)

psql -h localhost -p 5432 -U your.email@company.com -d boilstream
# Enter password from dashboard

DBeaver

1. New Database Connection → PostgreSQL
2. Host: localhost, Port: 5432
3. Database: boilstream
4. Username: your.email@company.com
5. Password: [from dashboard]

Power BI

1. Get Data → PostgreSQL database
2. Server: localhost:5432
3. Database: boilstream
4. Username/password authentication
5. Credentials: From BoilStream dashboard

Tableau

1. Connect → PostgreSQL
2. Server: localhost, Port: 5432
3. Database: boilstream
4. Authentication: Username and Password
5. Credentials: From BoilStream dashboard

GitHub RBAC Configuration

Map GitHub organization teams to database roles for fine-grained access control.

Role Types

Role	Permissions
admin	Full access: Create/alter/drop topics, manage views, query all data
write	Create topics, insert data, query all data
read	Query data only (no DDL operations)

Configuration Example

# config.yaml
oauth_providers:
  github:
    client_id: "your-github-app-client-id"
    client_secret: "your-github-app-client-secret"
    redirect_uri: "https://your-domain/auth/callback"

    # Restrict access to specific organizations
    allowed_orgs:
      - "your-company"
      - "partner-org"

    # Map GitHub teams to database roles
    team_role_mappings:
      "your-company/platform-admins": "admin"
      "your-company/data-engineers": "write"
      "your-company/analysts": "read"
      "partner-org/integration-team": "write"

    # Audit team membership even without RBAC
    audit_org_teams: true

GitHub App Setup

1. Create GitHub OAuth App

   • Go to GitHub Settings → Developer settings → OAuth Apps
   • Click "New OAuth App"
   • Application name: BoilStream Production
   • Homepage URL: https://your-domain
   • Authorization callback URL: https://your-domain/auth/callback
   • Click "Register application"

2. Get Credentials

   • Copy Client ID
   • Generate new client secret
   • Add to config.yaml

3. Configure Team Access

   • Ensure teams exist in GitHub organization
   • Team slugs are lowercase with hyphens (e.g., "Platform Admins" → "platform-admins")
   • Users must be team members before login

Default Behavior

If no team mappings configured:

• All users from allowed_orgs get read role by default
• To block access: Don't include org in allowed_orgs

Google OAuth Configuration

Restrict access by email domain and configure OAuth credentials.

Configuration Example

# config.yaml
oauth_providers:
  google:
    client_id: "your-google-client-id.apps.googleusercontent.com"
    client_secret: "your-google-client-secret"
    redirect_uri: "https://your-domain/auth/callback"

    # Restrict to specific email domains
    allowed_domains:
      - "yourcompany.com"
      - "partner.com"
    # Empty array = all domains allowed

Google Cloud Console Setup

1. Create OAuth 2.0 Credentials

   • Go to Google Cloud Console → APIs & Services → Credentials
   • Click "Create Credentials" → OAuth client ID
   • Application type: Web application
   • Name: BoilStream Production
   • Authorized redirect URIs: https://your-domain/auth/callback
   • Click "Create"

2. Get Credentials

   • Copy Client ID
   • Copy Client Secret
   • Add to config.yaml

Superadmin Access

The superadmin account provides direct database management capabilities.

Connect as Superadmin

psql -h localhost -p 5432 -U boilstream -d boilstream
# Enter superadmin password (set during first run)

Superadmin Capabilities

View All Users

SELECT user_id, email, display_name, auth_method, verified, active
FROM users.users
ORDER BY created_at DESC;

Check User Sessions

SELECT session_id, user_id, email, provider, created_at, expires_at, is_active
FROM users.sessions
WHERE is_active = true
ORDER BY created_at DESC;

Revoke User Sessions

UPDATE users.sessions
SET is_active = false
WHERE user_id = 'user-uuid-here';

View Login Attempts

SELECT email, success, ip_address, user_agent, attempt_time
FROM users.login_attempts
WHERE attempt_time > NOW() - INTERVAL '24 hours'
ORDER BY attempt_time DESC;

Deactivate User

UPDATE users.users
SET active = false
WHERE email = 'user@example.com';

Use Superadmin Sparingly

Superadmin has unrestricted access to user data. Use only for:

• User management tasks
• Security investigations
• Database maintenance
• Emergency access recovery

For regular queries, use a normal user account.

Security Features

Encrypted Storage

All authentication data is encrypted at rest:

Encrypted Databases:

• users.duckdb - User profiles, OAuth links, sessions, MFA settings
• superadmin.duckdb - Superadmin credentials only

Encryption Details:

• Algorithm: AES-256-GCM (DuckDB native)
• Key derivation: HMAC-SHA256 with domain separation
• Master key: 32 bytes (256 bits)

Session Management

Session Lifecycle:

1. Created on successful login
2. TTL: 8 hours (configurable via session_ttl_hours)
3. Automatic cleanup of expired sessions
4. Manual revocation available (superadmin or user logout)

Session Security:

• Secure random 64-character session tokens
• HttpOnly cookies (web UI)
• Session fixation protection
• Automatic expiration enforcement

Connection Termination: When a session is revoked (logout, password change):

• Web session invalidated immediately
• Active PostgreSQL connections terminated
• User must re-authenticate

Password Security

Email/Password Authentication:

• Minimum length: 12 characters (configurable in UI)
• Hashing: SCRAM-SHA-256 (PostgreSQL-compatible)
• Salt: Random per-user
• Iterations: 4096 (PBKDF2)

Password Storage:

-- Never stored in plaintext
SELECT email, password_data
FROM users.users
WHERE auth_method = 'EmailPassword';

-- Returns JSON with salt, stored_key, server_key

MFA Security

TOTP (Time-based One-Time Password):

• Algorithm: SHA-1 (RFC 6238)
• Time step: 30 seconds
• Code length: 6 digits
• Secret: 32-byte base32-encoded, encrypted at rest

Passkeys (WebAuthn):

• Algorithm: ES256 or RS256
• Challenge: 32-byte random
• User verification: Required
• Credential storage: Encrypted in database

Backup Codes:

• Count: 10 codes per user
• Format: 8-character alphanumeric
• Storage: Hashed (not plaintext)
• One-time use (invalidated after use)

Configuration Reference

auth_server Section

auth_server:
  enabled: true # Enable PostgreSQL web auth
  port: 443 # HTTPS port for web UI
  static_dir: "static/auth" # Web UI files directory
  session_ttl_hours: 8 # Session lifetime
  users_db_path: "data/users.duckdb" # User database location
  encryption_key_path: "encryption.key" # Optional key file path

  # WebAuthn (Passkeys) Configuration
  webauthn_rp_id: "your-domain.com"
  webauthn_rp_origin: "https://your-domain.com"

  # CORS for web UI
  cors_allowed_origins:
    - "https://your-domain.com"
    - "https://dashboard.your-domain.com"

  # TLS Configuration (optional, falls back to http_ingestion certs)
  tls_cert: "/path/to/cert.pem"
  tls_key: "/path/to/key.pem"

  # Disaster Recovery Backups
  users_backup_backend: "primary-s3" # Storage backend name
  users_backup_interval_seconds: 60 # Backup frequency
  users_backup_path: "auth/users.duckdb" # Backup file path in storage

oauth_providers Section

oauth_providers:
  github:
    client_id: "${GITHUB_CLIENT_ID}"
    client_secret: "${GITHUB_CLIENT_SECRET}"
    redirect_uri: "https://your-domain.com/auth/callback"
    allowed_orgs: ["your-org"]
    team_role_mappings:
      "your-org/admins": "admin"
      "your-org/developers": "write"
      "your-org/analysts": "read"
    audit_org_teams: false

  google:
    client_id: "${GOOGLE_CLIENT_ID}"
    client_secret: "${GOOGLE_CLIENT_SECRET}"
    redirect_uri: "https://your-domain.com/auth/callback"
    allowed_domains: ["yourcompany.com"]

  saml:
    - name: "aws-sso"
      enabled: true
      sp_entity_id: "https://your-domain.com"
      sp_acs_url: "https://your-domain.com/auth/saml/acs"
      sp_slo_url: "https://your-domain.com/auth/saml/logout"
      idp_entity_id: "arn:aws:siam::123456789012:saml-provider/YourProvider"
      idp_sso_url: "https://portal.sso.region.amazonaws.com/saml/assertion/..."
      idp_certificate: |
        -----BEGIN CERTIFICATE-----
        ...
        -----END CERTIFICATE-----
      sp_certificate: "/path/to/sp-cert.pem"
      sp_private_key: "/path/to/sp-key.pem"

Troubleshooting

"Encryption key required" on startup

Problem: Server prompts for encryption key every time

Solution:

1. Set encryption_key_path in config
2. Ensure key file exists and is readable
3. Check file permissions: chmod 600 encryption.key

"Superadmin password not configured"

Problem: Server prompts for superadmin password on startup

Solution: Normal on first run. Enter password when prompted. Subsequent runs will not prompt.

Cannot login to web UI

Problem: 404 or connection refused at https://localhost/auth

Solution:

1. Check auth_server.enabled: true in config
2. Verify port is accessible: curl -k https://localhost
3. Check logs for TLS certificate errors
4. Ensure static_dir path exists: ls static/auth/index.html

GitHub OAuth fails with "Redirect URI mismatch"

Problem: Error after clicking "Sign in with GitHub"

Solution:

1. Verify redirect_uri in config matches GitHub OAuth app settings
2. Check for trailing slashes (must match exactly)
3. Ensure protocol matches (http vs https)

"User not authorized" after GitHub login

Problem: User can authenticate but gets authorization error

Solution:

1. Check user is member of org in allowed_orgs
2. Verify team membership for team_role_mappings
3. Check team slug format (lowercase, hyphens)
4. Review logs: grep "GitHub authorization" logs/boilstream.log

PostgreSQL connection: "Invalid username or password"

Problem: Cannot connect with credentials from dashboard

Solution:

1. Verify session hasn't expired (check dashboard for expiration time)
2. Re-login to get fresh credentials
3. Check username is exact email from dashboard (case-sensitive)
4. Ensure PostgreSQL interface is enabled: pgwire.enabled: true

Session expired too quickly

Problem: Sessions expire before 8 hours

Solution:

1. Check session_ttl_hours in config
2. Verify server time is correct: date
3. Check for manual session revocation in superadmin queries
4. Review session cleanup logs

See Also

• FlightRPC JWT Authentication - For DuckDB Airport and programmatic access
• Configuration Reference - Complete config.yaml documentation
• PostgreSQL Interface - PostgreSQL protocol details
• Environment Variables - Alternative configuration method