ClickHouse supports native row policies that provide robust, secure row-level access control. Camel integrates with ClickHouse’s custom settings feature to enable dynamic, per-user data isolation.

​
How Camel Sets the UID

For every query executed through Camel, the system automatically sets a custom session setting: 
-- Set as part of the query session
SQL_camel_uid = '<user_uid>'
This setting is available throughout the query execution and can be referenced in your row policies.

​
Setting Up RLS

​
Step 1: Create a Role with Custom Settings

First, create a role that allows the custom setting to be used: 
CREATE ROLE camel_readonly_role;

-- Allow the custom setting to be modified in readonly mode
ALTER ROLE camel_readonly_role 
  SETTINGS SQL_camel_uid CHANGEABLE_IN_READONLY;

​
Step 2: Grant Table Permissions

Grant the necessary permissions to the role: 
GRANT SELECT ON database.table TO camel_readonly_role;

​
Step 3: Create a Row Policy

Create a row policy that uses the custom setting to filter data: 
CREATE ROW POLICY user_policy ON database.table
  USING user_id = getSetting('SQL_camel_uid')
  TO camel_readonly_role;

​
Step 4: Create Users and Assign Roles

-- Create a user for Camel connections
CREATE USER IF NOT EXISTS camel_user 
  IDENTIFIED BY 'secure_password'
  SETTINGS readonly = 1;

-- Assign the role
GRANT camel_readonly_role TO camel_user;
SET DEFAULT ROLE camel_readonly_role TO camel_user;

​
Complete Example: Multi-Tenant Data

Let’s implement RLS for a multi-tenant analytics platform where each tenant should only see their own data.

​
1. Create the Events Table

CREATE TABLE analytics.events (
  event_id UUID DEFAULT generateUUIDv4(),
  tenant_id String,
  user_id String,
  event_type String,
  event_data String,
  timestamp DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (tenant_id, timestamp);

-- Insert sample data
INSERT INTO analytics.events (tenant_id, user_id, event_type, event_data) VALUES
  ('tenant_001', 'user_123', 'page_view', '{"page": "/home"}'),
  ('tenant_001', 'user_456', 'click', '{"button": "signup"}'),
  ('tenant_002', 'user_789', 'page_view', '{"page": "/products"}'),
  ('tenant_002', 'user_012', 'purchase', '{"amount": 99.99}');

​
2. Set Up the Role and Permissions

-- Create the role
CREATE ROLE tenant_access_role;

-- Configure the custom setting
ALTER ROLE tenant_access_role 
  SETTINGS SQL_camel_uid CHANGEABLE_IN_READONLY;

-- Grant table access
GRANT SELECT ON analytics.events TO tenant_access_role;

​
3. Create the Row Policy

CREATE ROW POLICY tenant_isolation_policy 
  ON analytics.events
  USING tenant_id = getSetting('SQL_camel_uid')
  TO tenant_access_role;

​
4. Create the User

CREATE USER IF NOT EXISTS camel_app_user
  IDENTIFIED BY 'secure_password'
  SETTINGS readonly = 1;

GRANT tenant_access_role TO camel_app_user;
SET DEFAULT ROLE tenant_access_role TO camel_app_user;

​
Testing Your Policies

You can test your row policies using the ClickHouse client: 
-- Set the custom setting for testing
SET SQL_camel_uid = 'tenant_001';

-- This should only return data for tenant_001
SELECT * FROM analytics.events;

-- Test with a different tenant
SET SQL_camel_uid = 'tenant_002';
SELECT * FROM analytics.events;

​
Troubleshooting

​
Common Issues and Solutions

IssueSolution
”Unknown setting SQL_camel_uid”Ensure the role has CHANGEABLE_IN_READONLY permission for the setting
No data returnedVerify the getSetting value matches your data exactly (case-sensitive)
All data visibleCheck that the row policy is applied to the correct role
Poor performanceEnsure filter columns are in the ORDER BY clause

​
Debugging Queries

Enable query logging to debug issues: 
SET log_queries = 1;
SET log_query_settings = 1;

-- Run your query
SELECT * FROM analytics.events;

-- Check the log
SELECT 
  query,
  Settings['SQL_camel_uid'] as uid_setting,
  exception
FROM system.query_log
WHERE query_id = (SELECT query_id FROM system.query_log ORDER BY event_time DESC LIMIT 1);

​
Learn More

For assistance with ClickHouse RLS implementation, contact [email protected].