ClickHouse DB::Exception: IP address not allowed

The "DB::Exception: IP address not allowed" error in ClickHouse occurs when a client attempts to connect from an IP address that is not included in the allowed host list for the target user account. This is the IP_ADDRESS_NOT_ALLOWED error code, and it acts as a network-level access control mechanism built into ClickHouse's user configuration.

Impact

Connections from the blocked IP are refused before authentication even takes place. This means no queries can run, and any application connecting from a disallowed address will experience immediate failures. In environments with dynamic IPs or containerized deployments, this error can appear suddenly when infrastructure changes shift the source addresses of legitimate clients.

Common Causes

The user's <networks> or <ip> configuration in users.xml does not include the client's current IP address
SQL-managed users created with a HOST clause that restricts allowed addresses
Client connecting through a NAT gateway, VPN, or proxy whose egress IP differs from what is configured
Container orchestration (Kubernetes, Docker) assigning new pod IPs that fall outside the allowed ranges
IPv4 vs. IPv6 mismatch -- the client connects over IPv6 but only IPv4 addresses are whitelisted, or vice versa

Troubleshooting and Resolution Steps

Identify the client's IP address:
- Determine the IP that ClickHouse sees by checking the server log for the rejected connection attempt.
- From the client side, you can verify your outbound IP with:
```
curl ifconfig.me
```

Review the user's allowed hosts:

For XML-configured users, inspect users.xml or files in users.d/:

<users>
  <my_user>
    <networks>
      <ip>10.0.0.0/8</ip>
      <ip>::/0</ip>
    </networks>
  </my_user>
</users>

For SQL-managed users:
```
SHOW CREATE USER my_user;
```

Update the allowed network list:
- To add an IP or subnet via SQL:
```
ALTER USER my_user ADD HOST IP '192.168.1.0/24';
```
- To allow connections from any address (use with caution):
```
ALTER USER my_user HOST ANY;
```
Check for IPv4/IPv6 issues:
- If the client connects over IPv6 and only IPv4 is whitelisted, add the corresponding IPv6 address or range.
- The loopback address ::1 is the IPv6 equivalent of 127.0.0.1.
Verify proxy and NAT configuration:
- If connections pass through a load balancer or NAT device, ensure the egress IP of that device is in the allowed list.
- Some proxies support the PROXY protocol, which can pass the original client IP to ClickHouse.
Restart or reload if using XML configuration:
- After editing users.xml, ClickHouse typically picks up changes automatically, but you can force a config reload:
```
SYSTEM RELOAD CONFIG;
```

Best Practices

Use CIDR notation to allow entire subnets rather than individual IPs, especially in dynamic environments.
Prefer SQL-managed access control for easier auditing and modification without server restarts.
Maintain separate user accounts for different network zones (internal services, external clients) with appropriate IP restrictions.
Combine IP restrictions with strong authentication for defense in depth.
Document allowed IP ranges and review them periodically, especially after infrastructure changes.
In Kubernetes environments, consider using the pod CIDR range or a stable egress IP for ClickHouse access.

Frequently Asked Questions

Q: Can I allow all IP addresses for a user?
A: Yes, you can set HOST ANY in SQL or add <ip>::/0</ip> and <ip>0.0.0.0/0</ip> in XML configuration. However, this removes an important security layer and should only be done when other controls (firewalls, VPNs) are in place.

Q: Why does my local connection get rejected even though I added 127.0.0.1?
A: Your client may be connecting over IPv6, in which case the source address is ::1. Add both 127.0.0.1 and ::1 to the allowed list.

Q: How do I find out which IP ClickHouse sees for my connection?
A: Check the ClickHouse server log (typically at /var/log/clickhouse-server/clickhouse-server.log). Failed connection attempts include the client IP in the error message.

Q: Does changing the allowed hosts require a server restart?
A: For SQL-managed users, changes take effect immediately. For XML-based configuration, ClickHouse watches for file changes and reloads automatically, though you can also run SYSTEM RELOAD CONFIG to force it.