ClickHouse DB::Exception: Unacceptable URL

The "DB::Exception: Unacceptable URL" error in ClickHouse is raised when a query references a URL that the server considers invalid or disallowed. The error code is UNACCEPTABLE_URL. This happens either because the URL is malformed, or because ClickHouse's security configuration explicitly blocks access to the specified host or address range.

Impact

Any query that relies on an external URL, whether through the url() table function, an external dictionary, or a remote storage engine, will fail when the URL is deemed unacceptable. This prevents data from being loaded or queried from the remote source and can block ETL pipelines or dashboard queries that depend on external data.

Common Causes

1. The URL points to a private or internal IP address (e.g., 127.0.0.1, 10.x.x.x, 192.168.x.x) and ClickHouse is configured to block requests to local networks for security reasons.
2. The URL is malformed, containing invalid characters, missing the host portion, or using an incorrect format.
3. ClickHouse's remote_url_allow_hosts or URL filtering rules explicitly deny access to the target host.
4. The URL includes a port number that is blocked by the server's security configuration.
5. DNS resolution of the hostname results in a private IP address, which ClickHouse then blocks.

Troubleshooting and Resolution Steps

1. Examine the exact URL in the failing query. Ensure it is properly formatted with a valid scheme, host, and path:

   -- Verify the URL is well-formed
   SELECT * FROM url('https://example.com/data.csv', CSV);
   

2. Check whether the URL resolves to a private or loopback address, which ClickHouse blocks by default:

   nslookup example.com
   

3. If you need to access internal hosts legitimately, configure the allowed hosts in the ClickHouse server configuration:

   <remote_url_allow_hosts>
       <host>internal-service.local</host>
       <host_regexp>.*\.trusted-domain\.com</host_regexp>
   </remote_url_allow_hosts>
   

4. Review your ClickHouse configuration for any URL filtering rules that may be blocking the request. Look in config.xml or config.d files for remote_url_allow_hosts settings.

5. If the hostname resolves to a private IP due to DNS configuration (split-horizon DNS), consider using the direct IP or adjusting DNS to return a public address for ClickHouse queries.

6. Ensure the URL does not contain encoded characters that might cause parsing issues. Try a simplified version of the URL to isolate the problem.

Best Practices

• Keep the default security restrictions that block access to private IP ranges unless you have a specific, well-understood need to access internal resources.
• Use the remote_url_allow_hosts allowlist to explicitly permit only the external hosts that ClickHouse needs to reach, rather than disabling URL filtering entirely.
• Validate URLs programmatically before passing them to ClickHouse queries to catch malformed inputs early.
• Document which external endpoints your ClickHouse deployment is expected to access, and include them in configuration management.

Frequently Asked Questions

Q: Why does ClickHouse block URLs that resolve to private IP addresses?
A: This is a security measure to prevent Server-Side Request Forgery (SSRF) attacks, where a malicious query could trick ClickHouse into accessing internal services that should not be reachable from external inputs.

Q: Can I disable URL filtering entirely?
A: While it is technically possible to configure very permissive rules, doing so is strongly discouraged. Disabling URL filtering exposes your infrastructure to SSRF vulnerabilities. Instead, allowlist only the specific hosts you need.

Q: I am accessing a valid public URL but still getting this error. What could be wrong?
A: The hostname might resolve to a private IP in your network environment, or there may be a restrictive remote_url_allow_hosts configuration that does not include the target host. Check both DNS resolution and the server configuration.

Q: Does this affect the S3 table function as well?
A: The S3 table function has its own connection handling, but URL validation rules can still apply. If your S3 endpoint URL is blocked, you will see a similar error. Ensure the S3 endpoint is reachable and permitted by your configuration.