Skip to main content

Multi-Tenant SaaS API with OIDC

You run a B2B SaaS company. Customer A is a bank. Customer B is a hospital chain. Customer C is a 12-person startup. They all hit the same flAPI deployment, but every API response must be perfectly scoped to the calling tenant.

You do not want to:

  • Store customer passwords.
  • Build a federation layer on top of every customer's IdP.
  • Issue and rotate per-tenant API keys.

You do want to:

  • Let each customer authenticate against their own Microsoft Entra ID (Azure AD) tenant.
  • Verify tokens locally using the IdP's JWKS — no callback to the IdP per request.
  • Use the JWT's tenant_id and roles claims directly in the SQL template for row-level security.

flAPI's OIDC support, implemented in oidc_auth_handler.cpp and configured under auth.oidc.*, is built exactly for this.

Token Flow​

┌────────────┐        ┌───────────────────────┐        ┌──────────┐
│   Client   │        │  Microsoft Entra ID   │        │  flAPI   │
│  (browser, │        │  login.microsoftonline│        │  /customers
│   CLI, app)│        │       .com/{tenant}   │        │          │
└─────┬──────┘        └──────────┬────────────┘        └────┬─────┘
      │                          │                          │
      │ 1. Sign in (OAuth2)      │                          │
      ├─────────────────────────>│                          │
      │                          │                          │
      │ 2. Access token (JWT)    │                          │
      │<─────────────────────────┤                          │
      │                          │                          │
      │ 3. GET /customers/                                  │
      │    Authorization: Bearer eyJ...                     │
      ├────────────────────────────────────────────────────>│
      │                          │                          │
      │                          │ 4. Fetch JWKS (cached)   │
      │                          │<─────────────────────────┤
      │                          │                          │
      │                          │ 5. JWKS document         │
      │                          ├─────────────────────────>│
      │                          │                          │
      │                          │     6. Verify RS256      │
      │                          │        signature locally │
      │                          │     7. Check iss / aud / │
      │                          │        exp + clock-skew  │
      │                          │     8. Map claims:       │
      │                          │        preferred_username│
      │                          │        roles, tenant_id  │
      │                          │                          │
      │ 9. 200 OK + tenant-scoped JSON                      │
      │<────────────────────────────────────────────────────┤

JWKS is fetched the first time a token with a new kid arrives, then cached for jwks-cache-hours (default 24). Every subsequent request validates fully offline.

Configuration​

# flapi.yaml
project-name: customer-data-saas
project-description: Multi-tenant customer data API with Entra ID OIDC

template:
  path: './sqls'
  environment-whitelist:
    - '^MS_.*'
    - '^DB_.*'

connections:
  warehouse:
    init: |
      INSTALL postgres;
      LOAD postgres;
      ATTACH 'host={{env.DB_HOST}} dbname={{env.DB_NAME}} user={{env.DB_USER}} password={{env.DB_PASSWORD}}'
        AS warehouse (TYPE postgres, READ_ONLY);

# Global OIDC: applies to every endpoint unless an endpoint overrides it.
auth:
  enabled: true
  type: oidc
  oidc:
    provider-type: microsoft
    issuer-url: 'https://login.microsoftonline.com/{tenant}/v2.0'
    client-id: '{{env.MS_CLIENT_ID}}'
    allowed-audiences:
      - 'api://flapi-saas'
    scopes: [openid, profile, email]
    username-claim: preferred_username
    email-claim: email
    roles-claim: roles
    role-claim-path: roles
    jwks-cache-hours: 12
    clock-skew-seconds: 30

enforce-https:
  enabled: true

Every key above is parsed in config_manager.cpp::parseOIDCConfigNode. Anything not in that function is silently ignored by flAPI, so the snippet stays narrow on purpose.

