使用 Postgres 行级安全性的 Spring Boot 多租户 - bytefish

在这篇文章中，我将向您展示如何使用 Postgres 行级安全特性在 Spring Boot 应用程序中提供多租户。

什么是多租户
一旦您的应用程序有多个客户，您就需要为您的应用程序实现某种多租户。
微软在多租户应用程序上写道：
多租户应用程序是一种共享资源，允许单独的用户或“租户”查看应用程序，就好像它是他们自己的一样。适用于多租户应用程序的典型场景是，应用程序的所有用户可能希望自定义用户体验，但具有相同的基本业务需求。

多租户模型
有几种模型可以在应用程序中实现多租户：

每个租户的数据库
- 每个租户都有自己的数据库，并且与其他租户隔离。每个租户都有自己的房子。

共享数据库，独立架构

所有租户共享一个数据库，但有自己的数据库模式和自己的表。同一建筑物中的每个租户，但有自己的公寓。

共享数据库，共享架构

所有租户共享一个数据库和表。每个表都有一个带有租户标识符的列，它显示了该行的所有者。每个人都住在同一个公寓里，所有东西都用便签标记，以显示谁拥有它。

这篇文章的 GitHub 存储库可以在以下位置找到：

https://github.com/bytefish/SpringBootMultiTenancyUsingRowLevelSecurity

在此示例中，我们将开发一个多租户应用程序来管理客户。

创建数据库
首先创建philipp用于连接数据库的用户：

PS C:\Users\philipp> psql -U postgres
psql (9.4.1)
postgres=# CREATE USER philipp WITH PASSWORD 'test_pwd';
CREATE ROLE

然后我们可以创建数据库并将所有者设置为philipp：

postgres=# CREATE DATABASE sampledb
postgres-#   WITH OWNER philipp;
CREATE DATABASE

SQL 脚本
现在执行以下 SQL 脚本来创建架构、表和策略。该脚本还创建 app_user用于连接数据库的 ,。该存储库还附带一个批处理脚本和一个外壳脚本来创建数据库。

DO $$
BEGIN

---------------------------
-- Create the Schema     --
---------------------------
IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'sample') THEN

    CREATE SCHEMA sample;

END IF;

---------------------------
-- Create the Table      --
---------------------------
IF NOT EXISTS (
    SELECT 1 
    FROM information_schema.tables 
    WHERE  table_schema = 'sample' 
    AND table_name = 'customer'
) THEN

CREATE TABLE sample.customer
(
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    tenant_name VARCHAR(255) NOT NULL
);

END IF;

---------------------------
-- Enable RLS            --
---------------------------
ALTER TABLE sample.customer ENABLE ROW LEVEL SECURITY;

---------------------------
-- Create the RLS Policy --
---------------------------

DROP POLICY IF EXISTS tenant_isolation_policy ON sample.customer;

CREATE POLICY tenant_isolation_policy ON sample.customer
    USING (tenant_name = current_setting('app.current_tenant')::VARCHAR);

---------------------------
-- Create the app_user   --
---------------------------
IF NOT EXISTS (
  SELECT FROM pg_catalog.pg_roles
  WHERE  rolname = 'app_user') THEN

  CREATE ROLE app_user LOGIN PASSWORD 'app_user';
END IF;

--------------------------------
-- Grant Access to the Schema --
--------------------------------
GRANT USAGE ON SCHEMA sample TO app_user;
GRANT ALL ON SEQUENCE sample.customer_customer_id_seq TO app_user;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE sample.customer TO app_user;

END;
$$;

项目结构
首先看一下项目结构很有用：

async
- AsyncConfig
  - 提供为 TenantAware 处理装饰的 TaskExecutor。
- TenantAwareTaskDecorator
  - 添加一个 Spring Boot TaskDecorator，将 TenantName 传递给子线程。
core
- ThreadLocalStorage
  - 将租户标识符存储在ThreadLocal.
datasource
- TenantAwareHikariDataSource
  - 覆盖HikariDataSource#getConnection设置连接的租户信息的方法。
model
- Customer
  - Customer实体，将在每个租户数据库中进行管理。
