On 5/21/20 1:23 PM, Israel Brewster wrote:
On May 21, 2020, at 12:12 PM, Sándor Daku <daku.sandor@xxxxxxxxx
<mailto:daku.sandor@xxxxxxxxx>> wrote:
Hi,
On Thu, 21 May 2020 at 18:14, Christopher Browne <cbbrowne@xxxxxxxxx
<mailto:cbbrowne@xxxxxxxxx>> wrote:
On Thu, 21 May 2020 at 11:53, Israel Brewster
<israel@xxxxxxxxxxxxxxxx <mailto:israel@xxxxxxxxxxxxxxxx>> wrote:
- Table-based tenancy (e.g. - each table has a "tenant_id" and
queries need to specify the tenant)
The database/schema per tenant solution can be tedious when you want
to modify something on the structure and you have numerous tenants.
Therefore I used the "tables with tenant_id" version in a similar
situation but with a slight twist. One of the biggest issue of this
solution is that if you forget to add the tenant_id to the where
clause you are going to reveal one tenant's data to another.
I came up with the solution that the database user have no privileges
for accessing the base tables. Instead of that I generate views for
each tenant and they can access their own data in the underlying table
through these views. Now if forget to address the right tenant in my
client code(it still happens sometimes) and try to directly access the
base tables I get a strongly worded reminder from the server.
Nice solution! I think I may go to something like that once I upgrade to
a cloud solution that lets me add multiple users to the DB (the free
tier of Heroku does not). In the meantime, while I just have the single
customer, I can fake it easily enough.
Is there any shortcuts for referencing the proper views, or do you just
append/prepend something to every table reference in your SQL? One nice
thing about the database/schema approach is that I can just specify the
search_path (or database) in the connection command, and then all the
table references remain the same for all tenants. Also helps avoid the
situation you mentioned where you forget to address the right tenant,
since you only have to do it in one place. Of course, as you said, it
can be tedious when you want to modify the structure. I’ll have to think
about that a bit more.
If you want to take the tedium out of it take a look at Sqitch:
https://sqitch.org/
Then all you have to do is create the change once and deploy to the
targets.
---
Israel Brewster
BrewsterSoft Development
http://www.brewstersoft.com <http://www.brewstersoft.com/>
Home of EZPunch and Lyrics Presenter
Regards,
Sándor
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
