By default, postgres allow only local connections and and uses peer authentification to access the databases. In some cases this provoke the error message “
FATAL: Peer authentication failed for user "postgres"
This is an explanation how to configure postgres to ask for encrypted password and allow remote connections.
Change password for rootuser “postgres”
Connect to the default database with user postgres
sudo -u postgres psql template1
Set the password for user postgres
ALTER USER postgres with encrypted password 'yourpassword';
Exit (ctrl + d)
Config your postgres to allow remote access and to ask for passwords
Get your IP
curl ipinfo.io/ip
here 172.168.0.1
Find postgresql.conf
find / -name "postgresql.conf"
here /etc/postgresql/10/main/postgresql.conf
Edit the file
sudo vim /etc/postgresql/10/main/postgresql.conf
Replace
#listen_addresses = 'localhost'
With
listen_addresses = '*'
Find pg_hba.conf
sudo find / -name "pg_hba.conf
here /etc/postgres/10/main/pg_hba.conf
Edit the file
sudo vim /etc/postgresql/10/main/pg_hba.conf
Change lines
local all postgres peer
local all all peer
To
local all postgress md5
local all all md5
And add
host all all 172.168.0.1/32 md5
Restart the database
sudo /etc/init.d/postgresql restart
Check, if it worked
pql -U postgres
Exit (ctrl + d)
Create user and database
Create user with linuxusername
sudo createuser -U postgres -d -e -E -l -P -r -s $USER
Create database
createdb mynewdatabase
You can now remote connect to your database