PostgreSQL: Remotely connecting to Postgres instance using psql command

Question

I want to remotely connect to a Postgres instance. I know we can do this using the psql command passing the hostname

I tried the following:

psql -U postgres -p 5432 -h hostname

I modified the /etc/postgresql/9.3/main/pg_hba.conf file on the target machine to allow remote connections by default

I added the following line to the file

host all all source_ip/32 trust

I restarted the cluster using

pg_ctlcluster 9.2 mycluster stop
pg_ctlcluster 9.2 mycluster start

However, when I try to connect from the source_ip, I still get the error

Is the server running on host "" and accepting TCP/IP connections on port 5432?

What am I doing wrong here?

check you firewall settings, so port 5432 is open to accept connections — Dmitry S, Sep 28 '15 at 13:38
Assuming your client machine has `ssh` you can also try localhost-like connection to remote postgres simply by setting ssh tunnel: `ssh -L 5432:localhost:5432 your_server_ip` and connecting the same way as you would connect to localhost db. This can help debugging. — jangorecki, Sep 28 '15 at 13:43
By default postgresql only listens on localhost (and many distro's leave this default untouched) adjust the value of listen_addresses to '*' to make postgresql listen on all addresses your machine has. — Eelke, Sep 28 '15 at 16:28
@Dmitry : my postgresql is on an ec2 instance and I have configured the security group to allow connection on port 5432 — Hardik Kamdar, Sep 29 '15 at 11:55
@jangorecki : unable to connect using the command. It asks for public key authentication which fails — Hardik Kamdar, Sep 29 '15 at 12:01
@CraigRinger : the full message is psql: could not connect to server: connection refused Is the server running on host "" and accepting TCP/IP connections on port 5432? — Hardik Kamdar, Sep 29 '15 at 12:01
@Eelke : where am I add the entry that you have specified. are you saying that I should write host all all * trust in pg_hba.conf — Hardik Kamdar, Sep 29 '15 at 12:01
The error is 'connection refused' so pg_hba.conf is not involved. Most likely listen_addresses is bound to just localhost. — Craig Ringer, Sep 29 '15 at 12:08
@CraigRinger : where can I find the parameter listen_addresses? — Hardik Kamdar, Sep 29 '15 at 12:11
many thanks @CraigRinger and Eelke... you both saved my day.. setting listen_addresses='*' was the key — Hardik Kamdar, Sep 29 '15 at 12:26
follow this link: https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html — Reza Abbasi, Dec 28 '19 at 11:57

score 138 · Answer 1 · edited May 16 '22 at 08:52

138

I resolved this issue using below options:

Whitelist your DB host from your network team to make sure you have access to remote host
Install postgreSQL version 4 or above

Run below command:

psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>

edited May 16 '22 at 08:52

vvvvv

25,404
19
49
81

answered Jul 22 '20 at 10:43

Jagadish S

1,381
2
5
4

1

I use ElephantSql and the following worked: `psql -h -U `. The host can change in the free version, then I had to add the password, which is separated with colon from the user in the URL String. – Timo Jun 21 '22 at 09:33

score 57 · Answer 2 · edited Feb 14 '21 at 10:23

57

psql -h <IP_Address> -p <port_no> -d <database_name> -U <DB_username> -W

-W option will prompt for password. For example:

psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W

edited Feb 14 '21 at 10:23

William Miller

9,839
3
25
46

answered Sep 25 '20 at 16:35

devops-admin

1,447
1
15
26

for me, it also prompted for password without `-W` option – omerhakanbilici Apr 28 '23 at 11:37

score 24 · Accepted Answer · edited Dec 11 '18 at 11:59

24

I figured it out.

Had to set listen_addresses='*' in postgresql.conf to allow for incoming connections from any ip / all ip

edited Dec 11 '18 at 11:59

taras

6,566
10
39
50

answered Sep 29 '15 at 12:27

Hardik Kamdar

2,561
4
16
21

3

Is this a security concern or is this considered a normal practice? – Ben Feb 04 '19 at 11:55
3

@Ben Generally speaking, one should allow only specific IP addresses to connect to psql instance. – Abdus Apr 02 '20 at 17:13
1

@Ben you should use the firewall to limit access to your Postgres instance. The firewall inside Postgres is redundant IMO. – Alexander Suraphel Oct 10 '22 at 13:34

score 2 · Answer 4 · answered May 03 '23 at 13:50

2

If you want to use a postgres url you can just do something like this:

psql postgres://user:password@ip_add_or_domain:port/db_name

answered May 03 '23 at 13:50

aarona

35,986
41
138
186

score 1 · Answer 5 · answered Oct 26 '21 at 13:15

Step Wise below

Opening the Port - Make sure the PSQL Port is open to all remote connections or connections from a specific set of IPs as per your requirement. PSQL, in general, runs at port 5432, and it is configurable, so expose relevant Port accordingly.
Update Remote Server PSQL Configuration - Set listen_addresses = '*' in postgresql.conf file, path in general is /etc/postgresql/psql_version/main/postgresql.conf
Connect remotely - psql -U <db_username> -h <IP_address> - in case psql is running on a port other than 5432 on the remote server, specify port by adding -p <port_number>

A little plus below - In case the IP has been mapped to a domain name, you can connect by replacing <IP_address> with <host_name>. To do this, add a new connection rule in pg_hba.conf file

Note - All above explained can cause security issues - best practice always is to either keep your psql port closed, or only allow a list of IPs to connect through the port.

score 0 · Answer 6 · answered Nov 14 '21 at 21:32

0

Note that "ident" in pg_hba.conf requires a "ident server" to be running on the client.

answered Nov 14 '21 at 21:32

Richard T

4,570
5
37
49

PostgreSQL: Remotely connecting to Postgres instance using psql command

6 Answers6

Linked