Skip to main content

OAuth2 DB State (oauth2-db-state)

OAuth2 DB State manages OAuth tokens for database connections in Looker (such as BigQuery, Snowflake External OAuth, or Trino Okta) when using custom OAuth setups that Looker does not natively support. It handles user authentication, stores encrypted tokens in a SQLite database, and syncs active access tokens to Looker using create_oauth_application_user_state. A background worker periodically scans stored tokens, refreshes expiring ones with the identity provider, and removes records if a refresh token is revoked.

System Architecture​

oauth2-db-state consists of four core subsystems:

Core Subsystems​

HTTP Server (internal/server)​

Handles the OAuth authorization flow (/login), provider callback (/oauth/callback), health checks (/healthz), and status dashboard (/). It resolves the user's Looker User ID by querying the Looker API (SearchUsers) using the email address returned from the OAuth provider's userinfo endpoint.

Encryption & Storage (internal/db)​

Stores tokens in a local SQLite database, encrypting access and refresh tokens at rest using ENCRYPTION_KEY.

Looker API Integration (internal/looker)​

Wraps the official Looker SDK to manage Looker API session tokens and user state synchronization. It pushes active access tokens into Looker using CreateOauthApplicationUserState. Refresh tokens are kept in SQLite and omitted from Looker API requests so Looker does not attempt to refresh tokens directly.

Background Maintenance Worker (internal/worker)​

Runs a periodic loop (configured via REFRESH_INTERVAL_SECONDS, default 60s) scanning stored tokens in SQLite. It automatically refreshes tokens expiring within the threshold window (REFRESH_WINDOW_SECONDS, default 10m) with the OAuth provider, and purges token records from SQLite if a refresh token is revoked or rejected by the provider.

Looker User Resolution by Email​

When a user completes the OAuth flow, the server requests their email from the provider's userinfo endpoint (https://openidconnect.googleapis.com/v1/userinfo) and looks up their corresponding Looker User ID using sdk.SearchUsers.

info

The email address returned by the identity provider must match the user's registered email address in Looker.

Step-up Auth (Group-Based Scope Upgrades)​

Looker's native database OAuth integrations apply a single fixed set of OAuth scopes across an entire instance (for example, native BigQuery OAuth only requests https://www.googleapis.com/auth/bigquery.readonly). If specific users require write access, elevated data insertion permissions, or broader database roles, Looker cannot natively request elevated scopes for just a subset of users.

oauth2-db-state supports Step-up Auth (Incremental Authorization) to request elevated OAuth scopes exclusively for members of specified Looker Groups.

Configuration​

Define OAUTH_STEPUP_SCOPES in .env as comma-separated GROUP_ID:SCOPE pairs:

# Baseline scopes for all users
OAUTH_SCOPES="https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/bigquery.readonly"

# Elevated scopes triggered for specific Looker Group members (e.g. BigQuery write access for Group 123)
OAUTH_STEPUP_SCOPES="123:https://www.googleapis.com/auth/bigquery.insertData,456:https://www.googleapis.com/auth/bigquery"

Authorization Flow​

  1. User authenticates with standard baseline scopes (OAUTH_SCOPES).
  2. oauth2-db-state resolves the user email and queries Looker (sdk.User(userID, "group_ids")) to check group membership.
  3. If the user belongs to a group defined in OAUTH_STEPUP_SCOPES (such as Group 123) and their token lacks the required scope, the server redirects them back to the OAuth provider to grant the elevated scopes (including include_granted_scopes=true for Google providers).
  4. Upon consent, the server saves the upgraded token in SQLite and syncs it to Looker via CreateOAuthApplicationUserState.

Sequence Diagram​

Codebase Layout​

oauth2-db-state/
├── cmd/oauth2-db-state/   # CLI entrypoint & subcommand handlers
├── internal/
│   ├── config/            # Configuration loader & .env parser
│   ├── db/                # SQLite schema, encryption & CRUD operations
│   ├── looker/            # Looker SDK integration wrapper
│   ├── oauth/             # OAuth 2.0 authorization code exchange & userinfo
│   ├── server/            # HTTP server, handlers & HTML templates
│   └── worker/            # Background token refresh worker

