ClickHouse PostgreSQL SSRF to RCE Chain Testing

What It Is

ClickHouse PostgreSQL SSRF to RCE chain testing examines a multi-stage attack vector where Server-Side Request Forgery vulnerabilities escalate into Remote Code Execution. The attack exploits ClickHouse’s postgresql() table function, which allows querying external PostgreSQL databases. When combined with insufficient input validation and PostgreSQL’s COPY FROM PROGRAM feature, attackers can chain these capabilities to execute arbitrary commands on the target system.

The attack typically begins with an SSRF vulnerability in an application that accepts user-controlled input passed to ClickHouse. Attackers craft malicious queries using the postgresql() function to connect to a controlled PostgreSQL instance. From there, they leverage PostgreSQL’s ability to execute shell commands through the COPY statement, ultimately achieving code execution on the server hosting the database integration.

Why It Matters

This vulnerability chain demonstrates how seemingly isolated security features become critical weaknesses when combined. Organizations running ClickHouse with PostgreSQL integrations face exposure if they haven’t implemented proper input sanitization and network segmentation. The attack bypasses traditional perimeter defenses by operating entirely within trusted internal network communications.

Security teams benefit from understanding this chain because it highlights the importance of defense-in-depth strategies. A single mitigation point - whether input validation, network isolation, or database permission restrictions - can break the entire attack sequence. Penetration testers and red teams use these techniques to identify weaknesses in data pipeline architectures, particularly in analytics platforms that frequently integrate multiple database systems.

The broader ecosystem impact extends to any organization using ClickHouse for real-time analytics, especially those processing user-generated content or webhook data. Companies like PostHog, which use ClickHouse extensively, must carefully audit their query construction and database connection patterns to prevent exploitation.

Getting Started

Testing this chain requires a controlled environment with both ClickHouse and PostgreSQL instances. Start by verifying basic connectivity using ClickHouse’s HTTP interface:

For SSRF testing, URL-encode the query and submit it through the ClickHouse HTTP endpoint at http://clickhouse:8123/?query=SELECT+*+FROM+postgresql(...). Test parameter escaping by injecting closing parentheses and quotes to break out of the function context.

On the PostgreSQL side, create a table to capture command output:

COPY cmd_exec FROM PROGRAM $$bash -c "whoami"$$;
SELECT * FROM cmd_exec;

The PROGRAM keyword in PostgreSQL’s COPY statement executes the specified shell command and captures its output. Attackers chain this with ClickHouse queries to achieve remote execution.

Review the official PostgreSQL documentation at https://www.postgresql.org/docs/current/sql-copy.html for complete COPY syntax details. Check ClickHouse security advisories at https://github.com/ClickHouse/ClickHouse/security/advisories for known escaping bugs and patches.

Context

Alternative database integration methods include using read-only replicas, implementing strict query allowlists, or employing database proxies that filter dangerous SQL constructs. Some organizations opt for materialized views or scheduled ETL jobs instead of direct cross-database queries, eliminating real-time SSRF risks entirely.

Compared to traditional SQL injection, this chain requires multiple vulnerable components aligned in sequence. The attack fails if ClickHouse runs with restricted network access, if PostgreSQL operates without superuser privileges, or if the application properly sanitizes user input before query construction. Modern database systems increasingly disable dangerous features like COPY FROM PROGRAM by default, though legacy configurations remain vulnerable.

Limitations include the need for network connectivity between ClickHouse and a controlled PostgreSQL instance, plus sufficient privileges on the PostgreSQL side to execute COPY statements. Detection mechanisms can identify unusual postgresql() function calls, unexpected outbound database connections, or suspicious PROGRAM usage in PostgreSQL logs. Organizations should implement query parameterization, principle of least privilege for database accounts, and network segmentation to prevent this attack chain from succeeding.