readonly setting in query fails when set to be changeable in readonly on role

[dinmukhamedm]

Describe the bug

This is identical to #469 with the 2 differences:

• I don't create a settings profile, everything is managed on the role level
• The setting name is different from a bare readonly
  • The role is altered so that the setting is CHANGEABLE_IN_READONLY

Steps to reproduce

1. Create a dummy dataset

CREATE database test_db;
CREATE TABLE test_db.table (a String) ORDER BY tuple();
INSERT INTO test_db.table (a) VALUES ('test');

2. Create a role and allow changing a setting in readonly mode

CREATE ROLE IF NOT EXISTS my_readonly_role;
ALTER ROLE my_readonly_role SETTINGS SQL_RO_my_rls_key CHANGEABLE_IN_READONLY;
GRANT SELECT on test_db.table TO my_readonly_role;
CREATE ROW POLICY my_table_readonly_policy ON test_db.table USING a = getSetting('SQL_RO_my_rls_key') TO my_readonly_role;

3. Create a readonly user defaulted to this role

CREATE user my_readonly_user IDENTIFIED WITH sha256_password by 'Str0ngP@ssw0rd' DEFAULT ROLE my_readonly_role SETTINGS readonly = 1;

4. Appending the setting directly to a query works

import clickhouse_connect

client = clickhouse_connect.get_client(
    host="localhost",
    port="8123",
    user="my_readonly_user",
    password="Str0ngP@ssw0rd",
)
query= """SELECT * FROM test_db.table WHERE a = 'test' SETTINGS SQL_RO_my_rls_key='test'"""
res = client.query(query)
print(res)

5. Passing settings to the query breaks

```sql
import clickhouse_connect

client = clickhouse_connect.get_client(
    host="localhost",
    port="8123",
    user="my_readonly_user",
    password="Str0ngP@ssw0rd",
)
query= """SELECT * FROM test_db.table WHERE a = 'test'"""
res = client.query(query, settings={"SQL_RO_my_rls_key": 'test'})
print(res)

This results in

Query execution failed: Setting SQL_RO_my_rls_key is unknown or readonly

Environment

• clickhouse-connect version: 0.8.18
• Python version: 3.12
• Operating system: Debian (modal.com)
• Clickhouse cloud: version 25.4

Context

I am trying to follow this blog post https://www.highlight.io/blog/row-level-security

[joe-clickhouse]

@genzgd what are your thoughts here? If I'm understanding this correctly the idea is to create a role and assign it to a readonly user (with readonly = 1). However, to allow this readonly user to change a specific custom setting (e.g., SQL_RO_my_rls_key) you can apply the CHANGEABLE_IN_READONLY constraint to that specific setting at the role level. This constraint allows the setting to be changed even when the user is in readonly mode.

Looking into this, I can see in the system.settings_profile_elements table there's a "writability" column that shows if a setting is changeable in read only. E.g. select * FROM system.settings_profile_elements shows

  profile_name         user_name         role_name  index       setting_name   value   min   max             writability inherit_profile
0         <NA>  my_readonly_user              <NA>      0           readonly       1  <NA>  <NA>                    None            <NA>
1         <NA>           default              <NA>      0               <NA>    <NA>  <NA>  <NA>                    None         default
2         <NA>              <NA>  my_readonly_role      0  SQL_RO_my_rls_key    <NA>  <NA>  <NA>  CHANGEABLE_IN_READONLY            <NA>
3      default              <NA>              <NA>      0     load_balancing  random  <NA>  <NA>                    None            <NA>

So it's theoretically possible to detect if that setting is CHANGEABLE_IN_READONLY. However, since the client will need to detect this on connect they'd need permissions to read system.settings_profile_elements which (according to my tests) a user created in the way described above does not have. E.g. performing the select * FROM system.settings_profile_elements gives the following error:

DB::Exception: my_readonly_user: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(profile_name, user_name, role_name, index, setting_name, value, min, max, writability, inherit_profile) ON system.settings_profile_elements. (ACCESS_DENIED)

Given this, I'm now sure the is any reasonable way for a client to reliably discover which settings are CHANGEABLE_IN_READONLY for the current user/role, short of having extra privileges?

[genzgd]

I also don't see a clean way to detect settings at such a fine grained level. We probably need to allow bypassing settings validation for such circumstances and let the user deal with the actual ClickHouse error instead?