CLI Commands​

First-Time Setup​

./oauth2-db-state setup

Prompts interactively for OAuth client credentials and Looker instance settings, generates an ENCRYPTION_KEY in .env, discovers OIDC endpoints, and initializes oauth2_state.db.

Start Server and Background Worker​

./oauth2-db-state serve

Manual Token Refresh​

# Refresh all tokens expiring within threshold
./oauth2-db-state refresh

# Force refresh ALL stored user tokens regardless of expiration
./oauth2-db-state refresh -force

# Force refresh a specific Looker user ID
./oauth2-db-state refresh <looker_user_id>
# or
./oauth2-db-state refresh -user <looker_user_id>

Expire User Token (For Testing Background Worker)​

./oauth2-db-state expire-token <looker_user_id>

Sets the user's token expires_at timestamp in SQLite and Looker to 5 minutes in the past while preserving valid token credentials, allowing you to test background worker auto-refresh without waiting.

Invalidate User Token​

./oauth2-db-state invalidate-token <looker_user_id>

Sets the user's access_token to "__invalidated__" and expiration timestamp to 1 in SQLite, then syncs "__invalidated__" to Looker via create_oauth_application_user_state.

Test Looker API Connection​

./oauth2-db-state test-looker

List OAuth Connections and App IDs​

./oauth2-db-state connections

Queries Looker for database connections configured for OAuth and lists registered External OAuth Applications:

Found 1 OAuth Database Connection(s):

  • Connection Name:     bfw-oauth
    Dialect:             bigquery_standard_sql
    LOOKER_OAUTH_APP_ID: 3

Found 1 Registered External OAuth Application(s):

  • LOOKER_OAUTH_APP_ID: 3
    App Name:            lkr-dev-testing
    Dialect:             bigquery_standard_sql
    Client ID:           599613958943-ftrl8f9d4qoochj7i06a0neipdrsg62t.apps.googleusercontent.com

Environment Variables (.env)​

VariableDescriptionDefault
ENCRYPTION_KEYSecret key for token encryptionRequired
HOSTBind address for HTTP server (0.0.0.0 for containers)0.0.0.0
PORTHTTP server port8080
DB_PATHPath to SQLite database fileoauth2_state.db
OAUTH_CLIENT_IDOAuth provider client ID""
OAUTH_CLIENT_SECRETOAuth provider client secret""
OAUTH_ISSUER_URLOAuth provider issuer / base URL""
OAUTH_AUTH_URLOAuth authorization endpoint URL""
OAUTH_TOKEN_URLOAuth token endpoint URL""
OAUTH_USERINFO_URLOAuth userinfo endpoint URL""
OAUTH_REDIRECT_URIOAuth callback redirect URL""
OAUTH_SCOPESSpace-separated OAuth scope string""
LOOKER_BASE_URLLooker instance base URLhttps://instance.cloud.looker.com
LOOKER_CLIENT_IDLooker API 4.0 client ID""
LOOKER_CLIENT_SECRETLooker API 4.0 client secret""
LOOKER_OAUTH_APP_IDExternal OAuth application ID in Looker"1"
REFRESH_INTERVAL_SECONDSBackground worker check interval in seconds60
REFRESH_WINDOW_SECONDSToken refresh threshold window in seconds600 (10 minutes)

BigQuery & Google OAuth Scopes​

When configuring OAUTH_SCOPES for BigQuery OAuth integration with Looker, include the following space-separated scopes:

  • Include https://www.googleapis.com/auth/bigquery or https://www.googleapis.com/auth/cloud-platform to grant access for Looker to execute BigQuery queries on the user's behalf.
  • Include https://www.googleapis.com/auth/userinfo.email to allow oauth2-db-state to fetch the user's email address from the Google userinfo endpoint and resolve their Looker User ID automatically.
  • Include https://www.googleapis.com/auth/userinfo.profile to grant access to basic Google profile details.

