Cannot connect to postgres from remote host

Question

I have a database server (192.168.1.50) running postgres. I have created a database named "testdb" and a user "testuser" with password "testuserpw".
Locally, I can connect to the db using:

psql -d testdb -U testuser

When I issue the command from another host (192.168.1.60):

psql -h 192.168.1.50 -d testdb -U testuser

I have the error:

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

Any idea ?

araqnid · Accepted Answer · 2014-10-08 16:49:17Z

66

Check the setting of listen_addresses in your postgresql.conf file. Many distributions make it default to 127.0.0.1, i.e. listen only to connections coming in from localhost. It should be set to '*' to listen for connections on all interfaces.

If you are still having trouble, use lsof to see what network sockets the postgres process is listening on.

edited Oct 8, 2014 at 16:49

answered May 27, 2011 at 10:11

araqnid

135k25 gold badges163 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Travis Stevens Over a year ago

As a note, there is also a pg_hba.conf config file in which one can configure trust properties for specific machines.

Shah Muzaffar Over a year ago

For Postgres 9 the setting file will be in /var/lib/postgres/*. But for Postgres 11, this file can be found in /etc/postgresql/11/main/

Ahmed Ashour · Accepted Answer · 2023-07-02 09:12:39Z

6

On Ubuntu, I noticed that remote access at some point stopped working (currently using 9.1.9). The reason is, that postgres is no longer started with the -i switch [1] so no matter what you configure for listen_addresses, it will be ignored.

Fortunately, adding the following line to /etc/environment solves the problem after logging out and in again (or reboot):

PGOPTIONS="-i"

See [2] for more options. Note, that adding this to /etc/postgresql/9.1/main/environment did NOT work for me.

Now, when doing nmap ip-of-my-remote-server I finally get this again:

5432/tcp open  postgresql

Yay!

[1] http://www.postgresql.org/docs/9.1/static/runtime-config-short.html

[2] http://www.postgresql.org/docs/9.1/static/libpq-envars.html

edited Jul 2, 2023 at 9:12

Ahmed Ashour

5,66310 gold badges41 silver badges63 bronze badges

answered Apr 5, 2013 at 23:30

raven_arkadon

4001 gold badge5 silver badges12 bronze badges

2 Comments

Rich Apodaca Over a year ago

I'm also running 9.1.9 on Ubuntu 13.04 and this step doesn't seem to be necessary.

mdpatrick Over a year ago

I found I had to replace the double quotes with single quotes and it worked in the postgresql specific environment file just fine. Also, you have to actually use 'stop' and 'start' (as opposed to a simple reload) when you execute /etc/init.d/postgresql after making this change.

Miki · Accepted Answer · 2011-05-27 10:10:28Z

4

Is the firewall letting the connections through? Or, check if pg_hba.conf allows connecting from addresses other than localhost.

answered May 27, 2011 at 10:10

Miki

7,2382 gold badges34 silver badges42 bronze badges

Comments

ikraduya edian · Accepted Answer · 2019-03-09 15:10:46Z

2

I came across the same problem as yours, and my source of problem is the firewall settings. If you're using Ubuntu, print your firewall status: sudo ufw status verbose

It may looks like this:

Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
...

The default rule of incoming connection is "deny", so you need to specify the "allow"ed port.

type: sudo ufw allow 5432/tcp

reference: https://www.vultr.com/docs/how-to-configure-ufw-firewall-on-ubuntu-14-04

answered Mar 9, 2019 at 15:10

ikraduya edian

801 silver badge8 bronze badges

1 Comment

DevolamiTech Over a year ago

Thanks for this. I wasn't even thinking in this direction.

Ahmed Ashour · Accepted Answer · 2023-07-02 09:11:00Z

1

The listen_address configvar in postgresql.conf is not the only way to get postgres to listen on the non-local IP-address (or addresses).

Use option "-o -h *" if you start postgres from pg_ctl, otherwise do add "-h" "*" to the postgres command line, like e.g.

/usr/local/pgsql/bin/postgres -D /pg/data "-h" "*"

Of course /pg/data must be changed to your current datapath.

This is especially useful when experimenting.

edited Jul 2, 2023 at 9:11

Ahmed Ashour

5,66310 gold badges41 silver badges63 bronze badges

answered Sep 22, 2013 at 14:49

Donald J. Axel

112 bronze badges

Comments

Ferhat Özdogan · Accepted Answer · 2023-09-26 09:55:06Z

1

I had the same problem with my Ubuntu 22.04, PostGreSQL 16 setup and got stuck for quite a while. Besides the other answers that concern the *.conf-files, I also had to configure the firewall with the commands below:

root# firewall-cmd --zone=public --add-port=5432/tcp --permanent
success
root# firewall-cmd --reload
success

I found this on this webpage: https://www.project-open.com/en/howto-postgresql-port-secure-remote-access

I hope it helps and wish you (and everybody else concerned) 'good luck'!

answered Sep 26, 2023 at 9:55

Ferhat Özdogan

795 bronze badges

Comments

Erick Amoedo · Accepted Answer · 2020-04-10 19:21:32Z

0

Connection refused (0x0000274D/10061) i fixed here with:

Open the terminal and type:

VIM /var/lib/pgsql/data/postgresql.conf

Edit the "listen_adresses", it should be set to '*'

After this, rest it on terminal:

/scripts/restartsrv_postgres

answered Apr 10, 2020 at 19:21

Erick Amoedo

4831 gold badge4 silver badges10 bronze badges

Collectives™ on Stack Overflow

Cannot connect to postgres from remote host

7 Answers 7

2 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related