Monitoring and managing multiple tenants can add operational complexity, especially for small teams with limited resources.  To balance the need for rapid feature development with the benefits of multi-tenancy, one common approach is to use a shared database model with application-level tenancy control. However, relying on developers to manually add WHERE tenant_id=<> to every SQL query is highly risky and prone to errors. Perhaps, there is a better way?

Row Level Security

PostgreSQL enables you to assign row-level security policies, allowing you to control which rows are returned by queries based on the privileges defined in the policy. This is an effective method to ensure queries only return tenant-specific data. By defining policies that grant access to a single tenant at a time, you shift the responsibility for data access control from the application to the database, significantly reducing the risk of data leakage.

Before we proceed, you must be aware that the Row-Level Policies do not apply to the table owner. Therefore, we must create a different user to enforce privileges. 

• Create a new ROLE with login access such that we can use it in our application. 

CREATE ROLE dev WITH LOGIN PASSWORD 'supersecret';

• Create tenant table that contains the unique tenant_id that we will use the filter with RLS. 

CREATE TABLE app.tenant (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);


/** Seed some dummy data **/
INSERT INTO app.tenant(id, name) VALUES (1, 'one'), (2, 'two');

• Create product table that we will use to demonstrate enforcing RLS

CREATE TABLE app.product (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER,
    name VARCHAR(255) NOT NULL
);

/** Seed some dummy data **/
INSERT INTO app.product (tenant_id, name) 
VALUES 
    (1, 'Product A1'),
    (1, 'Product A2'),
    (2, 'Product B1'),
    (2, 'Product B2'),
    (1, 'Product C1'),
    (1, 'Product C2');

• Grant privileges to our newly created tables for our dev ROLE

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dev;

• Enable Row Level Security in product . Then, create a policy tenant_isolation_policy such that we check the app.current_tenant value to add the correct filter. 

ALTER TABLE product ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON product 
USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

Done 🎉 That’s all there is. Now let’s see it in action.

Demonstration

Logging in with the role devand running SELECT * FROM product; will return

ERROR: unrecognized configuration parameter "app.current_tenant"

This is expected since we haven’t configured app.current_tenant it yet. 

• SET app.current_tenant using SET command

/** You can run SHOW app.current_tenant to verify
the current value **/
SET app.current_tenant=1;
3. Now running SELECT * FROM product; will return only tenant 1 specific rows. 🆒
id  | tenant_id |    name    
----+-----------+------------
  1 |         1 | Product A1 
  2 |         1 | Product A2 
  5 |         1 | Product C1 
  6 |         1 | Product C2 

Try changing the value app.current_tenant to something else and see how Postgres automatically filters the row based on our tenant_isolation_policy. In our application layer, we can leverage this by dynamically setting the value of this configuration parameter to achieve our desired single database multi-tenancy. 🎉

For a full demonstration of how we can achieve this in Golang by dynamically setting and getting them tenant_id in the request context with the full example, you can check the repo here: https://github.com/Eddie023/multi-tenancy-with-RLS