ClickHouse DB::Exception: Unknown address pattern type

The "DB::Exception: Unknown address pattern type" error in ClickHouse occurs when a user's network access configuration contains a host pattern type that ClickHouse does not recognize. The UNKNOWN_ADDRESS_PATTERN_TYPE error code indicates a syntax or format problem in the <networks> section of the user definition.

Impact

The user with the malformed address pattern may be unable to connect, or the server may fail to load the user configuration entirely. If the invalid pattern is in the main users.xml file, it could affect server startup or configuration reloading for all users defined in that file.

Common Causes

1. Using an unsupported pattern format in the <networks> block of users.xml
2. Typos in the network configuration tags (e.g., <ips> instead of <ip>, or <hostname> instead of <host>)
3. Invalid CIDR notation or malformed subnet masks
4. Mixing configuration formats from different ClickHouse versions
5. Copy-pasting network patterns from other systems that use different syntax
6. Using regex or wildcard patterns where ClickHouse expects a specific format

Troubleshooting and Resolution Steps

1. Review the user's network configuration:

   • Check users.xml or files in users.d/ for the <networks> block of the affected user.
   • Valid sub-elements include:
     • <ip> -- an IP address or CIDR range (e.g., 10.0.0.0/8, ::1)
     • <host> -- a hostname resolved via DNS
     • <host_regexp> -- a regular expression matching hostnames

2. Fix the pattern format:

   • Valid examples:

     <networks>
       <ip>10.0.0.0/8</ip>
       <ip>192.168.1.100</ip>
       <ip>::1</ip>
       <host>db-client.example.com</host>
       <host_regexp>^.*\.internal\.example\.com$</host_regexp>
     </networks>
     

3. Remove or correct invalid entries:

   • If an unrecognized tag was used, replace it with one of the supported types (<ip>, <host>, or <host_regexp>).

4. For SQL-managed users, check the HOST clause:

   SHOW CREATE USER my_user;
   

   Valid SQL HOST options include:

   ALTER USER my_user HOST IP '10.0.0.0/8', NAME 'client.example.com', REGEXP '.*\.internal\.com';
   

5. Validate and reload configuration:

   SYSTEM RELOAD CONFIG;
   

   Check the server log for any errors related to the configuration reload.

6. Test connectivity after fixing the pattern:

   clickhouse-client --user my_user --password my_password --host localhost
   

Best Practices

• Use only the documented address pattern types: <ip>, <host>, and <host_regexp> in XML, or IP, NAME, REGEXP, LIKE in SQL.
• Validate configuration changes locally before deploying them to production servers.
• Use CIDR notation for IP ranges rather than attempting wildcard patterns.
• Test regex patterns with a regex tool before adding them to <host_regexp> to ensure correctness.
• Keep ClickHouse documentation bookmarked for the exact syntax of network configuration, as it differs from other database systems.

Frequently Asked Questions

Q: What address pattern types does ClickHouse support?
A: In XML configuration: <ip> for IP addresses and CIDR ranges, <host> for DNS hostnames, and <host_regexp> for regex-based hostname matching. In SQL: IP, NAME, REGEXP, and LIKE.

Q: Can I use a wildcard like 10.0.*.* in the IP field?
A: No. The <ip> field requires standard IP addresses or CIDR notation. Use 10.0.0.0/16 instead of 10.0.*.*.

Q: Does ClickHouse resolve hostnames in the <host> pattern at connection time?
A: Yes. When <host> is used, ClickHouse performs a reverse DNS lookup of the client's IP and compares the result to the specified hostname. This requires properly configured DNS.

Q: Can a bad address pattern prevent the server from starting?
A: In most cases, ClickHouse logs a warning and skips the invalid pattern rather than failing to start. However, it depends on the severity and location of the configuration error. Always check server logs after making changes.