How to remotely connect postgresql database



Right after successfully installing your postgresql database, go and use the following command to test the database.

$ sudo --user=postgres psql

then use the following command to display all users


Next, we need a new user for daily usage. the "user" is equivalent to "role".

$ create role newuser SUPERUSER LOGIN PASSWORD 'your_password';

do not forget the semicolon ";" in the end.

now use \q to quit


and log into the database with new created newuser .

$ psql -h localhost -d postgres -U newuser

Update postgresql.conf file

Use netstate command to show the current opening ports

$ netstat -nlt

You will see the 5432 port is limited to localhost only. we need to change it to

Proto Recv-Q Send-Q Local Address           Foreign Address         State  
tcp        0      0*               LISTEN

Find the configuration file

$ find / -name "postgresql.conf"

it is very high chance, you will find the conf file here


go use vim to open and edit it

$ sudo vim /etc/postgresql/11/main/postgresql.conf

do remember use sudo, or you will open the file in readonly mode. Next go and find

listen_addresses = 'localhost'

replace it with

listen_addresses = '*'

save and quit vim.

Update pg_hba.conf file

Use the same method to find and open pg_hba.conf. add the following to the very end of the file.

host    all             all                           md5
host    all             all              ::/0                            md5

save and quit vim.

Almost done

Now restart postgresql service and you should be able to connect the database server remotly.

$ sudo /etc/init.d/postgresql restart