5 ms·

Wow, just this week I started a project that will be my first multitenant website, using PostgreSQL no less, and have been wondering how to handle that in the d

Wow, just this week I started a project that will be my first multitenant website, using PostgreSQL no less, and have been wondering how to handle that in the db. I take a break, pop onto HN, and the top comment of the top story explains how to do exactly that. Thanks! You wouldn't happen to have come across any good tutorials on using PG schemas for this purpose have you? Also, do schemas provide enough separation of data, when strict client/tenant confidentially is a requirement?

sant0sk1 14y ago
Awhile back I wrote up some details on how to manage it from Rails: http://blog.jerodsanto.net/2011/07/building-multi-tenant-rails-apps-with-postgresql-schemas/ http://blog.jerodsanto.net/2011/07/building-multi-tenant-rai...
mekoka 14y ago
Unfortunately information is a bit scattered across the web about it. The main lines though are this: - at the start of your request, grab a connection to the database and make sure that it's only accessible to that specific request (i.e. ensure thread-safety). - start a transaction within that connection. - assuming you'll have a http://subdomain.domain.tld/url http://subdomain.domain.tld/url scheme such as http://clienta.myapp.com/some/controller http://clienta.myapp.com/some/controller, to switch to schema 'clienta' you'll execute query "SET search_path = clienta;" - now you can execute other statements and transactions within that main transaction. - at end of request, commit the main transaction, reset and release the connection to make it available to other threads. That's a broad description of the approach, but it should be enough to get you started. As for the data segregation, see what this commenter had to say about it in his (her?) 3rd point http://news.ycombinator.com/item?id=1567089 http://news.ycombinator.com/item?id=1567089 . I found this video that has some illustration near the end of the presentation. The presenter uses Rails. I use Flask and SQLAlchemy. Flask has some handy utilities (Flask.before_request(), Flask.teardown_request()) for this type of setting/unsetting of connection and schema. I imagine Django also has a pretty straightforward approach to this. http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-applications-in-rails-guy-naor.html http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi...
einhverfr 14y ago
We call a "schema" what they call a "database" in MySQL. The overall PostgreSQL documentation is pretty good. Note that you usually want to prefix your table names, etc, with schema names so instead of create table foo, you have create table myschema.foo Note that roles and users cannot be assigned to schemas (or databases either) since they are cluster-global.
ruckusing 14y ago
This is a good post all about using PG Schemas and Rails/ActiveRecord. Ideas could be adapted for Django, etc. http://blog.jerodsanto.net/2011/07/building-multi-tenant-rails-apps-with-postgresql-schemas/ http://blog.jerodsanto.net/2011/07/building-multi-tenant-rai...

sant0sk1 14y ago

Awhile back I wrote up some details on how to manage it from Rails: http://blog.jerodsanto.net/2011/07/building-multi-tenant-rails-apps-with-postgresql-schemas/ http://blog.jerodsanto.net/2011/07/building-multi-tenant-rai...

mekoka 14y ago

Unfortunately information is a bit scattered across the web about it. The main lines though are this: - at the start of your request, grab a connection to the database and make sure that it's only accessible to that specific request (i.e. ensure thread-safety). - start a transaction within that connection. - assuming you'll have a http://subdomain.domain.tld/url http://subdomain.domain.tld/url scheme such as http://clienta.myapp.com/some/controller http://clienta.myapp.com/some/controller, to switch to schema 'clienta' you'll execute query "SET search_path = clienta;" - now you can execute other statements and transactions within that main transaction. - at end of request, commit the main transaction, reset and release the connection to make it available to other threads. That's a broad description of the approach, but it should be enough to get you started. As for the data segregation, see what this commenter had to say about it in his (her?) 3rd point http://news.ycombinator.com/item?id=1567089 http://news.ycombinator.com/item?id=1567089 . I found this video that has some illustration near the end of the presentation. The presenter uses Rails. I use Flask and SQLAlchemy. Flask has some handy utilities (Flask.before_request(), Flask.teardown_request()) for this type of setting/unsetting of connection and schema. I imagine Django also has a pretty straightforward approach to this. http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-applications-in-rails-guy-naor.html http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi...

einhverfr 14y ago

We call a "schema" what they call a "database" in MySQL. The overall PostgreSQL documentation is pretty good. Note that you usually want to prefix your table names, etc, with schema names so instead of create table foo, you have create table myschema.foo Note that roles and users cannot be assigned to schemas (or databases either) since they are cluster-global.

ruckusing 14y ago

This is a good post all about using PG Schemas and Rails/ActiveRecord. Ideas could be adapted for Django, etc. http://blog.jerodsanto.net/2011/07/building-multi-tenant-rails-apps-with-postgresql-schemas/ http://blog.jerodsanto.net/2011/07/building-multi-tenant-rai...