User-Principal Propagation for JDBC Data Sources - Developer Guide

Intended audience: developers administrators

AO Platform: 5.0

Overview

Before this feature, every JDBC query the platform ran against a configured datasource (Snowflake, Redshift, BigQuery, …) executed as a single shared service account stored on the db_datasource row. Snowflake QUERY_HISTORY.USER_NAME, row-access policies, masking policies, warehouse-usage attribution, and audit logs all collapsed to that one service identity.

User-principal propagation forwards the platform user's authenticated identity to the warehouse via OAuth bearer tokens, so CURRENT_USER() on the database side resolves to the real platform user. Database-side RBAC, masking, and audit work as intended.

The framework is database-agnostic — Snowflake is the first concrete adapter, but adding Redshift, BigQuery, Azure SQL, Databricks, Trino, Postgres-AAD, or Oracle ADB is one new adapter file with no other changes.

The Contract (Hard Guarantee)

Every DataSource.getConnection() call returned to application code is backed by an access token whose remaining lifetime is at least safetyMarginSec (default 300 s) at the moment of borrow.

No application code path can receive a connection authenticated with a token that is expired or about to expire.

This is enforced by five layered defenses, evaluated in order on every borrow. Each is independently sufficient for "almost always"; combined they make the failure mode mathematically negligible.

Architecture

The Two Flows Side-by-Side

