CODEWITHSUNDEEP
postgresqlpsql
Hardik Kamdar
Hardik Kamdar

Reputation: 2981

PostgreSQL: Remotely connecting to Postgres instance using psql command

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?

Upvotes: 149

Views: 417633

Answers (7)

Desh Deepak Dhobi
Desh Deepak Dhobi

Reputation: 347

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!

Upvotes: 0

aarona
aarona

Reputation: 37253

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

Upvotes: 18

Jagadish S
Jagadish S

Reputation: 1741

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>

Upvotes: 174

Richard T
Richard T

Reputation: 4665

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

Upvotes: 0

Alok Mani
Alok Mani

Reputation: 124

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.

Upvotes: 3

devops-admin
devops-admin

Reputation: 1963

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

Upvotes: 69

Hardik Kamdar
Hardik Kamdar

Reputation: 2981

I figured it out.

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

Upvotes: 30

Related Questions