Accessing postgresql server over network in Mac

Question

I have installed Postgres App on my Mac and have a database running.

I am able to connect it from the terminal by using the command psql -h localhost

Now I want to access this server from another machine which is on the same network.

When I do psql -h <hostname> -U <username> from the other machine, I get the error

psql: could not connect to server: Connection refused
    Is the server running on host "my hostname" (xx.xx.xx.xxx) and accepting
    TCP/IP connections on port 5432?

In the machine that runs the server I did lsof -i | grep LISTEN and I get the following result.

postgres  3196 sudarm    5u  IPv6 0x1caf6120      0t0  TCP localhost:5432 (LISTEN)
postgres  3196 sudarm    6u  IPv4 0x14678db0      0t0  TCP localhost:5432 (LISTEN)
postgres  3196 sudarm    7u  IPv6 0x1caf6a80      0t0  TCP localhost:5432 (LISTEN)

Do I have to do anything else to connect to the server from another machine?

Answer 1

The error message asks the right question: is the server accepting connections on port 5432? The lsof output you provided indicates that no, it is not. PostgreSQL is listening on localhost:5432, meaning it will only accept connections from the database server itself.

Open the server's postgresql.conf, set listen_addresses = '*', and restart PostgreSQL. It will then be listening for connections over all interfaces, thus accepting connections over the network.

From here, the next problem you're likely to run into is authentication. (PostgreSQL will accept connections, but you probably haven't told it what to do once it has a connection from across the network.) Ensure the server's pg_hba.conf has an entry matching your database/user/source address combination -- something like host all all 10.0.0.0/8 md5 is probably appropriate -- and reload PostgreSQL as necessary to apply changes.

The md5 part tells PostgreSQL to attempt authentication using a password, so you will also need to set a password on the user in question if you don't have one set already. From however you normally administer your database (e.g. psql template1 on the database server), say ALTER USER whomever WITH PASSWORD 'your_new_password'.