Config Remote Connection to DATABASE using PgAdmin

Hi Guy’s Welcome to Proto Coders Point. In this article let’s enable postgresql remote connection & then by using pgadmin tools connect remote postgres database.

Install PostgreSQL on Ubuntu aws Se...
Install PostgreSQL on Ubuntu aws Server & Config postgres for remote connection using pgadmin

I have already wrte a complete step by step article on how to install postgresql on ubuntu server, check to out if you don’t have psql installed on your ubuntu server.

pgadmin connect to remote database – enable postgres to allow remote connections

I have psql been installed on my AWS ubuntu server, but by default postgresql remote connection is disabled for globally accessing db using server IP & can only by accessed locally on server itself.

Now, Suppose I want to use pgadmin 4 to connect to remote database & perform psql queries to perform CRUD operation on my server db. Then, I need to setup postgreSQL for globally accessable through IP & pgadmin 4 tools.

Video Tutorial

Video tutorial on configuring postgresql for remote access database

Let’s get started

Follow below Steps to configure postgresql remote access

Step 1: Setup password access to postgres user

Enter below command, to enter psql cli to interact with postgres database engine.

sudo -u postgres psql

set a password for postgres user

By Default, “postgres” admin user don’t have password been assigned, so we need to set a new password to postgres user by ourself.

enter \password <username>

\password postgres

Now, psql cli will prompt you to enter password twice.

set password to postgres

Step 2: Edit postgresql.conf file & change listen address

When postgresql is been installed, psql allows listening to localhost connections only, & thus block all the connection request from remote TCP/IP connection.

Open postgresql.conf file

sudo vim /etc/postgresql/14/main/postgresql.conf

Note: To enter Insert mode in ubuntu vim editor press “i”, & to save the file press “esc” > then enter “:wq”.

use down arrow to scroll, search for listen_addresses = “localhost”, which will commented, uncomment it and replace “localhost” with “*” as shown below:

listen_addresses = '*'
edit postgresql listen_addresses
edit postgresql listen_addresses = “*”

Step 3: Edit postgres pg_hba.conf for remote host access for all IP

By default, psql connection is only accepted for localhost & it refuses all the request from TCP/IP remote connection. Therefore, we need to allow a user to login from any IP address.

Open pg_hba.conf file & add this 2 lines at bottom of the file.

sudo vim /etc/postgresql/14/main/pg_hba.conf

#IPv4 Addresses
host all all 0.0.0.0/0 md5
#IPv6 Addresses
host all all ::0/0 md5

postgresql  allow remote conntection to database host all IPv4 & IPv6
postgresql allow remote conntection to database host all IPv4 & IPv6

Save the file.


Step 4: Restart postgreSQL

Now, To apply changed made in postgresql.cong & pg_hba.conf, we need to restart postgreSQL service.

sudo systemctl restart postgresql

Step 5: Check postgresql listening post number

ss -nlt | grep 5432

This should give response as running on global 5432, i.e. 0.0.0.0:5432. This means that psql is now enabled for remote access.

check postgresql listening port

Step 6: pgadmin 4 connect to remote connection

Important Note: Make sure you have defined InBound rules port range 5432 on your cloud service instance security for remote access postgress

add rules to remote access postgresql port 5432

Now let’s start pgadmin 4 tools & connect to postgresql remotely

On pgadmin dashboard click on add new server > In Connection Tab enter “HOST ID”, “port”, “Username” & “password”. reference below screenshot:

enter psql connection string for remote connection

Thus now, we are able to successfully access server postgresql remotely using pgadmin 4 tool.

how to connect to postgresql using pgadmin 4
connect to postgresql using pgadmin 4

Recommended Articles

Solution – pgadmin unable to connect to postgresql server