All Products
Search
Document Center

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

Last Updated:Dec 13, 2023

This topic describes how to isolate data permissions at the database level based on row-level security (RLS) policies. This helps manage small and micro-sized tenants in software as a service (SaaS) scenarios.

Background information

RLS policies in PostgreSQL are used to isolate data permissions. Administrators can create row-level access policies for tables to control which rows can be viewed and modified. In SaaS scenarios, the tenant-specific fields and RLS policies are used to resolve difficult O&M on a large number of databases and tables for small-sized tenants.

Introduction to RLS policies

RLS policies are supported in PostgreSQL 9.5.

RLS policies allow different users to view different data rows in a table. All operations on the data, including data queries and updates, are subject to the policies.

Application scope of RLS policies

  • RLS policies can be created for each of the following statements: SELECT, INSERT, UPDATE, and DELETE. RLS policies can also be created for all these statements.

  • An RLS policy can be attached to multiple roles.

  • RLS policies can be created for statements and attached to roles.

Syntax of RLS policies

CREATE POLICY name ON table_name 
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ] 
[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ] 
[ USING ( using_expression ) ] 
[ WITH CHECK ( check_expression ) ] 

The following list describes the parameters in the preceding statement:

  • If the USING clause is used, the system checks whether the existing statement complies with the created RLS policy. The check can be performed on the SELECT, INSERT, UPDATE, or DELETE statement or on all these statements.

  • If the WITH CHECK CLAUSE is used, the system checks whether a new statement complies with the created RLS policy. The check can be performed on the SELECT, INSERT, UPDATE, or DELETE statement or on all these statements.

  • If the USING clause is used but the WITH CHECK clause is not used, the USING clause also serves as the WITH CHECK clause. If you create multiple RLS policies for the same statement and TRUE is returned for one of the RLS policies, the statement complies with all the policies. For example, you create an RLS policy with ALL specified and create another RLS policy for the SELECT statement for a user. When the user executes the SELECT statement, if TRUE is returned for one of the RLS policies, the SELECT statement complies with all the policies.

Usage notes

  • You can use the ALTER TABLE statement to enable or disable RLS policies. If you want to disable RLS policies for applications, set row_security to off.

    ALTER TABLE table_name SET (row_security = off);
    Important

    After you execute the preceding statement, all queries and operations on tables are not subject to RLS policies. To ensure database security and data integrity, you must use RLS with caution and manage the users and roles who can disable RLS policies. In some cases, disabling RLS may cause data leaks or data corruption. We recommend that you disable RLS policies when necessary.

  • You can use the CREATE POLICY, ALTER POLICY, and DROP POLICY statements to create, modify, and delete RLS policies.

  • Each RLS policy has a name, and multiple policies can be defined for a table. Make sure that the names of RLS policies for a table are unique. However, RLS policies for different tables can have duplicate names. If multiple RLS policies are created for a table, the RLS policies are evaluated by using a logical OR.

  • Statements that are executed to manage all rows in a table, such as DELETE and UPDATE, and the TRUNCATE and REFERENCES statements are not subject to RLS policies due to their behavior characteristics. To ensure data security and integrity, we recommend that you revoke permissions on these statements when you grant permissions to a tenant.

Solution

Architecture

Each tenant is assigned a tenant ID specified by tenant_id. Each tenant deploys an application or sets app.tenant_id of a database to the tenant ID each time the tenant connects to the database. Before the connection, the tenant must enable RLS policies for tables. This way, the tenant can manage data in databases whose app.tenant_id is set to the value of tenant_id. This greatly reduces the threat of intrusion into applications and isolates data.

image

Examples

