Worker Service Database Connection Configuration

Overview

This document describes the database connection configuration for the worker service. The system uses a HikariDataSource connection pool to manage connections to the primary PostgreSQL database. This configuration is critical for the worker's performance, resilience, and ability to authenticate with the database.

The configuration supports two primary authentication methods:

1. IAM Authentication: The default mode for cloud-native deployments. It uses the Cloud SQL Java connector to manage authentication via IAM, including automatic token refresh and mTLS tunnel management.
2. Password Authentication: A traditional mode using a username and password, typically for on-premises or local development environments (e.g., Docker Compose).

A key feature of this component is its resilience to network partitions. It applies socket timeouts to prevent the connection pool from becoming exhausted by stale connections, allowing for self-healing once network connectivity is restored.

Finally, every connection obtained from the pool is wrapped by a TenantBindingDataSource. This wrapper associates the connection with the current tenant context, a foundational step for implementing multi-tenancy features like row-level security.

How It Works

The DataSource bean is initialized by the application framework at startup. The configuration logic proceeds through the following steps:

1. User Validation: It first validates that a database user (S2R_DB_USER) is configured. If not, the application will fail to start.
2. Authentication Mode Selection: The system determines which authentication mode to use. Password authentication is forcibly enabled if the S2R_DB_IAM_AUTH_ENABLED environment variable is set to false. Otherwise, the choice depends on the authMode configuration property.
3. Pool Initialization: A HikariDataSource instance is created with the pool name s2r-worker-hikari. The pool is sized with a maximum of 16 connections to handle burst workloads, such as processing a backlog of events after a service recovery.
4. Resilience and Timeout Configuration: Several timeout and resilience parameters are set. Most importantly, a socket timeout is applied to each connection. This prevents connections from hanging indefinitely during a network partition, which could otherwise exhaust the pool. The leak detection threshold is dynamically set to twice the socket timeout to accurately identify connections that are held for an anomalously long time.
5. JDBC URL Construction:
   • For IAM auth, the configuration builds a JDBC URL for the Cloud SQL connector, specifying the database name. It adds data source properties for the socket factory, Cloud SQL instance name, and IP type. No password is required, as the connector handles token-based authentication.
   • For Password auth, it constructs a standard jdbc:postgresql:// URL using the host, port, database name, and SSL mode. It requires the S2R_DB_HOST and S2R_DB_PASSWORD to be configured.
6. Tenant Context Binding: Before returning the data source, it is wrapped in a TenantBindingDataSource. This wrapper ensures that for every borrowed connection, the session-level parameter app.tenant_id is set to the ID of the current tenant. This is active in both authentication modes.

The following diagram illustrates the logic for selecting the authentication mode and constructing the final DataSource.

flowchart TD
    subgraph "Authentication Mode Selection"
        A[Start: dataSource bean creation] --> B{S2R_DB_IAM_AUTH_ENABLED is 'false'?};
        B -- Yes --> C[Password Auth];
        B -- No / Unset --> D{authMode property is 'iam'?};
        D -- Yes --> E[IAM Auth];
        D -- No --> C;
    end

    subgraph "DataSource Construction"
        E -- "Requires S2R_DB_INSTANCE_CONNECTION_NAME" --> F[Configure for Cloud SQL Connector];
        C -- "Requires S2R_DB_HOST" --> G[Configure for standard JDBC];
        F --> H[Create HikariDataSource];
        G --> H;
        H --> I[Wrap with TenantBindingDataSource];
        I --> J[End: DataSource is ready];
    end

Authentication Modes

You can configure the worker to use either IAM or password-based database authentication.

IAM Authentication (Cloud-based)

This is the default and recommended method for cloud deployments. It relies on the Google Cloud SQL Java connector (postgres-socket-factory), which handles mTLS and automatically refreshes short-lived IAM database credentials.

To enable IAM authentication:

1. Ensure the S2R_DB_IAM_AUTH_ENABLED environment variable is either unset or not set to false.
2. Set the authMode configuration property to iam.
3. Provide the S2R_DB_INSTANCE_CONNECTION_NAME.

When this mode is active, the connector manages the secure tunnel to the database, so S2R_DB_HOST and S2R_DB_PASSWORD are not used.

Password Authentication (On-premises/Development)

This mode uses a standard username and password. It is intended for local development (e.g., with Docker Compose) or on-premises deployments where IAM is not available.

To enable password authentication, set the environment variable S2R_DB_IAM_AUTH_ENABLED=false.

Configuration Reference

The database connection is configured through a combination of an environment variable and properties from the central DbProperties configuration. The property names listed below correspond to environment variables (e.g., user corresponds to S2R_DB_USER).

Connection Pool Parameters

The worker service uses a HikariCP connection pool with specific tuning parameters. These are set internally and are not directly configurable but are important for understanding the service's behavior.

Troubleshooting

Startup Failures

The application will fail to start with an IllegalStateException under the following conditions:

• S2R_DB_USER is not configured.
• IAM authentication is enabled (authMode is iam), but S2R_DB_INSTANCE_CONNECTION_NAME is not configured.
• Password authentication is enabled, but S2R_DB_HOST is not configured.

Connection Pool Exhaustion during Network Issues

In the event of a network partition between the worker and the database, active connections could previously become stuck waiting for a response, eventually exhausting the pool.

This configuration mitigates the issue by setting a socketTimeout on each database connection. If a connection is unresponsive for longer than this timeout, it will be terminated with an exception. This allows the connection to be returned to the pool and for the pool to self-heal by creating new connections once network connectivity is restored.

See Also

• Admin API Database Connection Configuration: The Admin API uses a similar database configuration, but with different connection pool sizing tailored to its workload.
• Tenant Context Propagation: The TenantBindingDataSource is a key component of the platform's multi-tenancy strategy. It ensures that all database operations are executed within the correct tenant's context.

All SOAP-to-REST docs