Postgresql is not allowed to be connected remotely

3
votes

Could someone help take a look this weird problem? I'm still not able to connect remotely to my Postgresql.

My Steps:

  1. Download and install the latest Postgresql to my local machine
  2. Setup postgresql
  3. Create a DB
  4. Modify "pg_hba", add row "host all all 0.0.0.0/0 md5"
  5. Modify "postgresql.conf", make sure "listen_addresses = '*'"
  6. Restart postgresql service
  7. Open local PgAdmin, and connect to DB <-- Success!
  8. From Remote desktop, do the same thing as #7 <-- Failed!

Error Message:

"Server doesn't listen" "Could not connect to server......accepting TCP/IP connections on port 5432?"

  • I found "TCP 0.0.0.0:5432 Listening" when I type "netstat -a"
  • I checked firewall, it's not enabled ......

Can someone please help? Does anyone encounter this situation? P.S, my os is Winserver 2008

Thanks in advance~

1
windowspostgresql
you also need to open up firewall rules for port 5432. typically on Windows almost everything is firewalled by default – mvp
Is the dbms-server behind a NAT box? From inside the LAN you should be able to connect via the local ip address (eg 192.168.x.y) From the outside you'll need the WAN adress. Try to ping it first to see if you have the righ address. BTW: excellent detailed question! – joop
Can you telnet to port 5432 from the other server? – marceljg

1 Answers

1
votes

If you're connecting to the local machine via RDP then you'll be connecting via localhost and no firewall or LAN/WAN/NAT settings should affect pgadmin.

When you edit the pg_hba and postgresql.conf files Server 2008 doesn't usually let you edit them directly where they are. I usually copy them out edit them and then paste them back in. You'll need to authorise the paste from an Admin account.

I usually have a separate rule in "pg_hba" with "host all all 127.0.0.1/32 md5" for local connections. Also ensure when you restart the service that it is running under the user "postgres" and not as some other user.