Step 1: Prepare the environment and configure account permissions

  1. Prepare an environment.

    Instance

    Database engine version

    Quantity

    Specifications

    ApsaraDB RDS for PostgreSQL instance

    15.0

    1

    4 cores, 16 GB, and enhanced SSDs (ESSDs)

    ECS instance

    CentOS 7.8 (64-bit)

    1

    4 cores, 16 GB

    Note

    If your RDS instance and ECS instance reside in the same region and virtual private cloud (VPC), you must add the private IP address of the ECS instance to the whitelist of the RDS instance.

  2. Create an account for your RDS instance.

    Note

    You need to only create a privileged account in the ApsaraDB RDS console. You can use SQL statements to create other accounts in subsequent steps. For more information about how to create a privileged account, see Create an account.

    Username

    Account type

    Description

    su_user

    Privileged account

    The database administrator account.

    bypassrls_user

    BYPASSRLS

    The account that is used to update multiple data records at a time.

    t1

    Standard account

    The account that is used to deploy the tenant application.

    t2

    Standard account

    The account that is used to deploy the tenant application.

  1. Install the PostgreSQL client on the ECS instance. For more information, see Official documentation.

  1. Use the CLI of the PostgreSQL client to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

  1. Use the privileged account to create test data.

    1. Create a database.

      CREATE DATABASE rls_vip;
    2. Create a schema in the rls_vip database.

      CREATE SCHEMA sh_vip;
    3. Create a table.

      CREATE TABLE sh_vip.t_user(
        tenant_id bigint,
        user_id bigint, 
        tenant_name varchar(50), 
        PRIMARY KEY(tenant_id, user_id)
      ); 
    4. Insert test data into the table.

      INSERT INTO sh_vip.t_user values(1, 10, 'Zhang San'); 
      INSERT INTO sh_vip.t_user values (2,20, 'Li Si'); 
  1. Use the privileged account to create a test account and an RLS policy.

    1. Create a standard user and grant required permissions to the user.

      - Create a standard account.
      CREATE USER t1 WITH PASSWORD 'TestPW123!';
      CREATE USER t2 WITH PASSWORD 'TestPW123!';
      
      - Grant the required permissions to the account.
      GRANT ALL ON SCHEMA sh_vip TO t1; 
      GRANT ALL ON TABLE sh_vip.t_user TO t1; 
      GRANT ALL ON SCHEMA sh_vip TO t2; 
      GRANT ALL ON TABLE sh_vip.t_user TO t2; 
    2. Create an RLS policy to add data to the sh_vip.t_user table.

      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
      );

      You can execute the following SQL statement to query the RLS policy details:

      SELECT * FROM pg_policies;

      Sample output:

       schemaname | tablename |  policyname   | permissive |  roles   | cmd |                              qual                              | with_ch
      eck
      ------------+-----------+---------------+------------+----------+-----+----------------------------------------------------------------+--------
      ----
       sh_vip     | t_user    | sh_vip_t_user | PERMISSIVE | {public} | ALL | (tenant_id = (current_setting('app.tenant_id'::text))::bigint) |
      (1 row)
  1. Check whether the RLS policy takes effect.

    \d sh_vip.t_user;
    Note
    • You must run the preceding command in the rls_vip database.

    • In the output, row security disabled is returned, which indicates that the RLS policy does not take effect.

    Sample output:

                            Table "sh_vip.t_user"
       Column    |         Type          | Collation | Nullable | Default
    -------------+-----------------------+-----------+----------+---------
     tenant_id   | bigint                |           | not null |
     user_id     | bigint                |           | not null |
     tenant_name | character varying(50) |           |          |
    Indexes:
        "t_user_pkey" PRIMARY KEY, btree (tenant_id, user_id)
    Policies (row security disabled):
        POLICY "sh_vip_t_user"
          USING ((tenant_id = (current_setting('app.tenant_id'::text))::bigint))
  1. Make the RLS policy take effect.

    ALTER TABLE sh_vip.t_user enable row level security;

    Check whether the RLS policy takes effect again. row security disabled is not returned.

                            Table "sh_vip.t_user"
       Column    |         Type          | Collation | Nullable | Default
    -------------+-----------------------+-----------+----------+---------
     tenant_id   | bigint                |           | not null |
     user_id     | bigint                |           | not null |
     tenant_name | character varying(50) |           |          |
    Indexes:
        "t_user_pkey" PRIMARY KEY, btree (tenant_id, user_id)
    Policies:
        POLICY "sh_vip_t_user"
          USING ((tenant_id = (current_setting('app.tenant_id'::text))::bigint))

