PostgreSQL

Search…
PostgreSQL
Some of the queries in the table below can only be run by an admin. These are marked with (PRIV) at the description.
Version
1
SELECT version();
Copied!
Comments
1
SELECT 1; --comment
2
SELECT /*comment*/1;
Copied!
Current User
1
SELECT user;
2
SELECT current_user;
3
SELECT session_user;
4
SELECT getpgusername();
Copied!
List Users
1
SELECT usename FROM pg_user;
Copied!
List Password Hashes (PRIV)
1
SELECT usename, passwd FROM pg_shadow;
Copied!
List Privileges
1
SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user;
Copied!
List DBA Accounts
1
SELECT usename FROM pg_user WHERE usesuper IS TRUE;
Copied!
Check if Current User is Superuser
1
SELECT current_setting('is_superuser')='on';
Copied!
Current Database
1
SELECT current_database();
Copied!
List Databases
1
SELECT datname FROM pg_database;
Copied!
List Tables
1
SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid);
Copied!
List Columns
1
SELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public');
Copied!
Find Tables from Column Name
1
#If you want to list all the table names that contain a column LIKE '%password%':
2
SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public') AND attname LIKE '%password%';
Copied!
Hostname, IP Address
1
#Returns db server IP address (or null if using local connection) 
2
SELECT inet_server_addr();
3
​
4
#Returns db server port
5
SELECT inet_server_port();
Copied!
Create Users (PRIV)
1
CREATE USER test1 PASSWORD 'pass1';
2
​
3
#Grant some privs at the same time
4
CREATE USER test1 PASSWORD 'pass1' CREATEUSER;
Copied!
Delete Users (PRIV)
1
DROP USER test1;
Copied!
Make User DBA (PRIV)
1
ALTER USER test1 CREATEUSER CREATEDB;
Copied!
Location of DB Files (PRIV)
1
SELECT current_setting('data_directory');
2
SELECT current_setting('hba_file');
Copied!
Read Files (PRIV)
1
COPY passwords from $c:\passwords.txt$;
2
SELECT content from passwords;
Copied!
Write Files (PRIV)
1
CREATE temp table passwords (content text);
2
COPY (SELECT $passwords$) to $c:\passwords.txt$;
Copied!
Oracle
Next - WEB ATTACKS
Cross-Site Scripting (XSS)
Last modified 8mo ago