Summary
A command injection flaw in pgAdmin 4's Import/Export Data tool allows crafted SQL queries to escape a psql \copy (...) wrapper and inject attacker-controlled TO PROGRAM clauses.
When a user can submit specially formatted queries to the Import/Export feature, psql may execute arbitrary OS commands on the pgAdmin host under the account running pgAdmin.
Affected context
This vulnerability appears when pgAdmin 4:
- Uses the Import/Export Data tool to build psql \copy commands
- Interpolates user-supplied SQL into a Jinja template passed to psql via --command
- Relies on a custom parenthesis-balance checker (_is_query_parens_balanced()) to constrain the query
The issue is relevant where users possess the commonly granted tools_import_export_data permission and can provide arbitrary SQL for import/export operations. It is influenced by PostgreSQL's standard_conforming_strings setting but becomes risky whenever pgAdmin's checker assumptions diverge from psql's actual string parsing.
Exploit preconditions
- Attack surface: HTTP endpoint POST /import_export/job/<sid> exposed through pgAdmin's web interface.
- Required access: Ability to authenticate to pgAdmin and use the Import/Export Data tool (tools_import_export_data permission).
- Trigger point: Submission of a specially crafted SQL query that includes single-quoted strings with backslashes and a trailing TO PROGRAM '<command>' clause.
- Why exploitation is possible: pgAdmin's _is_query_parens_balanced() assumes backslashes escape single quotes (as if standard_conforming_strings were off), while psql in modern PostgreSQL treats backslashes inside single-quoted strings as ordinary characters under the default standard_conforming_strings=on. This mismatch allows the checker to accept queries it believes are safely wrapped, even though psql will terminate the string earlier and interpret the remainder as a separate TO PROGRAM clause, leading to OS command execution via popen().
Why this happens
The root cause is a logic error in a custom query validator that attempts to enforce syntactic constraints without fully matching the downstream interpreter's parsing rules.
pgAdmin's _is_query_parens_balanced() performs manual parenthesis and string-literal balancing to prevent an attacker from breaking out of the \copy (...) wrapper. However, it treats backslashes before single quotes as escape characters, modeling legacy PostgreSQL behavior where standard_conforming_strings=off. Modern PostgreSQL and psql default to standard_conforming_strings=on, under which backslashes inside single-quoted strings are not escape characters. This discrepancy creates an inconsistency between pgAdmin's validation and psql's tokenization.
Conceptually, this maps to CWE-78: Improper Neutralization of Special Elements used in an OS Command ('OS Command Injection'), because attacker-controlled query content ultimately shapes a shell-visible TO PROGRAM clause. It is also closely related to CWE-115: Misinterpretation of Input, as the defense relies on an incomplete and incorrect model of how the downstream parser interprets backslashes and quotes. The secure pattern would avoid rebuilding command lines with partially validated user input and instead ensure the validator and executor share the same parsing semantics or use safer invocation primitives.
Potential impact
- Attackers may inject TO PROGRAM clauses that execute arbitrary OS commands via psql's popen() mechanism.
- Compromise of the system account running pgAdmin (e.g., reading or altering local files and configuration).
- Pivoting to database or network infrastructure reachable from the pgAdmin host.
- Interference with administrative operations such as import/export jobs, leading to data tampering or exfiltration.
- Potential use as a foothold for broader lateral movement in the environment.
General mitigation guidance
Effective mitigation centers on reducing the trust placed in user-supplied SQL when constructing external commands and aligning validation with the actual execution context.
Pattern-based defensive approaches include:
- Avoid building shell-visible command lines that interpolate untrusted SQL or parameters; prefer API-level integration (e.g., using database drivers directly) instead of wrapping psql with dynamically assembled \copy commands.
- Where external tools must be used, isolate user-provided input so it is not interpreted as part of control structures such as TO PROGRAM, and apply strict, conservative validation that rejects ambiguous constructs (e.g., disallow backslashes in single-quoted strings when semantics depend on server settings).
- Ensure any custom syntax or balance checkers derive their rules from the same parser or configuration as the actual execution engine, or reuse the engine's parser rather than re-implementing it.
- Run pgAdmin and associated tooling with least privilege, limiting filesystem and network access so that even if command injection occurs, its impact is constrained.
- Establish clear boundaries for what SQL users are allowed to submit in administrative tools, potentially restricting complex constructs for higher-risk operations like import/export.
How teams detect issues like this
Organizations can approach detection through both code-centric and operational techniques:
- Static analysis: Define rules that flag manual command construction patterns involving psql or other external tools where user-supplied SQL is interpolated into shell-visible command strings. Highlight custom "balancing" or "sanitization" routines as hotspots and verify they match the actual interpreter semantics.
- Code review: During review of administrative tools, look for:
- Hand-written query validators that attempt to model parser behavior.
- Assumptions about how backslashes, quotes, or meta-commands are treated in psql or database clients.
- Use of TO PROGRAM or similar features that bridge SQL operations to OS commands.
- Policy enforcement: Incorporate secure coding policies that discourage constructing raw command lines with untrusted input, and require documentation of how validators are kept in sync with underlying parser rules.
- Runtime monitoring: Use logging and monitoring to detect unusual Import/Export jobs, such as queries that appear to terminate the \copy subquery early or include TO PROGRAM clauses, and alert on or block such patterns.