Example .env configuration:

OAUTH_SCOPES="https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile"

Refresh Token Handling Across OAuth Providers​

Provider Differences​

  • Google OAuth 2.0 refresh tokens do not expire by default unless revoked by the user, inactive for 6 months, or if the limit of 100 active refresh tokens per user/app is reached. Google requires access_type=offline and prompt=consent in the auth URL to issue a refresh token during code exchange. User resolution uses https://openidconnect.googleapis.com/v1/userinfo with scope https://www.googleapis.com/auth/userinfo.email.
  • Okta, Auth0, and Azure AD (Entra ID) configure refresh token lifetimes in the provider admin console. When providers enforce Refresh Token Rotation (RTR), oauth2-db-state updates SQLite with the newly returned refresh token during each refresh cycle.
  • Snowflake External OAuth relies on external identity providers (such as Okta, Azure AD, or PingFederate). oauth2-db-state executes the authorization code grant with the IdP and stores encrypted tokens for Looker synchronization.
  • Trino Okta and Starburst integrate via Okta or OIDC discovery endpoints and handle token rotation according to Okta security policies.

Refresh Token Lifetimes and Worker Configuration​

  • Access token expiration (expires_in) is returned directly by the OAuth provider (typically 3600 seconds).
  • Refresh token lifetimes are set inside the OAuth provider's admin console, not in API request parameters.
  • Background refresh timing is controlled by REFRESH_INTERVAL_SECONDS (database scan frequency) and REFRESH_WINDOW_SECONDS (lead time before expiration to initiate refresh).

Quick Start​

Build and Run Tests​

go test -v ./...
CGO_ENABLED=0 go build -o oauth2-db-state ./cmd/oauth2-db-state

Local Development & Hot Reloading​

An .air.toml file is configured for hot reloading when .go, .html, or .css files change:

go run github.com/air-verse/air@latest

Public Tunneling for OAuth Callbacks (cloudflared)​

Development testing with HTTPS callbacks can use Cloudflare Tunnel:

# Start dev server and Cloudflare Tunnel concurrently:
make dev-tunnel

# Or run just the tunnel alongside an existing server:
./tunnel.sh

Copy the generated https://<subdomain>.trycloudflare.com URL and set OAUTH_REDIRECT_URI in .env to https://<subdomain>.trycloudflare.com/oauth/callback.

