Aiven currently supports hosting Kafka and a number of auxiliary services like Kafka Connect, Kafka REST and Schema Registry. Hosting KSQL in Aiven is, however, not supported and at the moment if you need KSQL you'll need to run it yourself.

Luckily running your own KSQL is not that difficult as it doesn't need persistent storage and uses regular consumer group model for scaling up. There are, however, a couple of gotchas in configuring KSQL so that it can successfully talk to Aiven Kafka and Schema Registry.

Aiven requires all network connections to use TLS and some sort of authentication. The type of authentication and the CA used for signing server certificates depends on the service. For Kafka itself authentication is based on client certificate while Schema Registry uses Basic authentication and Kafka uses Aiven's project CA while Schema Registry uses commonly trusted root CA.

The relevant Kafka and Schema Registry related connection and security settings for KSQL should look like this:

The bootstrap.servers setting is just the Kafka Service URL as shown in the Aiven web console. The ssl.keystore settings define the client certificate used to authenticate with Kafka servers and ssl.truststore settings define the CA that is expected to have signed the server certificate. The way how to generate these files is documented in this help article. All the passwords should be changed from changeit into the actual password you entered when generating the files.

KSQL by default uses the ssl.truststore settings also for the Schema Registry connection. In this case that is undesired because the Schema Registry CA is commonly trusted and wouldn't need any explicit truststore but as there is no way to specify Kafka-only truststore we need to explicitly define a truststore that contains the commonly trusted root CA of Schema Registry server.

To generate the Schema Registry truststore we first need to obtain the root CA of the server. This can be done with the following command:

openssl s_client -connect \
  -showcerts < /dev/null 2>/dev/null | \
  awk '/BEGIN CERT/{s=1}; s{t=t "\n" $0};
       /END CERT/ {last=t; t=""; s=0}; END{print last}' \
  > /tmp/ca.cert

Note that the port in the above needs to be that of the Schema Registry service, not the Kafka port. Instead of using this command you could also just connect to with your Internet browser and store the root CA from the browser UI.

To convert the certificate file into JKS format use keytool :

keytool -import -file /tmp/ca.cert -alias CA -keystore \

The final configuration options for Schema Registry are related to configuring Basic authentication. KSQL does not support parsing the authentication info from the URL itself so when specifying the ksql.schema.registry.url you need to omit the username and password values that are present in the URL you get from Aiven web console and instead specify separate basic auth settings for this. The ksql.schema.registry.basic.auth.credentials.source setting value needs to be literal USER_INFO as shown in the example configuration while the setting value needs to be the actual username:password pair.

After these settings you still need to configure some basic KSQL settings like listeners=http://localhost:8088 to make KSQL listen on port 8088 and start it with $KSQL_ROOT/bin/ksql-start-server and you're all set. You can then connect to it with $KSQL_ROOT/bin/ksql 'http://localhost:8088'.

Did this answer your question?