Allow access to Postgresql from any computer

Question

After researching GUI's for Postgresql I found the pgadmin 4 desktop client to be a valid choice.

I use an Ubuntu 16.04 server (droplet on Digital Ocean) and iptables firewall.

How do I allow access from remote computers? My computers with pgadmin installed have dynamic ip's.

Solution 1

I wanted to connect to my cloud hosted Postgresql database from remote desktop computers with a client such as pgadmin.

To achieve this I opened up port 5432 in iptables by the following commands:

iptables -I INPUT 1 -m tcp -p tcp --dport 5432 -j ACCEPT 
service iptables save 
service iptables restart  

I allowed access from any location in Postgresql like so:

nano /etc/postgresql/9.5/main/postgresql.conf

Edit listen_address like so:

listen_addresses = '*'

save and close nano editor and execute the postgresql restart command

service postgresql restart

Solution 2

Are you wanting to administer Postgresql from the local network or are you wanting to allow other users in the wide world to connect to your Postgresql server?

To allow yourself access to the server to administer it on the same subnet is very easy. To allow users in the wide world to access your server is unwise.

To allow remote access: You need to edit the pg_HBA.conf file. By default it only allows a user local to the server to connect to the database. This is a setup security thing.

The following snippet is from my own Postgresql server:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

This says that only allow the hosts that match 127.0.0.1/32 to access my server.

If you wish to allow all computers in your subnet to access your Postgresql server you may do something like the following:

# IPv4 local connections:
host    all             all             192.168.0.1/24            md5

The above example will get you going but it would be wise to go and briefly read the postgresql documentation for the pg_hba.conf file.

If you were particularly reckless and irrisponsible and enjoy the prospect of living dangerously and with regular intervals of fear and loathing you would use the following line in your pg_hba.conf file, although I seriously council against it.

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

This basically says "Anybody from anywhere can access my Postgresql server" I have provided it simply as an example of how careful you have to be.

The documentation for the current version of Postgresql 9.6 can be found here Previous versions are also available near that link but in the case of pg_hba.conf things do not appear to have changed between versions.