Why these particular values​

  • provider-type: microsoft — applies the microsoft preset from oidc_provider_presets.cpp, which validates that issuer-url contains a {tenant} placeholder.
  • issuer-url: '.../{tenant}/v2.0' — Microsoft requires the literal {tenant} placeholder in the issuer. The preset fails validation without it.
  • allowed-audiences: ['api://flapi-saas'] — tokens not minted for your app are rejected by OIDCAuthHandler::validateAudience.
  • role-claim-path: roles — for Entra ID app roles, the claim is flat. If you ever switch to a provider that nests roles (Keycloak's realm_access.roles), only this one key changes.
  • jwks-cache-hours: 12 — half the default. Tighter cache means faster recovery if Entra rotates a signing key.
  • clock-skew-seconds: 30 — production servers should be NTP-synced, so the 300s default is generous. 30s catches genuinely expired tokens sooner.

The Endpoint​

# sqls/customers/customers.yaml
url-path: /customers/
method: GET
connection:
  - warehouse
template-source: customers.sql

request:
  - field-name: segment
    field-in: query
    description: Filter customers by segment
    required: false
  - field-name: limit
    field-in: query
    description: Maximum rows to return
    required: false

No auth: block here — the endpoint inherits the global OIDC config.

The SQL Template​

auth.username, auth.email, and auth.roles are populated by the OIDC middleware after token validation. auth.roles is a comma-joined string, not a Mustache section — match it with LIKE.

-- sqls/customers/customers.sql
WITH caller AS (
  SELECT
    '{{{auth.username}}}'      AS user_login,
    '{{{auth.email}}}'         AS user_email,
    '{{auth.roles}}'           AS roles_csv
)
SELECT
    c.customer_id,
    c.tenant_id,
    c.company_name,
    c.segment,
    c.country
FROM warehouse.public.customers c
CROSS JOIN caller
WHERE 1=1
  -- Tenant scoping: derive tenant from the email domain (or a custom claim).
  AND (
       caller.roles_csv LIKE '%platform_admin%'   -- our own ops team: full read
    OR c.tenant_email_domain = split_part(caller.user_email, '@', 2)
  )
  -- Within a tenant, only tenant_admin sees inactive customers.
  AND (
       caller.roles_csv LIKE '%tenant_admin%'
    OR c.is_active = true
  )
  {{#params.segment}}
  AND c.segment = '{{{params.segment}}}'
  {{/params.segment}}
ORDER BY c.company_name
{{#params.limit}}
LIMIT {{{params.limit}}}
{{/params.limit}}

Two layers of authorization, both expressed in SQL:

  1. Tenant boundary: a user only sees rows whose tenant_email_domain matches the domain of their authenticated email. Platform admins (platform_admin role on your own Entra tenant) bypass this.
  2. In-tenant role: only tenant_admin sees deactivated customers within their tenant.

If a richer claim is available (for example, a custom tenant_id app role claim emitted by Entra), prefer matching on that instead of the email domain — it's harder to spoof and easier to audit.

Other Provider Presets​

flAPI ships seven preset values for provider-type. Every preset name below is enumerated in oidc_provider_presets.cpp::applyPreset.

provider-typeOne-liner
googleGoogle Workspace SSO. Default username-claim: email, issuer https://accounts.google.com.
microsoftMicrosoft Entra ID / Azure AD. Issuer https://login.microsoftonline.com/{tenant}/v2.0 — {tenant} placeholder is required.
keycloakSelf-hosted Keycloak. Issuer https://<host>/realms/{realm}. Roles nest under realm_access.roles by default.
auth0Auth0 SaaS. Issuer https://{domain}.auth0.com. Custom roles claim typically lives at a namespaced URL.
oktaOkta Workforce / Customer Identity. Issuer https://{domain}.okta.com/oauth2/default.
githubGitHub OAuth 2.0 (not full OIDC — token validation requires custom handling).
genericAny spec-compliant OIDC IdP. You must provide issuer-url explicitly.

Switching providers is usually just swapping provider-type and the issuer URL — claim mappings come from the preset.

Per-Endpoint Override​

Sometimes you want one endpoint open to a different audience. auth can be overridden per endpoint:

# sqls/health/health.yaml
url-path: /health/
method: GET
connection:
  - warehouse
template-source: health.sql

auth:
  enabled: false

A second OIDC pool — for example, a partner integration that authenticates against a different Entra tenant — is configured by repeating the full auth.oidc block under the endpoint. The endpoint-level block fully replaces the global one (see parseEndpointAuth in config_manager.cpp).

Test It​

1. Get a token from Entra ID​

For interactive testing, use the Azure CLI:

TENANT_ID=00000000-0000-0000-0000-000000000000
CLIENT_ID=11111111-1111-1111-1111-111111111111

TOKEN=$(az account get-access-token \
  --tenant "$TENANT_ID" \
  --resource "api://flapi-saas" \
  --query accessToken -o tsv)

For service-to-service flows, request a token with the client_credentials grant directly from the v2.0 token endpoint:

TOKEN=$(curl -s -X POST \
  "https://login.microsoftonline.com/$TENANT_ID/oauth2/v2.0/token" \
  -d "grant_type=client_credentials" \
  -d "client_id=$CLIENT_ID" \
  -d "client_secret=$CLIENT_SECRET" \
  -d "scope=api://flapi-saas/.default" \
  | jq -r .access_token)

2. Call the API​

curl -s -H "Authorization: Bearer $TOKEN" \
  "https://api.example.com/customers/?segment=enterprise&limit=10" \
  | jq .

A valid token returns 200 OK with tenant-scoped JSON:

{
  "next": null,
  "data": [
    {
      "customer_id": "c_8472",
      "tenant_id": "t_acme",
      "company_name": "Acme Pumps GmbH",
      "segment": "enterprise",
      "country": "DE"
    }
  ],
  "total_count": 1
}

3. Inspect failures​

Without a token:

curl -i https://api.example.com/customers/
# HTTP/1.1 401 Unauthorized
# WWW-Authenticate: Basic realm="flAPI"

With an expired or malformed token, the response is still 401 Unauthorized. Run flAPI with --log-level debug to see the exact rejection cause from OIDCAuthHandler::validateToken:

  • Failed to decode JWT — not a JWT at all
  • Token issuer mismatch — iss claim disagrees with auth.oidc.issuer-url (often a tenant typo)
  • Token audience validation failed — token wasn't minted for this API
  • Token has expired — beyond clock-skew-seconds
  • JWT signature verification failed — wrong key, or JWKS cache is stale (retry after jwks-cache-hours)

Claim Mapping in Practice​

OIDC tokens vary wildly between providers in where roles live. flAPI handles this with two knobs:

  • roles-claim — a flat claim name. The default is roles. Used when the token has "roles": [...] at the top level.
  • role-claim-path — a dot-separated path for nested claims. Used when the token has something like "realm_access": { "roles": [...] }.

When role-claim-path is set, roles-claim is ignored (see OIDCAuthHandler::validateToken). The dotted path supports arbitrary nesting; getClaimArray recurses on each . segment.

Examples by provider​

# Entra ID / Azure AD app roles — flat
oidc:
  provider-type: microsoft
  roles-claim: roles

# Keycloak realm roles — nested
oidc:
  provider-type: keycloak
  role-claim-path: realm_access.roles

# Keycloak client roles for a specific client — nested deeper
oidc:
  provider-type: keycloak
  role-claim-path: resource_access.my-api.roles

# Auth0 with a custom namespaced claim
oidc:
  provider-type: auth0
  role-claim-path: 'https://my-app.example.com/roles'

Inside SQL, every variant lands in the same place: {{auth.roles}}, a comma-joined string. The template never has to know which IdP issued the token.

Troubleshooting Checklist​

If a request is rejected and you don't know why, walk this list in order:

  1. Decode the token offline with jq -R 'split(".")[1] | @base64d | fromjson' and check iss, aud, exp, and the username claim.
  2. Match iss against auth.oidc.issuer-url, substituting the placeholder. For Entra ID, {tenant} must equal the literal tenant GUID — not the domain (contoso.onmicrosoft.com) and not the friendly name.
  3. Check aud against allowed-audiences — Entra ID usually emits the App ID URI (api://...) when you're calling your own API; some flows emit the raw client GUID instead. List both if needed.
  4. Confirm exp is in the future with date -u -d @$(jq -R 'split(".")[1] | @base64d | fromjson | .exp'). If it's close, check NTP on the flAPI host.
  5. Look up the kid in the JWKS at <issuer-url>/.well-known/openid-configuration → jwks_uri. If the key isn't there, the IdP rotated it and flAPI's cache is stale — restart flAPI or wait for jwks-cache-hours to expire.
  6. Inspect the role claim — confirm roles-claim / role-claim-path matches the token's actual structure. A null from getClaimArray means the path didn't resolve.

Operational Notes​

  • Key rotation: JWKS rotation is handled automatically. When a token arrives with a kid not in the cache, flAPI re-fetches jwks_uri once and caches the new set.
  • Multiple tenants: With Entra ID's "multi-tenant" app registration, the literal {tenant} in the issuer template lets the same flAPI instance accept tokens from any Entra tenant that has consented to your app. Use allowed-audiences to keep the API surface bound to your client.
  • Roles in the IdP: Define Entra ID app roles on the API app registration, assign them to users / groups, and configure your token configuration to include them. They land in the roles claim and become auth.roles inside SQL.
  • HTTPS: The Authorization header carries a bearer token. Set enforce-https.enabled: true (already in the config above) and put flAPI behind a TLS terminator in production.

See Also​

🍪 Cookie Settings