PostgreSQL – port 5432

PostgreSQL – also known as Postgres – database quick reference.

By default, newly created databases have a schema called public, but any further schemas can be added, and the public schema isn’t mandatory. If no schema is specified for an object, the search path defaults to $user (current user), and then public.

PostgreSQL manages its internal security on a per-role basis. A role is generally regarded to be a user (a role that can log in), or a group (a role of which other roles are members). Permissions can be granted or revoked on any object down to the column level, and can also allow/prevent the creation of new objects at the database, schema or table levels.

Table of Contents

PostgreSQL Service

Start PostgreSQL service on Kali

sudo service postgresql start

Connect:

sudo -u postgres psql postgres

Stop PostgreSQL service on Kali

sudo service postgresql stop

Connection

Use command-line psql (installed by default on Kali Linux)

psql [options] dbname [username]
psql -c "SELECT * FROM foo" -h hostname dbmaster [username]

# List all available databases
psql -l -h hostname -p 5432
psql -d $DBNAME -h $IP -p 5432 -U $USER --password

On the database server

An OS user postgres is create.

sudo -u postgres psql

List databases

\l
\list

Queries

See queries from NetSPI.

Comments

--comment
/*comment*/

String manipulation

Concatenation

SELECT 'foo'||'bar'

Substring

SELECT SUBSTRING('foobar', 4, 2)

Output

Write query results to a file.

\o results.txt

Revert back to the console.

\o

Generate a script to dump all tables

See COPY (postgresql) command.

This will generate copy commands for all tables.

Add a limit number of lines as the files could be too big.

\o script.sql
SELECT '\copy (select * from ' || table_name || ' LIMIT 100000) to ''/home/kali/db/mydb-' || table_name || '.txt'' WITH DELIMITER ''|'' HEADER'
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
\o

Execute the script.

\i script.sql

Database

Database version

SELECT version();

List databases

SELECT * from pg_database;
SELECT datname, datacl from pg_database;

List tables

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

List columns

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'mytablenamehere'

List users

SELECT * FROM pg_user;

List password hashes

SELECT usename, passwd FROM pg_shadow;

Create a database

See official documentation for all options.

CREATE DATABASE test;

Reference