Issue
The title might make this seem like a duplicate of some existing questions here on SO, but its kind of not in a way, in my opinion.
href="https://stackoverflow.com/questions/8140863/unable-to-connect-to-postgresql-server-could-not-connect-to-server-permission">8140863, 30617357, 27749691
But I can't for the life of me make the answers to these threads work, instead of asking questions on those threads, I would like to create my own question, I'd like to discuss my setup and then my problem, and then what I've tried
My server setup is this, I have a local(an actual physical machine) and a VPS I'm renting, both are running at Centos7.x, PostgreSQL10, Php 7.x, and Python-2.x, BOTH can ping and ssh each other through a VPN just fine.
Both can connect to their own respective databases just fine through PHP, Python, PSQL Terminal
On my VPS Server, I can connect to my Local Server's PostgreSQL database using Python(psycopg2), PHP(php-pgsql) and Terminal(psql) totally fine.
On my local server, I can connect to my VPS PostgreSQL using Python(psycopg2) and Terminal(psql) EXCEPT through PHP(php-pgsql)
Whenever I connect to VPS's PostgreSQL through PHP, I'm getting this error like everyone else
pg_connect(): Unable to connect to PostgreSQL server:
could not connect to server: Permission denied
Is the server running on host "123.456.0.789" and accepting TCP/IP connections on port 5432?
Here are what I've done.
- On postgresql.conf set listen_address = "*" -- this is OK
- Added pg_hba entries for both -- this is OK (python and terminal can connect on both)
- Port 5432/tcp is both enabled and allowed -- this is OK
- Selinux is disabled -- this is the answers for the thread referenced above, but it doesn't work for me even after reboot of course.
these are the result of my netstat
#netstat -na | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp6 0 0 :::5432 :::* LISTEN
tcp6 0 94 ::1:55110 ::1:5432 ESTABLISHED
tcp6 0 0 ::1:5432 ::1:55060 ESTABLISHED
tcp6 0 0 ::1:5432 ::1:55110 ESTABLISHED
tcp6 12 0 ::1:5432 ::1:55108 ESTABLISHED
tcp6 0 12 ::1:55108 ::1:5432 ESTABLISHED
tcp6 0 0 ::1:55060 ::1:5432 ESTABLISHED
unix 2 [ ACC ] STREAM LISTENING 31102 /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 31104 /tmp/.s.PGSQL.5432
iptables
#iptables-save | grep 5432
-A IN_public_allow -p tcp -m tcp --dport 5432 -m conntrack --ctstate NEW,UNTRACKED -j ACCEPT
getenforce
#getenforce
Disabled
Selinux
#/usr/sbin/sestatus | grep SELinux
SELinux status: disabled
#sudo setsebool -P httpd_can_network_connect_db 1
setsebool: SELinux is disabled.
postgresql.conf
listen_addresses = '*'
port = 5432
pg_hba.conf (i just changed the ip for posting here) I tried using trust, but its still the same
# "local" is for Unix domain socket connections only
local all all password
# IPv4 local connections:
host all all 127.0.0.1/32 password
host all all 123.456.0.1/32 password
host all all 123.456.0.11/32 password
host all all 123.456.0.20/32 password
# IPv6 local connections:
host all all ::1/128 password
edit start: My PHP pg_connect code (I just changed the IP just for this post, but it points to the VPS IP)
$pgcon = pg_connect("dbname=database1 user=some_user password=some_password host=123.456.0.789");
edit end : ==
I would like to emphasize that BOTH servers can connect on each others and their own PostgreSQL database through other means, with the exception of my Local Server, it couldn't connect to VPS PostgreSQL using PHP.
VPS To Self(localhost connection)
VPS to Self via PHP is OK
VPS to Self via Python is OK
VPS to Self via PSQL(Terminal) is OK
VPS To Local Server(Connecting using Local Server's VPN IP)
VPS to Local Server via PHP is OK
VPS to Local Server via Python is OK
VPS to Local Server via PSQL(Terminal) is OK
Local Server to Self(localhost connection)
Local Server to Self via PHP is OK
Local Server to Self via Python is OK
Local Server to Self via PSQL(Terminal) is OK
Local Server to VPS(Connecting using VPS's IP by VPN and Public IP)
Local Server to VPS via Python is OK
Local Server to VPS via PSQL(Terminal) is OK
Local Server to VPS via PHP Is NOT OK
I'm really sorry for the long thread, I wanted to give as much information as I can.
Solution
Let's split error by types. I'd made several tests with your case and what I found:
If we mess with connection parameters (i.e. wrong port, or IP address) we get the error "could not connect to server: Connection refused" or "Operation timed out Is the server running on host "128.0.0.1" and accepting")
If we mess with credentials, we get the error "FATAL: password authentication failed for user ..."
Permission denied can be thrown only if you have not permissions to open a TCP connection from your PHP library.
Pls, double-check if you disable SElinux on your Local Server as for me, this is still the closest workaround for your problem.
Answered By - a13xg0 Answer Checked By - Mary Flores (WPSolving Volunteer)