Admin API Database Connection Configuration

Overview

This document describes the database connection pool configuration for the Admin API service. The system establishes and manages two distinct connection pools to the PostgreSQL database: one for general application use and another dedicated to database migrations.

The configuration supports two primary authentication methods:

1. IAM Authentication: The default mode for cloud-native deployments (e.g., GCP), using the Cloud SQL Java connector to manage credentials and secure transport automatically.
2. Password Authentication: A standard host-and-password method for on-premises, local development (e.g., Docker Compose), or other cloud environments.

This component is also responsible for critical operational concerns including connection resilience against network partitions, multi-tenancy enforcement through Row-Level Security (RLS), and health monitoring of the RLS configuration.

How it works

The system configures two HikariDataSource connection pools, each with a specific role and configuration profile.

Authentication Modes

The authentication mode is determined by the S2R_DB_IAM_AUTH_ENABLED environment variable and the authMode property.

flowchart TD
    subgraph "Authentication Mode Selection"
        A[Start] --> B{S2R_DB_IAM_AUTH_ENABLED is 'false'?};
        B -- Yes --> C[Use Password Authentication];
        B -- No (default) --> D{authMode property is 'iam'?};
        D -- Yes --> E[Use IAM Authentication];
        D -- No --> C;
    end

IAM Authentication (Default)

When IAM authentication is active, the system uses the official Google Cloud SQL Java connector (com.google.cloud.sql.postgres.SocketFactory). This connector handles the entire connection lifecycle:

• It automatically creates and refreshes short-lived IAM database access tokens.
• It establishes a secure mTLS tunnel to the database instance.

This approach eliminates the need for manual password rotation and avoids connection pool "eviction storms" that could occur with previous token refresh mechanisms. The JDBC URL does not specify a host or SSL mode, as the connector manages the transport layer.

Password Authentication

When S2R_DB_IAM_AUTH_ENABLED is set to false or the authMode property is set to password, the system falls back to traditional password-based authentication. It constructs a standard JDBC URL using the configured host, port, database name, and SSL mode, and authenticates with the provided database password. This path ensures portability across different environments.

Connection Pools

Two separate connection pools are configured to isolate application traffic from database migration operations.

Application Pool

The primary application pool (s2r-admin-api-hikari) handles all runtime API requests and background jobs. Its key characteristics are:

• Resilience: It is configured with a socketTimeout to prevent active connections from hanging indefinitely during a network partition. This allows the pool to recover by abandoning stalled socket reads.
• Row-Level Security (RLS): Upon acquiring a new connection, it executes an initialization SQL statement (connectionInitSql) to switch the database role to s2r_app_rls. This ensures that all application queries are subject to tenant-based RLS policies, as the primary login role is a table owner and would otherwise bypass them.
• Tenancy: It is wrapped by TenantBindingDataSource to automatically associate the current app.tenant_id with each connection. In SaaS deployments, a "gate" is installed that causes any attempt to use a connection without a tenant ID to fail, preventing cross-tenant data access.

Migration Pool

A dedicated pool (s2r-admin-api-flyway-hikari) is used exclusively by Flyway for database schema migrations.

• No Socket Timeout: This pool is intentionally configured without a socket timeout. DDL operations like CREATE INDEX CONCURRENTLY can legitimately block for long periods, and terminating them mid-flight can leave the database in an inconsistent state (e.g., an INVALID index).
• Ephemeral: The pool is configured with a small size (4 connections) and a short idle timeout (1 minute). This ensures that connections are held only during the migration process and are released shortly after, minimizing resource consumption during normal operation.
• Tenancy: It is also wrapped by TenantBindingDataSource to ensure migrations operate within the correct tenant context.

Row-Level Security and Health Checks

To enforce data isolation in multi-tenant environments, the platform relies on PostgreSQL's Row-Level Security.

