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.

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

Comments

--comment

/*comment*/

String manipulation

Concatenation

SELECT 'foo'||'bar'

Substring

SELECT SUBSTRING('foobar', 4, 2)

Database

Database version

SELECT version()

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'

Create a database

See official documentation for all options.

CREATE DATABASE test;