Setting up PostgreSQL on Debian Jessie

Firstly we install the server and client with:

apt-get install postgresql postgresql-client

Now change user to 'postgres' and launch the postgres shell:

sudo su postgres
psql

Now we can exit the shell by typing:

\q

and then pressing enter. ('exit' or 'quit' will not work)

In order to create a new user we should run a command (as the postgres user) directly from bash:

createuser --interactive

e.g.

createuser --interactive
Enter name of role to add (the username): myuser
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

and to set a password issue:

psql
ALTER USER myuser WITH PASSWORD 'mystrongpassword';

And now to create a new database we can issue:

createdb mydatabase

and to grant permisions to a user for a specific database we can use something like:

GRANT ALL PRIVILEGES ON DATABASE mydatabase to myuser;