Local Setup Steps​

  1. Run initial setup and endpoint discovery:

    ./oauth2-db-state setup --discover https://accounts.google.com

  2. Start dev server and public tunnel if testing remote callbacks:

    make dev-tunnel

  3. Add the redirect URI (http://127.0.0.1:8080/oauth/callback or your tunnel URL) to authorized redirect URIs in your OAuth provider console, and update OAUTH_CLIENT_ID, OAUTH_CLIENT_SECRET, and OAUTH_REDIRECT_URI in .env.

  4. Start the server:

    ./oauth2-db-state serve

    Access the web interface at http://127.0.0.1:8080/ and view active sessions at http://127.0.0.1:8080/status.

Known Issues​

Looker /account Page Buttons​

On Looker's /account page, the standard Reauthorize and Logout buttons for external database OAuth connections do not display and will show as Login instead. This is a known Looker bug.

If a new Looker user attempts to use a database with OAuth without using this application first, they may see a Login button that links to Looker's default database authorization URL rather than this hosted application. Users should authenticate through this hosted application first to set up their OAuth state.

Deployment​

Resource Requirements & Free Tier Sizing​

oauth2-db-state is a lightweight application using a local SQLite database. It has minimal resource overhead and can comfortably run on cloud provider free-tier VMs or small container instances:

  • CPU: 0.25 – 1 vCPU
  • RAM: 256 MB – 512 MB (1 GB recommended for large user counts with concurrent background refresh jobs)
  • Disk: < 1 GB (SQLite database size scales with the number of active users, ~1–2 KB per token record)

Free Tier Compatibility:

  • Google Cloud Platform (GCP): Fully compatible with GCP Compute Engine e2-micro Always Free instance (1 vCPU, 1 GB RAM).
  • Amazon Web Services (AWS): Compatible with AWS EC2 t2.micro / t3.micro Free Tier instances.
  • Google Cloud Run: Fits within Cloud Run free tier allowances (2 million requests/month, 360,000 GB-seconds memory).

SQLite Storage Requirement in Containers​

When deploying to serverless platforms (such as Google Cloud Run or AWS Fargate), mount a persistent volume (e.g. Cloud Storage FUSE, Filestore/NFS, or EFS) for the database directory (DB_PATH). Ephemeral container storage will erase SQLite token records across restarts or scaling events.

Google Cloud Run​

Build and deploy using Cloud Build and Cloud Run with a GCS bucket or Filestore volume mount:

gcloud builds submit --config=cloudbuild.yaml

gcloud run deploy oauth2-db-state \
  --image us-central1-docker.pkg.dev/lkr-dev-production/oauth2-db-state/server:latest \
  --region us-central1 \
  --platform managed \
  --port 8080 \
  --add-volume name=sqlite-storage,type=cloud-storage,bucket=YOUR_GCS_BUCKET \
  --add-volume-mount volume=sqlite-storage,mount-path=/app/data \
  --set-env-vars DB_PATH=/app/data/oauth2_state.db,HOST=0.0.0.0

Docker Deployment​

Public Container Image (lkr-dev-production)​

docker pull us-central1-docker.pkg.dev/lkr-dev-production/oauth2-db-state/server:latest

docker run -d \
  -p 8080:8080 \
  -v $(pwd)/data:/app/data \
  -e DB_PATH=/app/data/oauth2_state.db \
  -e HOST=0.0.0.0 \
  --name oauth2-db-state \
  us-central1-docker.pkg.dev/lkr-dev-production/oauth2-db-state/server:latest

Local Container Build​

docker build -t oauth2-db-state .

docker run -d \
  -p 8080:8080 \
  -v $(pwd)/data:/app/data \
  -e DB_PATH=/app/data/oauth2_state.db \
  -e HOST=0.0.0.0 \
  --name oauth2-db-state \
  oauth2-db-state

Linux VM Deployment (systemd)​

  1. Clone the repository, build the binary, and generate the initial .env file:

    git clone https://github.com/lkrdev/oauth2-db-state.git
    cd oauth2-db-state
    go build -o oauth2-db-state ./cmd/oauth2-db-state
    ./oauth2-db-state setup

  2. Copy the binary and .env file to /opt/oauth2-db-state:

    sudo mkdir -p /opt/oauth2-db-state/data
    sudo cp oauth2-db-state /opt/oauth2-db-state/
    sudo cp .env /opt/oauth2-db-state/.env
    sudo chmod 600 /opt/oauth2-db-state/.env

    [!TIP] For production deployments, store secrets in Google Secret Manager, AWS Secrets Manager, HashiCorp Vault, or systemd credential storage instead of a flat .env file.

  3. Create systemd unit file /etc/systemd/system/oauth2-db-state.service:

    [Unit]
    Description=OAuth2 DB State Service for Looker
    After=network.target

    [Service]
    Type=simple
    User=root
    WorkingDirectory=/opt/oauth2-db-state
    ExecStart=/opt/oauth2-db-state/oauth2-db-state serve
    EnvironmentFile=/opt/oauth2-db-state/.env
    Restart=always
    RestartSec=5s

    [Install]
    WantedBy=multi-user.target

  4. Enable and start the service:

    sudo systemctl daemon-reload
    sudo systemctl enable oauth2-db-state
    sudo systemctl start oauth2-db-state

  5. Check service status and inspect live structured logs:

    sudo systemctl status oauth2-db-state
    sudo journalctl -u oauth2-db-state -f