Row Filter Rules

Row filter rules are the most powerful mechanism in Kaelio's SQL security policies. They inject WHERE clauses into queries so that users only see the rows they are authorized to access. This enables multi-tenant isolation, department-scoped data, and regional access control -- all without users needing to write any filtering logic themselves.

Rules are defined in the row_filter_rules section of your policy YAML.

Basic Usage

Each rule specifies a table_name and a filter_sql clause. The filter_sql is a WHERE condition that gets injected into every query that touches the matching table.

version: "1.0"
default_allow_tables: true

row_filter_rules:
  - table_name: orders
    filter_sql: "tenant_id = '{tenant_id}'"

Template variables like {tenant_id} are replaced with the actual value from the user's security properties at query time.

Before and After

Given the policy above and a user whose tenant_id property is set to acme:

SELECT * FROM orders

SELECT * FROM orders WHERE tenant_id = 'acme'

The user sees only their own tenant's orders without writing any filtering logic.

Template Variables

Template variables are placeholders in your filter_sql that get replaced with user-specific values. Wrap them in curly braces: {variable_name}.

Built-in Variables

These variables are always available for every user:

Custom Variables

Any key defined in a user's security properties is available as a template variable. For example, if a user has the property department: "engineering", you can use {department} in your filter:

row_filter_rules:
  - table_name: projects
    filter_sql: "department = '{department}'"

See User Security Properties for how to configure properties on user profiles.

Filtering with Existing WHERE Clauses

When a query already contains a WHERE clause, the row filter is combined with it using AND. Both conditions must be true for a row to appear in the results.

SELECT * FROM orders WHERE status = 'shipped'

SELECT * FROM orders WHERE status = 'shipped' AND tenant_id = 'acme'

The user sees only shipped orders belonging to their tenant.

Missing Template Variables

For example, if a row filter uses {region} but the user has no region property set, any query touching that table returns zero rows. This is intentional -- it is safer to deny access than to show all rows because a filter could not be applied.

Make sure all users who need access to a filtered table have the required properties configured in their profile. See User Security Properties for setup instructions.

Filtering Across JOINs

Row filters are applied to each table individually, even when tables are joined. Kaelio qualifies the filter with the correct table alias so that each table gets its own independent filter.

row_filter_rules:
  - table_name: orders
    filter_sql: "tenant_id = '{tenant_id}'"
  - table_name: customers
    filter_sql: "tenant_id = '{tenant_id}'"

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.tenant_id = 'acme' AND c.tenant_id = 'acme'

Both tables are independently filtered. A user cannot bypass their tenant filter by joining to an unfiltered table.

Rule Matching Priority

When multiple row filter rules could match a table, the first matching rule wins. The matching order follows the same precedence as table rules:

1. Exact name -- orders matches only the orders table
2. Specific glob -- sales_* matches tables starting with sales_
3. Wildcard -- * matches all tables

Conditional Row Filters

Add a condition block to apply a row filter only when the user's security properties match. This lets you define different filters for different roles or departments.

row_filter_rules:
  - table_name: support_tickets
    filter_sql: "assigned_to = '{user_id}'"
    condition:
      role: "agent"

  - table_name: support_tickets
    filter_sql: "department = '{department}'"
    condition:
      role: "manager"

In this example:

• Agents see only tickets assigned to them
• Managers see all tickets in their department
• Users with other roles have no row filter rule match for support_tickets, so they see all rows (unless a wildcard rule covers it)

When a condition has multiple keys, all keys must match (AND logic). If the user does not have a required property, the condition does not match and the rule is skipped.

Using Glob Patterns

Use glob patterns in table_name to apply a row filter across many tables at once. The * wildcard is especially useful for enforcing tenant isolation across your entire database.

row_filter_rules:
  - table_name: "*"
    filter_sql: "tenant_id = '{tenant_id}'"

This adds a tenant_id filter to every table in every query. If some tables do not have a tenant_id column, you can override the wildcard with more specific rules:

row_filter_rules:
  - table_name: public_settings
    filter_sql: "1 = 1"
  - table_name: "*"
    filter_sql: "tenant_id = '{tenant_id}'"

Because exact names take priority over wildcards, public_settings gets the passthrough filter 1 = 1 (no restriction), while all other tables are filtered by tenant.

Common Patterns

Multi-Tenant Isolation

Ensure every user only sees data belonging to their organization:

row_filter_rules:
  - table_name: "*"
    filter_sql: "tenant_id = '{tenant_id}'"

Department Scoping

Restrict tables to show only rows relevant to a user's department:

row_filter_rules:
  - table_name: expenses
    filter_sql: "department = '{department}'"
  - table_name: headcount
    filter_sql: "department = '{department}'"
  - table_name: budget_allocations
    filter_sql: "department = '{department}'"

Regional Access Control

Limit data visibility based on the user's geographic region:

row_filter_rules:
  - table_name: sales
    filter_sql: "region = '{region}'"
  - table_name: warehouses
    filter_sql: "region = '{region}'"
  - table_name: logistics
    filter_sql: "region = '{region}'"

Related

• Policy YAML Reference -- full schema for glob patterns, conditions, and all policy fields
• User Security Properties -- configure the user attributes used in template variables
• Table Access Rules -- control which tables users can query
• Column Filtering Rules -- hide sensitive columns from query results