Worker Service Database Connection Configuration

Overview

This document describes the database DataSource configuration for the worker service. This component establishes and manages a pool of connections to the primary PostgreSQL database, ensuring resilient, performant, and secure data access for all worker operations.

The configuration provides two primary authentication methods:

1. IAM Authentication: The default method for cloud-native deployments. It uses the Cloud SQL Java connector to manage secure mTLS tunnels and automatically refresh short-lived IAM database credentials.
2. Password Authentication: A traditional method using a static username and password, suitable for on-premises or containerized environments where IAM is not available.

Beyond authentication, this component is responsible for:

• Connection Pooling: Manages a HikariCP connection pool tuned for worker-specific workloads, such as handling bursts of activity during data replay events.
• Network Resilience: Implements socket-level timeouts to prevent the entire connection pool from becoming unresponsive during network partitions, allowing for automatic recovery.
• Multi-Tenancy: Integrates with the platform's tenancy model by binding a tenant identifier to each connection and enforcing access controls in multi-tenant deployments.

All worker processes that interact with the database, including background job processors and event listeners, use the DataSource configured here.

How it works

The system initializes a single DataSource bean on startup. The configuration process follows several key steps: authentication mode selection, pool configuration, and multi-tenancy enforcement.

flowchart TD
    subgraph Initialization
        A[Start] --> B{Check S2R_DB_IAM_AUTH_ENABLED};
        B -- "Is 'false'" --> PwAuth[Use Password Auth];
        B -- "Is not 'false' (default)" --> C{Check authMode property};
        C -- "Is 'iam'" --> IamAuth[Use IAM Auth];
        C -- "Is not 'iam'" --> PwAuth;
    end

    subgraph Configuration
        IamAuth --> D[Configure Cloud SQL Connector];
        PwAuth --> E[Configure Standard JDBC];
        D --> F[Set Pool Properties];
        E --> F;
        F --> G[Apply Socket Timeouts];
        G --> H[Set Dynamic Leak Detection];
    end

    subgraph Finalization
        H --> I[Wrap for Tenant ID Binding];
        I --> J[Install SaaS Security Gate];
        J --> K[DataSource Ready];
    end

Authentication Mode Selection

The choice between IAM and password authentication is determined by the S2R_DB_IAM_AUTH_ENABLED environment variable and the authMode configuration property.

• IAM Authentication is used if S2R_DB_IAM_AUTH_ENABLED is not set to false (case-insensitive) AND the authMode property is set to iam.
• Password Authentication is used in all other cases. This includes explicitly setting S2R_DB_IAM_AUTH_ENABLED to false or using an authMode other than iam.

A log message S2R-CFG: IAM DB auth disabled — using password auth (worker) is emitted when password authentication is activated via the environment variable.

IAM Authentication

When IAM authentication is active, the system configures the connection pool to use the Cloud SQL Java connector (com.google.cloud.sql.postgres.SocketFactory).

1. The JDBC URL is set to jdbc:postgresql:///<database_name>, omitting the host and port.
2. The connector is configured with the cloudSqlInstance connection name (e.g., project:region:instance).
3. The connector establishes a secure mTLS tunnel to the specified instance.
4. It automatically handles fetching and refreshing IAM database access tokens, so a static password is not required.
5. The IP address type for the connection (e.g., PRIVATE or PUBLIC) can be specified via the ipType property.

A log message S2R-CFG: Cloud SQL connector IAM auth (worker) instance=... ipType=... confirms this mode is active.

Password Authentication

When password authentication is active, the system configures a standard JDBC connection.

1. It requires the database host, port, name, and password.
2. It constructs a JDBC URL in the format jdbc:postgresql://<host>:<port>/<name>?sslmode=<ssl_mode>.
3. The sslmode defaults to require if not otherwise specified.

Connection Pooling and Resilience

The service uses a HikariDataSource instance with settings tailored for the worker's workload.

• Pool Size: The pool is configured with a maximum size of 16 connections (maximumPoolSize) and a minimum of 4 idle connections (minimumIdle). This provides headroom for high-load events like data replay storms.
• Timeouts: Standard connection timeouts are set to ensure timely acquisition of connections.
• Resilience: To prevent pool exhaustion during network outages, a socket read timeout (socketTimeoutSeconds) is applied to each underlying database connection. This ensures that a connection borrowed from the pool will not hang indefinitely on a dead socket, allowing the application to handle the error and the pool to self-heal once network connectivity is restored.

• Leak Detection: The connection leak detection threshold (leakDetectionThreshold) is dynamically set to twice the value of the socketTimeoutSeconds. This avoids spurious "leak" warnings for long-running but legitimate queries, ensuring that logged warnings are more likely to indicate a genuine problem, such as a connection that is stuck or not being closed properly. If socketTimeoutSeconds is 0, leak detection is disabled.

Multi-Tenancy Support

After the base connection pool is configured, it is wrapped to support multi-tenancy.

1. Tenant ID Binding: The DataSource is wrapped in a TenantBindingDataSource. This wrapper ensures that for every connection borrowed from the pool, the session variable app.tenant_id is set to the ID of the current tenant in context. This mechanism is foundational for enabling database-level Row-Level Security (RLS). A log message S2R-TENANCY-DB binding=app.tenant_id ... is emitted.

2. SaaS Security Gate: A "SaaS gate" is installed on the DataSource. In a multi-tenant deployment, this gate prevents database operations from executing if no tenant context is bound to the connection. This acts as a fail-safe mechanism, ensuring that code cannot accidentally access data without proper tenant scoping. The gate's status is determined by SettingBackedSaasCapability, which relies on a cached value and does not query the database during the check. A log message S2R-TENANCY-DB saas-unbound-gate installed=true ... confirms its installation.

Configuration Reference

Configuration is provided through a combination of environment variables and a DbProperties object, which is typically populated from the application's configuration files or environment variables.

Internal Pool Settings

The following HikariCP settings are hardcoded in the worker configuration to optimize for its specific workload. They are not user-configurable.

Troubleshooting

Startup Failures

The service will fail to start if required configuration is missing. Check the logs for one of the following IllegalStateException messages:

• S2R_DB_USER must be configured: The user property is missing or empty.
• S2R_DB_INSTANCE_CONNECTION_NAME required for IAM auth: IAM authentication is enabled, but the instanceConnectionName property is missing.
• S2R_DB_HOST must be configured for password auth: Password authentication is enabled, but the host property is missing.

Runtime Errors

• SaaS Gate Failure: In a multi-tenant deployment, if an operation fails with an error related to S2R-TENANCY-UNBOUND, it means a database connection was used without a tenant context being set. This is a security measure to prevent cross-tenant data access. The calling code must be wrapped in a tenant context (e.g., via TenantContext.runAs).
• Connection Leak Logs: If you see log messages about a "connection leak", it means a connection was held open for longer than the leakDetectionThreshold. This indicates a slow database query or an application code path that is not closing a database connection in a timely manner.

See Also

• Admin API Data Source Configuration: For the original rationale behind the authentication and resilience patterns, see the documentation for the Admin API's data source configuration.
• Platform Data Model: For details on the app.tenant_id session variable and its role in the multi-tenancy architecture, refer to the platform's data model documentation.

All SOAP-to-REST docs