PostgreSQL default user name is postgres. This is a system user, so to login, first change to this user with command

su - postgres

Now you can connect to PostgreSQL by simply running psql

psql

Now you have full rights to PostgreSQL installation.

Create User

To create user, run

CREATE USER USERNAME-HERE WITH PASSWORD 'PASSWORD-HERE';

Create Database

CREATE DATABASE DB-NAME-HERE WITH OWNER USERNAME-HERE;

You can do this in 2 steps as follows.

CREATE DATABASE DB-NAME-HERE;
grant all privileges on database DB-NAME-HERE to USERNAME-HERE;

Logging in as User

To login as newly created user, you need to run

psql --username=USERNAME-HERE --password

If you get error related to Peer authentication failed, this is because by default PostgreSQL only allow system users to login. PostgreSQL uses a client authentication file “pg_hba.conf” in PostgreSQL’s “data” folder to configure which users can connect from which hosts.

To allow a user to autenticate locally, add following to your “pg_hba.conf” file.

host        DB-NAME-HERE      USERNAME-HERE      127.0.0.1/32        password

For more information, see FATAL: Peer authentication failed for user “USER_NAME”

You can find location of pg_hbq.conf on your server by running

root@hon-vpn:~# find /etc -name "pg_hba.conf"
/etc/postgresql/9.4/main/pg_hba.conf
root@hon-vpn:~# 

Back to PostgreSQL