2

I've installed Postgresql 9.4 on Ubuntu Trusty from the PGDG ppa. I've created a database and set it listen-addresses to '*'. I've made an entry in the pg_hba.conf file. I can connect locally with no trouble. Here is the entry from my pg_hba.conf:

host all tarka 192.168.0.0/24 md5

The problem is that the port seems blocked by UFW. I've tried several variations of the ufw command to allow postgres such as

sudo ufw allow postgresql/tcp

sudo ufw allow 5432/tcp

and most recently

sudo ufw allow from 192.168.0.0/24 to any port 5432

I've restarted ufw each time.

This is the status currently:

sudo ufw status verbose
Status: active
Logging: on (low)
Default: allow (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
22                         ALLOW IN    Anywhere
5432                       ALLOW IN    192.168.0.0/24
22 (v6)                    ALLOW IN    Anywhere (v6)

The entries in iptables seem valid:

Chain ufw-user-input (1 references)
target     prot opt source               destination         
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:ssh
ACCEPT     udp  --  anywhere             anywhere             udp dpt:ssh
ACCEPT     tcp  --  192.168.0.0/24       anywhere             tcp dpt:postgresql
ACCEPT     udp  --  192.168.0.0/24       anywhere             udp dpt:postgresql

Never the less, when I try to connect from a remote machine, ufw logs:

Sep  2 13:55:28 estuary kernel: [242754.395342] [UFW BLOCK] IN=eth0 OUT= MAC=94:de:80:27:4a:7e:b4:75:0e:97:21:29:08:00 SRC=192.168.0.13 DST=192.168.0.12 LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=43525 DF PROTO=TCP SPT=36382 DPT=21 WINDOW=29200 RES=0x00 SYN URGP=0 

In fact I can't even connect by disabling ufw. In all cases nmap reports the port 5432 is closed:

nmap estuary -p5432

Starting Nmap 6.40 ( http://nmap.org ) at 2015-09-02 16:43 PDT
Nmap scan report for estuary (192.168.0.12)
Host is up (0.0059s latency).
PORT     STATE  SERVICE
5432/tcp closed postgresql

In addition, I'm running nginx as a web server and it is completely accessible from the other machine.

How can I get ufw (or whatever is actually doing it) to stop blocking port 5432?

Edit as requested:

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 estuary:domain          *:*                     LISTEN     
tcp        0      0 *:51413                 *:*                     LISTEN     
tcp        0      0 *:ssh                   *:*                     LISTEN     
tcp        0      0 localhost:ipp           *:*                     LISTEN     
tcp        0      0 localhost:postgresql    *:*                     LISTEN     
tcp        0      0 *:smtp                  *:*                     LISTEN     
tcp        0      0 localhost:6010          *:*                     LISTEN     
tcp        0      0 *:49152                 *:*                     LISTEN     
tcp        0      0 *:9091                  *:*                     LISTEN     
tcp        0      0 *:5900                  *:*                     LISTEN     
tcp        0      0 *:http                  *:*                     LISTEN     
tcp        0      0 *:http-alt              *:*                     LISTEN     
tcp        0      0 192.168.0.12:ssh        cutter:46943            ESTABLISHED
tcp        1      0 192.168.0.12:46461      104.28.7.98:http        CLOSE_WAIT 
tcp        1      0 192.168.0.12:59407      89.234.156.205:http     CLOSE_WAIT 
tcp        0      0 localhost:38145         localhost:6010          ESTABLISHED
tcp        1      1 192.168.0.12:59404      89.234.156.205:http     LAST_ACK   
tcp        0      0 localhost:6010          localhost:38144         ESTABLISHED
tcp        0      0 localhost:6010          localhost:38145         ESTABLISHED
tcp        1      0 192.168.0.12:45068      89.218.2.238.stati:http CLOSE_WAIT 
tcp        0      0 192.168.0.12:9091       cutter:46825            ESTABLISHED
tcp        0      0 localhost:38144         localhost:6010          ESTABLISHED
tcp6       0      0 [::]:51413              [::]:*                  LISTEN     
tcp6       0      0 [::]:ssh                [::]:*                  LISTEN     
tcp6       0      0 ip6-localhost:ipp       [::]:*                  LISTEN     
tcp6       0      0 [::]:smtp               [::]:*                  LISTEN     
tcp6       0      0 ip6-localhost:6010      [::]:*                  LISTEN     
tcp6       0      0 [::]:5900               [::]:*                  LISTEN     
tcp6       0      0 [::]:http               [::]:*                  LISTEN  

My client (cutter) connects by wireless.

10
  • 1
    In your ufw log blocked destination port is DPT=21 not 5432. Can you give as output from command netstat -at
    – 2707974
    Sep 3, 2015 at 6:12
  • Did you restart postgresql ? listen_adresses changes require restart... What error message does your distant client give you when you try to connect ?
    – alci
    Sep 3, 2015 at 8:16
  • 1
    Yes I certainly restarted Postgresql. I get the usual message about host not listening on IP/port. The issue seems to be the port is blocked on the server. See the latest edit. Sep 3, 2015 at 8:41
  • You're only listening on localhost (127.0.0.1). In your original question you mentioned the "listen_address" parameter. Should this be "listen_addresses"? Sep 6, 2015 at 1:52
  • Yes, it is plural. But it's also set to "*". What makes you say I'm only listening on localhost? Sep 6, 2015 at 4:11

1 Answer 1

3

Just noticed in your question you have 'listen-addresses' with a hyphen - the documentation has an underscore ('listen_addresses')

2
  • Man my eyes are getting bad. Sep 8, 2015 at 17:13
  • 2
    Do like I do and blame it on the font ;-) Sep 8, 2015 at 20:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .