Secrets Management

ERPL-Web integrates with DuckDB's secrets management system to keep your credentials secure. Never hardcode passwords or API tokens again.

Overview

DuckDB secrets provide a secure way to store and manage authentication credentials. ERPL-Web extends this with custom secret types and providers for SAP and cloud services.

What you get:

✅ Encrypted storage of credentials
✅ OAuth2 flows with automatic token refresh
✅ Multiple provider types (oauth2, config, file)
✅ Secret scoping and isolation
✅ No plaintext passwords in SQL
✅ Integration with all ERPL-Web functions

Quick Start

-- Create a simple HTTP basic auth secret
CREATE SECRET my_api (
  TYPE http_basic,
  username 'api_user',
  password 'api_password'
);

-- Use it automatically in requests
SELECT * FROM http_get('https://api.example.com/data');

-- Or specify explicitly
SELECT * FROM http_get(
  'https://api.example.com/data',
  secret := 'my_api'
);

Secret Types

http_basic

For HTTP Basic Authentication (username/password).

Usage:

CREATE SECRET sap_system (
  TYPE http_basic,
  username 'SAP_USER',
  password 'SAP_PASSWORD'
);

-- Used in ODP, OData, HTTP functions
SELECT * FROM odp_odata_read('https://sap/odata/...', secret='sap_system');

http_bearer

For HTTP Bearer Token Authentication (API tokens).

Usage:

CREATE SECRET api_token (
  TYPE http_bearer,
  token 'your-bearer-token-here'
);

-- Used in HTTP requests
SELECT * FROM http_get('https://api.example.com/data');

datasphere

For SAP Datasphere OAuth2 authentication (custom type).

Usage:

CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'your-tenant',
  DATA_CENTER 'eu10',
  SCOPE 'default'
);

-- Used in Datasphere functions
SELECT * FROM datasphere_show_spaces();

Secret Providers

Providers control how secrets are created and managed.

oauth2 Provider

Interactive OAuth2 flows for cloud services.

Features:

Opens browser for authentication
Stores access and refresh tokens
Automatic token refresh
Supports authorization_code and client_credentials flows

Authorization Code Flow (Interactive):

CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-corp',
  DATA_CENTER 'eu10',
  SCOPE 'default',
  REDIRECT_URI 'http://localhost:65000'  -- Optional, default shown
);

What happens:

Local server starts on port 65000
Browser opens to OAuth2 provider
You log in with your credentials
Authorization code is captured
Tokens are exchanged and stored
Secret is ready to use

Client Credentials Flow (Service Account):

CREATE SECRET datasphere_svc (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-corp',
  DATA_CENTER 'eu10',
  CLIENT_ID 'service-account-id',
  CLIENT_SECRET 'service-account-secret',
  GRANT_TYPE 'client_credentials',
  SCOPE 'default'
);

No browser needed - uses service account credentials directly.

config Provider

Load secrets from INI-style configuration files.

Config File Format:

# ~/.erpl/sap-prod.conf
[sap_system]
username=SAP_USER
password=SAP_PASSWORD

[datasphere]
tenant_name=acme-corp
data_center=eu10
client_id=abc123
client_secret=secret456

Create Secret from Config:

CREATE SECRET sap_prod (
  TYPE http_basic,
  PROVIDER config,
  CONFIG_FILE '/home/user/.erpl/sap-prod.conf',
  SECTION 'sap_system'  -- Optional, defaults to secret name
);

Benefits:

Separate credentials from code
Easy to manage multiple environments
Version control friendly (exclude config files)
Rotate credentials without changing SQL

file Provider

Load secrets from JSON or text files.

JSON File Format:

{
  "tenant_name": "acme-corp",
  "data_center": "eu10",
  "access_token": "eyJhbGc...",
  "refresh_token": "eyJhbGc...",
  "expires_at": "2024-01-15T15:30:00Z"
}

Create Secret from File:

CREATE SECRET datasphere_prod (
  TYPE datasphere,
  PROVIDER file,
  FILEPATH '/secure/credentials/datasphere-prod.json'
);

Use Cases:

Store pre-acquired tokens
Integration with secret managers (Vault, AWS Secrets Manager)
Share credentials across team
Backup/restore secrets

Managing Secrets

List Secrets

-- Show all secrets
SELECT * FROM duckdb_secrets();

Output:

┌─────────────┬──────────────┬───────────┬─────────┐
│    name     │     type     │  provider │  scope  │
├─────────────┼──────────────┼───────────┼─────────┤
│ my_api      │ http_basic   │ inline    │ user    │
│ datasphere  │ datasphere   │ oauth2    │ user    │
└─────────────┴──────────────┴───────────┴─────────┘

Update a Secret

-- Drop and recreate
DROP SECRET my_api;

CREATE SECRET my_api (
  TYPE http_basic,
  username 'new_user',
  password 'new_password'
);

Remove a Secret

DROP SECRET my_api;

Temporary vs Persistent Secrets

