# Multitenancy with Spring

Observation:
> All spring implementations are the same

| Feature | Multi Databases | RLS | Schema |
| -------------------- | ----------------------- | -------------- | --------------------------- |
| DB Migrations | 1 migration for each db | 1 migration | 1 migration for each schema |
| DB deployment | Create a new DB + migration | Create User | Create User + Schema + migration |
| Security | High | Normal | Normal Plus |
| Cost | High | Normal | Normal |
| Performance | High | Normal | Normal |
| Code Maintainability | Easy | Easy | Easy |
| Testing | Test each config | Test each config | Test each config |
| MS deployment | Lots of config | Lots of config | Lots of config |

## Pros/Cons
Pros of all cases:
1. Maintainability of code
i. Spring code is easy to mantain and do not affect current codebase
2. Improved security on data leaks between vendors

Cons of all cases:

1. Micro-services deployment aspects
i. All credentials should be mapped on helm chart, hence it will become very big
ii. Also include mapping of vendors to credentials on application.yaml
2. Testing
i. All tenants should be tested

Multi Databases:
1. Security
i. There is less chance of exposing all data at same time and data is very segregated.
ii. Possible source of security incidents: wrong configuration of vendor per database on microsservice
2. Performance vs Cost:
i. Most performant
ii. Most expensive
iii. There is data redundancy (on trained models and labels)
3. Maintainability of code
ii. New tests to consider multiple database case
4. Database deployment and migrations
i. Create a new database for each vendor
ii. Migration multiple databases (might requer new scripts, no standard on spring)
iii. Trained models and labels must be inserted on some/all databases, so we might need special ways of handling it

Row Level Security:

1. Security
i. Data is segregated on one database per multiple users
ii. Possible source of security incidents: wrong configuration of vendor per database on microsservice, wrong configuration on database, wrong queries on tables without RLS (labels - trained models)
iii. Care should be taken with admin user, as RLS does not apply to admin
iiii. Technically, on our architecture, one vendor could create config to another vendor
2. Performance vs Cost:
i. Performant
ii. There is no data redundancy
iii. Much cheaper and scallable
4. Database deployment and migrations
i. Create a new user for each vendor
iii. Activate RLS on new tables that depends on vendor_id
ii. Migration on 1 database only

Multi - Schema:

1. Security
i. Data is segregated on one database per multiple users
ii. Possible source of security incidents: wrong configuration of vendor per database on microsservice, wrong configuration on database, wrong queries on tables sharing schemas (if choosen to share schema)
iii. Care should be taken with admin user, as RLS does not apply to admin
2. Performance vs Cost:
i. Performant
ii. There is no data redundancy if schema is shared, otherwise there is redundancy
iii. Much cheaper and scallable
4. Database deployment and migrations
i. Create a new user and schema for each vendor
ii. Migration for each schema
iii. handling shared schema (if enabled) might become complex

## 1 - Row Level Secury

To setup the Row Level Security on the database, follow those steps:

1. Enable RLS on tables and include

ALTER TABLE configs ENABLE ROW LEVEL SECURITY;

2. Create policy for filtering rows given the user

CREATE POLICY config_vendor_id ON configs
USING (vendor_id = CAST (current_user AS uuid));

3. Create users for each vendor (the username of the user must be the vendor id)

CREATE USER "1a5f8705-f07c-4f20-bc67-f379c94b0a90" WITH PASSWORD '123';
GRANT ALL ON ALL TABLES IN SCHEMA public TO "1a5f8705-f07c-4f20-bc67-f379c94b0a90";

Besides configuring the database, we also need to include the tenants on the application.yaml file:

multitenancy:
  tenants:
    - vendorId: 1a5f8705-f07c-4f20-bc67-f379c94b0a90
      url: ${POSTGRESQL_CONNECTION_STRING}
      username: 1a5f8705-f07c-4f20-bc67-f379c94b0a90
      password: ${POSTGRESQL_PASSWORD_1}
    - vendorId: 13b246ee-fda3-477c-9a32-69bd6a67794f
      url: ${POSTGRESQL_CONNECTION_STRING}
      username: 13b246ee-fda3-477c-9a32-69bd6a67794f
      password: ${POSTGRESQL_PASSWORD_2}

That is enough !

**Warning**
> For testing purpose, I wasn't able to replicate that with testcontainers, but a practical test worked though.
>
## 2 - Multiple Databases
Create multiple tenants on the application.yaml (one for each database you have)

multitenancy:
  tenants:
    - vendorId: 1a5f8705-f07c-4f20-bc67-f379c94b0a90
      url: ${POSTGRESQL_CONNECTION_1_STRING}
      username: username1
      password: ${POSTGRESQL_PASSWORD_1}
      driverClassName: org.postgresql.Driver
    - vendorId: 1a5f8705-f07c-4f20-bc67-f379c94b0a90
      url: ${POSTGRESQL_CONNECTION_2_STRING}
      username: username2
      password: ${POSTGRESQL_PASSWORD_2}
      driverClassName: org.postgresql.Driver

## 3 - Multiple schemas

Multiples schemas works very like the multiple databases

This SQL creates the new schema and gives access:

