SpringBoot Add testcontainers init script with admin privileges

spring-boot-testcontainers supports automatic initialization of a mysql testcontainer instance simply by using jdbc url like:

jdbc:tc:mariadb:10.6.4:///test

This creates a default database named "test".

Problem: if you have a JdbcTemplate configuration that makes use of multiple database schemas, this gets you into trouble.

#notice there is no database schema at the url definition!
spring.datasource.url=localhost:8080/
spring.datasource.username=test
spring.datasource.password=test

The jdbcTemplate created from this can execute both select * from schema1.table, as well as select * from schema2.table.

But with a fixed testcontainers url, I'm required to bind the tc instance to either jdbc:tc:mariadb::///schema1 or jdbc:tc:mariadb::///schema2.

Resolution: To fix this, it would be sufficient to grant the default testcontainers test user permissions to create any database schema himself. This could be done by the following url property: jdbc:tc:mariadb:10.6.4:///test?TC_INITSCRIPT=create-testuser.sql.

create-testuser.sql:

-- CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

Problem here is that the TC_INITSCRIPT is not executed with the root user, which would have the permission to do so. Thus, the above fails with:

Caused by: org.testcontainers.ext.ScriptUtils$ScriptStatementFailedException: Script execution failed (create-testuser.sql:1): GRANT ALL PRIVILEGES ON *.* TO 'test'@'%'
    at org.testcontainers.jdbc.JdbcDatabaseDelegate.execute(JdbcDatabaseDelegate.java:63)
    at org.testcontainers.delegate.AbstractDatabaseDelegate.execute(AbstractDatabaseDelegate.java:38)
    at org.testcontainers.ext.ScriptUtils.executeDatabaseScript(ScriptUtils.java:307)
    ... 19 more
Caused by: java.sql.SQLInvalidAuthorizationSpecException: (conn=5) Access denied for user 'test'@'%' (using password: YES)
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:293)
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:378)
    at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:189)
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:1235)
    at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:1174)
    at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:1093)
    at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:1017)
    at org.mariadb.jdbc.Statement.executeInternal(Statement.java:1034)

So we either need a flag to tell testcontainers to run the TC_INITSCRIPT with the root user. Or a new property to execute privileged actions explicit on container start.

By the way: a workaround would be to initialize the testcontainer manually, but that's rather bad as we have the jdbc-url feature, so it should support the below out of the box.

    private static MariaDBContainer<?> CONTAINER;

    static {
        CONTAINER = new MariaDBContainer<>("mariadb:10.6.4")
                .withUsername("root")
                .withPassword("rootpw")
                .withInitScript("create-testuser.sql");
        CONTAINER.start();
    }

    @DynamicPropertySource
    static void mysqlDatabaseInitializer(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.intranet.url", () -> CONTAINER.getJdbcUrl());
    }

Comment From: wilkinsona

Thanks for the proposal but I don't think there's anything that's specific to Spring Boot here. As such, I think it would be better handled directly in Testcontainers. Looking at https://github.com/testcontainers/testcontainers-java/issues/3893, you may already be able to configure the user with the user query parameter.