ApsaraDB RDS:Isolate data of SaaS tenants based on RLS policies

How it works

Each tenant is assigned a unique tenant ID. When a tenant's application connects to the database, it sets a session variable (app.tenant_id) to that tenant's ID. The RLS policy then filters every query automatically, so tenants can only read or write rows where tenant_id matches their session variable.

Think of an RLS policy as a WHERE clause that PostgreSQL silently appends to every query:

-- RLS policy:
CREATE POLICY sh_vip_t_user ON sh_vip.t_user
  FOR ALL TO PUBLIC
  USING (tenant_id = (current_setting('app.tenant_id'::text))::bigint);

-- Equivalent to adding this WHERE clause to every query:
SELECT * FROM sh_vip.t_user
WHERE tenant_id = current_setting('app.tenant_id')::bigint;

Key concepts

RLS policy scope

An RLS policy applies to SELECT, INSERT, UPDATE, DELETE, or ALL statements, and can be attached to one or more roles. If multiple policies apply to the same table, they are combined with logical OR—a row is visible if any one policy returns TRUE.

USING vs. WITH CHECK

• USING clause: Filters existing rows. Rows where the expression evaluates to FALSE are invisible to the query.

• WITH CHECK clause: Validates new or updated rows before they are written. If omitted, the USING expression is also used as the WITH CHECK expression.

BYPASSRLS

Roles with the BYPASSRLS attribute bypass all RLS policies. Use this for administrative accounts that need full-table access, such as bulk updates or cross-tenant reporting.

Statements not covered by RLS

TRUNCATE and REFERENCES are not subject to RLS policies. DELETE and UPDATE statements that operate across all rows without a WHERE clause are also not subject to RLS. Revoke these permissions from tenant accounts.

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for PostgreSQL instance (PostgreSQL 15.0 or later)

• An Elastic Compute Service (ECS) instance in the same region and virtual private cloud (VPC) as the RDS instance

• The private IP address of the ECS instance added to the RDS instance whitelist

• The PostgreSQL client installed on the ECS instance (download)

Set up tenant isolation

env PGOPTIONS="-c app.tenant_id=1" psql -h pgm-****.pg.rds.aliyuncs.com -d rls_vip -U t1 -p 5432 -W

SELECT * FROM sh_vip.t_user;

 tenant_id | user_id | tenant_name
-----------+---------+-------------
         1 |      10 | Zhang San
(1 row)

-- Succeeds
INSERT INTO sh_vip.t_user VALUES (1, 11, 'Leo');

-- Fails with: new row violates row-level security policy for table "t_user"
INSERT INTO sh_vip.t_user VALUES (2, 21, 'Darren');

-- Succeeds
UPDATE sh_vip.t_user SET tenant_name = 'Leo1' WHERE user_id = 11;

-- Fails silently (0 rows affected)
UPDATE sh_vip.t_user SET tenant_name = 'Leo1' WHERE user_id = 20;

-- Succeeds
DELETE FROM sh_vip.t_user WHERE user_id = 11;

-- Fails silently (0 rows affected)
DELETE FROM sh_vip.t_user WHERE user_id = 20;

env PGOPTIONS="-c app.tenant_id=2" psql -h pgm-****.pg.rds.aliyuncs.com -d rls_vip -U t2 -p 5432 -W

SELECT * FROM sh_vip.t_user;

 tenant_id | user_id | tenant_name
-----------+---------+-------------
         2 |      20 | Li Si
(1 row)

CREATE USER bypassrls_user BYPASSRLS PASSWORD 'TestPW123!';
GRANT ALL ON SCHEMA sh_vip TO bypassrls_user;
GRANT ALL ON TABLE sh_vip.t_user TO bypassrls_user;

Java example

The following example shows how to set app.tenant_id at the JDBC connection level using Maven and the PostgreSQL JDBC driver.

1. Add the PostgreSQL driver dependency to pom.xml.

   <dependency>
     <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <version>42.3.1</version>
   </dependency>

2. Create a SaaSrlsController class. The key is setting app.tenant_id in the connection options property—PostgreSQL applies RLS filtering for every statement on this connection.

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.Properties;
   
   public class SaaSrlsController {
     public static void main(String[] args) {
       try {
         Class.forName("org.postgresql.Driver");
       } catch (ClassNotFoundException e) {
         e.printStackTrace();
       }
   
       String hostname = "pgm-****.pg.rds.aliyuncs.com"; // RDS instance endpoint
       int    port     = 5432;
       String dbname   = "rls_vip";
       String username = "t1";
       String password = "*****";
   
       String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname;
   
       try {
         Properties props = new Properties();
         props.setProperty("user",     username);
         props.setProperty("password", password);
         // Set the tenant ID for this connection — RLS enforces isolation automatically
         props.setProperty("options",  "-c app.tenant_id=1");
   
         Connection connection = DriverManager.getConnection(dbUrl, props);
         Statement  statement  = connection.createStatement();
   
         ResultSet rs = statement.executeQuery("SELECT * FROM sh_vip.t_user;");
         while (rs.next()) {
           System.out.println(rs.getString("tenant_id") + "," + rs.getString("tenant_name"));
         }
       } catch (SQLException e) {
         e.printStackTrace();
       }
     }
   }

Usage notes

• Disabling RLS: Use ALTER TABLE table_name SET (row_security = off) to disable RLS, or set row_security = off at the session level. When RLS is disabled, all rows are accessible regardless of any policies. Restrict who can disable RLS to prevent accidental data exposure.

• Managing policies: Use CREATE POLICY, ALTER POLICY, and DROP POLICY to manage RLS policies. Policy names must be unique per table but can be reused across different tables.

• Policy evaluation: When multiple policies apply to a table, they are combined with logical OR. A row is accessible if any one policy returns TRUE.

• Statements not covered: TRUNCATE and REFERENCES are not subject to RLS. Revoke these permissions from tenant accounts to prevent cross-tenant data access.

What's next

• Add an index on the tenant_id column to avoid full-table scans when RLS filters rows at scale.

• Create a PostgreSQL event trigger that automatically enables row level security whenever a new table is created, so new tables are never accidentally left unprotected.