Accessing postgresql server over network in Mac
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'
.
Sudar
I am a developer from Chennai, India, mostly interested in WordPress, Android and Arduino programming. I write about my projects at my blog. You can also checkout my code that I have released as open source in my github account. You can follow me on twitter.
Updated on June 14, 2022Comments
-
Sudar almost 2 years
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 errorpsql: 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?
-
Craig Ringer over 11 yearsThere may also be a system firewall to deal with. It's common to need to add an exception on a per-port or per-process basis to allow incoming connections.
-
Sudar over 11 yearsI am using Postgres app. Do you know where I can find these config files? Also @CraigRinger I have disabled the firewall.
-
Craig Ringer over 11 years@Sudar
SHOW config_file;
andSHOW hba_file;
in psql is the easiest way when Pg is running. -
Sudar over 11 yearsThanks it solved it. And special thanks to @CraigRinger for help with the config variables.
-
mythicalcoder about 7 yearsI am stuck. Can you help me with this question plz ? stackoverflow.com/questions/43718604/…