Using pgAdmin 4 to access a remote PostgreSQL server

0

Sorry for the long post.. I wanted to be as detailed as possible to make it easier to spot a problem..

So I've followed the instructions on the PostgreSQL download page and started running a PostgreSQL server running on port 5432 of my CentOS 7 VM.

## Install the repository RPM
sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

## Install the client packages
sudo yum install postgresql11

## Install the server packages
sudo yum install postgresql11-server

## Initialise the database and enable automatic start
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

After the above steps, I did the following steps to make the postgres server accessible remotely.

# Open DB port
sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent # success

sudo firewall-cmd --reload # success

# Added "listen_addresses = '*'" in the file below
nano /var/lib/pgsql/11/data/postgresql.conf

# and wrote the following lines...
host    all             all             ::0/0                   md5
host    all             all             0.0.0.0/0               md5
# at the end of the `pg_hba.conf` file. 

Before I move on, I just wanted to see if I could loggin to psql as postgres from a different user. So I loggin with my personal account and do this psql -h 127.0.0.1 -U postgres, which gives me this:

psql: FATAL: Ident authentication failed for user "postgres"

This fine gentlemen says that the VM username postgres loggin password and the database role postgres password are two different things (Link), so I do the following to alter postgres role's password

# bash
su - postgres    
psql    
# postgreSQL
ALTER USER postgres PASSWORD 'new_password'
\q
# bash
exit
psql -h 127.0.0.1 -U postgres # this works now

Now I felt very ready.

I fire up pgAdmin 4 on my local machine and do the following and hit save. I get the following error message:

Unable to connect to server:
could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "XX.XXX.XX.XX" and accepting
TCP/IP connections on port 5432?

So I go and double check that the port is listening.

sudo netstat -nlt | grep 5432 
# Output
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp6       0      0 :::5432                 :::*                    LISTEN

As an alternative, I establish an ssh tunnel with ssh -N -L localhost:5432:localhost:5432 postgres@XX.XXX.XX.XX, and doing this in pgAdmin and hitting save. I get the following error message:

Unable to connect to server:
FATAL: Ident authentication failed for user "postgres"

I am baffled.. I don't understand why I get this message since I thought this was solved when I assigned a password for the postgres role..

I've read so many SOF posts related to this problem and experimented with the pg_hba.conf file to try make a connection with no success...

I've been struggling for hours now.. Someone please help..

Young-Chan Park

Posted 2019-02-17T09:11:31.277

Reputation: 1

Your connection through 127.0.0.1 is denied by the rules in pg_hba.conf that are not shown in your question. – Daniel Vérité – 2019-02-21T04:58:09.627

The other attempt to remote connect failing with Connection timed out seems denied by a firewall that drops the packets. Either outbound connections are not allowed on the local machine on TCP/5432 or there is another firewall between it and the destination. – Daniel Vérité – 2019-02-21T05:01:16.827

No answers