Step 2: Check whether the standard account can be used to add, delete, modify, and query data

After the RLS policy is enabled for a table, each tenant is assigned an ID specified by tenant_id. This way, a tenant can read and modify data only of the tenant.

  1. Use the t1 account to connect to the RDS instance.

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

    The ID assigned to the t1 account is 1, and app.tenant_id is set to 1. During a database connection, run the env command to set the PGOPTIONS environment variable to -c app.tenant_id=1. In the value, -c specifies the connection parameter.

  2. Query test data.

    SELECT * FROM sh_vip.t_user;
    Note

    After you connect to the RDS instance by using a standard tenant account, you can view only the data of the tenant whose tenant_id is 1.

    Sample output:

     tenant_id | user_id | tenant_name
    -----------+---------+-------------
             1 |      10 | Zhang San
    (1 row)
  3. Insert test data.

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

    If tenant_id of the tenant that you use to insert test data is not 1, the new row violates row-level security policy for table "t_user" error is reported.

    INSERT INTO sh_vip.t_user VALUES(2, 21, 'Darren'); 
  4. Modify test data.

    UPDATE sh_vip.t_user SET tenant_name='Leo1' WHERE user_id = 11;
    Note
    • If you modify the data of a tenant whose tenant_id is not 1, the modification fails.

      UPDATE sh_vip.t_user SET tenant_name='Leo1' WHERE user_id = 20;
    • You can use the privileged account to connect to the RDS instance and check whether the modification is successful.

  5. Delete test data.

    DELETE FROM sh_vip.t_user WHERE user_id = 11;
    Note
    • If you delete the data of a tenant whose tenant_id is not 1, the deletion fails.

      DELETE FROM sh_vip.t_user WHERE user_id = 20;
    • You can use the privileged account to connect to the RDS instance and check whether the deletion is successful.

Step 3: Manage global data

Standard tenants can manage only their own data. Administrators and roles that have the BYPASSRLS permission are not subject to RLS policies. If you want to update multiple data records at a time or query full data, you must create a role or user that has the BYPASSRLS permission. You can use the privileged account to execute the following SQL statement to create the role or user:

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 examples

In this example, a Java application that is built by using Maven is used as an example.

  1. Configure dependencies in the pom.xml file.

    <dependency>
    	<groupId>org.postgresql</groupId>
    	<artifactId>postgresql</artifactId>
    	<version>42.3.1</version>
    </dependency>
  1. Create a SaaSrlsController file and copy the following sample code in Java to the file.

    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();
    		}
    
    		// The endpoint of the RDS instance
    		String hostname = "pgm-****.pg.rds.aliyuncs.com";
    		// The port number of the RDS instance
    		int port = 5432;
    		// The database name
    		String dbname = "rls_vip";
    		// The username.
    		String username = "t1";
    		// The password.
    		String password = "*****";
    
    		String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname;
    		Connection dbConnection;
    		try {
    			Properties props = new Properties();
    			props.setProperty("user", username);
    			props.setProperty("password", password);
                           // Note: Use the tenant ID that is assigned.
    			props.setProperty("options","-c app.tenant_id=1");
    
    			dbConnection = DriverManager.getConnection(dbUrl, props);
    			Statement statement = dbConnection.createStatement();
    
    			// Enter the SQL statement that you want to execute. 
    			String selectSql = "select * from rls_schema.t_user;";
    			ResultSet resultSet = statement.executeQuery(selectSql);
    			while (resultSet.next()) {
    				System.out.println(resultSet.getString("tenant_id") + "," +resultSet.getString("tenant_name"));
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    	}
    }

Conclusion

In this solution, the combination of tenant-specific fields and RLS policies allows the system to automatically identify tenant IDs during data query, modification, and deletion. This greatly reduces the threat of intrusion into applications.