-- Temporary (session only)
CREATE TEMPORARY SECRET temp_api (
  TYPE http_basic,
  username 'user',
  password 'pass'
);

-- Persistent (saved to database)
CREATE SECRET persistent_api (
  TYPE http_basic,
  username 'user',
  password 'pass'
);

OAuth2 Flows in Detail

Authorization Code Flow

Best for interactive use (desktop, notebooks).

CREATE SECRET interactive_ds (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'mytenant',
  DATA_CENTER 'eu10',
  SCOPE 'default'
);

Step-by-Step:

Secret creation starts OAuth2 flow
Local callback server starts (default: http://localhost:65000)
Browser opens to authorization URL
User authenticates with username/password
User grants permissions
Browser redirects to callback with authorization code
Extension exchanges code for tokens
Tokens stored in secret (encrypted)

Token Refresh

Tokens expire (typically after 1 hour). ERPL-Web handles refresh automatically:

-- First use after token expiry
SELECT * FROM datasphere_show_spaces();
-- Automatically refreshes token if expired

-- You never need to manually refresh!

Client Credentials Flow

Best for automation (scripts, services, CI/CD).

CREATE SECRET service_ds (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'mytenant',
  DATA_CENTER 'eu10',
  CLIENT_ID 'service-id',
  CLIENT_SECRET 'service-secret',
  GRANT_TYPE 'client_credentials',
  TOKEN_URL 'https://mytenant.authentication.eu10.hana.ondemand.com/oauth/token',  -- Optional
  SCOPE 'default'
);

No browser interaction - ideal for:

Scheduled jobs
Docker containers
CI/CD pipelines
Backend services

Security Best Practices

1. Never Commit Secrets

# .gitignore
*.conf
*-credentials.json
.env
secrets/

2. Use Environment Variables

-- Good: Load from environment
CREATE SECRET api_key (
  TYPE http_bearer,
  token getenv('API_TOKEN')
);

-- Bad: Hardcoded
-- CREATE SECRET api_key (TYPE http_bearer, token 'abc123');

3. Restrict File Permissions

# Only owner can read secret files
chmod 600 ~/.erpl/credentials.conf
chmod 600 /secure/datasphere.json

4. Use Different Secrets Per Environment

-- Development
CREATE SECRET datasphere_dev (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-dev',
  DATA_CENTER 'eu10'
);

-- Production
CREATE SECRET datasphere_prod (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'acme-prod',
  DATA_CENTER 'eu10'
);

-- Use explicitly
SELECT * FROM datasphere_show_spaces(secret := 'datasphere_prod');

5. Rotate Credentials Regularly

-- Update secret with new credentials
DROP SECRET old_api;
CREATE SECRET new_api (...);

-- Update all queries to use new secret

6. Use Temporary Secrets for Testing

-- Temporary secret (not saved to disk)
CREATE TEMPORARY SECRET test_api (
  TYPE http_basic,
  username 'test',
  password 'test123'
);

-- Automatically dropped at end of session

Advanced Patterns

Multiple Tenants

-- Create secrets for each tenant
CREATE SECRET tenant_a (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'tenant-a',
  DATA_CENTER 'eu10'
);

CREATE SECRET tenant_b (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'tenant-b',
  DATA_CENTER 'us10'
);

-- Query specific tenant
SELECT * FROM datasphere_show_spaces(secret := 'tenant_a');
SELECT * FROM datasphere_show_spaces(secret := 'tenant_b');

Secret Rotation Without Downtime

-- Create new secret with new credentials
CREATE SECRET api_v2 (
  TYPE http_basic,
  username 'new_user',
  password 'new_pass'
);

-- Test new secret
SELECT * FROM http_get('https://api.example.com/test', secret := 'api_v2');

-- If successful, make it the default
DROP SECRET api_v1;
ALTER SECRET api_v2 RENAME TO api_v1;  -- If supported

-- Or update queries to use api_v2

Config File with Multiple Environments

# config.conf
[dev]
tenant_name=dev-tenant
data_center=eu10
client_id=dev-client

[staging]
tenant_name=staging-tenant
data_center=eu10
client_id=staging-client

[prod]
tenant_name=prod-tenant
data_center=eu10
client_id=prod-client

-- Load different environments
CREATE SECRET datasphere_dev (
  TYPE datasphere,
  PROVIDER config,
  CONFIG_FILE 'config.conf',
  SECTION 'dev'
);

CREATE SECRET datasphere_prod (
  TYPE datasphere,
  PROVIDER config,
  CONFIG_FILE 'config.conf',
  SECTION 'prod'
);

Integration with External Secret Managers

#!/bin/bash
# fetch_secret.sh - Retrieve from AWS Secrets Manager

aws secretsmanager get-secret-value \
  --secret-id datasphere/prod \
  --query SecretString \
  --output text > /tmp/datasphere-creds.json

-- Use the fetched secret
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER file,
  FILEPATH '/tmp/datasphere-creds.json'
);

-- Clean up after use
!rm /tmp/datasphere-creds.json

Troubleshooting

Secret Not Found

