23

I'm trying to setup a PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this?

I have installed the PostgreSQL database on Ubuntu using:

sudo apt-get install postgresql

In my /etc/postgresql/9.1/main/pg_hba.conf have I this line:

host    all    all    all    password

so it should accept connections from all IPv4-addresses and passwords should be sent in clear text (this is for development reasons).

If I run this command to see what services is running:

sudo netstat -tulpn

I can see these lines, that is showing that PostgreSQL is accepting connections on the default port:

tcp    0    0    127.0.0.1:5432    0.0.0.0:*    LISTEN
3561/postgres

When I try to connect to this PostgreSQL server from a remote computer on the same local network I get this error message:

Server doesn't listen

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

could not connect to server: Connection refused Is the server running on host "10.0.1.7" and accepting TCP/IP connections on port 5432?

I used postgres as username and no password. But I have also tried with postgres as password. On the local server I can login using:

sudo -u postgres psql postgres

How can I connect to a PostgreSQL database running on Ubuntu from a remote machine using pgAdmin3?

Michael Green
  • 24,839
  • 13
  • 51
  • 96
Jonas
  • 32,975
  • 27
  • 61
  • 64

1 Answers1

25

The line in your netstat report shows that the database is only listening on localhost:5432 (127.0.0.1) for incoming tcp connections.

Proto Recv-Q Send-Q Local Address   Foreign Address  State   PID/Program name
tcp        0      0 127.0.0.1:5432  0.0.0.0:*        LISTEN  3561/postgres

So it can only accept local tcp connections regardless of what permissions you've specified in pg_hba.conf. pg_hba.conf only specifies allowed connections, but does not specify what interfaces your service will listen to.

The addresses the server listens on is specified with the listen_addresses GUC in postgresql.conf. If you want the server to listen for remote connections you should specify the ip(s) you want it to listen on or * to listen on all available interfaces on the host.

To have your postgresql server listen on all interfaces on the host, you should have the following line in postgresql.conf:

listen_addresses = '*'
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
dbenhur
  • 805
  • 7
  • 8
  • but I have already specified all in the pg_hba.conffile... so it should accept connetions from all ip-addresses. – Jonas Jun 01 '12 at 22:10
  • Perhaps I could have said it more clearly. pg_hba doesn't specify what the database listens on, only what protocol/db/user/remote-addr connections are permitted. – dbenhur Jun 01 '12 at 22:12
  • Ok, so how do I fix this? do you have any suggestions? – Jonas Jun 01 '12 at 22:15
  • 1
    you fix it by putting a line listen_addresses = '*' in postgresql.conf and restarting your server. – dbenhur Jun 01 '12 at 22:16
  • Thanks, this solved this problem. Now I just got problems with the default password :( – Jonas Jun 01 '12 at 22:24
  • I have set a new password with sudo passwd postgres but I´m still unable to use it from pgAdmin3. – Jonas Jun 01 '12 at 22:37
  • I had to use sudo -u postgres psql postgres and then \password postgres as from this answer http://serverfault.com/a/248162/33300 to get it working, now it works! – Jonas Jun 01 '12 at 22:41
  • I have confirmed that my setup follow the instructions above. I still cannot connect from a Windows 10 machine running PgAdmin3 to a Ubuntu Server 14.04. Thoughts? – LandArch Jan 16 '16 at 15:51
  • @LandArch can you telnet to the port from the Win box to the database server? Perhaps there's some network layer restrictions going on. – dbenhur Jan 18 '16 at 23:44
  • @dbenhur I cannothis failed – LandArch Jan 18 '16 at 23:51
  • @LandArch look at your network. Some switch/router/firewall is preventing your win client from connecting to the ubuntu server. – dbenhur Feb 10 '16 at 22:28