Row Level Security (RLS)

1. Introduction

Row Level Security (RLS) is a data access control mechanism that restricts users’ access to records at the row level. Instead of controlling access only at the object or table level, RLS ensures that users can view or modify only those records they are explicitly permitted to access.

In this application, RLS is implemented at the application layer using metadata-driven rules and permission objects. The system dynamically injects filtering conditions into queries based on the current user, their permissions, and configured RLS rules.

---

2. Key Concepts

2.1 Permission Object

A Permission Object is a special object used to define row-level access rules. It acts as a bridge between users and the data they are allowed to access.

Each Permission Object contains:

• system_user_id / citta_user_id – Reference to the platform user

• citta_permissions – Bitwise integer representing access level

• One or more business fields – Used to match rows in the target object (e.g., department, business unit, owner_id)

The Permission Object does not store data; it stores who can access which data.

---

2.2 Permission Bits

The citta_permissions field controls allowed operations using a 2-bit binary model:

Decimal Value	Binary	Access Level
1	01	Read only
2	10	Write (Insert, Update, Delete)
3	11	Read + Write

Write permission implicitly includes Insert, Update, and Delete operations.

---

2.3 Target Object

A Target Object is the data object on which RLS is enforced. Examples include business data such as orders, profit and loss records, or uploaded files.

---

2.4 Derived Permissions

Derived Permissions allow RLS to work across indirect relationships.

When the Permission Object is not directly linked to the Target Object, intermediate objects can be marked as:

"citta": {
  "isUsedToDerivePermissions": true
}

This enables RLS to traverse multiple relationships (e.g., Country → Business Unit → Team) to derive valid rows.

---

3. RLS Modes

3.1 None Mode

• RLS is disabled

• No row-level filtering is applied

• All data is visible (subject to object-level permissions)

• This mode is by default selected for all objects.

---

3.2 Standard Mode

Standard Mode is used for simple RLS scenarios where filtering is based on a single field.

Configuration includes:

• Target Object field used for filtering

• Permission Object selection

Example:

• Target Object field: department

• Permission Object field: department

Resulting logic:

• Users can only see rows where the department matches their permission record

This mode automatically generates filtering conditions without requiring SQL input.

---

3.3 Advanced Mode

Advanced Mode is used for complex RLS scenarios involving:

• Multiple fields

• Hierarchical data

• Custom business logic

In this mode, administrators define a custom SQL query that must return:

• citta_user_id

• citta_permissions

• rls_on (the field value on which RLS is applied)

Example pattern:

SELECT p.citta_user_id,
       p.citta_permissions,
       t.team_id AS rls_on
FROM permission_object p
JOIN mapping_table m ON ...
JOIN target_table t ON ...

The system uses the query result to dynamically restrict rows visible to the user.

---

4. Role-Based Behavior

Role	RLS Behavior
Tenant Admin	Sees all data (RLS bypassed)
Environment Admin	RLS applied
Application Admin	Sees all data (RLS bypassed)
Application User	RLS applied

---

5. Failure Scenarios

Scenario	Result
No permission record	No data visible
Invalid SQL in Advanced Mode	RLS not applied / error
Permission removed	Data access revoked immediately

---

6. Summary

Row Level Security provides a flexible, metadata-driven mechanism to enforce fine-grained data access. By separating permission logic from data objects and supporting both simple and advanced configurations, the system enables secure, scalable, and maintainable access control across complex data models.

---