Connection with readonly user fails

[stasimus]

When using readonly user credentials - connection fails with Code: 164. DB::Exception: Cannot modify 'join_use_nulls' setting in readonly mode. (READONLY) (version 22.12.3.5 (official build))

[yurifal]

It's a known limitation of the Connector (and it is made intentionally). Session settings (ie join_use_nulls=1) couldn't be modified by the 'readonly' user -- or any other user from the 'readonly' profile set in the /etc/clickhouse-server/users.xml.

A more straightforward approach is to GRANT SELECT ON <schema>.* TO <username> to get the user a readonly rights on the schema objects. Of course, the user has to be created via CREATE USER ...

[stasimus]

Didn't work, I've created the user via

CREATE USER tableau2 HOST ANY IDENTIFIED WITH plaintext_password BY 'qwerty' SETTINGS PROFILE 'readonly' DEFAULT DATABASE 'dwh';
GRANT SHOW TABLES, SELECT ON * TO tableau2;

and getting the same error: Code: 164. DB::Exception: Cannot modify 'join_use_nulls' setting in readonly mode. (READONLY) (version 22.12.3.5 (official build))

Additionally strange it tells db=default in error message, despite DEFAULT DATABASE 'dwh'

[yurifal]

SETTINGS PROFILE 'readonly' is the point of concern. Could you test by creating yet another user without applying the PROFILE settings?

[stasimus]

yep, it works without PROFILE 'readonly',