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

22-08-02 banq

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

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

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

  1. 每个租户的数据库
    • 每个租户都有自己的数据库,并且与其他租户隔离。每个租户都有自己的房子。
  • 共享数据库,独立架构
    • 所有租户共享一个数据库,但有自己的数据库模式和自己的表。同一建筑物中的每个租户,但有自己的公寓。
  • 共享数据库,共享架构
    • 所有租户共享一个数据库和表。每个表都有一个带有租户标识符的列,它显示了该行的所有者。每个人都住在同一个公寓里,所有东西都用便签标记,以显示谁拥有它。



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

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

    创建数据库
    首先创建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
    
    <p class="indent">[{"id":13,"firstName":"Philipp","lastName":"Wagner"},{"id":14,"firstName":"Max","lastName":"Mustermann"}]
    

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

    > curl -H "X-TenantID: TenantTwo" -X GET http://localhost:8080/customers
    
    <p class="indent">[]
    

    我们现在可以将客户插入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
    
    <p class="indent">[{"id":13,"firstName":"Philipp","lastName":"Wagner"},{"id":14,"firstName":"Max","lastName":"Mustermann"}]
    

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

    > curl -H "X-TenantID: TenantTwo" -X GET http://localhost:8080/customers
    
    <p class="indent">[{"id":15,"firstName":"Hans","lastName":"Wurst"}]
    


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

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

    猜你喜欢