Remote connection to postgres running on Ubuntu

0
votes

I'm struggling to enable remote connection on Postgres running on Ubuntu. I've done the following steps:

  1. Opened up the firewall; running sudo netstat -ntlp | grep LISTEN gives me: 
    tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      2947/postgres
  2. Added in listen_addresses to postgresql.conf: 
    listen_addresses='*'            # what IP address(es) to listen on;
                                # comma-separated list of addresses;
                                # defaults to 'localhost'; use '*' for all
  3. Added in the connection table in pg_hba.conf as follows: 
    # TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

I can connect locally but I cannot connect remotely; not even connecting via telnet works. It just says connection refused. Has anyone got an idea as to the cause of the problem?

Thanks.

Cheers,
Neil

1
postgresql
How exactly are you trying to connect? – Laurenz Albe

1 Answers

0
votes

  • Restart postgres
  • Check if it's listening on all interfaces 
    # netstat -lnp --tcp | grep postgres
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      6096/postgres

  • Add host line to pg_hba.conf: 
    host    all             all             0.0.0.0/0               md5

  • Add a password for your user 
    $ sudo -u postgres psql
postgres=# \password username