pwny.cc
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!