Is it possible to use Query Parameters in ROW POLICY?

[yusufozturk]

We would like to use Query Parameters in Row Policy.

First we create our row policy to limit higher distances and we assign it via parameter way.

CREATE ROW POLICY org_policy ON default.nyc_taxi FOR SELECT USING trip_distance > {distance:Float64} to org_user;

Then we query table as:

curl -sS "https://x.clickhouse.cloud:8443/?param_distance=1.2" -d "select * from default.nyc_taxi"

But it gives error:

"Query parameter 'distance" was not set."

Seems like it's not supported at the moment or maybe we do something wrong. But if Row Policy would support Query Parameters, then we could have do dynamic filtering on row level.

Thanks.

[UnamedRus]

You can look into additional_table_filters

#38475

[yusufozturk]

This is the case:

1. Customer provides the Clickhouse environment and they handle the support & maintenance. They only provide us a connection string so we can use it store logs and metrics.
2. Customer does not give role/user management to us because it's a shared environment.
3. Customer does not want to create multiple roles & users for us. They want us to handle it with a single role or user.

In this case, best method would be to use a row policy with a parametered filter. We want to use parameter in the row filter and send that parameter with the each query as in "query parameters".

@UnamedRus I think you also have a similar case?

[UnamedRus]

I don't believe a lot in parametrized row policy TBH.

I guess, you can try to make it work via using custom_settings and something like

CREATE ROW POLICY ....    column_x = getSetting('policy_parameter')

But it's extremely hacky, and i would prefer additional_table_filters for that case