Всем привет, Пытаюсь настроить s3 integration и получаю CERTIFICATE_VERIFY_FAILED В /etc/clickhouse-server/config.xml:

<s3>
<endpoint-name>
<endpoint>https://s3.us-east-2.amazonaws.com/test</endpoint>
<region>us-east-2</region>
<use_environment_credentials>true</use_environment_credentials>
<use_insecure_imds_request>true</use_insecure_imds_request>
</endpoint-name>
</s3>

Выполняю вот такой запрос:

SELECT *
FROM s3('https://s3.us-east-2.amazonaws.com/test/2.csv', 'CSV', 'fqdn String,schema_path String,restconf_path String')

Query id: 9222a01c-9d56-43e4-87aa-36728b2214da

Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)
0 rows in set. Elapsed: 128.619 sec.

Received exception from server (version 22.4.2):
Code: 499. DB::Exception: Received from 0.0.0.0:9000. DB::Exception: Poco::Exception. Code: 1000, e.code() = 0, SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED (version 22.4.2.1 (official build)): While executing ParallelParsingBlockInputFormat: While executing S3. (S3_ERROR)

В логах

2022.09.15 15:05:11.522882 [ 21719 ] {9222a01c-9d56-43e4-87aa-36728b2214da} <Error> AWSClient: HTTP response code: -1
Resolved remote host IP address:
Request ID:
Exception name:
Error message: Poco::Exception. Code: 1000, e.code() = 0, SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED (version 22.4.2.1 (official build))
0 response headers:
2022.09.15 15:05:11.522891 [ 21719 ] {9222a01c-9d56-43e4-87aa-36728b2214da} <Warning> AWSClient: If the signature check failed. This could be because of a time skew. Attempting to adjust th
e signer.
2022.09.15 15:05:11.522930 [ 21719 ] {9222a01c-9d56-43e4-87aa-36728b2214da} <Debug> ReadBufferFromS3: Caught exception while reading S3 object. Bucket: test, Key: 2.csv, Offset: 0, Attempt: 3, Message: Poco::Exception. Code: 1000, e.code() = 0, SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED (version 22.4.2.1 (official build))
2022.09.15 15:05:11.522975 [ 21719 ] {9222a01c-9d56-43e4-87aa-36728b2214da} <Error> void DB::ParallelParsingInputFormat::onBackgroundException(size_t): Code: 499. DB::Exception: Poco::Exception. Code: 1000, e.code() = 0, SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED (version 22.4.2.1 (official build)). (S3_ERROR), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xb6fa67a in /usr/bin/clickhouse
1. DB::ReadBufferFromS3::initialize() @ 0x1512627c in /usr/bin/clickhouse
2. DB::ReadBufferFromS3::nextImpl() @ 0x1512517b in /usr/bin/clickhouse
3. ? @ 0x170cff04 in /usr/bin/clickhouse
4. DB::ParallelParsingInputFormat::segmentatorThreadFunction(std::__1::shared_ptr<DB::ThreadGroupStatus>) @ 0x17126eaa in /usr/bin/clickhouse
5. ThreadFromGlobalPool::ThreadFromGlobalPool<void (DB::ParallelParsingInputFormat::*)(std::__1::shared_ptr<DB::ThreadGroupStatus>), DB::ParallelParsingInputFormat*, std::__1::shared_ptr<DB::ThreadGroupStatus> >(void (DB::ParallelParsingInputFormat::*&&)(std::__1::shared_ptr<DB::ThreadGroupStatus>), DB::ParallelParsingInputFormat*&&, std::__1::shared_ptr<DB::ThreadGroupStatus>&&)::'lambda'()::operator()() @ 0x1712c58c in /usr/bin/clickhouse
6. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xb7a5f27 in /usr/bin/clickhouse
7. ? @ 0xb7a995d in /usr/bin/clickhouse
8. start_thread @ 0x7ea5 in /usr/lib64/libpthread-2.17.so
9. clone @ 0xfeb0d in /usr/lib64/libc-2.17.so
(version 22.4.2.1 (official build))

где может быть проблема?

