Hibernate schema based multi tenancy: wrong schema for connection to reader instance

[matheisco]

Hi!

In our Spring Boot application we use schema-based multi tenancy from hibernate. When activating the readWriteSplitting plugin, the connection to the read-instance doesn't get the correct schema set.

Our setup:
We instantiate two TenantBasedConnectionProvider, each with a different schema:

import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;

@RequiredArgsConstructor
public class TenantBasedConnectionProvider implements ConnectionProvider {
    
    @NonNull
    private final transient DataSource dataSource;
    @NonNull
    private final String schema;
    
    // ...

    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setSchema(schema);
        return connection;
    }

And here the correct TenantBasedConnectionProvider is selected, depending on which schema should be used for the current request.

import org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider;
import org.springframework.boot.autoconfigure.orm.jpa.HibernatePropertiesCustomizer;

@Component
public class CustomMultiTenantConnectionProvider extends AbstractMultiTenantConnectionProvider
        implements HibernatePropertiesCustomizer {

    private final Map<String, ConnectionProvider> tenantIdentifierToConnectionProviderMap;

    // ...

    @Override
    protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) {
        return tenantIdentifierToConnectionProviderMap.get(tenantIdentifier);
    }

The problem is that connection.setSchema(schema); is only executed for the write-instance. Therefore, when the aws wrapper uses the read-instance, the public schema is used, which is not correct.

Thank you for any tips on how to solve this!

[crystall-bitquill]

This discussion was continued at issue #679.