休眠和多租户数据库在PostgreSQL中使用模式 [英] Hibernate and Multi-Tenant Database using Schemas in PostgreSQL

问题描述

背景

我正在研究未来的多租户Web应用程序，该应用程序需要支持数千个用户。该应用程序正在基于Java的Play上构建！使用JPA / Hibernate和PostgreSQL的MVC框架。

我观看了Guy Naor在在Rails中编写多租户应用程序，其中他谈到了几种方法多租户（数据隔离随着列表的减少而减少）：

1. 每位客户都有独立的数据库
   
2. 一个数据库，每个客户都有独立的模式和表（表名称空间）。


3. 一个数据库，包含一组带有客户ID列的表。

我在第2种方法中找到了解决方法，在该方法中，某种用户标识从请求中解析出来，然后用于访问该用户表空间。在进行任何查询之前，给出postgres SET search_path to customer_schema，public 命令，以确保客户的表是查询的目标。这很容易通过 Play！中的控制器方法中的 @Before 控制器注释来完成（这是Guy在他的rails示例中使用的方法）。 postgres中的 search_path 与操作系统中的 $ PATH 完全相同。真棒！

这听起来很棒，但是我立即遇到了在JDBC / Hibernate / JPA栈上实现它的困难，因为似乎没有在运行时动态切换模式。

问题

如何获得JDBC或Hibernate支持在运行时动态地切换postgres模式？

似乎数据库连接是由连接工厂静态配置的（请参阅：如何使用hibernate管理一个数据库上的许多模式）。我发现类似的问题，每个用户使用多个SessionFactorys类似的问题，但是因为我理解SessionFactorys是重量级的对象，所以你可以支持数百个用户，更不用说成千上万的用户了。 b
$ b

我还没有完全致力于上述第二种方法，但我还没有完全放弃第3种方法。

  SET search_path to customer_schema，public 
 $ / code> 

可以在同一个连接/会话/事务中随时获得。它只是另一个命令，如 SELECT 1; 。更多信息请参阅手册。

当然，您也可以为每个用户预设 search_path 。

  ALTER ROLE foo SET search_path = foo，public; 
  

如果每个用户或其中很多用户的模式与他们的用户名相匹配，您可以简单地postgresql.conf中的默认设置：

  search_path =$ user，public; 
  

更多设置 search_path 这里：

search_path如何影响标识符解析和当前模式

Background

I am working on a future multi-tenant web application that will need to support thousands of users. The app is being built on top of the Java based Play! MVC Framework using JPA/Hibernate and postgreSQL.

I watched Guy Naor's talk on Writing Multi-tenant Applications in Rails in which he talks about a few approaches to multi-tenancy (data isolation decreases as you go down the list):

1. Each customer has a separate database
2. One database with separate schemas and tables (table namespaces) for each customer.
3. One database with 1 set of tables with customer id columns.

I settled on approach #2, where a user id of some sort is parsed out of a request and then used to access that users tablespace. A postgres SET search_path TO customer_schema,public command is given before any query is made to make sure the customer's tables are the target of a query. This is easily done with @Before controller annotations in controller methods in Play! (this is the approach Guy used in his rails example). The search_path in postgres acts exactly like the $PATH does in an OS; awesome!

All this sounded great, but I immediately ran into difficulties in implementing it on top of a JDBC/Hibernate/JPA stack because there doesn't seem to be a way to dynamically switch schemas at runtime.

The Problem

How do I get either JDBC or Hibernate to support dynamically switching postgres schemas at runtime?

It seems database connections are statically configured by a connection factory (see: How to manage many schemas on one database using hibernate). I have found similar questions with similar answers of using multiple SessionFactorys per user, but since I understand SessionFactorys are heavy weight objects so it's implausible that you could support hundreds of users, let alone thousands of users, going this route.

I haven't committed myself completely to approach #2 above, but I haven't quite abandoned it for approach #3 quite yet either.

解决方案

You can execute the command

SET search_path TO customer_schema,public

as often as you need to, within the same connection / session / transaction. It is just another command like SELECT 1;. More in the manual here.

Of course, you can also preset the search_path per user.

ALTER ROLE foo SET search_path=foo, public;

If every user or many of them have a schema that matches their user name, you can simply go with the default setting in postgresql.conf:

search_path="$user",public;

More ways to set the search_path here:
How does the search_path influence identifier resolution and the "current schema"

这篇关于休眠和多租户数据库在PostgreSQL中使用模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！