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
Now you have full rights to PostgreSQL installation.
To create user, run
CREATE USER USERNAME-HERE WITH PASSWORD 'PASSWORD-HERE';
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 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