repositories
- ICustomerRepository
  - 用于持久化客户的 CRUD 存储库。
web
- configuration
  - WebMvcConfig
    - 配置 Spring MVC 拦截器。
- controllers
  - CustomerController
    - 实现用于持久化和删除客户的 REST Web 服务。
- converter
  - Converters
    - 在域模型和数据传输对象之间转换。
- interceptor
  - TenantNameInterceptor
    - 从传入请求中提取租户标识符。

领域层
1、客户实体
客户实体对客户实体建模。我们使用命名空间中的javax.persistence注释来注释域模型并设置数据库列。Hibernate 可以很好地使用这些注释。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.model;

import javax.persistence.*;

@Entity
@Table(schema = "sample", name = "customer")
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "tenant_name")
    private String tenantName;

    protected Customer() {
    }

    public Customer(Long id, String firstName, String lastName, String tenantName) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.tenantName = tenantName;
    }

    public Long getId() {
        return id;
    }

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public String getTenantName() {
        return tenantName;
    }
}

2、客户资料库
使用 Spring Boot 添加 CRUD 功能很简单，它提供了一个所谓的CrudRepository. 您只需从 CrudRepository接口扩展，Spring 会自动为您的实体提供所有 CRUD 功能。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.repositories;

import de.bytefish.multitenancy.model.Customer;
import org.springframework.data.repository.CrudRepository;

public interface ICustomerRepository extends CrudRepository<Customer, Long> {
}

基础设施
存储租户标识符
该类ThreadLocalStorage包装 aThreadLocal以将租户数据存储在当前线程上下文中。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.core;

public class ThreadLocalStorage {

    private static ThreadLocal<String> tenant = new ThreadLocal<>();

    public static void setTenantName(String tenantName) {
        tenant.set(tenantName);
    }

    public static String getTenantName() {
        return tenant.get();
    }

}

创建 TenantAwareHikariDataSource

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.datasource;

import com.zaxxer.hikari.HikariDataSource;
import de.bytefish.multitenancy.core.ThreadLocalStorage;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class TenantAwareHikariDataSource extends HikariDataSource {

    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = super.getConnection();

        try (Statement sql = connection.createStatement()) {
            sql.execute("SET app.current_tenant = '" + ThreadLocalStorage.getTenantName() + "'");
        }

        return connection;
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        Connection connection = super.getConnection(username, password);

        try (Statement sql = connection.createStatement()) {
            sql.execute("SET app.current_tenant = '" + ThreadLocalStorage.getTenantName() + "'");
        }

        return connection;
    }

}

Web层
1、提取租户信息
有几种方法可以从传入请求中提取租户标识符。X-TenantIDWebservice 客户端将发送一个带有示例中名称的 HTTP 标头。在 Spring MVC 中，您可以实现 aHandlerInterceptorAdapter来拦截传入的请求并从中提取数据。

TenantNameInterceptor读取X-TenantID标头并将其值存储在ThreadLocalStorage.

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.web.interceptors;

import de.bytefish.multitenancy.core.ThreadLocalStorage;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class TenantNameInterceptor extends HandlerInterceptorAdapter {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

        // Implement your logic to extract the Tenant Name here. Another way would be to
        // parse a JWT and extract the Tenant Name from the Claims in the Token. In the
        // example code we are just extracting a Header value:
        String tenantName = request.getHeader("X-TenantID");

        // Always set the Tenant Name, so we avoid leaking Tenants between Threads even in the scenario, when no
        // Tenant is given. I do this because if somehow the afterCompletion Handler isn't called the Tenant Name
        // could still be persisted within the ThreadLocal:
        ThreadLocalStorage.setTenantName(tenantName);

        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {

        // After completing the request, make sure to erase the Tenant from the current Thread. It's
        // because Spring may reuse the Thread in the Thread Pool and you don't want to leak this
        // information:
        ThreadLocalStorage.setTenantName(null);
    }
}

2、数据传输对象和转换器
您应该始终将 Web 层与域层分开。在理想的世界中，Web 层应该只关心接收和发送数据传输对象给消费者。它应该知道如何在数据传输对象和域模型之间进行转换，因此它可以使用域存储库。

