Multi tenancy with Schemas and RLS in PostgreSQL

Question

I am new to Postgres/databases and am thinking of how to design a multi tenant application. I read some of the basic stuff such as schemas supported by Postgres and Row Level Security which is added since Postgres 9.5. So in my case a tenant can have many users. Two different tenants can have users with same name ( Kind of like a hierarchy). So at the top level, I can have a schema for each tenant. And then within each there is a RLS policy for users. Can this be done in Postgres and is this a good option ? With RLS, I will need a role for each user. This will probably blow up since my understanding is that roles are global across schemas.

Or other thing I can think of is that I keep everything in same tables but have policies which honor the user as well as tenant columns in the table. Is this possible in Postgres ? For example, when I add a policy with current_users = "column_name" ,can I add another condition where I add a check that the top level tenant name matches. But where and how is this set, similar to SET ROLE "user"

Bit confused about the right approach to use.

"I will need a role for each user" - you already have that. A user is a role (with the "login" privilege). But if you don't need cross-tenant queries, then I agree with Neil: use one database per tenant — a_horse_with_no_name

Neil McGuigan Neil McGuigan · Accepted Answer · 2016-02-10T20:45:34

I would go with tenant per database. This is a little easier to backup (per tenant) and a little more secure by default. And a little easier to manage w pgAdmin.

You don't need a db user for each application user to use RLS, but it's probably a good idea.

Set db_user_namespace=true in postgresql.conf to allow per-db usernames. See http://www.postgresql.org/docs/9.5/static/runtime-config-connection.html for caveats

Multi tenancy with Schemas and RLS in PostgreSQL

1 Answers