ClickHouse RLS

-- Set as part of the query session
SQL_camel_uid = '<user_uid>'

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;

GRANT SELECT ON database.table TO camel_readonly_role;

CREATE ROW POLICY user_policy ON database.table
  USING user_id = getSetting('SQL_camel_uid')
  TO camel_readonly_role;

-- 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;

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}');

-- 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;

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

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;

-- 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;

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);