数据传输对象使用CustomerDtoJackson 注释来提供 JSON 映射。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.web.model;

import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonProperty;

public class CustomerDto {

    private final Long id;

    private final String firstName;

    private final String lastName;

    @JsonCreator
    public CustomerDto(@JsonProperty("id") Long id, @JsonProperty("firstName") String firstName, @JsonProperty("lastName") String lastName) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
    }

    @JsonProperty("id")
    public Long getId() {
        return id;
    }

    @JsonProperty("firstName")
    public String getFirstName() {
        return firstName;
    }

    @JsonProperty("lastName")
    public String getLastName() {
        return lastName;
    }
}

并且该类提供了两种方法在模型和模型Converters之间进行转换。CustomerDtoCustomer

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.web.converter;

import de.bytefish.multitenancy.model.Customer;
import de.bytefish.multitenancy.web.model.CustomerDto;

import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;

public class Converters {

    private Converters() {

    }

    public static CustomerDto convert(Customer source) {
        if(source == null) {
            return null;
        }

        return new CustomerDto(source.getId(), source.getFirstName(), source.getLastName());
    }

    public static Customer convert(CustomerDto source, String tenantName) {
        if(source == null) {
            return null;
        }

        return new Customer(source.getId(), source.getFirstName(), source.getLastName(), tenantName);
    }

    public static List<CustomerDto> convert(Iterable<Customer> customers) {
        return StreamSupport.stream(customers.spliterator(), false)
                .map(Converters::convert)
                .collect(Collectors.toList());
    }
}

控制器
使用 Spring MVC 实现 RESTful Web 服务需要我们实现一个RestController. 我们使用ICustomerRepository来查询数据库并使用Converters来在两种表示之间进行转换。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.web.controllers;

import de.bytefish.multitenancy.core.ThreadLocalStorage;
import de.bytefish.multitenancy.model.Customer;
import de.bytefish.multitenancy.repositories.ICustomerRepository;
import de.bytefish.multitenancy.web.converter.Converters;
import de.bytefish.multitenancy.web.model.CustomerDto;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;

@RestController
public class CustomerController {

    private final ICustomerRepository repository;

    @Autowired
    public CustomerController(ICustomerRepository repository) {
        this.repository = repository;
    }

    @GetMapping("/customers")
    public List<CustomerDto> getAll() {
        Iterable<Customer> customers = repository.findAll();

        return Converters.convert(customers);
    }

    @GetMapping("/customers/{id}")
    public CustomerDto get(@PathVariable("id") long id) {
        Customer customer = repository
                .findById(id)
                .orElse(null);

        return Converters.convert(customer);
    }

    @GetMapping("/async/customers")
    public List<CustomerDto> getAllAsync() throws ExecutionException, InterruptedException {
        return repository.findAllAsync()
                .thenApply(x -> Converters.convert(x))
                .get();
    }

    @PostMapping("/customers")
    public CustomerDto post(@RequestBody CustomerDto customer) {
        // Get the current Tenant:
        String tenantName = ThreadLocalStorage.getTenantName();

        // Convert to the Domain Object:
        Customer source = Converters.convert(customer, tenantName);

        // Store the Entity:
        Customer result = repository.save(source);

        // Return the DTO:
        return Converters.convert(result);
    }

    @DeleteMapping("/customers/{id}")
    public void delete(@PathVariable("id") long id) {
        repository.deleteById(id);
    }

}

配置
要配置 Spring MVC，我们需要扩展并将其WebMvcConfigurer添加TenantNameInterceptor到拦截器列表中。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy.web.configuration;

import de.bytefish.multitenancy.web.interceptors.TenantNameInterceptor;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new TenantNameInterceptor());
    }

}

合在一起
最后，是时候使用 Spring Boot 将所有内容组合在一起了。我们所要做的就是为要使用的定义Bean一个DataSource。这是TenantAwareHikariDataSource使用 Postgres 驱动程序和连接字符串。

我还为 Spring JPA 添加了一些健全的属性，因此 Spring 不会尝试自动检测数据库。

所有其他依赖项都由 Spring Boot 自动解决。

// Copyright (c) Philipp Wagner. All rights reserved.
// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.multitenancy;

