Multitenant PostgreSQL

As more and more of our applications move into "the cloud", multi-tenancy has become a pretty big thing these days. In a nutshell, "multi-tenancy" means handling multiple customers data using, say, a single server. This concept scales, of course, to clusters, etc., but the concept is the same: a bunch of people’s data all mixed together in one big bucket. The problem, then, for the development team is isolating one customer’s data from another’s, disallowing, for example, the viewing or editing of another customer’s information. There are a myriad of ways to accomplish this, but I’d like to discuss here a way to accomplish this using a single database.

While the concept can certainly be applied on just about any database, for this discussion, we’ll use PostgreSQL, my favorite database (and, yes, as a geek I’m allowed to say things like that). The concepts we’ll use are views, triggers, and session variables.

To keep things simple, our entity/model will be a very simple one called Item, which has one column, data. The DDL for its table might look something like this:

It’s not very exciting, but that’s OK. I’m probably not either. In thinking about multiple customers, though, we need a way to discriminate between one customer’s Items and another, so we’ll add a company ID:

But how do we restrict customer #1 from seeing customer #2’s data? We’ll do that with a view:

As you can tell, that’s not quite going to work. Where does the value for company_id come from? The answer to that is session variables. Unfortunately, as best as I can tell, Postgres doesn’t support this idea natively, so we have to roll our own, which we’ll do using plperl, the Postgres extension which allows writing stored procedures in Perl. There are other languages available, and perhaps I’ll port this to, say, Python, because Perl is pretty nasty stuff. :) Nevertheless, this is working code:

This creates two functions, set_var and get_var, whose functionality should be apparent from the function names. With these in place, we can update the view as follows:

To get the value from a function in Postgres, you SELECT from it, which we’ve added to the query that builds the view. You’ll also notice that get_var and set_var deal with strings, so the value needs to be cast to NUMERIC or the view creation will fail.

With all of this in place, we can test this using psql:

As you can see, the contents of the view company_items is controlled by the current state of the session variable company_id. It’s also clear that if it’s not set, then the view is empty, clearly indicating that the app must take care to set this session variable on each use. In the context of a web app, this would be done per request, just after authentication: the user’s credentials are verified, the company to which the user belongs is identified, and the session variable is set.

One final nugget. In such an environment, it would be nice if the application didn’t have to remember to set the company ID on the Company_Item explicitly. We can, in fact, handle that in the database layer with a trigger:

With this trigger in place, whenever a client inserts into either company_items or items, the field company_id is set automatically to whatever the current state of the session variable company_id is. Depending on your application’s architecture, frameworks, etc., this may not be necessary, but, if it’s helpful (and possible), this is an approach for handling it.

There you have it. The seeds, at least, of a fairly robust multitenant database approach that doesn’t involve separate databases or schemas for each tenant. As long as your application operates on only the views (exercise for the reader: can access to the tables be restricted at the database-level, leaving only the views accessible?) you should have a nice, clean segregation of customer data that is easy to maintain and migrate.

Find any holes? Problems? Brain dead ideas? Let me know below! 8-)