16

I'm recently setting up and running a Windows 2008 r2 server with VMware Workstation 8.

I'm running a Centos 6.2 virtual linux box with Postgresql 9.1. Everything seems ok locally and I can connect to the CentOS box via a bridged network connection.

I have followed David Ghedini's walkthrough for setting up Postgresql 9.1. So I am able to start a putty session window, log in as root and start psql shell and issue statements as postgres user. My problem is that I cannot connect using pgAdmin III from my Windows 7 client. I have updated the /var/lib/pgsql/9.1/data/postgresql.conf to enable listen_addresses '*', etc.

Could anyone give me any tips on finding my fault. Have ensured Windows Firewall is disabled on the Windows server, too.

The pgAdmin III client reports;

'Server doesn't listen'

The server doesn't accept connections: the connection library reports

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

This is the pg_hba.conf file entry added:

#TYPE DATABASE   USER   ADDRESS          METHOD
host  all        all    192.168.1.0/24   trust

And the iptables file entries added:

-A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 192.168.1.26 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -s 192.168.1.26 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Jez
  • 404
  • 1
  • 6
  • 16
  • did you restart the postgresql service after updating the conf file? also, pg_hba.conf may be what's causing you troubles. [here's a copy that i use for my dev postgres instance](https://gist.github.com/2854761). it is **extremely** permissive but should work for dev for you. – swasheck Jun 23 '12 at 18:06
  • barring that, please post the error that is given by pgAdminIII – swasheck Jun 23 '12 at 18:10
  • Mmm.. damn, felt sure it must have been the `pg_hba.conf` host entry. I followed your synatax and added a host all all all trust entry, reloaded the conf file but still getting the 'Server doesn't listen' message. The wait for connect is beginning to haunt me! – Jez Jun 23 '12 at 18:21
  • D'OH! Then the problem is *most likely* resolved in AndrewSmith's second link. Check IPTables/firewall on the VM – swasheck Jun 23 '12 at 18:25
  • @MaRK I think I might be in the same situation. On my VMware hosted Windows client, I can't make any connection to PG through my Mac hosted localhost. I did all the things you have in your question/answer. But I was curious how you are actually testing the PG connection from Windows. Do you have PG installed on both your Windows and Mac clients? – Trip Jul 11 '14 at 15:13
  • @Trip - My setup is a physical Windows 7 client running pgAdmin, connecting to a VMware hosted Centos box running on a Windows 2008 R2 server. No Macs here! – Jez Jul 16 '14 at 13:53
  • Is it possible that access to one db might be allowed for a particular IP but access to another db on the same postgres server be denied for the same ip? I am facing such scenario. The error shown in this question - I see that for only one db on postgres server but am able to access another db on the same postgres server from my machine. – m.beginner May 22 '19 at 16:35

8 Answers8

13

You need to adjust pg_hba.conf:

http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Once you add your own host and corresponding user, restart postgresql server so hopefully you can connect.

Also you might need to enable it's port on the network: http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Andrew
  • 1,037
  • 9
  • 17
  • If it was the pg_hba.conf file, I think the user would be getting a different error message. – kgrittn Jun 24 '12 at 15:15
  • Andrew, I have followed the guides in the two links provided and have updated the OP to show these entries. I thought the iptables entries would provide the solution but unfortunately I am still unable to connect with pgAdmin III and receive the same error. – Jez Jun 24 '12 at 17:17
  • 1
    OK, I'm now connected! Whoohoo! Great stuff. I restarted the service and was then able to connect. Many thanks, I'm sure the only thing outstanding was the iptables config. Anyway, many thanks. – Jez Jun 24 '12 at 17:34
1

i have the same problem. I disabled all my third party antiviruses and firewalls and reinstall PostgreSQL again and its now working fine... :)

jeswin
  • 382
  • 4
  • 9
1

Make sure PostgreSQL Server is allowed through the firewall:

On the server machine, go to

Control Panel > System and Security > Windows Firewall > Allow an app through Windows Firewall

Scroll through Allowed apps and features, make sure 'PostgreSQL Server' is checked.

Bugalugs Nash
  • 492
  • 1
  • 6
  • 21
1

Probably a firewall issue, check it by

systemctl status firewalld.service

you could simply disable it by:

systemctl stop firewalld.service
systemctl disable firewalld.service
Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Lei Chi
  • 216
  • 1
  • 14
0

Is the server running on host "192.168.1.26" and accepting TCP/IP connections on port 5432?

Check that the server has that IP address. Check that there are postgres processes running. Use netstat to confirm that the server is listening on port 5432.

kgrittn
  • 18,113
  • 3
  • 39
  • 47
  • Yes, the server is definately on that IP as I can ssh to it and login. As originally stated I have also verified postgres is running by executing commands in the psql shell as postgres user. Interestingly when I run the netstat command I don't seem to see any mention of port 5432. – Jez Jun 24 '12 at 17:13
  • 2
    Log in with `psql` and type `show port;` and `show listen_addresses;`. Perhaps something has overridden the settings you think you're using, or you need to restart the server. Also, is there any chance that the VM is implementing some sort of firewall? – kgrittn Jun 24 '12 at 19:28
0

Adding to the great answers above, after implementing all the firewall rules and the configuration file changes, it still wasn't connecting to my host's IP address. Then I realized I had installed pgadmin locally on the host that my postgres db was installed on. I didn't need to use the external IP address for the host, I simply needed to connect to "localhost" in the pgadmin connection process. Extremely frustrating but made sense. So, if you

  • host pgadmin from the same server as your postgres database
  • have ruled out that the firewall is not in your way
  • have ruled out that any security groups are not blocking you
  • have fiddled enough with your configuration files pg_hba.conf and postgresql.conf

then maybe... just maybe, try connecting to "localhost" or "127.0.0.1".

it's worth a shot right!

texasdave
  • 506
  • 1
  • 4
  • 12
0

I had the same problem of yours:

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

Maybe you need to set your reversed ip to the whitelist:
26.1.168.192

igorkf
  • 3,159
  • 2
  • 22
  • 31
0

You should also allow port 5432 on the firewall.

example on centos 8:

sudo firewall-cmd --zone = public --add-port = 5432 / tcp --permanent
sɐunıɔןɐqɐp
  • 3,332
  • 15
  • 36
  • 40
Yustinus Letsoin Rumabar
  • 1