在 PostgreSQL 中使用模式的 Hibernate 和多租户数据库 [英] Hibernate and Multi-Tenant Database using Schemas in PostgreSQL
问题描述
我正在开发一个未来的多租户 Web 应用程序，该应用程序需要支持数千个用户.该应用程序建立在基于 Java 的 Play 之上！使用 JPA/Hibernate 和 postgreSQL 的 MVC 框架.
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.
我在 在 Rails 中编写多租户应用程序，其中他谈到了几种多租户的方法(数据隔离随着列表的下降而降低):
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):
- 每个客户都有一个单独的数据库
- 一个数据库，每个客户都有单独的架构和表(表命名空间).
- 一个数据库，其中包含一组带有客户 ID 列的表.
我选择了方法 #2，其中从请求中解析出某种用户 ID，然后用于访问该用户表空间.postgres SET search_path TO customer_schema,public 命令在进行任何查询之前给出，以确保客户的表是查询的目标.这可以通过Play! 中控制器方法中的@Before 控制器注释轻松完成(这是Guy 在他的rails 示例中使用的方法).postgres 中的 search_path 与操作系统中的 $PATH 完全一样；太棒了！
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!
所有这些听起来都很棒，但我在 JDBC/Hibernate/JPA 堆栈之上实现它时立即遇到了困难，因为似乎没有一种方法可以在运行时动态切换模式.
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.
似乎数据库连接是由连接工厂静态配置的(请参阅:如何使用 hibernate 在一个数据库上管理多个模式).我发现类似的问题和每个用户使用多个 SessionFactorys 的答案相似，但由于我知道 SessionFactorys 是重量级的对象，所以你不可能支持数百个用户，更不用说数千个用户了，走这条路.
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.
我还没有完全承诺采用上面的方法 #2，但我也还没有完全放弃它用于方法 #3.
I haven't committed myself completely to approach #2 above, but I haven't quite abandoned it for approach #3 quite yet either.
推荐答案
可以执行命令
SET search_path TO customer_schema,public
在同一个连接/会话/事务中，根据需要经常使用.它只是另一个类似于 SELECT 1; 的命令.更多内容请参见手册.
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.
当然，您也可以为每个用户预设search_path.
Of course, you can also preset the search_path per user.
ALTER ROLE foo SET search_path=foo, public;
如果每个用户或其中许多用户都有与其用户名匹配的架构，您可以简单地使用 postgresql.conf 中的默认设置:
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;
更多设置search_path 在这里:
search_path 如何影响标识符分辨率和当前模式"
这篇关于在 PostgreSQL 中使用模式的 Hibernate 和多租户数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
