Remotely access postgresql database

Question

I need to access a postgresql database from a remote machine on a VPS at DigitalOcean running 12.10 and postgresql 9.1.

How do I do this? I noticed port 5432 is closed, how do I open this?

Follow steps mentioned in http://javabypatel.blogspot.in/2015/07/enable-remote-access-to-postgresql-database-server.html and change the port number present in postgresql.conf file. after changing port restart PostgreSQL server. — Jayesh, Aug 11 '15 at 03:49
Url posted by @Jayesh did the trick. Followed instructions and succesfully made one of my development computers connect to another (from Windows with pgAdmin4 to Ubuntu 18.04 postgresql 10.9) — EAmez, Jul 15 '19 at 10:46

score 100 · Accepted Answer · edited May 07 '18 at 23:48

100

To open the port 5432 edit your /etc/postgresql/9.1/main/postgresql.conf and change

listen_addresses='localhost'

to

listen_addresses='*'

and restart your DBMS

invoke-rc.d postgresql restart

now you can connect with

$ psql -h hostname -U username -d database

if you are unable to authentify yourself, then you need to give your user access rights to your database

Edit your

/etc/postgresql/9.1/main/pg_hba.conf

and add

host all all all md5

(This is for a wide open access. For stricter control, consult the pg_hba.conf documentation and adjust according to your needs).

Hereafter you need also a reload

invoke-rc.d postgresql reload

I don't need to mention that this is a basic configuration, now you should think about modify your firewall and improve the security of your DBMS.

edited May 07 '18 at 23:48

Daniel Vérité

1,163
7
10

answered Feb 19 '14 at 14:37

4

In particular, you should enable SSL. – Craig Ringer Feb 19 '14 at 14:38
Okey, I tried this, but when I try to connect using pgAdmin from my computer, I get "Server not listening". I added to iptables, and when I run iptables -L the following shows: ACCEPT tcp -- anywhere anywhere tcp dpt:postgresql When checking the IP and PORT on this site (http://www.yougetsignal.com/tools/open-ports/), it says the port is closed – Øyvind Feb 19 '14 at 14:46
3

is the server listening? check with `netstat -nlt|grep :5432` – Feb 19 '14 at 14:50
2

I would insert the host row in a more strict way: `host /24 md5` – gc5 Feb 25 '15 at 15:58
For Postgresql version 9.5 you may need to restart the server before the listen_addresses will take effect. – Heather92065 Nov 07 '16 at 19:00
Check correct paths at server by PSQL with `SHOW hba_file;` and restart with standard `service postgresql restart` – Peter Krauss May 07 '18 at 02:16
When using Ubuntu 16 LTS with PostgreSQL v10, I solved (thanks DanielVérité and @Kethryweryn!) with `host all all all md5` . Need also do all [ssl-tcp](https://www.postgresql.org/docs/current/static/ssl-tcp.html) instructions, from "To create a simple self-signed certificate..." first commands to "Finally, create a server certificate..." last commands. – Peter Krauss May 09 '18 at 15:55

score 30 · Answer 2 · edited Jun 12 '20 at 14:37

30

This does not work anymore, if it ever did :

host all all * md5

The correct possible lines for this are :

host all all 0.0.0.0/0 md5 #ipv4 range

host all all ::0/0 md5 #ipv6 range

host all all all md5 #all ip

Source

edited Jun 12 '20 at 14:37

Community

1

answered Sep 20 '16 at 10:34

Kethryweryn

401
4
3

4

This definitely did the trick. The above answer definitely did not work. – Mike Aug 06 '17 at 18:43
Please @Mike express what is correct: `host all all all md5` will work fine? It is correct? any security problem? – Peter Krauss May 07 '18 at 22:48
@peterkrauss Yes, host all all all md5 worked for me. Security problem? Of course it is, but for what I was doing it was just fine. (Internal network) – Mike May 14 '18 at 21:57

score 4 · Answer 3 · answered Apr 08 '19 at 12:21

The highest-voted and accepted answer has serious security-impolications. This method is disabled by default for good reasons.

Better use local port forwarding with ssh:

ssh -L local_port:localhost:foreign_port user@server

Start the port forwarding:

ssh -L 5432:localhost:5432 user@your-server.com
#or
ssh -L 5432:127.0.0.1:5432 user@your-server.com

(Change local and foreign ports to fit your configuration).

Then you can directly connect to the database from your local computer:

psql -U db_user -p local_port -l

nothing else worked for me, other than that! thanks! – pcko1 Oct 16 '21 at 23:46 — pcko1, Oct 16 '21 at 23:46

score 4 · Answer 4 · answered Jun 24 '15 at 19:23

4

For the message "server not listening", that happen to me was, that i don't erase of # on the archive postgresql.conf i mean:

#listen_addresses='localhost'

to:

listen_addresses='*'

(Sorry for my english).

answered Jun 24 '15 at 19:23

mrlinux

41
1

Ramesh Ponnusamy · Answer 5 · 2021-04-30T15:16:39.903

3

Following configuration, you need to set:

In /etc/postgresql/10/main/postgresql.conf

# Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;

In /etc/postgresql/10/main/pg_hba.conf

# IPv4 local connections:
host    all             all             0.0.0.0/0           md5

Restart your server:

sudo /etc/init.d/postgresql restart

edited Apr 30 '21 at 15:16

answered May 28 '20 at 14:54

Ramesh Ponnusamy

391
2
5

1

This is a nice and short summary of the above answers. – zx485 May 28 '20 at 22:03

score 1 · Answer 6 · answered May 05 '21 at 00:13

1

To open your port 5432, you need to run this command

sudo ufw allow 5432/tcp

answered May 05 '21 at 00:13

ysrtymz

11
2

Remotely access postgresql database

6 Answers6