<use_insecure_imds_request>true</use_insecure_imds_request> а это зачем? <use_environment_credentials>true</use_environment_credentials> AWS_* переменные для процесса clickhouse-server заданы? sudo cat /proc/$(pgrep clickhouse-server | head -n 1)/environ их показывает?

нет sudo cat /proc/29411/environ LANG=en_US.UTF-8PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/binHOME=/nonexistentLOGNAME=clickhouseUSER=clickhouseSHELL=/bin/false

попробуйте явно задать через named collection ? https://clickhouse.com/docs/en/operations/named-collections/#named-collections-for-accessing-s3

на всякий случай уточню у меня есть IAM роль на этом EC2 с полным доступом к S3

вот тут утверждали что должно работать https://github.com/ClickHouse/ClickHouse/issues/24470

да, IAM должен работать тогда... непонятно ... awscli пробовали на этом EC2 запускать?

да - он работает

есть один ньюанс у нас внутри VPC закрыт доступ в интернет

а какая версия OS? может надо apt-get install -y ca-certificates обновить?

КХ ходит в s3 через интернет имена

sudo yum install ca-certificates Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile Package ca-certificates-2021.2.50-72.el7_9.noarch already installed and latest version Nothing to do

а сделайте issue на github и тегните @excitoon ?

т.е. КХ не сможет сюда https://s3.us-east-2.amazonaws.com/test пойти без интернета

понял спасибо

https://github.com/ClickHouse/ClickHouse/blob/365438d6172cb643603d59a81c12eb3f10d4c5e6/src/IO/S3Common.cpp#L167-L201 можно еще Trace log посмотреть и поглядеть пробуют получать credentials или нет

понял сейчас включу

2022.09.15 15:29:29.689212 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Trace> AWSEC2InstanceProfileConfigLoader: Getting default credentials for EC2 instance. 2022.09.15 15:29:29.690028 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Error> AWSClient: AWSHttpResourceClient: Http request to retrieve credentials failed 2022.09.15 15:29:29.690042 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Warning> AWSClient: AWSHttpResourceClient: Request failed, now waiting 0 ms before attempting again. 2022.09.15 15:29:29.690620 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Error> AWSClient: AWSHttpResourceClient: Http request to retrieve credentials failed 2022.09.15 15:29:29.690638 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Error> AWSClient: AWSHttpResourceClient: Can not retrive resource from http://169.254.169.254/latest/meta-data/i am/security-credentials 2022.09.15 15:29:29.690661 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Information> AWSClient: Aws::Config::AWSProfileConfigLoader: Failed to reload configuration. 2022.09.15 15:29:29.690670 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Information> AWSClient: Aws::Config::AWSConfigFileProfileConfigLoader: Unable to open config file /nonexistent/. aws/credentials for reading. 2022.09.15 15:29:29.690673 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Information> AWSClient: Aws::Config::AWSProfileConfigLoader: Failed to reload configuration. 2022.09.15 15:29:29.692831 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Debug> AWSInstanceProfileCredentialsProvider: Checking if latest credential pull has expired. 2022.09.15 15:29:29.700927 [ 25804 ] {5f58e8b0-2e21-4428-b32c-cdb7362ef53d} <Error> AWSClient: Failed to make request to: https://s3.us-east-2.amazonaws.com/test/2.csv: Poco::Exce ption. Code: 1000, e.code() = 0, SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED, Stack trace (when copying this message, always include the lines belo w):

curl -vvv http://169.254.169.254/latest/meta-data/iam/security-credentials что на EC2 инстансе показывает?

curl -vvv http://169.254.169.254/latest/meta-data/iam/security-credentials * About to connect() to 169.254.169.254 port 80 (#0) * Trying 169.254.169.254... * Connected to 169.254.169.254 (169.254.169.254) port 80 (#0) > GET /latest/meta-data/iam/security-credentials HTTP/1.1 > User-Agent: curl/7.29.0 > Host: 169.254.169.254 > Accept: */* > < HTTP/1.1 401 Unauthorized < Content-Length: 0 < Date: Thu, 15 Sep 2022 16:34:15 GMT < Server: EC2ws < Connection: close < Content-Type: text/plain < * Closing connection 0

выглядит так что у вас никакого IAM не включено... для ec2