First Steps in Postgres

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

 


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.