How to use dynamic row-level security with Camel, ClickHouse, and PostgreSQL.
uid
for every query it runs. This variable can then be referenced in your database’s RLS policies to filter data dynamically per user.
camel.uid
using SET LOCAL camel.uid = '<uid>'
for each query.SQL_camel_uid
for each query.orders
with a column user_id
. You want each user to only see their own orders.
camel.uid
variable:current_setting('camel.uid')
returns the current user’s uid as set by Camel.orders
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.
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:
SQL_camel_uid
setting is omitted, the query fails, preventing accidental data leaks.uid
for each user.uid
.uid
is being set as expected.