Database Connection Pool Configuration

Overview

This document describes the database connection pool configuration for the Admin API service. The system uses HikariCP for connection pooling and provides robust, configurable data source management for connecting to a PostgreSQL database.

Two distinct connection pools are configured:

1. Application Pool: A general-purpose pool for all runtime API operations. It is configured for high availability and resilience against network issues.
2. Migration Pool: A specialized, short-lived pool used exclusively by Flyway for database schema migrations. Its configuration is optimized for the safety of long-running Data Definition Language (DDL) operations.

The configuration supports two primary authentication methods, allowing for flexible deployment across different environments:

• IAM Authentication: The default method, designed for specific cloud environments. It uses a provider's socket factory to manage credentials and secure transport automatically.
• Password Authentication: A standard method using a username and password, suitable for on-premises, containerized, or multi-cloud deployments.

Additionally, every connection from both pools is automatically tagged with a tenant identifier to support multi-tenancy at the database level.

How It Works

The system defines two DataSource beans, dataSource and flywayDataSource, to manage database connections.

Connection Pools

Application Pool (dataSource)

This is the primary connection pool used by the service for handling all API requests and background tasks during normal operation. It is created by the dataSource() bean.

Key characteristics:

• Resilience: It is configured with a socketTimeout to prevent active connections from being permanently stuck during a network partition. If a read from the database socket hangs, the timeout will trigger, allowing the connection to be closed and the pool to recover.
• Sizing: The pool size is dynamically configured based on maxPoolSize and minIdle properties, with a minimum of 8 connections for maximumPoolSize and 1 for minimumIdle.
• Lifecycle: Connections have a maximum lifetime of 30 minutes (maxLifetime) and are checked for liveness every 10 minutes (keepaliveTime) to maintain pool hygiene.

Migration Pool (flywayDataSource)

This dedicated pool is used only when the service starts up to perform database schema migrations via Flyway. It is created by the flywayDataSource() bean and is marked with @FlywayDataSource.

Key characteristics:

• No Socket Timeout: This pool is deliberately configured with an infinite socket timeout. Long-running migration operations, such as CREATE INDEX CONCURRENTLY, can legitimately block for extended periods. A socket timeout could interrupt these operations mid-flight, potentially leaving the database schema in an inconsistent or invalid state and blocking future migrations.
• Fixed Sizing: The pool has a fixed maximum size of 4 connections. This is the minimum required to prevent deadlocks, as Flyway may use multiple connections simultaneously for the migration itself, the schema history table, and locking.
• Short-Lived: The pool is configured with a minimumIdle of 0 and an idleTimeout of 1 minute. This ensures that once migrations are complete, all connections are promptly closed, and the pool does not consume resources during normal application runtime.

Tenant ID Propagation

Both the application and migration pools are wrapped by a TenantBindingDataSource. This wrapper intercepts each connection as it is borrowed from the pool and executes a command to set the app.tenant_id session variable. This ensures that all database operations, whether from API requests or schema migrations, are associated with the correct tenant context, enabling features like Row-Level Security (RLS). The default tenant ID is used if no specific context is available.

Authentication Flow

The buildPool method determines which authentication strategy to use based on configuration properties and environment variables.

flowchart TD
    A[Start Pool Creation] --> B{S2R_DB_IAM_AUTH_ENABLED is 'false'?};
    B -- Yes --> P[Use Password Auth];
    B -- No / Not Set --> C{authMode property is 'iam'?};
    C -- Yes --> I[Use IAM Auth];
    C -- No --> P;

    subgraph IAM Auth
        I --> I1[Verify S2R_DB_INSTANCE_CONNECTION_NAME is set];
        I1 --> I2[Build JDBC URL for socket factory];
        I2 --> I3[Configure Cloud SQL Socket Factory];
    end

    subgraph Password Auth
        P --> P1[Verify S2R_DB_HOST is set];
        P1 --> P2[Build standard host:port JDBC URL];
        P2 --> P3[Set password from S2R_DB_PASSWORD];
    end

Authentication Modes

You can select the authentication mode by setting the S2R_DB_IAM_AUTH_ENABLED environment variable or the authMode property.

IAM Authentication

This is the default authentication method. It relies on a specialized JDBC Socket Factory (com.google.cloud.sql.postgres.SocketFactory) to handle secure connectivity.

Mechanism:

• The socket factory automatically creates and manages short-lived IAM access tokens.
• It establishes a secure mTLS tunnel to the database instance.
• The JDBC URL does not contain a host or SSL mode, as the socket factory manages the transport.
• No password is required in the configuration, as the factory provides credentials dynamically.

To use this mode, S2R_DB_IAM_AUTH_ENABLED must not be set to false, and the authMode property must be iam.

Example JDBC URL:

jdbc:postgresql:///<database_name>

Password Authentication

This mode uses a standard username and password to connect to the database. It is intended for environments where IAM authentication is not available, such as local development, containerized deployments, or other cloud providers.

Mechanism:

• This mode is activated by setting the environment variable S2R_DB_IAM_AUTH_ENABLED to false or by setting the authMode property to password.
• It requires a standard JDBC URL containing the database host, port, and sslmode.
• The database password must be provided.

Example JDBC URL:

jdbc:postgresql://<host>:<port>/<database_name>?sslmode=require

Configuration Reference

The following properties and environment variables are used to configure the database connection pools. Properties are accessed via the DbProperties configuration object.

Troubleshooting

Configuration Errors

The service will fail to start if required configuration is missing.

• java.lang.IllegalStateException: S2R_DB_USER must be configured
  • Cause: The database username has not been provided.
• java.lang.IllegalStateException: S2R_DB_INSTANCE_CONNECTION_NAME required for IAM auth
  • Cause: IAM authentication is enabled, but the unique instance connection name has not been provided.
• java.lang.IllegalStateException: S2R_DB_HOST must be configured for password auth
  • Cause: Password authentication is enabled, but the database host has not been provided.

Connection Pool Issues

• Application Hangs During Network Partition:

  • Symptom: The service becomes unresponsive and cannot serve requests after a temporary network issue between the service and the database.
  • Cause: Before the introduction of socketTimeout, active connections could get stuck in a read operation on a dead socket. The pool would exhaust all connections and be unable to recover even after the network was restored.
  • Resolution: The socketTimeoutSeconds property on the application pool ensures that stuck connections are eventually terminated, allowing the pool to self-heal by creating new, healthy connections.

• Migration Deadlocks or Failures:

  • Symptom: The service fails to start, with logs indicating a timeout while waiting for a connection from the Flyway pool.
  • Cause: The Flyway connection pool is too small. Flyway requires multiple connections to perform a migration (one for the lock, one for the schema history table, and one for the migration DDL). A pool size of 2 can lead to deadlock.
  • Resolution: The migration pool size is fixed at 4 to provide enough connections for Flyway to operate without deadlocking.

All SOAP-to-REST docs