By default, PostgreSQL only listens for connections from the localhost. All remote access to the server is denied by default. To enable remote access, run the commands below to open PostgreSQL configuration file.
sudo vim /etc/postgresql/11/main/pg_hba.conf
Add the following line to the bottom of the file then save and close.
host all all 0.0.0.0/0 md5
host all all ::/0 md5
Restart PostgreSQL server to apply the changes:
sudo systemctl restart postgresql
Configuring postgresql.conf
$ find / -name "postgresql.conf"
sudo vim /etc/postgresql/11/main/postgresql.conf
Open postgresql.conf file and replace line
listen_addresses = 'localhost'
with
listen_addresses = '*'
sudo systemctl restart postgresql
Create a User and Apply Permissions
Logon to postgresL
sudo -u postgres psql
psql=>
Create the database testdb:
psql=> create database testdb;
Create s user and assign a password:
psql=> create user testuser with encrypted password 'testuser123';
Grant all privileges for the user on the database:
psql=> grant all privileges on database testdb to testuser;
psql=> \q
Restart the server:
sudo systemctl restart postgresql
Reference:
https://www.bigbinary.com/blog/configure-postgresql-to-allow-remote-connection