Issue: Error: Secret 'xyz' not found

Solutions:

-- List all secrets
SELECT name FROM duckdb_secrets();

-- Create the missing secret
CREATE SECRET xyz (...);

OAuth2 Flow Fails

Issue: Browser doesn't open or redirect fails

Solutions:

-- Try different redirect port
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER oauth2,
  TENANT_NAME 'mytenant',
  DATA_CENTER 'eu10',
  REDIRECT_URI 'http://localhost:8080'  -- Try 8080, 3000, etc.
);

-- Check firewall settings
-- Ensure localhost can bind to the port

Token Expired

Issue: 401 Unauthorized after period of inactivity

Solution:

-- Tokens auto-refresh, but if refresh fails:
DROP SECRET datasphere;
CREATE SECRET datasphere (...);  -- Re-authenticate

Config File Not Found

Issue: Error: Could not read config file

Solutions:

# Check file exists
ls -la /path/to/config.conf

# Check permissions
chmod 600 /path/to/config.conf

# Use absolute path
CREATE SECRET (..., CONFIG_FILE '/home/user/.erpl/config.conf');

Wrong Credentials in Config

Issue: Authentication fails with config-based secret

Solutions:

# Verify config file format
[section_name]
key=value  # No spaces around =
key2=value2

# Not this:
# key = value  (spaces cause issues in some parsers)

Examples by Use Case

Local Development

-- Simple inline secret for local testing
CREATE TEMPORARY SECRET local_sap (
  TYPE http_basic,
  username 'DEV_USER',
  password 'dev123'
);

Team Shared Config

# team-config.conf (committed to repo)
[sap_dev]
base_url=https://sap-dev.example.com
# Sensitive values in environment variables

[datasphere_dev]
tenant_name=team-dev-tenant
data_center=eu10
# Client ID/secret in environment

CREATE SECRET sap_dev (
  TYPE http_basic,
  PROVIDER config,
  CONFIG_FILE 'team-config.conf',
  SECTION 'sap_dev',
  username getenv('SAP_DEV_USER'),
  password getenv('SAP_DEV_PASS')
);

Production Deployment

# Use secret manager
export DATASPHERE_CREDS=$(vault kv get -field=json secret/datasphere/prod)
echo "$DATASPHERE_CREDS" > /run/secrets/datasphere.json

-- Load from mounted secret
CREATE SECRET datasphere (
  TYPE datasphere,
  PROVIDER file,
  FILEPATH '/run/secrets/datasphere.json'
);

Next Steps

Apply secrets in HTTP Functions
Use with OData services
Secure Datasphere connections
Protect ODP extractions

Summary

ERPL-Web's secrets management provides:

✅ Secure - Encrypted storage, no plaintext passwords
✅ Flexible - Multiple providers (oauth2, config, file)
✅ Automatic - OAuth2 token refresh built-in
✅ DuckDB Native - Integrated with DuckDB secrets system
✅ Production Ready - Environment separation and rotation

Keep your credentials secure while querying APIs and SAP systems!

Overview​

Quick Start​

Secret Types​

http_basic​

http_bearer​

datasphere​

Secret Providers​

oauth2 Provider​

config Provider​

file Provider​

Managing Secrets​

List Secrets​

Update a Secret​

Remove a Secret​

Temporary vs Persistent Secrets​

OAuth2 Flows in Detail​

Authorization Code Flow​

Token Refresh​

Client Credentials Flow​

Security Best Practices​

1. Never Commit Secrets​

2. Use Environment Variables​

3. Restrict File Permissions​

4. Use Different Secrets Per Environment​

5. Rotate Credentials Regularly​

6. Use Temporary Secrets for Testing​

Advanced Patterns​

Multiple Tenants​

Secret Rotation Without Downtime​

Config File with Multiple Environments​

Integration with External Secret Managers​

Troubleshooting​

Secret Not Found​

OAuth2 Flow Fails​

Token Expired​

Config File Not Found​

Wrong Credentials in Config​

Examples by Use Case​

Local Development​

Team Shared Config​

Production Deployment​

Next Steps​

Summary​

Overview

Quick Start

Secret Types

http_basic

http_bearer

datasphere

Secret Providers

oauth2 Provider

config Provider

file Provider

Managing Secrets

List Secrets

Update a Secret

Remove a Secret

Temporary vs Persistent Secrets

OAuth2 Flows in Detail

Authorization Code Flow

Token Refresh

Client Credentials Flow

Security Best Practices

1. Never Commit Secrets

2. Use Environment Variables

3. Restrict File Permissions

4. Use Different Secrets Per Environment

5. Rotate Credentials Regularly

6. Use Temporary Secrets for Testing

Advanced Patterns

Multiple Tenants

Secret Rotation Without Downtime

Config File with Multiple Environments

Integration with External Secret Managers

Troubleshooting

Secret Not Found

OAuth2 Flow Fails

Token Expired

Config File Not Found

Wrong Credentials in Config

Examples by Use Case

Local Development

Team Shared Config

Production Deployment

Next Steps

Summary