Multi-Tenant SaaS With PostgreSQL

So you have a multi-tenant SaaS application that is using PostgreSQL as the database of choice. As you are serving multiple customers, how do you protect each customer’s data? How do you provide full data isolation (logical and physical) between different customers? How do you minimize impact of attack vectors such as SQL Injection? How do you retain the flexibility to potentially move the customer to a higher hosting tier or higher SLAs?

1. One DB per customer

Instead of putting every customer’s data in one database, simply create one database per customer. This allows for physical isolation of data within your Postgres cluster. So, for every new customer that registers, do this as part of the workflow:

In the example above customer_template_v1 is a custom database template with all the tables, schemas, procedures pre-created.

Note: You can use Schema or Row Level Security (v9.5) to effect isolation. However, Schema and Row Level Security would only allow for logical isolation. You could go the other extreme and use a DB cluster (as opposed to a database) per customer to effect complete data isolation. But the management overhead makes it a less than ideal option in most cases.

2. Separate DB user(s) per customer

After the Database is created as mentioned above, create a unique Database user as well. This user only would have permission to one (and only one) database: customer_A.

Now, in your middleware code, make sure to connect to customer_A database only using customer_A_user. In other words, when a user from customer_A organization logs into your SaaS application, use appropriate database and database user name.

If you wish, you can even create separate READ and WRITE users. So, to create a read user for database: customer_A

With the above you have fine grained control in terms of database access privileges and every activity from the middleware needs to decide carefully as to which role (read or read/write) needs to be used for access.

So, what DB User/Role do you use to create the new customer database in the first place? Create a special DB User (say create_db_user) just for this purpose. Audit and monitor this user’s activity closely. Don’t use this DB User for anything else. Or you can create a new user for each new database and simply specify that at database creation time. Whatever happens, don’t use the Postgres root user for your web connections!

As you may have noticed, a number of SaaS applications give vanity URLs (example: https://customerA.example.com) to their customers. Some other SaaS applications have a concept of ‘customerId’ which is a required field for authentication into SaaS application. The benefit is two fold:

1. As the user logs into the SaaS application, the middleware code knows exactly which database to connect to.
2. This also helps to keep the URL space isolated, allowing the SaaS application to start isolation at the web server level itself.

3. Separate crypto keys per customer

If you are doing any encryption within the database (say with pgcrypto), make sure to use separate encryption keys for each customer. This adds cryptographic isolation between your customer data. Finally, when it comes to encryption and key management, avoid these common encryption errors developers keep making.

Comment and do let us know what other best practices make sense for multi-tenant SaaS access with PostgreSQL.