spring-boot: Explicit user/password for hikari/liquibase is ignored when using Docker Compose support

Spring Boot 3.2.5

The explicitly configured usernames and passwords are not used when using the Docker Compose support:

spring:
  datasource:
    hikari:
      username: example_rw
      password: example_rw
  liquibase:
    user: example_ow
    password: example_ow

they should not be overwritten by the one configured in compose.yaml:

services:
  db:
    environment:
      POSTGRES_USER: sa
      POSTGRES_PASSWORD: sa

Logs

$ ./gradlew bootRun
...
liquibase.database : Connected to sa@jdbc:postgresql://127.0.0.1:5432/example?ApplicationName=docker-compose-datasource-test
...
com.zaxxer.hikari.HikariConfig : jdbcUrl.........................jdbc:postgresql://127.0.0.1:5432/example?ApplicationName=docker-compose-datasource-test
...
com.zaxxer.hikari.HikariConfig : schema.........................."example"
...
com.zaxxer.hikari.HikariConfig : username........................"sa"
$ docker compose logs db -f

... POSTGRES_DB from environment is created with 'sa' - correct

db-1  | 2024-05-16 09:25:45.782 UTC [47] LOG:  connection received: host=[local]
db-1  | 2024-05-16 09:25:45.783 UTC [47] LOG:  connection authorized: user=sa database=postgres application_name=psql
db-1  | 2024-05-16 09:25:45.785 UTC [47] LOG:  statement: CREATE DATABASE "example" ;

... init scripts use 'sa' - correct

db-1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/001-create-users-and-database.sh
db-1  | 2024-05-16 09:25:45.832 UTC [50] LOG:  connection received: host=[local]
db-1  | 2024-05-16 09:25:45.832 UTC [50] LOG:  connection authorized: user=sa database=example application_name=psql
db-1  | 2024-05-16 09:25:45.842 UTC [50] LOG:  statement: REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;

... liquibase uses 'sa' - should be 'example_ow'

db-1  | 2024-05-16 09:25:48.670 UTC [65] LOG:  connection received: host=192.168.65.1 port=40715
db-1  | 2024-05-16 09:25:48.751 UTC [65] LOG:  connection authenticated: identity="sa" method=scram-sha-256 (/var/lib/postgresql/data/pg_hba.conf:128)
db-1  | 2024-05-16 09:25:48.751 UTC [65] LOG:  connection authorized: user=sa database=example
...
db-1  | 2024-05-16 09:25:49.509 UTC [65] LOG:  execute <unnamed>: CREATE TABLE example.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))

... hikari uses 'sa' - should be 'example_rw'

db-1  | 2024-05-16 09:26:38.544 UTC [32] LOG:  connection received: host=192.168.65.1 port=40792
db-1  | 2024-05-16 09:26:38.559 UTC [32] LOG:  connection authenticated: identity="sa" method=scram-sha-256 (/var/lib/postgresql/data/pg_hba.conf:128)
db-1  | 2024-05-16 09:26:38.559 UTC [32] LOG:  connection authorized: user=sa database=example
db-1  | 2024-05-16 09:26:38.562 UTC [32] LOG:  execute <unnamed>: SET extra_float_digits = 3
db-1  | 2024-05-16 09:26:38.563 UTC [32] LOG:  execute <unnamed>: SET application_name = 'docker-compose-datasource-test'
db-1  | 2024-05-16 09:26:38.564 UTC [32] LOG:  execute <unnamed>: SET SESSION search_path TO 'example'

Setup

application.yaml

spring:
  application:
    name: docker-compose-datasource-test
  datasource:
    hikari:
      schema:  example
      username: example_rw
      password: example_rw
  liquibase:
    default-schema: example
    user: example_ow
    password: example_ow
logging:
  level:
    com:
      zaxxer:
        hikari:
          HikariConfig: DEBUG
    liquibase:
      database: DEBUG
  pattern:
    console: '%c : %m%n'

compose.yaml

services:
  db:
    image: postgres:16.3-alpine3.19
    restart: always
    ports:
      - '5432:5432'
    command: ["postgres", "-c", "log_statement=all", "-c", "log_connections=true"]
    environment:
      POSTGRES_USER: sa
      POSTGRES_PASSWORD: sa
      POSTGRES_DB: example
    volumes:
      - ./docker/db/init/001-create-users-and-database.sh:/docker-entrypoint-initdb.d/001-create-users-and-database.sh
      - ./docker/db/init/002-create-schema.sh:/docker-entrypoint-initdb.d/002-create-schema.sh
    labels:
      org.springframework.boot.jdbc.parameters: 'ApplicationName=docker-compose-datasource-test'

