Column Filtering Rules

Hide sensitive columns like SSNs, passwords, and salary data from query results using column 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:

policy.yaml

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 are not 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 would become SELECT name FROM users -- both the WHERE and ORDER BY clauses referencing ssn are stripped.

Unlike table rules, column rules never block a query entirely. They silently remove restricted columns and let the rest of the query run.

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.

policy.yaml

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.

policy.yaml

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 does not match them, so the restriction does not apply.

A condition on a column rule means the restriction only applies when the condition matches. If you want to hide columns from everyone, omit the condition field entirely.

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.

policy.yaml

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 does not apply because its condition does not match). The rules stack -- each matching rule adds its restricted columns to the total set.

This cumulative behavior differs from table rules, where the most specific match wins. With column rules, every matching rule contributes its restricted columns.

Practical Examples

PII Protection

Hide personally identifiable information across your database:

policy.yaml

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:

policy.yaml

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:

policy.yaml

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, 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.

Column Filtering Rules

On this page