Flow A — SERVICE_ACCOUNT (today's behavior, unchanged)

┌──────────┐
│  User  │
└────┬─────┘
    │ HTTP / OData / pipeline call
    ▼
┌────────────────────────────────────────────────────────┐
│ Platform Controller / Pipeline                │
└────┬───────────────────────────────────────────────────┘
    │ datasource.getConnection()
    ▼
┌───────────────────────────────────────────────────────────────────┐
│ SqlDatasourceFactory                                   │
│   cacheKey = dsId                                      │
│   miss → DbcpDataSourceResolver.buildDataSource(ds)    │
└────┬──────────────────────────────────────────────────────────────┘
    │ BasicDataSource pool shared across ALL users
    ▼
┌─────────┐
│  DB    │  ← every query attributed to svcUser
└─────────┘

Flow B — USER_PRINCIPAL (new)

┌──────────────┐
│ End User   │
│ (OIDC SSO) │
└────┬─────────┘
    │ access_token in OpenIdConnectUserDetails
    ▼
┌──────────────────────────────────────────────────────────────────────────────────────┐
│ SqlDatasourceFactory.getDataSource(name)                               │
│   key = (dsName, userId), bound to AccessToken.tokenId                 │
│   ⚑ L2 JIT: if cached token near expiry → mintFresh + rebuild pool     │
└────┬─────────────────────────────────────────────────────────────────────────────────┘
    │
    ▼
┌──────────────────────────────────────────────────────────────────────────────────────┐
│ UserPrincipalDataSourceResolver (dispatcher)                           │
│   adapter        = UserPrincipalAuthRegistry.lookup(dbType)            │
│   tokenProvider  = OAuthTokenProviderRegistry.lookup(oauthFlow)        │
│   token          = tokenProvider.getAccessToken(cfg, user)             │
│   adapter.applyAuthProperties(basicDataSource, cfg, user, token)       │
└────┬─────────────────────────────────────────────────────────────────────────────────┘
    │ adapter sets driver-specific properties:
    │   • Snowflake:  authenticator=oauth, token=<jwt>, role, warehouse, db, schema
    │   • Redshift:   IdpTokenAuthPlugin, AccessToken, DbUser     [Phase 2]
    │   • BigQuery:   OAuthType=2, OAuthAccessToken=<jwt>         [Phase 2]
    │   • Azure SQL:  accessToken=<jwt>                           [Phase 3]
    │   • Databricks: AuthMech=11, Auth_AccessToken=<jwt>         [Phase 3]
    │   • Trino:      accessToken, externalAuthentication=false   [Phase 3]
    ▼
┌──────────────────────────────────────────────────────────────────────────────────────┐
│ Per-(dsName, userId) BasicDataSource pool                              │
│   maxTotal == initialSize == 4 (no lazy growth with stale token)       │
│   maxConnLifetimeMillis = tokenTtl − safetyMargin                      │
│   testOnBorrow + validationQuery (L4)                                  │
└────┬─────────────────────────────────────────────────────────────────────────────────┘
    │ wrapped by UserPrincipalRetryingDataSource (L5)
    ▼
┌─────────┐
│  DB   │  ← query attributed to the real platform user
└─────────┘

Strict-fail path (no user token / system job / non-OIDC auth):
  → UserPrincipalRequiredException
     → OData controller: 401 + WWW-Authenticate: Bearer
     → Pipeline scheduler: non-retryable, INFO log, no email blast

Token Acquisition (Zoom)

   OAuthTokenProvider.getAccessToken(cfg, user)
                  │
   ┌─────────────────┼────────────────────────────────────────────┐
   │              │                                     │
   ▼              ▼                                     ▼
EXTERNAL_SAME_IDP   EXTERNAL_TOKEN_EXCHANGE        VENDOR_BUILTIN
(recommended)       (RFC 8693 / Azure AD OBO)      (e.g. Snowflake OAuth IdP)
   │              │                                     │
   │ read         │  POST IdP token endpoint            │  redirect user to vendor
   │ OpenId       │  grant_type=…token-exchange         │  /authorize, persist
   │ Connect      │  subject_token = user's OIDC token  │  refresh token per
   │ UserDetails  │  audience      = vendor URI         │  (user, ds) encrypted
   │ .accessToken │  scope         = DB scope           │  [Phase 4 — deferred]
   │              │                                     │
   ▼              ▼                                     ▼
        UserPrincipalTokenCache (ConcurrentHashMap.compute single-flight)
                  │
        cache hit AND not near expiry?
                 / \
               yes   no → mintFresh; on stale OIDC token, refresh
                  │       via OpenIdRefreshTokenService
                  ▼
            return AccessToken { value, expEpochSec, tokenId }

Three Orthogonal SPIs

Adding a new database = one new UserPrincipalAuthAdapter bean. No changes to pooling, token plumbing, or context propagation.

OAuth Strategies

All three are implementable behind the single OAuthTokenProvider SPI. Strategy 1 is shipped; Strategy 2 is a configuration-only delta; Strategy 3 is deferred to Phase 4.

Strategy 1 — External OAuth, Same IdP as Platform ✅ (Recommended; Phase 1)

The platform's OIDC IdP (Azure AD / Okta / Ping / …) is also configured as the database's External OAuth security integration. The platform already holds the user's OAuth2AccessToken on OpenIdConnectUserDetails.

• If the platform token's aud already includes the database, forward verbatim (requiresExchange: false).

• Otherwise, mint a DB-scoped token via Azure AD OBO or RFC 8693 token exchange (requiresExchange: true).

• Refresh chain: re-exchange the Layer-1 token first; if Layer-1 itself expired, use existing OpenIdRefreshTokenService.

A single impl (ExternalSameIdpTokenProvider) serves Snowflake, Redshift, BigQuery, Azure SQL, Databricks, Trino — only audience/scope differs per datasource.

Strategy 2 — External OAuth, Different IdP (Deferred)

Platform authenticates against IdP-A; the database trusts IdP-B (federation broker). Same code path as Strategy 1, differs by token endpoint, audience, and an optional actor_token. Configuration-only delta.

Strategy 3 — Vendor-Built-In OAuth (Deferred)

Snowflake's own /oauth/authorize, BigQuery via Google consent, etc. Requires:

• One-time interactive consent via browser redirect to the vendor's /authorize.

• A new persistent table user_vendor_oauth_token (user_id, ds_id, refresh_token_encrypted, exp_epoch, created_at) — encrypted via CryptoConverter.

• UI plumbing to drive the consent dance.

Scaffold ships in Phase 1 (VendorBuiltinOAuthTokenProvider) — currently throws a clear "Phase 4 not implemented; use EXTERNAL_SAME_IDP" error.

Connection Pooling Strategy

Pooling is preserved in both modes. Both use DBCP2 BasicDataSource.

Why Per-User Pools Are Unavoidable

All target JDBC drivers (Snowflake, BigQuery, Azure SQL, Databricks, Trino, Oracle ADB) bind the OAuth token at connect() time — the resulting JDBC session is authenticated as that user. No driver API allows swapping identity on a live connection. A single shared pool would either force fresh connections per borrow (defeating pooling) or mix users on shared sessions (security breach). Per-user pools preserve both identity propagation and connection reuse.

Capacity Protection (Two Caps)

   live connections to a DB  =  activeUsers(t) × maxTotalPerUser  ≤  globalCap

1. adapter.recommendedMaxPerUser — per-DB sensible default (Snowflake 4, Redshift 2, BigQuery 4, Postgres-AAD 2). Deployer overrides per-datasource.

2. Global LRU cap on SqlDatasourceFactory's per-user pool cache (default 5 000 live (dsName, userId) pools). When the cap is hit, the least-recently-used pool is closed. Protects platform-server file-descriptor budget.

Pool Rotation on Token Refresh

DBCP2 cannot rotate connection properties on a live BasicDataSource. On token refresh:

1. UserPrincipalTokenCache returns a token with a different tokenId.

2. SqlDatasourceFactory sees the cache entry's boundTokenId no longer matches.

3. The old pool is closed — in-flight queries on borrowed connections complete; idle connections close immediately.

4. A new pool is built lazily on the next borrow with the fresh token.

5. maxConnLifetimeMillis = tokenTtl − safetyMargin enforces that no physical connection outlives its credentials.

Token Lifecycle

Three Layers of Tokens

Layer 1: Platform OIDC access token (held in OpenIdConnectUserDetails)
   issued at OIDC login by Platform IdP        TTL: typically 1 h
   refreshed by OpenIdRefreshTokenService      using IdP refresh token (sliding)

Layer 2: Database-scoped exchanged token (Strategies 1 & 2)
   minted via OBO / RFC 8693 from Layer 1       TTL: typically = Layer 1 TTL
   cached in UserPrincipalTokenCache keyed (userId, dsId)
   re-minted by re-exchanging the Layer 1 token (no separate refresh token needed)

Layer 3: BasicDataSource pool (cached in SqlDatasourceFactory)
   keyed (dsName, userId) bound to AccessToken.tokenId
   on token rotation, bound id changes → old pool closed, new pool built

Refresh Paths

• Proactive (L1): UserPrincipalDataSourceSweeper runs every 30 s, evicts cache entries and pools whose token is within safetyMarginSec of expiry. Off the user request path.

• JIT (L2): UserPrincipalTokenCache.get() re-mints synchronously under single-flight if the cached token is missing or near expiry. The only layer that can block a borrower.

• Reactive (L5): when the driver reports a SQLException that the adapter classifies as a revoked-token error (Snowflake: SQLState 08001 + vendor codes 390318 / 390302), the UserPrincipalRetryingDataSource decorator invalidates and rebuilds before retrying once.

Single-Flight Refresh

UserPrincipalTokenCache uses ConcurrentHashMap.compute so when N threads simultaneously miss on the same (userId, dsId) key, exactly one executes the loader and the other (N-1) block until done. Prevents thundering-herd IdP calls at expiry boundaries.

Logout / Session Invalidation

When Spring Security's SessionDestroyedEvent fires (logout, session-fixation rotate), UserPrincipalDataSourceSweeper listens and immediately evicts all (userId, *) entries from both UserPrincipalTokenCache and SqlDatasourceFactory. A logged-out user's token can't linger in any pool.

Configuration

db_datasource Row (Per-Data Source)

Three new columns added by schema_user_principal_5.0.xml:

Default value preserves existing rows as SERVICE_ACCOUNT — no backfill needed.

oauth_config_json Schema (Snowflake, Strategy 1 Example)

{
  // Top-level (provider-agnostic) — read by ExternalSameIdpTokenProvider
  "requiresExchange":      true,           // false = forward Platform token verbatim
  "useRfc8693":            false,          // false = Azure AD OBO; true = generic RFC 8693
  "tokenEndpoint":         "https://login.microsoftonline.com/<tenant>/oauth2/v2.0/token",
  "clientIdSecretRef":     "snowflake-obo-client-id",      // resolved via SecretResolver
  "clientSecretSecretRef": "snowflake-obo-client-secret",  // never in plaintext here
  "scope":                 "https://<account>.snowflakecomputing.com/session:role-any",
  "audience":              "https://<account>.snowflakecomputing.com",

  // Per-driver hints — read by SnowflakeUserPrincipalAuthAdapter
  "driverHints": {
    "defaultRole": "ANALYST",
    "warehouse":   "ANALYTICS_WH",
    "db":          "ANALYTICS_DB",
    "schema":      "PUBLIC",
    "roleClaimMapping": {
      "groupClaim": "groups",
      "rules": [
        { "ifGroupContains": "snowflake-admins", "useRole": "SYSADMIN" },
        { "ifGroupContains": "snowflake-finance", "useRole": "FINANCE_ANALYST" }
      ]
    }
  }
}

Same shape for BigQuery / Azure SQL / Redshift / Databricks / Trino — only audience, scope, and driverHints change.

application.yml (Server-Wide)

Lives in cloudseer/src/main/resources/application.yml:

security:
  user-principal:
    enabled: false                        # master kill-switch; default off

    refresh:
      safetyMarginSec: 300                # required remaining TTL at borrow time
      sweeperIntervalSec: 30              # proactive sweeper cadence
      clockSkewSec: 30                    # IdP clock tolerance for JWT exp/nbf
      maxIdpRetryAttempts: 3              # per-call IdP retry budget
      idpRetryBackoffMs: 500              # exponential backoff base

    pool:
      maxTotalPerUser: 0                  # 0 = use adapter recommendation
      globalMaxLiveUserPools: 5000        # LRU cap across all per-user pools
      validationQueryTimeoutSec: 2        # testOnBorrow validation timeout
      maxIdleMillis: 60000                # idle eviction window per-pool

Secret Handling

OAuth client secrets are never stored in oauth_config_json directly. They are stored as named references (e.g. "clientSecretSecretRef": "snowflake-obo-client-secret") and resolved at runtime via the SecretResolver SPI.

Phase 1 ships EnvironmentSecretResolver — looks up the reference name in Spring's Environment (system properties, env vars, application.yml). Production deployments should replace this bean with an AWS Secrets Manager-backed impl per AWS-SECRETS-MANAGER-PLAN.md.

Snowflake Setup Runbook

Step-by-step for the Snowflake side of a Strategy 1 deployment.

Register the Platform as an OAuth Client in Your IdP

Azure AD example (the most common case):

1. In the Azure portal → App registrations → New registration, register the platform as an app.

2. Under Certificates & secrets, create a client secret; capture the value into your secrets manager as snowflake-obo-client-secret (or whatever name you use in oauth_config_json).

3. Under API permissions, grant the Snowflake session:role-any (or specific roles) scope, then grant admin consent for the tenant.

4. Record the Application (client) ID as snowflake-obo-client-id.

Create the External OAuth Security Integration in Snowflake

CREATE SECURITY INTEGRATION ao_platform_azure_oauth
  TYPE = EXTERNAL_OAUTH
  ENABLED = TRUE
  EXTERNAL_OAUTH_TYPE = AZURE                       -- or OKTA, PING_FEDERATE, CUSTOM
  EXTERNAL_OAUTH_ISSUER = 'https://sts.windows.net/<tenant-id>/'
  EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.microsoftonline.com/<tenant-id>/discovery/v2.0/keys'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('https://<account>.snowflakecomputing.com')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'upn'   -- match a Snowflake LOGIN_NAME
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'LOGIN_NAME'
  EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE_FOR_PRIVILEGE'
;

⚠️ Issuer string trap. Azure AD has v1 (https://sts.windows.net/<tenant>/) and v2 (https://login.microsoftonline.com/<tenant>/v2.0) issuers. The integration's EXTERNAL_OAUTH_ISSUER must match the token's actual iss claim exactly. Copy from a real decoded token, not from docs.

Provision Snowflake Users + Roles

• Every platform user who will query this datasource must have a corresponding Snowflake user with matching LOGIN_NAME (the claim configured in EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM).

• Grant each user the Snowflake roles the platform will request via the role= JDBC property (and/or via driverHints.roleClaimMapping).

• Manage at scale via SCIM / IdP sync — don't hand-provision.

Configure the Platform Data Source

UPDATE common.db_datasource
SET auth_mode = 'USER_PRINCIPAL',
    oauth_flow = 'EXTERNAL_SAME_IDP',
    oauth_config_json = '<JSON per §7.2>'::jsonb
WHERE name = 'snowflake_analytics';

Enable the Feature Flag

In application.yml (or via your runtime config layer):

security:
  user-principal:
    enabled: true

Smoke Test

See section Verification below.

Developer Guide: Adding a New Database Adapter

Goal: add user-principal support for a new database vendor. Estimated effort: one new file.

Create the Adapter

// ao-jpa/.../jpa/cpool/userprincipal/redshift/RedshiftUserPrincipalAuthAdapter.java

@Component
public class RedshiftUserPrincipalAuthAdapter implements UserPrincipalAuthAdapter {

    @Override
    public Set<String> supportedTypes() {
        // Match SQLDatabaseType.getName() — case-insensitive.
        return Set.of("REDSHIFT");
    }

    @Override
    public void applyAuthProperties(BasicDataSource ds, SqlDatasource cfg, AOUser user, AccessToken token) {
        ds.addConnectionProperty("plugin_name",
                "com.amazon.redshift.plugin.IdpTokenAuthPlugin");
        ds.addConnectionProperty("token",   token.value());
        ds.addConnectionProperty("DbUser",  user.getUsername());
        ds.addConnectionProperty("AutoCreate", "true");
        // pull region/cluster from oauth_config_json.driverHints if needed
    }

    @Override
    public boolean isTokenRevokedError(SQLException e) {
        // Redshift surfaces revoked-token errors as SQLState 28000.
        return "28000".equals(e.getSQLState())
            && e.getMessage() != null
            && e.getMessage().contains("token");
    }

    @Override
    public int recommendedMaxPerUser() {
        return 2;
    }
}

That's It

• Component scan picks the bean up automatically.

• UserPrincipalAuthRegistry registers it under "REDSHIFT".

• Datasources with database.name = 'REDSHIFT' and auth_mode = 'USER_PRINCIPAL' start working.

• No changes to the factory, the providers, the cache, the sweeper, the L5 wrapper, or any consumer code.

Adapter Cookbook

Quick reference for the JDBC properties each vendor accepts:

Code Map

Where everything lives in the repo.

Framework SPIs (ao-jpa/.../jpa/cpool/userprincipal/)

Token Providers (ao-security/.../security/userprincipal/)

Modified Existing Files

Liquibase

Operational Runbook

Metrics

Emit from UserPrincipalTokenCache, SqlDatasourceFactory, and the providers (wire into your Micrometer / Prometheus stack):

Log Discipline

🔒 Never log token values. Always log tokenId (SHA-256 prefix), expEpoch, and the (userId, dsName) pair instead.

The framework respects this throughout; reviewers should check any new code touching AccessToken.value() for accidental logging.

Common Errors And What They Mean

Force a Token Rotation (Operator)

If you suspect a token is bad and want to force a re-mint without restarting:

1. Use the platform's session-eviction endpoint (or sign the user out and back in) — this triggers SessionDestroyedEvent, which evicts every cache entry for that user.

2. The next query will mint fresh.

11.5 Disable User-Principal Mode Temporarily

Master kill-switch in application.yml:

security:
  user-principal:
    enabled: false

USER_PRINCIPAL datasources will fail with a clear configuration error; SERVICE_ACCOUNT datasources continue working unchanged. No code rollback required.

Security Considerations

Credential Storage

• Service-account passwords continue to be encrypted via CryptoConverter (Jasypt + PKCS12).

• OAuth client secrets are stored as named references, not values. Values resolve at runtime via SecretResolver (env var by default; AWS Secrets Manager in production).

• User OAuth tokens are held in memory only; never persisted.

• Strategy 3 vendor refresh tokens (when Phase 4 ships) will be encrypted via CryptoConverter, stored in user_vendor_oauth_token.

Strict-Fail (No Silent Fallback)

When a USER_PRINCIPAL datasource is queried without a usable user OAuth token, the framework fails fast with UserPrincipalRequiredException. There is no silent fallback to service-account credentials. The audit trail must remain accurate.

This is intentional and load-bearing. The whole point of user-principal mode is per-user attribution at the warehouse layer — falling back to a shared account would defeat it.

Token Forwarding

• The framework forwards the user's token to the database driver. It does not modify, re-sign, or extend it.

• For exchanged tokens (Azure AD OBO / RFC 8693), the platform IdP signs the new token; the platform never holds a signing key for the database's audience.

• Tokens never appear in URLs (Snowflake/BigQuery accept the token in Properties, not the JDBC URL).

Logout Propagation

SessionDestroyedEvent triggers immediate eviction of all (userId, *) cache entries and pools — closes any open connections borrowed by the logged-out user. A logged-out session cannot continue to query.

Audit Trail

When user-principal mode is in effect:

• Database side: QUERY_HISTORY.USER_NAME (Snowflake) / equivalent attribution columns elsewhere now identify the real platform user. RBAC, masking, and row-access policies bind to them.

• Platform side: existing audit logging continues to identify the user via AOContextHolder.getContext().getAOUser().

• IdP side: every token exchange is logged in the IdP's audit stream (Azure AD Sign-in logs, Okta System Log, …).

End-to-end: a single user action is now traceable across three audit streams (platform, IdP, warehouse).

Verification (Manual Smoke Test)

ℹ️ No unit tests are part of this work, per scope. The following is the manual validation procedure.

1. Snowflake side: register an External OAuth security integration trusting the platform IdP (§8.2); grant the test user a Snowflake role (e.g. ANALYST).

2. Datasource config: insert a db_datasource row with auth_mode='USER_PRINCIPAL', oauth_flow='EXTERNAL_SAME_IDP', oauth_config_json per §7.2.

3. Login: sign into Vulcanux as a user federated via the same IdP.

4. Query path: open an MSO/visualization backed by that datasource. In Snowflake QUERY_HISTORY, confirm USER_NAME is the platform user (not the legacy service account).

5. RBAC enforcement: revoke the user's Snowflake role → next query surfaces a Snowflake-side access-denied error.

6. Token refresh (proactive, L1): idle past safetyMarginSec → next query succeeds; sweeper logs show a proactive mint with no extra borrow latency.

7. Token refresh (JIT, L2): simulate sweeper delay (disable temporarily) → next query waits briefly on single-flight refresh, succeeds; userprincipal.token.refresh.proactive does NOT increment but mint.total does.

8. Bounded lifetime (L3): confirm BasicDataSource.maxConnLifetimeMillis via JMX is set to tokenTtl − safetyMargin * 1000.

9. Validation query (L4): kill a connection server-side; confirm next borrow drops it via testOnBorrow.

10. Reactive retry (L5): revoke the IdP token between two queries; confirm the second query succeeds via one-shot retry and userprincipal.token.revoked.retry increments.

11. Strict-fail (scheduler): trigger a scheduled pipeline against the same datasource → fails with UserPrincipalRequiredException; INFO log, no email blast, status FAILED, no retry.

12. Strict-fail (OData / Basic): hit OData endpoint with Basic auth → HTTP 401 + WWW-Authenticate: Bearer. Same call with Organizational Account → query history shows the real user.

13. LRU cap: simulate globalMaxLiveUserPools + 1 distinct users querying → LRU pool eviction; userprincipal.pool.evicted{reason=lru_cap} increments.

14. Logout eviction: log a user out → confirm their pool and token cache entries disappear immediately via metrics.

15. Service-account regression: a Postgres datasource left at auth_mode='SERVICE_ACCOUNT' continues unchanged — same pool behavior as before.

16. Framework extensibility (paper test): walk through adding Redshift — confirm it's a single new RedshiftUserPrincipalAuthAdapter file with no other changes.

FAQ

Q: Does this break existing data sources? A: No. Default auth_mode = SERVICE_ACCOUNT preserves every existing row's behavior exactly. The new code paths are dormant until a datasource is explicitly switched.

Q: What if our IdP is not Azure AD? A: Strategy 1 works with any OIDC IdP that supports OBO or RFC 8693 token exchange (Okta, PingFederate, Auth0, Keycloak, custom). Set useRfc8693: true and configure the IdP's token endpoint + audience.

Q: Does this work with Power BI / Excel? A: Yes, when connecting via OData with Organizational Account (OIDC bearer). Basic auth callers receive HTTP 401. See ODATA_POWERBI_SOLUTION_AND_USAGE.md.

Q: What about background pipelines/schedulers? A: They fail fast with UserPrincipalRequiredException (no silent service-account fallback). The scheduler logs INFO and skips the failure-email blast. If you need a pipeline to query a user-principal datasource on a schedule, either (a) move that datasource back to SERVICE_ACCOUNT, or (b) wait for Phase 4, which introduces persisted user refresh tokens for offline use.

Q: How does this interact with Snowflake row-access / masking policies? A: This is the headline benefit. With CURRENT_USER() resolving to the real user, your existing row-access policies, masking policies, and column-level security rules apply per-user — exactly the security posture they were designed for.

Q: Per-user pools — won't this exhaust connections at scale? A: Two caps prevent it. (1) recommendedMaxPerUser is small (Snowflake 4, Redshift 2, …). (2) globalMaxLiveUserPools (default 5 000) closes the least-recently-used pool on overflow. In practice: with 5 000 active users × 4 = 20 000 connections worst-case, which Snowflake handles trivially; for traditional DBs with hard limits (Postgres ≈ 100), drop maxTotalPerUser to 1.

Q: What happens if the IdP is down? A: L2's single-flight refresh blocks the borrower; if the IdP doesn't recover within the request timeout, UserPrincipalRequiredException propagates cleanly. No half-broken connections are handed out. Existing pools continue serving until their maxConnLifetime expires.

Q: Can I roll this back? A: Yes — flip security.user-principal.enabled: false. The Liquibase changes only add columns with safe defaults; no data migration. To fully revert, drop the columns and reset any auth_mode='USER_PRINCIPAL' rows to SERVICE_ACCOUNT.

References

• Snowflake OAuth introduction

• Snowflake External OAuth overview

• Microsoft — OAuth 2.0 On-Behalf-Of flow

• RFC 8693 — OAuth 2.0 Token Exchange

Contact App Orchid | Disclaimer