Need RLS for other databases?
RLS currently supports ClickHouse and PostgreSQL. Email us to request support for your database.
How It Works
When a user interacts with Camel (for example, through an iframe or the API), Camel sets a session variable to the currentuid
for every query it runs. This variable can then be referenced in your database’s RLS policies to filter data dynamically per user.
- Supported Databases:
- ClickHouse
- PostgreSQL
- Other databases: RLS is not yet supported, but may be added in the future.
Setting the UID Variable
- PostgreSQL:
- Camel sets the session variable
camel.uid
usingSET LOCAL camel.uid = '<uid>'
for each query.
- Camel sets the session variable
- ClickHouse:
- Camel sets the session setting
SQL_camel_uid
for each query.
- Camel sets the session setting
Example: PostgreSQL RLS Policy
Suppose you have a tableorders
with a column user_id
. You want each user to only see their own orders.
- Enable RLS on the table:
- Create a policy using the
camel.uid
variable:
- Here,
current_setting('camel.uid')
returns the current user’s uid as set by Camel.
Example: ClickHouse RLS Policy
ClickHouse supports native row policies that can use custom settings for dynamic, per-user or per-tenant isolation. You do not need to use views for RLS—row policies are more robust and secure. Suppose you have a tableorders
with a column tenant_id
, and you want to restrict each user to only see their own tenant’s data. You can use a custom setting (e.g., SQL_camel_uid
) that Camel sets for each query.
- Create a role and allow the custom setting:
- Create a row policy using the custom setting:
- Create a user and assign the role:
SQL_camel_uid
setting for the session. The row policy will enforce that only rows matching the user’s uid (or tenant id) are visible.
Security Benefits:
- If the
SQL_camel_uid
setting is omitted, the query fails, preventing accidental data leaks. - The setting is provided in the session context, not merged into the SQL, making it more resistant to SQL injection.
- You only need to ensure that queries do not allow overriding the setting in the SQL itself.
How to Use
- When embedding Camel iframes or using the API, always set a unique
uid
for each user. - All queries run by Camel for that user will have the session variable set automatically.
- You can use this variable in your database RLS policies, views, or queries to restrict data access.
Best Practices
- Always use a unique, stable identifier for each user as the
uid
. - Test your RLS policies with different uids to ensure correct data isolation.
- Document your RLS logic for your team.
Limitations
- RLS via session variable is only supported for ClickHouse and PostgreSQL at this time.
- Other databases do not support this feature yet.
- The session variable is only set for queries run through Camel.
Troubleshooting
- If users see too much or too little data, double-check your RLS policy logic and that the
uid
is being set as expected. - You can log or audit the value of the session variable in your database for debugging.