1. Role Switching: The application pool's connectionInitSql attempts to SET ROLE s2r_app_rls. This is a non-privileged role that is subject to RLS policies. The SQL logic is designed to fail gracefully: if the role doesn't exist or the login user isn't a member, it does nothing or raises a warning, allowing the application to function with RLS bypassed.

2. Startup Probe: After the application starts, a health indicator named rlsRole probes the database to verify that RLS is active for the application pool. It checks the value of row_security_active('public.s2r_service').

3. Readiness State: The outcome of the probe affects the application's readiness.

   • In a SaaS deployment, if RLS is not active or the probe fails, the rlsRole health check reports DOWN. This prevents the instance from being marked as ready to receive traffic.
   • In a non-SaaS deployment, a failure only generates a WARN log, and the instance can still become ready.

This mechanism ensures that a misconfigured database in a SaaS environment cannot lead to data leakage.

Configuration

Configuration is managed through environment variables and corresponding DbProperties settings.

Environment Variables

Other Properties

These properties are typically configured via the same environment variable mapping used by Spring Boot (e.g., S2R_DB_MAX_POOL_SIZE for maxPoolSize).

Connection Initialization SQL

The application pool is configured with a connectionInitSql statement to enable Row-Level Security. This SQL is executed every time a new physical connection is created.

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

Health Contributor

The configuration provides one health contributor that is exposed via the actuator health endpoint.

• ID: rlsRole
• Description: Reports the status of Row-Level Security for the main application connection pool.
• Status:
  • UP: RLS is active on the application pool connections.
  • DOWN: In SaaS mode, this status is reported if RLS is not active or if the probe query fails. A DOWN status prevents the service from becoming ready.
  • UP (with details): In non-SaaS mode, this status is reported if RLS is not active. The service is considered healthy, but a warning is logged indicating that RLS is bypassed.

Troubleshooting

Configuration Errors

• S2R_DB_USER must be configured: The S2R_DB_USER environment variable is not set. This is a required property.
• S2R_DB_INSTANCE_CONNECTION_NAME required for IAM auth: IAM authentication is enabled, but the S2R_DB_INSTANCE_CONNECTION_NAME variable is missing.
• S2R_DB_HOST must be configured for password auth: Password authentication is enabled, but the S2R_DB_HOST variable is missing.

RLS Probe Failures

• S2R-RLS-ROLE app pool ... rlsActive=false: The RLS health probe determined that queries from the application pool are not being constrained by RLS policies. This typically means the login role is an owner of the tables.

  • In SaaS mode: This is a critical error. The log level is ERROR, and the rlsRole health check will report DOWN, preventing the service from accepting traffic.
  • In non-SaaS mode: This is a WARN condition. The service will function, but without the data isolation guarantees of RLS.
  • Fix: The log message includes the recommended fix: run the admin-api-app-rls-role.sql script with the appropriate app_owner_role and restart the service.

• S2R-RLS-ROLE probe failed: The SQL query used to check RLS status failed to execute.

  • In SaaS mode: This is an ERROR and will cause the rlsRole health check to report DOWN.
  • In non-SaaS mode: This is a WARN.

Unbound Tenant Access in SaaS

• S2R-TENANCY-UNBOUND error: In a SaaS deployment, code that attempts to borrow a database connection without a tenant ID in the current context will fail with this error. This is a security feature to prevent accidental access to default or incorrect tenant data. Ensure that all code paths (e.g., request handlers, scheduled jobs) properly bind a tenant context before accessing the database.

Connection Pool Issues

• Flyway Deadlocks on Startup: The migration pool requires a minimum of 4 connections. A smaller size can cause deadlocks during startup, as Flyway may hold multiple connections simultaneously for the migration, the schema history table, and locking.
• Application Unresponsive After Network Partition: Older versions without a socketTimeout on the application pool could become permanently wedged if a network partition occurred while connections were in use. The socketTimeoutSeconds property ensures that connections will eventually time out and be replaced, allowing the pool to self-heal.

All SOAP-to-REST docs