Isolate multi-tenant data in PostgreSQL DB using Row Level Security (RLS)

With the ease of deployment that SaaS services bring, most organizations are moving their infrastructure in the cloud. The ease of deployment can also lead to increasing costs if you do not plan ahead of time. Spinning up a new database for every new customer can easily be automated today, but down the line that would increase the hosting costs. At the same time, sharing customer data within a common database could result in leaking sensitive information across customers which could harm your reputation as a software provider.

Question: What are we trying to learn from this article?

1. How to create a database with 3 layers of security — postgres as root, table owner for DML and DDL, end/api users with DML permissions
2. How to grant the right access privileges to non-superusers so they can access the information in the tables without being able to manipulate the structure
3. Understanding how to create new schemas and assign owners to it
4. How to set the search_path for users so they can view tables for their schemas
5. How to set config_parameters for a postgres session
6. Finally — understand how to implement RLS by using separate users and a common DB user

Database design for schema, roles and permissions

PostgreSQL comes with a feature called Row Level Security (RLS) that restricts access to table data based on a row security policy. Here are some notable features of PostgreSQL’s RLS:

1. The restrictions can be applied based on specific commands, to roles or to both.
2. Superusers and roles with the BYPASSRLS attribute always bypass the RLS when accessing a table.
3. Table owners normally bypass RLS as well but you can execute a special command to enforce RLS on table owners as well.
4. Only table owners can enable/disable RLS as well as add policies to a table. Multiple policies can be defined for a table.

Think of RLS in a table as a means to share data from all customers in a single table without running the risk of data accessed across tenants. Think of the RLS policy as a WHERE clause that filters the data for the tenant at the Database layer without having you to deal with the additional burden of placing the filtering logic in the application layer — how convenient.

There are two ways by which you can achieve the data isolation across tenants. The first one is where you create a new DB user for each tenant that you onboard that matches the tenant identifier within your data. The second approach is to use a shared database user and set a configuration_property every time you connect to the DB that can be be mapped to the customer_code for filtering.

For the sake of simplicity, I’d use a customer_code as the identifier. Let’s image you have a hosted product that stores your customers’ contacts. Image the contacts table contains the following fields:

contacts

• customer_code (text)
• contact_first_name (text)
• contact_last_name (text)
• contact_email (text)
• contact_phone (text)

Common DB setup

Switch to user postgres and connect to database

~$ sudo su postgres
~$ psql

Create new database, user(s) and schema. Revoke privileges for all users from public schema. In addition, we also set the search_path for the apps_owner and api_user to be apps (schema) so that executing \d returns the list of objects in the schema v/s showing a message that no relations exist in the database.

-- Create the schema owner
CREATE USER apps_owner WITH PASSWORD 'appsowner';
-- If you're using RDS, you would have to assign the role apps_owner to postgres/root whoever is creating the schema, postgres in our case.
GRANT apps_owner TO postgres;
-- Create database
CREATE DATABASE apps;
-- Connect to the apps database
\connect apps;
-- Create new schema for the apps
CREATE SCHEMA apps AUTHORIZATION apps_owner;
-- Set search_path for the apps_owner user
ALTER ROLE apps_owner SET search_path TO apps;
-- Remove ability for all users to do everything in public schema
REVOKE ALL ON SCHEMA public FROM public;
-- Ensure users can list down objects in public schema
GRANT USAGE ON SCHEMA public TO public;
-- Create an api_user that will only have DML privileges on apps
CREATE USER api_user WITH PASSWORD 'apiuser';
-- Set the search_path for the api_user to be apps
ALTER ROLE api_user SET search_path TO apps;

Disconnect the database and now connect as the apps_owner user that we created to perform table operations on the database and schema and to grant DML privileges to api_user. It’s very important that you execute the GRANT queries using the apps_owner user and not using the postgres user — or otherwise it wouldn’t work.

• Granting permissions to the api_user at this stage would ensure that they have privileges to perform DML operations on tables in the apps schema.
• Granting permission on a table does not automatically extend permissions to any sequences used by the table, including sequences tied to SERIAL columns. Permissions on sequences must be set separately.

