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.
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
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.
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 = '*'
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
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.
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
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:
Thus now, we are able to successfully access server postgresql remotely using pgadmin 4 tool.
Recommended Articles
Solution – pgadmin unable to connect to postgresql server
