PostgreSQL remote connection through pgAdmin4

April 26, 2018 354 views
PostgreSQL Django DigitalOcean Ubuntu 16.04

Hey!

I recently started developing my droplet, and I'm quite new to this.
Im trying to establish a remote PostgreSQL connection through pgAdmin4.
The problem is, when I try to connect through pgAdmin4 by going through the Add new server and clicking Save button, it does nothing, sort of freezes.

My droplet is running on Ubuntu 16.04, I followed an instruction on how to set up PostgreSQL on ubuntu 16.04 on Django, installed all the necessary components, set up an environment, and a project, to the point where I even managed to create a db and a table.
I even edited the settings.py file:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresqlpsycopg2',
'NAME': '.....',
'USER': '....',
'PASSWORD': '....',
'HOST': '127.0.0.1',
'PORT': '5432',
I even added that to the end of the page:
STATIC
ROOT = os.path.join(BASE_DIR, 'static/')

And, ofcourse changed the ALLOWED HOSTS = ['....'] to with my droplet ip aswell.

Then I edited both postgresql.conf and pghba.conf aswell.
The listen
address is set to ' * ' and it is uncommented

The pg_hba.conf file:

local all postgres peer

TYPE DATABASE USER ADDRESS METHOD

"local" is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all all 0.0.0.0/0 md5

IPv6 local connections:

host all all ::1/128 md5

I also allowed port 5432 through my firewall.

Any ideas why I can't set up a connection with pgAdmin?

Thanks

1 Answer

I would strongly recommend against setting up your database server to listen publicly as it can open you up to potential attacks or attempts to compromise your server.

Rather than making PostgreSQL configuration changes you can use the SSH Tunnel option in pgadmin. With this type of a connection you would add your ssh connection details (user/pass or user/key) and pgadmin will make an ssh connection to your server and then connect to your database as "localhost".

Have another answer? Share your knowledge.