Runtime Database Connection Configuration

Overview

This document describes the configuration of the primary database DataSource for the runtime service. This component is responsible for creating and managing a pool of database connections used by the application for all its database operations.

The configuration establishes a HikariDataSource connection pool, tuned for performance and resilience. It supports two primary authentication methods:

1. IAM Authentication: The default method for cloud-native deployments. It uses the official Google Cloud SQL Java connector to securely connect to a PostgreSQL database using Identity and Access Management (IAM) credentials. The connector handles mTLS and automatic token refresh.
2. Password Authentication: A standard method for on-premises or local development environments (e.g., Docker Compose). It uses a traditional host, port, username, and password combination.

Beyond connection management, this configuration integrates critical features for multi-tenancy and security. It wraps the data source to bind a tenant context to each connection, a prerequisite for row-level security (RLS). It also enforces RLS by switching the database role for each connection and includes a health check to verify that RLS is active.

How it Works

The system initializes the database DataSource bean through a sequence of configuration and wrapping steps. This process ensures that connections are resilient, secure, and correctly configured for a multi-tenant environment.

flowchart TD
    subgraph "1. Load Configuration"
        A[Environment Variables & Application Properties]
    end

    subgraph "2. Initialize Connection Pool"
        B(Create HikariDataSource)
        C{IAM Auth<br>Enabled?}
        D[Configure for IAM Auth<br>(Cloud SQL Connector)]
        E[Configure for Password Auth<br>(JDBC URL)]
        F[Set Common Pool Properties<br>(size, lifetime)]
        G[Set Resilience Timeouts<br>(socket, leak detection)]
        H[Set RLS Role-Switching SQL<br>(connectionInitSql)]
    end

    subgraph "3. Apply Multi-Tenancy Wrappers"
        I(Wrap with TenantBindingDataSource)
        J(Install SaaS Unbound Connection Gate)
    end
    
    subgraph "4. Verify at Startup"
        K(ApplicationReadyEvent)
        L[Probe RLS Status]
        M[Update 'rlsRole' Health Check]
    end

    A --> B
    B --> F
    F --> C
    C -- Yes --> D
    C -- No --> E
    D --> G
    E --> G
    G --> H
    H --> I
    I --> J
    J --> Z[DataSource Bean Ready]

    K --> L
    L --> M

1. Configuration Loading: The process starts by reading database properties, which are typically supplied through environment variables.
2. Authentication Mode Selection: The system checks the S2R_DB_IAM_AUTH_ENABLED environment variable and the authMode property. If IAM is enabled and the mode is iam, it proceeds with IAM configuration. Otherwise, it falls back to password-based authentication.
3. Pool Initialization: A HikariDataSource instance is created. It is configured with common pool settings (e.g., maximumPoolSize, maxLifetime) and crucial resilience settings. A socketTimeout is applied to prevent borrowed connections from getting stuck on dead sockets during network partitions, and a leakDetectionThreshold is set to detect anomalously long-running operations.
4. Authentication Configuration:
   • For IAM auth, the configuration uses the com.google.cloud.sql.postgres.SocketFactory and sets properties like cloudSqlInstance. It does not provide a password, as the connector manages token-based authentication.
   • For password auth, it constructs a standard JDBC URL with the host, port, and database name, and sets the password.
5. RLS Role Switching: The connectionInitSql property is set on the data source. This SQL statement runs every time a new connection is created, switching the session's role to s2r_runtime_rls. This ensures that row-level security policies are applied to all queries from the runtime service.
6. Tenant Context Binding: The fully configured HikariDataSource is wrapped in a TenantBindingDataSource. This wrapper intercepts connection borrowing to associate the current tenant's ID with the database session via the app.tenant_id setting.
7. SaaS Gate Installation: After the application context is initialized, a "SaaS gate" is installed on the DataSource. In a SaaS deployment, this gate acts as a fail-closed mechanism, preventing any database operation from proceeding on a connection that does not have a tenant ID bound.
8. RLS Health Check: Once the application is ready, a probe runs to verify that RLS is active for the connections in the pool. The result of this probe updates the rlsRole health contributor, which is exposed via the actuator health endpoint. In a SaaS environment, if RLS is not active, the service will report as DOWN.

Configuration

The database connection is configured using a set of properties, typically provided as environment variables.

Primary Configuration

IAM Authentication Properties

These properties are used when IAM authentication is active.

Password Authentication Properties

These properties are used when IAM authentication is disabled.

Resilience Properties

Connection Pool Settings

The HikariCP connection pool is configured with the following static values for performance and stability.

Row-Level Security (RLS) Integration

To ensure multi-tenant data isolation, the runtime service relies on the database's row-level security capabilities. The DataSource configuration is instrumental in enforcing RLS.

Role Switching on Connect

Every connection borrowed from the pool executes an initial SQL statement to switch its role to s2r_runtime_rls. The application's login user may own database tables, which would cause it to bypass RLS policies. By switching to a dedicated, non-login role that does not own tables but has the necessary privileges, RLS policies are guaranteed to apply to all queries.

The SQL statement is designed to be safe and resilient:

• It first checks if the s2r_runtime_rls role exists.
• It then checks if the session user has been granted membership in that role.
• If both are true, it attempts to SET ROLE.
• If the SET ROLE command is refused by the database, it raises a WARNING but does not throw an error, preventing the connection pool from failing.

DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 's2r_runtime_rls') THEN
    IF pg_catalog.pg_has_role(session_user, 's2r_runtime_rls', 'MEMBER') THEN
      BEGIN EXECUTE 'SET ROLE s2r_runtime_rls';
      EXCEPTION WHEN insufficient_privilege THEN
        RAISE WARNING 'S2R-RLS-ROLE SET ROLE s2r_runtime_rls refused: %', SQLERRM;
      END;
    END IF;
  END IF;
END $$

RLS Health Indicator

The service includes a health contributor named rlsRole that is exposed via the actuator health endpoint (e.g., /actuator/health). At application startup, this component probes the database to confirm that RLS is active on the table public.s2r_service for the connections in the pool.

• SaaS Deployments: If the probe determines RLS is inactive or the probe itself fails, the rlsRole contributor will report DOWN. This causes the application's overall readiness status to become DOWN, preventing it from receiving traffic.
• Non-SaaS Deployments: If RLS is inactive, the contributor logs a WARN message, but the service health is not affected.

Troubleshooting

See Also

• Admin API Database Configuration: The Admin API service uses a nearly identical configuration for its database connection pool.
• Health and Monitoring Endpoints: The status of the RLS configuration can be checked via the rlsRole contributor at the actuator health endpoint.
• Multi-Tenancy Architecture: The TenantBindingDataSource and SettingBackedSaasCapability components are central to the platform's multi-tenancy implementation.

All SOAP-to-REST docs