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?

• klin: What operating system the postgres server runs on?
• Dzmitry Savinkou: check you firewall settings, so port 5432 is open to accept connections
• jangorecki: 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.
• Eelke: 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.
• Reza Abbasi: follow this link: blog.bigbinary.com/2016/01/23/…

Answer 1

I resolved this issue using below options:

1. Whitelist your DB host from your network team to make sure you have access to remote host
2. Install postgreSQL version 4 or above
3. Run below command:

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

• Timo: I use ElephantSql and the following worked: psql -h <surus.db.elephantsql.com> -U <user>. 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.

Answer 2

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

• omerhakanbilici: for me, it also prompted for password without -W option

Answer 3

I figured it out.

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

• Ben: Is this a security concern or is this considered a normal practice?
• Abdus: @Ben Generally speaking, one should allow only specific IP addresses to connect to psql instance.
• Alexander Suraphel: @Ben you should use the firewall to limit access to your Postgres instance. The firewall inside Postgres is redundant IMO.

Answer 4

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

Answer 5

To remotely access the db in Postgresql, follow the below steps:

i) Edit the file postgresql.conf

sudo nano /etc/postgresql/{version}/main/postgresql.conf

Edit the postgresql.conf file

ii) Add the following line that will allow anyone to access your database.

listen_addresses = '*'

Added the line "listen_addresses = '*'"

Note: It's best practice to keep your database private but in multiple cases, you might need to make it accessible to other machines. Although you can also white list specific IP addresses instead of opening it to all (shown below)

iii) And then edit another file "pg_hba.conf" at the same location.

sudo nano /etc/postgresql/{version}/main/pg_hba.conf

And then add the following line:

// For IPv4

host all all 0.0.0.0/0 md5

// Whitelist a single or multiple IP addresses if you don't want all over the world people to acecss your DB

host all all 65.2.129.40/32 md5

// For IPv6

host all all ::0/0 md5

enter image description here

iv) Save it and restart the Postgresql DB

sudo systemctl restart postgresql

And then try connecting again to the database and you see that you are able to get into the database now from your local device and also from other devices. Successfully connected with the Postgres DB

And you are good with the connection!

• GP Van Eron: well explained answer. Thank you.

Answer 6

Step Wise below

1. 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.
2. Update Remote Server PSQL Configuration - Set listen_addresses = '*' in postgresql.conf file, path in general is /etc/postgresql/psql_version/main/postgresql.conf
3. 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.

Answer 7

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