docker/db/init/001-create-users-and-database.sh

#!/usr/bin/env bash

set -Eeu -o pipefail -o posix

readonly example_admin_pw='example_admin'
readonly example_ow_pw='example_ow'
readonly example_rw_pw='example_rw'
readonly example_ro_pw='example_ro'

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;
  GRANT ALL PRIVILEGES ON DATABASE postgres TO $POSTGRES_USER;

  CREATE USER example_admin WITH LOGIN REPLICATION PASSWORD '$example_admin_pw';
  CREATE USER example_ow WITH LOGIN PASSWORD '$example_ow_pw';
  CREATE USER example_rw WITH LOGIN PASSWORD '$example_rw_pw';
  CREATE USER example_ro WITH LOGIN PASSWORD '$example_ro_pw';

  CREATE DATABASE tmp;

  \c tmp

  DROP DATABASE IF EXISTS example;

  CREATE DATABASE example WITH OWNER example_admin TEMPLATE template0
    ENCODING UTF8 LC_COLLATE 'de_DE.UTF8' LC_CTYPE 'de_DE.UTF8';

  \c example

  DROP DATABASE IF EXISTS tmp;

  DROP SCHEMA IF EXISTS public;

  REVOKE ALL ON DATABASE example FROM PUBLIC;

  GRANT ALL ON DATABASE example TO $POSTGRES_USER;
  GRANT ALL ON DATABASE example TO example_admin;

  GRANT CONNECT,TEMPORARY ON DATABASE example TO example_ow;
  GRANT CONNECT,TEMPORARY ON DATABASE example TO example_rw;
  GRANT CONNECT ON DATABASE example TO example_ro;
EOSQL

docker/db/init/002-create-schema.sh

#!/usr/bin/env bash

set -Eeu -o pipefail -o posix

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE SCHEMA IF NOT EXISTS example AUTHORIZATION example_ow;

  REVOKE ALL ON SCHEMA example FROM PUBLIC;

  GRANT ALL ON SCHEMA example TO $POSTGRES_USER;
  GRANT ALL ON SCHEMA example TO example_admin;

  GRANT ALL ON SCHEMA example TO example_ow;
  ALTER ROLE example_ow IN DATABASE example SET search_path = 'example';

  GRANT pg_read_all_data, pg_write_all_data TO example_rw;
  ALTER ROLE example_rw IN DATABASE example SET search_path = 'example';

  GRANT pg_read_all_data TO example_ro;
  ALTER ROLE example_ro IN DATABASE example SET search_path = 'example';
EOSQL

src/main/resources/db/changelog/db.changelog-master.yaml

databaseChangeLog:
  - changeSet:
      id: INIT-1-1
      logicalFilePath: INIT-1
      author: sdavids
      changes:
        - tagDatabase:
            tag: INIT-1

I have not verified but I suspect that all spring.datasource.*.username and spring.datasource.*.password properties are affected.

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Reactions: 1
  • Comments: 18 (8 by maintainers)

Most upvoted comments

Thanks for suggestion.

I’m not sure that Spring Boot’s Docker Compose support should be that tightly coupled to the configuration properties that are defined in spring-boot-autoconfigure. Just for the case of a DataSource’s username, it would require the Docker Compose support to know about spring.datasource.username, spring.datasource.hikari.username, spring.datasource.dbcp2.username, etc.

There’s also the possibility that the JdbcConnectionDetails bean has been defined using some other properties. The Docker Compose support would then have no way of knowing what properties to use when overriding it with the JDBC URL from the compose-managed container.

While it may make the compose YAML slightly more verbose, I think it will be better to configure the use of properties explicitly rather than trying to do it automatically. That also allows people who want to hardcode the custom credentials rather than using their application’s properties to achieve their goal too.

You’d specify the profiles as you normally would when starting the app. As usual, the profiles would influence the application properties and YAML files that are loaded into the Spring environment. The placeholders in the label values are then resolved against the environment.

Thanks for the offer. That’s definitely room for improvement here but I’m not yet sure what we should do. Ideally, you wouldn’t have to mess around for connection details factories at all to do what you want. We’ll discuss it as team and try to figure out what we want to do here.