How to get the tenant id in a custom login statement in a partitioned multi tenant spring application?

Question

I got an almost working (partitioned) multi tenant spring application. The only remaining hurdle is the custom login statement. The SQL below is fictive but illustrates the problem without details getting in the way: how do I get the active value for the tenant_id (derived from the URL) in there?

    @Autowired
    public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().passwordEncoder(new BCryptPasswordEncoder())
            .dataSource(dataSource)
            .usersByUsernameQuery("select username, password, enabled from user where username=? and tenant_id='howtodothis'")
            .authoritiesByUsernameQuery("select username, role from user where username=? and tenant_id='howtodothis'");
    }

Two different tenants should be allowed to use the same usernames.

---

I have not been able to solve this, but it was possible to flip the problem: instead of adding the tenant to the where clause, add it to the login name.

It turns out that the spring-security login happens before any filters, so the TenantFilter can derive the tenant from the name of the logged in user. By prefixing the username with the tenant id and make that the username which needs to be logged in: 'tenant/username'.

    @Autowired
    public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication().passwordEncoder(new BCryptPasswordEncoder())
            .dataSource(dataSource)
            .usersByUsernameQuery("select concat(tenant_id, '/', username) as username, password, enabled from user where username=?")
            .authoritiesByUsernameQuery("select concat(tenant_id, '/', username) as username, role from user where username=?");
    }

It does mean you need to split the logged-in-username into tenant id and actual username at places. But it also removes any need for domain name configuration; the application does the whole multi tenancy solely based on the login. Even the user table itself is tenant aware.

That said, the original question remains unanswered. Relevant if you want to do it on hostname.

Answer 1

Beside seriously modifying/reimplementing org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl, you can't.

The easiest way to solve this, is to implement a custom UserDetailsService. In my case:

@Service
public class TenantUserDetailService implements UserDetailsService {

    @Autowired
    PersonRepo personRepo;

    @Override
    public UserDetails loadUserByUsername(String loggedInUsername) throws UsernameNotFoundException {
        String tenantId = TenantFilter.extractTenantId(loggedInUsername);
        String username = TenantFilter.extractUsername(loggedInUsername);
        try {
            return TenantFilter.runWithTenant(tenantId, () -> {
                Person person = personRepo.findByUsername(username);
                if (person == null) {
                    throw new UsernameNotFoundException("User not found");
                }
                return new org.springframework.security.core.userdetails.User(
                        tenantId + "/" + person.getUsername(),
                        person.getEncyrptedPassword(),
                        List.of(new SimpleGrantedAuthority(person.getRole())));

            });
        }
        catch (Exception e) {
            throw new UsernameNotFoundException("User not found", e);
        }
    }
}

And then:

@Autowired
public void configAuthentication(AuthenticationManagerBuilder auth, UserDetailsService userDetailService) throws Exception {
    auth.userDetailsService(userDetailService)
            .passwordEncoder(new BCryptPasswordEncoder());
}