Remotely access postgresql database
Solution 1
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.
Solution 2
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
Solution 3
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).
Solution 4
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
Solution 5
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
Related videos on Youtube
Øyvind
Updated on September 18, 2022Comments
-
Øyvind over 1 year
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?
-
Jayesh almost 9 yearsFollow steps mentioned in javabypatel.blogspot.in/2015/07/… and change the port number present in postgresql.conf file. after changing port restart PostgreSQL server.
-
EAmez almost 5 yearsUrl 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)
-
-
Øyvind about 10 yearsOkey, 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
-
Admin about 10 yearsis the server listening? check with
netstat -nlt|grep :5432
-
Kaluã Bentes about 9 yearsI would insert the host row in a more strict way:
host <database> <user> <remote_client_IPaddress>/24 md5
-
Heather92065 over 7 yearsFor Postgresql version 9.5 you may need to restart the server before the listen_addresses will take effect.
-
Mike almost 7 yearsThis definitely did the trick. The above answer definitely did not work.
-
Peter Krauss about 6 yearsCheck correct paths at server by PSQL with
SHOW hba_file;
and restart with standardservice postgresql restart
-
Peter Krauss about 6 yearsPlease @Mike express what is correct:
host all all all md5
will work fine? It is correct? any security problem? -
Peter Krauss about 6 yearsWhen 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 instructions, from "To create a simple self-signed certificate..." first commands to "Finally, create a server certificate..." last commands. -
Mike about 6 years@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)
-
Olaini Lature almost 4 yearsThis is a nice and short summary of the above answers.
-
pcko1 over 2 yearsnothing else worked for me, other than that! thanks!