Connect to clickhouse http via tunnel

[abalalaev]

I'm trying to setup connection to the clickhouse db using this instruction

This step is ok:
tsh proxy db --db-user=admin --tunnel example-clickhouse-http --port 8123

But i'm facing auth error in the next step:
echo 'select currentUser();' | curl http://localhost:8123/ --data-binary @- Code: 516. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

I got same error when i was trying to use DBeaver, but I was able to connect using another user with a password. However, the connection via tcp (native) works as expected.
I expected to be able to connect using a local proxy without authentication (as it works with mysql)
I would be grateful for any comments.

[webvictim]

Can you please share:

• the logs from the running Teleport db_service you have which is configured for Clickhouse
• the /etc/teleport.yaml config file for the Teleport db_service you have which is configured for Clickhouse (or the teleport-kube-agent Helm chart values, if you're using that)
• any logs from Clickhouse when you try to connect?

[abalalaev]

Hello @webvictim

Teleport log from restart to connection attempt

2024-10-16T06:00:38Z INFO [PROC:1]    Got shutdown signal, exiting within timeout. pid:303509.1 signal:terminated timeout:3 service/signals.go:103
2024-10-16T06:00:38Z WARN [DEBUG:1]   Debug server exited with error. pid:303509.1 error:[accept unix /var/lib/teleport/debug.sock: use of closed network connection] service/service.go:3488
2024-10-16T06:00:38Z INFO [NODE:1]    Shutting down gracefully. pid:303509.1 service/service.go:3020
2024-10-16T06:00:38Z INFO [DEBUG:1]   Shutting down gracefully. pid:303509.1 service/service.go:3499
2024-10-16T06:00:38Z INFO [DEBUG:1]   Exited. pid:303509.1 service/service.go:3503
2024-10-16T06:00:38Z INFO [UPLOAD:1]  File upload completer is shutting down. pid:303509.1 error:<nil> service/service.go:3224
2024-10-16T06:00:38Z INFO [UPLOAD:1]  File upload completer has shut down. pid:303509.1 service/service.go:3226
2024-10-16T06:00:38Z INFO [UPLOAD:1]  File uploader is shutting down. pid:303509.1 service/service.go:3192
2024-10-16T06:00:38Z INFO [UPLOAD:1]  File uploader has shut down. pid:303509.1 service/service.go:3194
2024-10-16T06:00:38Z INFO [DB:SERVIC] Shutting down gracefully. pid:303509.1 service/db.go:214
2024-10-16T06:00:38Z INFO [NODE:1]    Exited. pid:303509.1 service/service.go:3030
2024-10-16T06:00:38Z INFO [DB:SERVIC] Exited. pid:303509.1 service/db.go:225
2024-10-16T06:00:38Z INFO [PROC:1]    All services stopped or timeout passed, exiting immediately. pid:303509.1 service/signals.go:120
2024-10-16T06:00:38Z INFO  Starting Teleport with a config file version:16.4.2 config_file:/etc/teleport.yaml common/teleport.go:756
2024-10-16T06:00:38Z INFO [PROC:1]    Service is creating new listener. pid:321324.1 type:debug address:/var/lib/teleport/debug.sock service/signals.go:249
2024-10-16T06:00:38Z INFO [PROC:1]    Connecting to the cluster with TLS client certificate. pid:321324.1 cluster:teleport.test service/connect.go:241
2024-10-16T06:00:38Z INFO [PROC:1]    Connecting to the cluster with TLS client certificate. pid:321324.1 cluster:teleport.test  service/connect.go:241
2024-10-16T06:00:38Z INFO [PROC:1]    Connecting to the cluster with TLS client certificate. pid:321324.1 cluster:teleport.test  service/connect.go:241
2024-10-16T06:00:39Z INFO [PROC:1]    features loaded from auth server pid:321324.1 identity:Instance features:Kubernetes:true App:true DB:true Desktop:true DeviceTrust:<> AccessRequests:<> AccessList:<> AccessMonitoring:<> Policy:<> SupportType:SUPPORT_TYPE_FREE JoinActiveSessions:true entitlements:<key:"AccessLists" value:<> > entitlements:<key:"AccessMonitoring" value:<> > entitlements:<key:"AccessRequests" value:<> > entitlements:<key:"App" value:<enabled:true > > entitlements:<key:"CloudAuditLogRetention" value:<> > entitlements:<key:"DB" value:<enabled:true > > entitlements:<key:"Desktop" value:<enabled:true > > entitlements:<key:"DeviceTrust" value:<> > entitlements:<key:"ExternalAuditStorage" value:<> > entitlements:<key:"FeatureHiding" value:<> > entitlements:<key:"HSM" value:<> > entitlements:<key:"Identity" value:<> > entitlements:<key:"JoinActiveSessions" value:<enabled:true > > entitlements:<key:"K8s" value:<enabled:true > > entitlements:<key:"MobileDeviceManagement" value:<> > entitlements:<key:"OIDC" value:<> > entitlements:<key:"OktaSCIM" value:<> > entitlements:<key:"OktaUserSync" value:<> > entitlements:<key:"Policy" value:<> > entitlements:<key:"SAML" value:<> > entitlements:<key:"SessionLocks" value:<> > entitlements:<key:"UpsellAlert" value:<> > entitlements:<key:"UsageReporting" value:<> >  service/connect.go:1150
2024-10-16T06:00:39Z INFO [INSTANCE:] Successfully registered instance client. pid:321324.1 service/service.go:2695
2024-10-16T06:00:39Z INFO [PROC:1]    Reusing Instance client. pid:321324.1 identity:Node additional_system_roles:[Node Db] service/connect.go:1118
2024-10-16T06:00:39Z INFO [UPLOAD:1]  starting upload completer service pid:321324.1 service/service.go:3136
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log/upload service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log/upload/streaming service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log/upload/streaming/default service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log/upload service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log/upload/corrupted service/service.go:3152
2024-10-16T06:00:39Z INFO [UPLOAD:1]  Creating directory. pid:321324.1 directory:/var/lib/teleport/log/upload/corrupted/default service/service.go:3152
2024-10-16T06:00:39Z INFO [PROC:1]    Reusing Instance client. pid:321324.1 identity:Db additional_system_roles:[Node Db] service/connect.go:1118
2024-10-16T06:00:39Z INFO [UPLOAD]    uploader will scan /var/lib/teleport/log/upload/streaming/default every 5s filesessions/fileasync.go:196
2024-10-16T06:00:39Z INFO [UPLOAD:1:] upload completer will run every 5m0s events/complete.go:167
2024-10-16T06:00:39Z INFO [DB:SERVIC] Cache "db" first init succeeded. cache/cache.go:1035
2024-10-16T06:00:39Z INFO [DB:SERVIC] Database service has successfully started. pid:321324.1 database:[Database(Name=clickhouse-1-http, Type=self-hosted, Labels=map[env:prod teleport.dev/origin:config-file]) Database(Name=clickhouse-1-native, Type=self-hosted, Labels=map[env:prod teleport.dev/origin:config-file])] service/db.go:229
2024-10-16T06:00:39Z INFO [IAM]       Started IAM configurator service. cloud/iam.go:125
2024-10-16T06:00:39Z INFO [NODE:1:CA] Cache "node" first init succeeded. cache/cache.go:1035
2024-10-16T06:00:39Z INFO [NODE:1]    Service is starting in tunnel mode. pid:321324.1 service/service.go:3000
2024-10-16T06:00:39Z INFO [PROC:1]    The new service has started successfully. Starting syncing rotation status. pid:321324.1 max_retry_period:4m16s service/connect.go:700
2024-10-16T06:03:13Z INFO [AUDIT]     db.session.start cluster_name:teleport.test code:TDB00I db_origin:config-file db_protocol:clickhouse db_service:clickhouse-1-http db_type:self-hosted db_uri:clickhouse://clickhouse-1:8443 db_user:db_admin ei:0 event:db.session.start namespace:default private_key_policy:none server_id:5db153d3-b05a-4a2c-9128-d33e4cef532f server_version:16.4.2 sid:97b11658-2e75-43b3-88fb-da3f686a5641 success:true time:2024-10-16T06:03:13.815670111Z uid:90a9db5a-76f0-4dbb-9491-9420d864c6b4 user:test@test user_kind:1 events/emitter.go:288
2024-10-16T06:03:14Z INFO [AUDIT]     db.session.end cluster_name:teleport.test code:TDB01I db_origin:config-file db_protocol:clickhouse db_service:clickhouse-1-http db_type:self-hosted db_uri:clickhouse://clickhouse-1:8443 db_user:db_admin ei:1 event:db.session.end private_key_policy:none session_start:2024-10-16T06:03:13.815670111Z session_stop:2024-10-16T06:03:14.371194443Z sid:97b11658-2e75-43b3-88fb-da3f686a5641 time:2024-10-16T06:03:14.371194443Z uid:79e2adce-ad02-4b71-a7c4-92b966a0af05 user:test@test user_kind:1 events/emitter.go:288

Config file for the Teleport db_service

version: v3
teleport:
  nodename: "clickhouse-1"
  pid_file: "/run/teleport.pid"
  data_dir: "/var/lib/teleport"
  
  auth_token: "token"
  proxy_server: "teleport.test:443"
  log:
    output: "/var/log/teleport/teleport.log"
    severity: "INFO"
    format:
      output: "text"

proxy_service:
  enabled: "no"

auth_service:
  enabled: "no"

ssh_service:
  enabled: "yes"
  labels:
    env: "prod"
    os: "Ubuntu 22.04"


app_service:
  enabled: "no"

db_service:
  enabled: "yes"
  databases:
  - name: "clickhouse-1-http"
    description: "connection via http"
    protocol: "clickhouse"
    uri: "clickhouse-1:8443"
    static_labels:
      env: "prod"
    tls:
      ca_cert_file: /etc/clickhouse/ca/ca-certificate.crt
  - name: "clickhouse-1-native"
    description: "connection via tcp"
    protocol: "clickhouse"
    uri: "clickhouse-1:9440"
    static_labels:
      env: "prod"
    tls:
      ca_cert_file: /etc/clickhouse/ca/ca-certificate.crt

Clickhouse log during connection attempt

2024.10.16 06:03:14.159814 [ 303738 ] {} <Error> Access(user directories): from: 127.0.0.1, user: default: Authentication failed: Code: 192. DB::Exception: There is no user `default` in local_directory. (UNKNOWN_USER), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbcedbb
1. DB::Exception::Exception<String, String const&>(int, FormatStringHelperImpl<std::type_identity<String>::type, std::type_identity<String const&>::type>, String&&, String const&) @ 0x0000000007ee8e5d
2. DB::IAccessStorage::throwNotFound(DB::AccessEntityType, String const&) const @ 0x000000000fbb0d37
3. DB::IAccessStorage::authenticateImpl(DB::Credentials const&, Poco::Net::IPAddress const&, DB::ExternalAuthenticators const&, bool, bool, bool) const @ 0x000000000fbb4c8d
4. DB::MultipleAccessStorage::authenticateImpl(DB::Credentials const&, Poco::Net::IPAddress const&, DB::ExternalAuthenticators const&, bool, bool, bool) const @ 0x000000000fbebbba
5. DB::AccessControl::authenticate(DB::Credentials const&, Poco::Net::IPAddress const&, String const&) const @ 0x000000000fb27ac5
6. DB::Session::authenticate(DB::Credentials const&, Poco::Net::SocketAddress const&) @ 0x0000000010ff2083
7. DB::HTTPHandler::authenticateUser(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&) @ 0x00000000122edf84
8. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::optional<DB::CurrentThread::QueryScope>&, StrongTypedef<unsigned long, ProfileEvents::EventTag> const&) @ 0x00000000122ef79e
9. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&, StrongTypedef<unsigned long, ProfileEvents::EventTag> const&) @ 0x00000000122f7d54
10. DB::HTTPServerConnection::run() @ 0x00000000123772fa
11. Poco::Net::TCPServerConnection::start() @ 0x0000000014c9bef2
12. Poco::Net::TCPServerDispatcher::run() @ 0x0000000014c9cd39
13. Poco::PooledThread::run() @ 0x0000000014d954a1
14. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000014d93a3d
15. ? @ 0x00007feff7d13ac3
16. ? @ 0x00007feff7da5850
 (version 24.3.2.23 (official build))
