Spring Boot 3.1 enhanced docker-compose support, made it lot simpler and better suited for local development. With that we don't need to worry about installing services like database locally and managing them manually, letting Docker do that for us and Spring Boot do the rest of starting and stopping docker container.
This post is about details explored on - how to run additional init db script with PostgreSQL service defined in Docker compose file in a Spring Boot application.
Environment: Java 21, Spring Boot 3.2.1, PostgreSQL 16, maven 3.9.6 on macOS Catalina 10.15.7
The Scenario
My Spring Boot 3.2.x application uses PostgreSQL database, a specific version of it. By leveraging Spring Boot support for docker-compose in development, I would like to have a new schema and user created, granting the user required privileges on the schema.
A typical PostgreSQL Service configuration in docker compose file looks like:
docker-compose.yml
version: '3'
services:
PostgreSQL16:
image: 'postgres:16.1'
ports:
- '54321:5432'
environment:
- 'POSTGRES_DB=my_app'
- 'POSTGRES_USER=postgres'
- 'POSTGRES_PASSWORD=s3cr3t'
In the above docker compose configuration, we have specified database name, user, and password through environment variables in the container, and mapped host port (local port) to container port (default postgres port). With this when the docker-compose command: docker-compose up is run to create and start the container, the my_app database gets created, and PostgreSQL will be up and running in the container. The postgres user created is the superuser with access and ownership to all database objects including the public schema. When the docker container is created for PostgreSQL16 database service, the value of POSTGRES_USER environment variable is used to create the superuser, and public is the default schema created.
PostgreSQL 15 changes to public schema
From version 15 onwards, privileges on public schema are restricted and the schema is accessible to superuser only. So, it is good to create an application specific schema, and application specific database user with all needed privileges granted on the application schema. This requires a way to run a one-time initial databases script for creating application schema and user. The following shell script is an example to do so:
init-database.sh
#!/bin/sh
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
/* Create schema, user and grant permissions */
CREATE SCHEMA my_app_schema;
CREATE USER my_app_user_local WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON SCHEMA my_app_schema TO my_app_user_local;
EOSQL
In order to run the above db init script when the container is created, reference the shell script file under volumes: to attach the init file directory (./) to the container directory (/docker-entrypoint-initdb.d/) as shown below:
docker-compose.yml
version: '3'
services:
PostgreSQL16:
image: 'postgres:16.1'
ports:
- '54321:5432'
environment:
- 'POSTGRES_DB=my_app'
- 'POSTGRES_USER=postgres'
- 'POSTGRES_PASSWORD=s3cr3t'
volumes:
- ./init-database.sh:/docker-entrypoint-initdb.d/init-database.sh
With this, when the docker container is created for PostgreSQL service, the init db script gets executed which results with new schema my_app_schema and user my_app_user_local. with privileges granted.
Gotchas
Auto configured datasource properties
When the application is run, the PostgreSQL container is created and run by Spring Boot. It also auto configures dataSource bean with properties: url, username, and password taking them from docker compose file. The user is superuser created from the POSTGRES_USER container environment variable. If the application has any initialization database scripts within the application under main/resources dir like schema.sql for initial schema or even flyway scripts in flyway enabled application under main/resources/db.migration dir, all the database tables and other objects created are owned by the superuser as the datasource uses superuser for connecting to the database.
If you want the data objects like tables, indices created in the application schema instead of public, you may need to specify it as the prefix in your schema.sql or for an app with flyway support, add the property spring.flyway.schemas appropriately, e.g in application.yml.
TIPS
1. With docker-compose managing the database service, if you need to use psql, the terminal based frontend to PostgreSQL to connect to db and run commands, invoke psql like:
$ # list docker containers running
$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7caf031c31a4 postgres:16.0 "docker-entrypoint.s…" 56 minutes ago Up 56 minutes 0.0.0.0:5222->5432/tcp docker-PostgreSQL16-1
45fa1a477ac3 postgres:15.3 "docker-entrypoint.s…" 4 days ago Up 3 days 0.0.0.0:54321->5432/tcp docker-compose-postgres15-1
$ # run psql command to coonect to PostgreSQL16 db and list users
$ docker exec -it docker-PostgreSQL16-1 psql -U postgres
psql (16.0 (Debian 16.0-1.pgdg120+1))
Type "help" for help.
postgres=#
postgres=# \?
General
\bind [PARAM]... set query parameters
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
--More--
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
postgres=# \du
List of roles
Role name | Attributes
-------------------+------------------------------------------------------------
my_app_user_local |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# select current_date;
current_date
--------------
2024-01-15
(1 row)
postgres=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
--------------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
boot-graalvm | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c boot-graalvm
You are now connected to database "boot-graalvm" as user "postgres".
boot-graalvm=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+----------
public | account_holder | table | postgres
public | accounts | table | postgres
public | addresses | table | postgres
public | flyway_schema_history | table | postgres
(4 rows)
boot-graalvm=#
boot-graalvm=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# \q
Posts
