2

I'm trying to connect to my Postgres instance on a local IP, which I can connect to with no problem using the psql -h localhost command. However, which trying to run via rails, I get this: 

$ rails c production
/home/avishai/.rvm/gems/ruby-1.9.3-p125/gems/activerecord-3.2.9/lib/active_record/connection_adapters/postgresql_adapter.rb:1208:in `initialize': could not connect to server: Connection refused (PG::Error)
    Is the server running on host "10.61.99.194" and accepting
    TCP/IP connections on port 5432?

Here's my /etc/postgresql/9.1/main/pg_hba.conf:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             10.80.85.130/32         trust
host    all             all             10.80.85.130/32         md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

How do I make sure that Postgres can accept incoming connections from localhost as well as on select internal IPs?

Adam
  • 103
  • 3
Avishai
  • 123
  • 1
  • 4

2 Answers2

4

Please, try to check this two points:

1) In the file /etc/postgresql/9.1/main/postgresql.conf you have to have this line:

listen_addresses = '*'

instead of

listen_addresses = '127.0.0.1'

You can check it via command netstat -tpln | grep 5432. You will see in the output somethink like:

tcp  0  0  0.0.0.0:5432  0.0.0.0:* LISTEN 1150/postgres

If the first IP address is 0.0.0.0, postgres is listening on the all interfaces, which is, what you want. If you will see in the first IP address field only 127.0.0.1, postgresql is listening only on the localhost interface.

2) Try to check your firewall rules via iptables -L -n. In the INPUT chain there must be rule accepting connection to port 5432 from IP adress of server, where are running rails. You can add this rule via command: iptables -I INPUT -p tcp -s 10.80.85.130 --dport 5432 -j ACCEPT

But I think, that problem will be in the point 1).

Jan Marek
  • 2,180
  • 1
  • 13
  • 14
  • Great call on `listen_addresses = '*'`! I opened it up and added the firewall rule and now things seem to be working quite nicely. – Avishai Dec 28 '12 at 01:58
  • 1
    `listen_addresses = '127.0.0.1'` is the default setting in the Ubuntu server. It's more secure and if you would play only on the localhost, it is enough... I can advice you to remove from your `pg_hba.conf` file line: `host all all 10.80.85.130/32 trust`. Line, which ended with `md5` is sufficient. – Jan Marek Dec 28 '12 at 09:49
0

I had that same trouble.

In my case, it was using the IPV6 Localhost for some reason.

Try this in your pg_hba.conf:

host    all             all             ::1/128                 md5
Jimbob
  • 11
  • 2