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?
-
1Follow steps mentioned in javabypatel.blogspot.in/2015/07/… and change the port number present in postgresql.conf file. after changing port restart PostgreSQL server.– JayeshAug 11, 2015 at 3: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)– EAmezJul 15, 2019 at 10:46
Add a comment
|
6 Answers
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.
-
4
-
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 (yougetsignal.com/tools/open-ports), it says the port is closed– ØyvindFeb 19, 2014 at 14:46
-
3is the server listening? check with
netstat -nlt|grep :5432– user224465Feb 19, 2014 at 14:50 -
2I would insert the host row in a more strict way:
host <database> <user> <remote_client_IPaddress>/24 md5– gc5Feb 25, 2015 at 15:58 -
For Postgresql version 9.5 you may need to restart the server before the listen_addresses will take effect. Nov 7, 2016 at 19:00
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
-
4This definitely did the trick. The above answer definitely did not work.– MikeAug 6, 2017 at 18:43
-
Please @Mike express what is correct:
host all all all md5will work fine? It is correct? any security problem? May 7, 2018 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)– MikeMay 14, 2018 at 21:57
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).
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 [email protected]
#or
ssh -L 5432:127.0.0.1:5432 [email protected]
(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
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
-
1
To open your port 5432, you need to run this command
sudo ufw allow 5432/tcp