~$ psql -hlocalhost -Uapps_owner -dapps -W
(enter your database user password to login)
-- Grant privileges for user to list objects
GRANT USAGE ON SCHEMA apps TO api_user;
-- Grant privileges for user to existing tables and sequences
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA apps TO api_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA disclosures TO api_user;
-- Grant privileges for user on all future tables and sequences in apps by default
ALTER DEFAULT PRIVILEGES IN SCHEMA apps GRANT ALL PRIVILEGES ON TABLES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA disclosures GRANT ALL PRIVILEGES ON SEQUENCES TO api_user;
-- Create a table for contacts in the apps schema
CREATE TABLE apps.contacts
(
    id serial,
    customer_code text,
    contact_name text,
    contact_email text,
    contact_phone text
);
-- Insert some sample data into the table
INSERT INTO apps.contacts(customer_code, contact_name, contact_email, contact_phone) VALUES('apollo', 'John Doe', 'jdoe@cust-apollo.com', '536-847-5857');
INSERT INTO apps.contacts(customer_code, contact_name, contact_email, contact_phone) VALUES('apollo', 'David Brown', 'dbrown@cust-apollo.com', '254-457-8452');
INSERT INTO apps.contacts(customer_code, contact_name, contact_email, contact_phone) VALUES('ryndall', 'Mary Miller', 'mmiller@cust-ryndall.com', '235-654-7845');
INSERT INTO apps.contacts(customer_code, contact_name, contact_email, contact_phone) VALUES('ryndall', 'Tommy Smith', 'tsmith@cust-ryndall.com', '854-965-6514');
-- Enable RLS for the table
ALTER TABLE apps.contacts ENABLE ROW LEVEL SECURITY;
-- FYI: To force table owner to No BYPASSRLS. Avoid this so user can query the master set of data
-- ALTER TABLE apps.contacts FORCE ROW LEVEL SECURITY;

Creating a separate DB user per tenant

Segregating the data in a shared table by a separate DB user per tenant is definitely a recommended approach if the tenants or applications themselves have direct access to the database table. Or if the number of tenants are limited (e.g. a connector configuration table that stores the configuration details for the connectors and you only anticipate a handful of connectors ever be built for your systems integration), this approach can be handy.

Let’s login to the database as postgres user create new database users, one per company.

-- Create user apollo and ryndall with DML privileges on apps
CREATE USER apollo WITH PASSWORD 'apollo';
CREATE USER ryndall WITH PASSWORD 'ryndall';
-- Set the search_path for the users to be apps
ALTER ROLE apollo SET search_path TO apps;
ALTER ROLE ryndall SET search_path TO apps;

Now let’s login as the apps_owner user and set permissions for the new users.

-- Grant privileges for user to list objects
GRANT USAGE ON SCHEMA apps TO apollo;
GRANT USAGE ON SCHEMA apps TO ryndall;
-- Grant privileges for user to existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA apps TO apollo;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA apps TO ryndall;
-- Grant privileges for user on all future tables in apps by default
ALTER DEFAULT PRIVILEGES IN SCHEMA apps GRANT ALL PRIVILEGES ON TABLES TO apollo;
ALTER DEFAULT PRIVILEGES IN SCHEMA apps GRANT ALL PRIVILEGES ON TABLES TO ryndall;

Stay logged in as the apps_owner user and let’s create a new policy that will segregate data based on the logged in DB user.

CREATE POLICY customer_currentuser_isolation_policy ON apps.contacts
USING (customer_code = current_user);

If you’ve used the common DB user approach already (below), then please delete the other policy

DROP POLICY IF EXISTS customer_shareduser_isolation_policy ON contacts;

Sharing a common DB user across all tenants

This approach is useful when you have an unlimited number of tenants that are going to share a common table. e.g. an account table that contains the customer’s secure information about EIN, annual revenue, number of employees, etc. If you have thousands of records in the table, it would be quite an overhead to create a new DB user per record. Any unique identifier from the table can be setup as a configuration_property during connection initiation and place a policy on the table to filter rows based on the property set. But for the sake of it, we’ll use the contacts table in the above example to focus on the DB and user setup needed to configure this approach.

Connect to the database using postgres user and create a new DB user with only DML (SELECT/INSERT/UPDATE/DELETE) privileges.

-- Create user if not already created
CREATE USER api_user WITH PASSWORD 'apiuser';
ALTER ROLE api_user SET search_path TO apps;

Connect to database using apps_owner user to create policy on the table.

~$ psql -hlocalhost -Uapps_owner -dapps -W
(enter your database user password to login)
/*
Create a policy to map config_property to unique identifier. Requires you to set 'cust.code' for every DB connection that equals the customer_code in the table for filtering
*/
CREATE POLICY customer_shareduser_isolation_policy ON apps.contacts
USING (customer_code = current_setting('cust.code'));

If you’ve used the common DB user approach already (below), then please delete the other policy

DROP POLICY IF EXISTS customer_currentuser_isolation_policy ON contacts;

To test out the magic, connect to the database using api_user and let’s set the config_setting and run some select commands:

1. You can set the config_param using SET or set_config() function.
2. To reset the value of config param, you can use RESET config_param.

~$ psql -hlocalhost -Uapi_user -dapps -W
(enter your database user password to login)
SET cust.code='apollo';
SELECT * FROM apps.contacts;
SELECT set_config('cust.code', 'ryndall', false);
SELECT * FROM apps.contacts;