How To Access PostgreSQL from pgAdmin

By default PostgreSQL is configured to be bound to localhost only, and any attempt to connect to the PostgreSQL server from outside will be refused. To make the connection possible from outside, you will need to make few changes in PostgreSQL configuration file.

This guide is applicable if your PostgreSQL is running on CentOS, RHEL 7/8, Ubuntu 18/19/20.04 and Debian 9, 10. You can install and use pgAdmin either on Windows, Linux or macOS.

 

You will need to set the postgres user password if you are working on a freshly installed PostgreSQL server.

sudo -u postgres psql postgres

On postgres=# prompt, type the following to set the postgres user password:

ALTER USER postgres WITH PASSWORD 'TypeStrongPasswordHere';

Exit from postgres=# prompt:

\q

Configure PostgreSQL

First you will need to locate postgresql.conf file using the following command:

sudo find / -name postgresql.conf

This will return you the absolute path of the file, edit postgresql.conf file with any of your favorite text editor:

sudo vi /var/lib/pgsql/12/data/postgresql.conf

Uncomment the following lines, and replace localhost like below:

listen_addresses = '*'
port = 5432

Save and close the file when you are finished.

Next, locate pg_hba.conf file using the following command:

sudo find / -name pg_hba.conf

This will return you absolute path of the file, edit pg_hba.conf file with any of your favorite text editor:

sudo vi /var/lib/pgsql/12/data/pg_hba.conf

Add the following line at the end of the file:

host    all        all             0.0.0.0/0               md5

Save and close the file when you are finished. 

Restart PostgreSQL to take changes into effect:

sudo systemctl restart postgresql-12

Now go to the pgAdmin and access your PostgreSQL database:

Enter your database server name in the Name box, then click Connection tab.

Enter the postgresql_server_ip in the Host name/address box, then enter postgres user's password and click Save button.

This will establish a connection to your PostgreSQL server, and show you all the information of your databases as shown in below screenshot: