Schema multi-tenant using Hibernate and Spring (Jhipster)

Hibernate ORM
1

Hello there,

I’m using Jhipster (Gradle + React) for one of my project, and recently, I want to add Multi-tenancy support to the whole application (tenant will be the company name, each user need to set during login a account ID which is the tenant)

The RequestInterceptor and all of these things are working great, but I got a strange behavior for some login, on different schema

  • Default Schema is set to a schema that doesn’t exist (master)
  • When I try to connect to public schema with good credentials, it’s working good
  • When I try to connect to a schema that doesn’t exist, error message is fired ;

The problem is when I try to connect to schema “Test” with credential only present in schema “public”, the login works (but the requests after are in error because data not found).

It seems the for first request the schema is not changed immediatly, but changed when new request is incomming or something similar.

Here is my MultiTenantConnectionProvider

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        System.out.println("Get connection for tenant " + tenantIdentifier);
        final Connection connection = getAnyConnection();
        connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "';");
        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException 
    {
        System.out.println("Release connection for tenant " + tenantIdentifier);
        String DEFAULT_TENANT = "master";
        connection.createStatement().execute("SET SCHEMA '" + DEFAULT_TENANT + "';");
        releaseAnyConnection(connection);
    }

And here a part of my HIbernateConfig

    @Bean
    LocalContainerEntityManagerFactoryBean entityManagerFactory(
        DataSource dataSource,
        MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
        CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl
    ) {

        Map<String, Object> jpaPropertiesMap = new HashMap<>(jpaProperties.getProperties());

        jpaPropertiesMap.put(Environment.PHYSICAL_NAMING_STRATEGY, "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
        jpaPropertiesMap.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
        jpaPropertiesMap.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
        jpaPropertiesMap.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.project.webapp.domain");
        em.setJpaVendorAdapter(this.jpaVendorAdapter());
        em.setJpaPropertyMap(jpaPropertiesMap);
        return em;
    }

When I check a debug message on the tenant handler, it seems the tenant change correctly when a login request is send to the back-end, but maybe it’s not the case for the schema.

Any idea ? Error with my configuration ?

Kindly,
Florian

2

Just to add more informations on the case

  • If in the method getConnection and releaseConnection, I use
connection.createStatement().execute( "SET SCHEMA '" + tenantIdentifier + "'" );

My application say alway that the credentials is bad

  • But If i use in the same method
connection.setSchema(tenantIdentifier);

On schema public, credentials are correct - disconnect - try on schema test and it’s works (even if the user doesn’t exist on the same table, but in schema test !)

Very strange, seems the schema doesn’t change at the same time or at the first request

Any idea ?

3

Anyone got an idea, on how I can achieve this (simple) use case ?
Why I handle this behavior according to explanations ? :pray:

Kindly,

4

No need to ask again, there simply doesn’t seem to be anyone that can help you here. Your issue might be related to how Spring handles data sources or how your connection pool implementation works. There are so many possible problems here and the only way for you I see is, to debug the whole thing and try to understand what is happening exactly or pay a consultant to do so for you.

1 Like
5

Thanks for your answer !

I tried a lot of debug, and it seems the error is only on Login request to DB, because when login works when it shouldn’t, the other requests are return errors (data not found and so one) which is great !

But the first login request let the user load the dashboard, even if his account isn’t registered in the schema he choosed

For the DataSource, I got only one DataSource

6

Sounds like you are setting a tenant identifier value that you shouldn’t. You can try to debug your login and observe that the schema is wrong, and if so, try to determine where that value comes from.