Column filtering rules

Column rules let you hide specific columns from users without blocking access to the table itself. When a query references a restricted column, Kaelio silently removes it rather than returning an error. Users see their query results normally, just without the sensitive fields.

Basic usage

Define column rules in the column_rules section of your policy. Each rule specifies a table and the columns to hide:

version: "1.0"
default_allow_tables: true

column_rules:
  - table_name: users
    restricted_columns:
      - ssn
      - password_hash

With this rule in place, a query that selects restricted columns is automatically transformed:

Original query:

SELECT id, name, ssn FROM users

Transformed query:

SELECT id, name FROM users

The user receives results with only id and name. The ssn column is removed without any error message.

What gets filtered

Restricted columns aren't just removed from SELECT projections. Kaelio also strips them from other clauses to prevent data leakage through indirect access:

• SELECT — Restricted columns are removed from the output
• WHERE — Conditions referencing restricted columns are removed (prevents filtering to guess values)
• ORDER BY — Sorting by restricted columns is removed
• GROUP BY — Grouping by restricted columns is removed
• HAVING — Conditions on restricted columns are removed

For example, a query like SELECT name FROM users WHERE ssn = '123-45-6789' ORDER BY ssn becomes SELECT name FROM users — both the WHERE and ORDER BY clauses referencing ssn are stripped.

Using glob patterns

You can use glob patterns in table_name to apply column rules across multiple tables at once. This is useful when a naming convention means the same sensitive column appears in many places.

column_rules:
  - table_name: "*"
    restricted_columns:
      - created_by_ip
      - internal_notes

  - table_name: "customer_*"
    restricted_columns:
      - credit_card_number
      - billing_address

The first rule hides created_by_ip and internal_notes from every table. The second hides payment fields from any table whose name starts with customer_.

See Policy YAML Reference for the full list of supported glob patterns.

Conditional column rules

Conditions let you restrict columns only for certain users, based on their security properties. A rule with a condition block only takes effect when the user's properties match all specified key-value pairs.

column_rules:
  - table_name: employees
    restricted_columns:
      - salary
      - bonus
      - bank_account
    condition:
      department: ["engineering", "marketing"]

In this example, the salary, bonus, and bank_account columns are hidden from users in engineering or marketing. Users in other departments (like HR or finance) can see these columns because the condition doesn't match them, so the restriction doesn't apply.

See Policy YAML Reference for details on how conditions are evaluated.

Cumulative behavior

When multiple column rules match the same table, their restricted columns are combined. This lets you layer rules for different concerns without conflicts.

column_rules:
  - table_name: users
    restricted_columns:
      - ssn
      - date_of_birth

  - table_name: users
    restricted_columns:
      - salary
    condition:
      department: "HR"

  - table_name: "*"
    restricted_columns:
      - password_hash

For an HR user querying the users table, all of these columns are hidden: ssn, date_of_birth, salary, and password_hash. For a non-HR user, only ssn, date_of_birth, and password_hash are hidden (the salary restriction doesn't apply because its condition doesn't match). The rules stack — each matching rule adds its restricted columns to the total set.

Practical examples

PII protection

Hide personally identifiable information across your database:

column_rules:
  - table_name: "*"
    restricted_columns:
      - ssn
      - social_security_number
      - passport_number
      - drivers_license

  - table_name: customers
    restricted_columns:
      - email
      - phone_number
      - home_address

Financial data

Hide financial details from most users, restricting visibility to only certain departments:

column_rules:
  - table_name: employees
    restricted_columns:
      - salary
      - bonus
      - stock_options
      - tax_id

  - table_name: "invoice_*"
    restricted_columns:
      - payment_method
      - card_last_four

These rules hide financial columns from everyone. Since there are no conditions, the restrictions apply universally.

Healthcare

Enforce data minimization for patient records:

column_rules:
  - table_name: patients
    restricted_columns:
      - diagnosis
      - treatment_plan
      - medication_list
    condition:
      role: ["receptionist", "billing"]

  - table_name: patients
    restricted_columns:
      - ssn
      - billing_address
      - payment_info

In this setup, clinical data (diagnosis, treatment plan, and medication list) is hidden from receptionists and billing staff, while physicians and other roles can see it. Financial fields like ssn and payment_info are hidden from everyone, including physicians, because the second rule has no condition and applies universally.