import com.zaxxer.hikari.HikariDataSource;
import de.bytefish.multitenancy.datasource.TenantAwareHikariDataSource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@SpringBootApplication
@EnableAsync
@EnableTransactionManagement
public class SampleSpringApplication {

    public static void main(String[] args) {
        SpringApplication.run(SampleSpringApplication.class, args);
    }

    @Bean
    public DataSource dataSource() {
        HikariDataSource dataSource = new TenantAwareHikariDataSource();

        dataSource.setInitializationFailTimeout(0);
        dataSource.setMaximumPoolSize(5);
        dataSource.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource");
        dataSource.addDataSourceProperty("url", "jdbc:postgresql://127.0.0.1:5432/sampledb");
        dataSource.addDataSourceProperty("user", "app_user");
        dataSource.addDataSourceProperty("password", "app_user");

        return dataSource;
    }
}

警告去除
Spring Boot 引入了许多神奇的功能，可以用最少的编码使事情正常工作……有时约定优于配置会让人头疼。当 Spring Boot 启动时，Thread 中没有设置 Tenant，所以我们不能使用诸如自动检测数据库之类的东西。

所以我添加了一个属性文件application.properties来配置 Spring：

# Get Rid of the OIV Warning:
spring.jpa.open-in-view=false
# Show the SQL Statements fired by JPA:
spring.jpa.show-sql=true
# Set sane Spring Hibernate properties:
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# Prevent JPA from trying to Initialize...:
spring.jpa.database=postgresql
# ... and do not Auto-Detect the Database:
spring.datasource.initialize=false
# Prevent Hibernate from Automatic Changes to the DDL Schema:
spring.jpa.hibernate.ddl-auto=none

同样的事情发生在 Hibernate 内部，它尝试读取元数据来初始化 JDBC 设置。为了防止那些我们不想完成的连接，我添加了一个

hibernate.properties由 Hibernate 自动读取的属性文件：

hibernate.temp.use_jdbc_metadata_defaults=false

测试应用程序
我们首先将客户插入到 Tenant 的数据库中TenantOne：

> curl -H "X-TenantID: TenantOne" -H "Content-Type: application/json" -X POST -d "{\"firstName\" : \"Philipp\", \"lastName\" : \"Wagner\"}"  http://localhost:8080/customers

{"id":13,"firstName":"Philipp","lastName":"Wagner"}

> curl -H "X-TenantID: TenantOne" -H "Content-Type: application/json" -X POST -d "{\"firstName\" : \"Max\", \"lastName\" : \"Mustermann\"}"  http://localhost:8080/customers

{"id":14,"firstName":"Max","lastName":"Mustermann"}

获取所有客户的列表TenantOne现在将返回两个客户：

> curl -H "X-TenantID: TenantOne" -X GET http://localhost:8080/customers

[{"id":13,"firstName":"Philipp","lastName":"Wagner"},{"id":14,"firstName":"Max","lastName":"Mustermann"}]

在请求所有客户的列表时TenantTwo返回一个空列表：

> curl -H "X-TenantID: TenantTwo" -X GET http://localhost:8080/customers

[]

我们现在可以将客户插入TenantTwo数据库：

> curl -H "X-TenantID: TenantTwo" -H "Content-Type: application/json" -X POST -d "{\"firstName\" : \"Hans\", \"lastName\" : \"Wurst\"}"  http://localhost:8080/customers

{"id":15,"firstName":"Hans","lastName":"Wurst"}

查询TenantOne数据库仍然返回两个客户：

> curl -H "X-TenantID: TenantOne" -X GET http://localhost:8080/customers

[{"id":13,"firstName":"Philipp","lastName":"Wagner"},{"id":14,"firstName":"Max","lastName":"Mustermann"}]

现在查询TenantTwo数据库将返回插入的客户：

> curl -H "X-TenantID: TenantTwo" -X GET http://localhost:8080/customers

[{"id":15,"firstName":"Hans","lastName":"Wurst"}]

结论
使用 Spring Boot 和 Postgres 行级安全性提供多租户真的很容易。

如果您在项目中遇到问题，请随时在 GitHub 存储库中打开问题。