CREATE SCHEMA IF NOT EXISTS myschema
GRANT USAGE ON SCHEMA myschema TO "1769e9ef-722b-4c19-b136-b3fc69ea15e8";
GRANT ALL ON ALL TABLES IN SCHEMA myschema TO "1769e9ef-722b-4c19-b136-b3fc69ea15e8";

multitenancy:
  tenants:
    - vendorId: 1a5f8705-f07c-4f20-bc67-f379c94b0a90
      url: ${POSTGRESQL_CONNECTION_STRING}
      username: username1
      password: ${POSTGRESQL_PASSWORD_1}
      schema: schema1
    - vendorId: 13b246ee-fda3-477c-9a32-69bd6a67794f
      url: ${POSTGRESQL_CONNECTION_STRING}
      username: username2
      password: ${POSTGRESQL_PASSWORD_2}
      schema: schema2

## 0 - Java Setup

In spring, there is a feature called AbstractRoutingDataSource, which allows us to "route" resources given some variable. This is a very generic methodology that handles all cases.

In order make this work, I need to implement some classes:
1. Data class that has a ThreadLocal<String> variable with set/getter
2. Class that extends AbstractRoutingDataSource and provides the determineCurrentLookupKey
3. Bean that extends javax.servelet.Filter (and sets dataclass given the request)
4. Configuration class that provides the DataSource bean
5. Dataclasses to load properties from application.yaml

### Tenant Context

This class holds the information for which tenant should use for each thread, that is why I use ThreadLocal, otherwise one thread would interfere in others threads.

public class TenantContext {

    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();

    public static String getCurrentTenant() {
        return CURRENT_TENANT.get();
    }

    public static void setCurrentTenant(String tenant) {
        CURRENT_TENANT.set(tenant);
    }
}

### Routing Data Source

This class connects our TenantContext with Spring's routing data source functionallities. Implementing this class is enough for Spring give each thread the correct connection with the database.

public class MultitenantDataSource extends AbstractRoutingDataSource {
    @Override
    protected String determineCurrentLookupKey() {
        return TenantContext.getCurrentTenant();
    }
    
    public void close(){
        Map<Object, DataSource> resolvedDataSources = this.getResolvedDataSources();
        resolvedDataSources.forEach( (o, dataSource) ->  ((HikariDataSource) dataSource).close() );
    }

}
 

### Servelet Filter

One of this implementation's goals is to remove the logic of which tenant to use from the rest of our services business logic, so we don't incur in errors when querying the database for the wrong tenant. The best way to protect our services is to have a specific filter that acts before running our business logic, so we can create a filter that routes the correct database connection upon receiving a connection.

@Component
@AllArgsConstructor
@Order(1)
class TenantFilter implements Filter {

    JwtParser jwtParser;

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {

        HttpServletRequest req = (HttpServletRequest) request;

        final String authorization = req.getHeader(HttpHeaders.AUTHORIZATION);
        String vendorId = jwtParser.parseVendorId(authorization);

        if (StringUtils.isBlank(vendorId)) {
            vendorId = req.getHeader(VENDOR_ID_HEADER);
        }

        TenantContext.setCurrentTenant(vendorId);

        try {
            chain.doFilter(request, response);
        } finally {
            TenantContext.setCurrentTenant("");
        }
    }
}

This class is able to get the tenant name from the header or from the token.

### Configuration

@Configuration
@EnableConfigurationProperties(MultitenancyConfigurationProperties.class)
public class MultitenancyConfiguration {

    private final MultitenancyConfigurationProperties multitenancyProperties;

    public MultitenancyConfiguration(MultitenancyConfigurationProperties multitenancyProperties) {
        this.multitenancyProperties = multitenancyProperties;
    }

    @Primary
    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        Map<Object, Object> resolvedDataSources = new HashMap<>();

        for (TenantConfigurationProperty properties : this.multitenancyProperties.getTenants()) {
            HikariConfig hikariConfig = new HikariConfig();
            String tenantId = properties.getVendorId();

            if (StringUtils.isBlank(properties.getDriverClassName())) {
                hikariConfig.setDriverClassName("org.postgresql.Driver");
            } else {
                hikariConfig.setDriverClassName(properties.getDriverClassName());
            }

            hikariConfig.setUsername(properties.getUsername());
            hikariConfig.setPassword(properties.getPassword());
            hikariConfig.setJdbcUrl(properties.getUrl());
            resolvedDataSources.put(tenantId, new HikariDataSource(hikariConfig));
        }

        MultitenantDataSource dataSource = new MultitenantDataSource();
        dataSource.setDefaultTargetDataSource(resolvedDataSources.get("default"));
        dataSource.setTargetDataSources(resolvedDataSources);
        dataSource.afterPropertiesSet();

        return dataSource;
    }
}

### Properties

Those last classes are responsible for loading the properties from the application.yaml

@Data
@ToString
@ConfigurationProperties(prefix = "multitenancy")
@Validated
public class MultitenancyConfigurationProperties {

    @NotEmpty
    private List<TenantConfigurationProperty> tenants;
}

@Data
@ToString
@Validated
public class TenantConfigurationProperty {

    @NotEmpty
    private String vendorId;
    @NotEmpty
    private String url;
    @NotEmpty
    private String password;
    @NotEmpty
    private String username;
    private String driverClassName;

}