current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

I am facing error while connecting to the PostgreSQL server from different host, I configured both postgresql.conf and pg_hba.conf but unfortunately with no result.

So I have installed PostgreSQL on Debian from package, added user with createuser and useradd, then created db via createdb as my custom user as an owner. Afterwards I have set postgresql.conf with these values

listen_addresses = '*'
ssl = false

And added this line in pg_hba.conf 

host    all      all     192.168.0.0/24  md5

Then I have restarted service and tried to connect from my machine via psql -U admin -d admin -h 10.10.30.88and I am getting this error

psql: FATAL:  no pg_hba.conf entry for host "192.168.10.195", user "admin", database "admin", SSL off

So have I forgotten about something?

Whole pg_hba.conf

local   all             postgres                                peer

local   all             all                                     peer
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all     all     192.168.0.0/24  md5
host    all     all     10.10.0.0/24    md5

ipconfig PostgreSQL server

eth0      Link encap:Ethernet  HWaddr 52:54:00:00:00:03  
      inet addr:10.10.30.88  Bcast:10.10.30.255  Mask:255.255.255.0
      inet6 addr: fe80::5054:ff:fe00:3/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:4001 errors:0 dropped:0 overruns:0 frame:0
      TX packets:2297 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000 
      RX bytes:365126 (356.5 KiB)  TX bytes:306782 (299.5 KiB)

ipconfig from other VM in same network

eth0      Link encap:Ethernet  HWaddr 52:54:00:00:00:15  
      inet addr:10.10.30.83  Bcast:10.10.30.255  Mask:255.255.255.0
      inet6 addr: fe80::5054:ff:fe00:15/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:144749 errors:0 dropped:0 overruns:0 frame:0
      TX packets:18950 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000 
      RX bytes:16160192 (15.4 MiB)  TX bytes:1323052 (1.2 MiB)

ipconfig from my machine

eth0      Link encap:Ethernet  HWaddr c4:7d:46:11:5c:ab  
      inet addr:192.168.10.195  Bcast:192.168.10.255  Mask:255.255.255.0
      inet6 addr: fe80::c67d:46ff:fe11:5cab/64 Scope:Link
      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
      RX packets:198498 errors:0 dropped:0 overruns:0 frame:0
      TX packets:78205 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000 
      RX bytes:168115954 (168.1 MB)  TX bytes:10312744 (10.3 MB)
      Interrupt:20 Memory:f0600000-f0620000

I have my own KVM as well (not running)

virbr0    Link encap:Ethernet  HWaddr 2e:e4:de:95:07:1b  
      inet addr:192.168.122.1  Bcast:192.168.122.255  Mask:255.255.255.0
      UP BROADCAST MULTICAST  MTU:1500  Metric:1
      RX packets:0 errors:0 dropped:0 overruns:0 frame:0
      TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:0 
      RX bytes:0 (0.0 B)  TX bytes:0 (0.0 B)

Wifi is not enabled

wlan0     Link encap:Ethernet  HWaddr ac:fd:ce:b3:dc:e7  
      inet6 addr: fe80::aefd:ceff:feb3:dce7/64 Scope:Link
      UP BROADCAST MULTICAST  MTU:1500  Metric:1
      RX packets:32251 errors:0 dropped:0 overruns:0 frame:0
      TX packets:159 errors:0 dropped:0 overruns:0 carrier:0
      collisions:0 txqueuelen:1000 
      RX bytes:3268413 (3.2 MB)  TX bytes:21321 (21.3 KB)
share|improve this question
    
192.168.10.195 - it's in the error log. – Petr Mensik yesterday
    
Show us the whole pg_hba.conf file. But you are trying to connect to -h 10.10.30.88. Not to a 192.168.x.x address. – ypercubeᵀᴹ yesterday
    
Yes, I am connecting to the virtual machine which runs on the 192.168.x.x - I can SSH to it so I thought it doesn't matter from the connection perspective. – Petr Mensik yesterday
    
Btw result is the same when I try to connect from 10.10.x.x machine. – Petr Mensik yesterday
    
OK. What does ifconfig shows (in the machine where the Postgres server runs)? And what does it show on the machine you are trying to connect from (it should be 192.168.10.195 and perhaps some more)? – ypercubeᵀᴹ yesterday
up vote 5 down vote accepted

The issue is probably due to this line:

host    all     all   192.168.0.0/24  md5

This (the 24) basically tells Postgres to allow connections from any machine that has IP of the form: 192.168.0.x. So, your 192.168.10.195 does not match the criteria. You can replace it with:

host    all     all   192.168.0.0/16  md5     -- any 192.168.x.x

or

host    all     all   192.168.10.0/24  md5    -- any 192.168.10.x

or 

host    all     all   192.168.10.195/32  md5  -- only 192.168.10.195

depending on what range of IPs you want to allow.

share|improve this answer
    
Great, that seems to work! I really don't know how could I have overlooked this :) – Petr Mensik yesterday
    
Did it work? You may want to try altering the 10.10.0.0./24 instead (to 10.10.0.0./16 or 10.10.30.0/24 or ..). I'm not sure you've shown the whole ifconfig outputs. Your comment says that the VM runs in 192.168.x.x but the output shows 10.10.x.x – ypercubeᵀᴹ yesterday
    
I meant the host OS which has the KVM installed, VMs itself are in 10.10.x.x. It works now so great :) – Petr Mensik yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.