How to enable Advance RLS for an Object ?

1. Overview

Advanced RLS provides full control over row-level data access using custom SQL conditions. It is designed for complex access rules that cannot be expressed using simple field mappings.

Advanced RLS is query-driven, flexible, and evaluated at runtime for every data access request

---

2. Prerequisites

Before enabling Advanced RLS:

• Target object must exist

• User context (user ID, role, tenant) must be available

• Required source and permission objects must exist

• SQL author must understand object schema

---

3. Step-by-Step: Enabling Advanced RLS

Note: Please refer to the Standard RLS setup first.

Step 1: Navigate to Target Object

Open the target object on which RLS must be applied.

---

Step 2: Enable RLS

Enable RLS and select:

• RLS Type: Advanced

---

Step 3: Define Custom SQL Filter

Provide a SQL condition that evaluates to TRUE for rows the user is allowed to access.

Example:

EXISTS (
  SELECT 1
  FROM permission p
  WHERE p.citta_user_id = CURRENT_USER_ID
    AND p.department = target.department
    AND p.is_deleted = FALSE
)

---

Step 4: Validate SQL

Validate the SQL syntax and ensure:

• References to target object fields are correct

• User context variables are supported

• Query returns a boolean condition

---

Step 5: Activate RLS

Save and activate Advanced RLS.

Once activated, all queries on the target object automatically apply the SQL filter.

---

4. Differences: Standard vs Advanced RLS

Feature	Standard RLS	Advanced RLS
Configuration	UI-based	SQL-based
Complexity	Low	High
Custom Logic	❌	✅
Performance	High	Depends on SQL
Maintenance	Low	Medium–High

---

5. Limitations and Risks

• Incorrect SQL can block all data

• Poorly optimized queries impact performance

• Requires SQL expertise

• Harder to debug than Standard RLS

---

6. Failure Scenarios

• Invalid SQL → RLS evaluation fails

• Missing user context → No data returned

• Permission table misconfiguration → Empty result set