2024.10.16 06:03:14.160033 [ 303738 ] {} <Error> DynamicQueryHandler: Code: 516. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml
and deleting this file will reset the password.
See also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed.

. (AUTHENTICATION_FAILED), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbcedbb
1. DB::Exception::Exception(PreformattedMessage&&, int) @ 0x000000000774d36c
2. DB::AccessControl::authenticate(DB::Credentials const&, Poco::Net::IPAddress const&, String const&) const @ 0x000000000fb27f8b
3. DB::Session::authenticate(DB::Credentials const&, Poco::Net::SocketAddress const&) @ 0x0000000010ff2083
4. DB::HTTPHandler::authenticateUser(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&) @ 0x00000000122edf84
5. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::optional<DB::CurrentThread::QueryScope>&, StrongTypedef<unsigned long, ProfileEvents::EventTag> const&) @ 0x00000000122ef79e
6. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&, StrongTypedef<unsigned long, ProfileEvents::EventTag> const&) @ 0x00000000122f7d54
7. DB::HTTPServerConnection::run() @ 0x00000000123772fa
8. Poco::Net::TCPServerConnection::start() @ 0x0000000014c9bef2
9. Poco::Net::TCPServerDispatcher::run() @ 0x0000000014c9cd39
10. Poco::PooledThread::run() @ 0x0000000014d954a1
11. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000014d93a3d
12. ? @ 0x00007feff7d13ac3
13. ? @ 0x00007feff7da5850
 (version 24.3.2.23 (official build))

[webvictim]

Thanks. Your protocol as set in /etc/teleport.yaml will need to be clickhouse-http rather than clickhouse to use the HTTP interface - please update the config file, restart Teleport and try again.

  databases:
  - name: "clickhouse-1-http"
    description: "connection via http"
    protocol: "clickhouse-http"
    uri: "clickhouse-1:8443"
    static_labels:
      env: "prod"
    tls:
      ca_cert_file: /etc/clickhouse/ca/ca-certificate.crt

[abalalaev]

@webvictim Thank you for your help and sorry for my inattention!