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..
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.627The 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