Cannot pass user authentication for confluent schema registry on clickhouse kafka engine

Question

I want to consume messages with AvroConfluent format using the clickhouse kafka engine. Following clickhouse documentation, I have added the format_avro_schema_registry_url to a file in the etc/clickhouse-server/users.d directory.
This is what the file looks like:

<?xml version="1.0"?>
<clickhouse>
    <users>
        <default>
            <format_avro_schema_registry_url>https://{username}:{password}@{schema_registry_url}</format_avro_schema_registry_url>
        </default>
    </users>
</clickhouse>

I tried to create the kafka table using the query below but I get this error -

[Code: 1000, SQL State: ]  ClickHouse exception, code: 1000, host: xxx.xx.xx.xx, port: xxxx; Poco::Exception. Code: 1000, e.code() = 0, Bad URI syntax: bad or invalid port number: {password} 

Query

CREATE TABLE table_a (
field1 String,
field2 String
)
ENGINE = Kafka
SETTINGS 
kafka_broker_list = {kafka_broker},
kafka_topic_list = {topic},
kafka_group_name = {name},
kafka_format = 'AvroConfluent',
kafka_num_consumers = '1',
format_avro_schema_registry_url = https://{username}:{password}@{schema_registry_url};

I removed the user authentication from the schema_registry_url and I got another error, requiring authentication.

Code: 86. DB::Exception: Received error from remote server /schemas/ids/{schema_id}. HTTP status code: 401 Unauthorized, body: {"error_code":401,"message":"Unauthorized"}: while fetching schema id = {schema_id}: while parsing Kafka message (topic: {topic}, partition: {partition}, offset: {offset})': While executing Kafka. (RECEIVED_ERROR_FROM_REMOTE_IO_SERVER)

When I remove the format_avro_schema_registry_url parameter from the CREATE TABLE query, I get another error.

Code: 36. DB::Exception: Empty Schema Registry URL: While executing Kafka. (BAD_ARGUMENTS)

Answer 1

Found out that this is a reported bug on clickhouse - https://github.com/ClickHouse/ClickHouse/issues/32887