Rails多租户架构，限制对多个租户的访问 [英] Rails multitenant architecture, scoping access to multiple tenants

问题描述

目前，我们具有单租户数据库体系结构，其中MySQL可运行100个以上的数据库.我们使用Apartment gem在子域上切换数据库连接，一切都变得很花哨！

We have a single-tenant database architecture at the moment with MySQL running upward of 100 databases. We switch database connection on subdomain using the Apartment gem and all is dandy!

但是，我们现在需要创建所谓的"Umbrella"客户端，该客户端可以访问一组现有客户端中的所有数据.对于我们的单租户数据库体系结构，我认为这不是立即可行的(我调查了一下，查询多个MySQL数据库似乎有些令人毛骨悚然)，所以我开始研究采用Postgres模式的不同实现.

However, we have a requirement now to create so-called "Umbrella" clients which can access all the data from a group of our existing clients. I don't see this as immediately feasible with our single-tenant database architecture (I looked into it and querying multiple MySQL databases just seemed hellish), so I'm beginning to look at different implementations with Postgres schemas.

我正在寻找一些建议:

• 是否可以在Postgres中查询多个模式并以某种方式整理结果(寻找Rails的实现)?我可以预见主键冲突的问题吗?

• is it possible to query multiple schemas in Postgres and collate the results somehow (looking for Rails implementation)? I can foresee problems with conflicting primary keys?

最好以某种方式创建一个新的架构 表示/复制需要的模式组中的所有数据 被访问?它需要是实时的.

Would it be better to have a new schema that is somehow represents/duplicates all the data in the group of schemas that need to be accessed? It would need to be realtime.

如果是这样，是否可以在我当前的多个数据库中实现类似的效果 用MySQL设置? (以减轻痛苦)

If so, can something similar be achieved in my current multiple DB set-up with MySQL? (to minimise the pain)

我对使用数据库字段在MySQL中实现多租户持谨慎态度，因为该产品的数据安全性/隐私性是一件很重要的事情，这样开发人员就有很大的潜在错误发生机会.

I'm wary of using a database field to achieve multitenancy in MySQL as data security/privacy is a huge thing for this product, and there's so much potential for developer error that way.

推荐答案

只是通过高级方法来思考此问题.

just thinking through this on a high level approach to this problem.

您可以创建一个pg 视图表来访问此表数据(尽管 比访问数据库本身要慢).

You could create a pg view table to access this data (although it will be slower than accessing the databases themselves).

然后，您希望表中有足够的唯一字段来创建复合键或复合键. (然后，您不必创建新的键列，而只需创建索引).由于Rails 3与ORM无关，因此您可以使用DataMapper(或者可能是新的ROM gem)为该模型建立连接.

Then you hopefully have enough unique fields in your table to create a compound or composite key. (Then you wouldn't have to create a new key column, just an index). Because Rails 3 is ORM agnostic, you could then use DataMapper (or maybe the new ROM gem) to establish the connection for this one model.

如果您使用复合键，请意识到您可能必须在模型中显式定义* to_param *方法才能将键构建为字符串.这是用于在URL中发送:id时将其展开.

If you do compound keys, realize that you might have to explicitly define the *to_param* method in your model to build the key as a string. This is for unwrapping the :id when you send it in a url.

您可以通过其他Postgres用户设置对此视图的访问权限，然后使用Rails的多个连接功能为其创建模型.我们以前这样做是为了汇总来自多个表的数据，但对显示的内容有一些限制，但是我不明白为什么这不适用于您的用例.

You can setup access to this view through a different Postgres user and then use Rails' ability for multiple connections to create a model for it. We did this previously to aggregate data from multiple tables with some restrictions on what was being shown, but I don't see why that wouldn't apply in your use case.

另一个选择是，也许您可​​以将Mongo用作瞬态查询数据库". BSON会自动为您提供唯一的密钥.您可以创建本质上是SQL标量对象的对象.虽然不确定在这种情况下您是否想写回原始数据库...但是您可以做到这一点.

Another option is that perhaps you could use Mongo as the "transient query database". BSON would give you unique keys automatically. And you could create Objects that are essentially SQL Scalar Objects. Not sure you'd want to do a write back to the original database in this case though... but you feasibly could do it.

最重要的是，IMO是我认为最好的解决方案在于房屋的数据库一侧，因为您正在使用多个数据库.在数据库层处理这些项目似乎是最好的解决方案.

Bottom line IMO is I think the best solution lies on the database side of the house because you're using multiple databases. Dealing with these items at the database layer seems the best solution.

说了这么多...这似乎还像是过程的气味.如果我正确阅读了该问题，则认为您在这种情况下真正想做的是 Hadoop 旨在...实质上是对相关数据进行映射/归约(又名大数据分析)

Having said all of this... this also seems like a process smell. If I read the problem as stated correctly, I think what you're really trying to do in this case is what Hadoop is designed for... essentially map/reduce of relevant data (aka Big Data Analysis)

祝你好运！

这篇关于Rails多租户架构，限制对多个租户的访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！