Golang and accessing postgres via a client in an Ubuntu VPS?

Question

I'm trying to follow the digital ocean tutorial on configuring pgadmin4 in server mode, but damn it is long, and I have to first configure apache server, python and virtualenv (via other 2 tutorials).

I don't want to install so many dependencies in my server just to access postgres via pgamin 4.

How do you guys do it?

I'm running a go webserver via https listening on ports 443 and redirecting 80 to 443

You can access a PostgreSQL database through a Go app, but pgAdmin is a python application, it has to be run by a python interpreter. Go is not going to help. — Adrian, Apr 16 '19 at 18:27
yes I already access it through go app. Ok for python but do I really need apache? — CommonSenseCode, Apr 16 '19 at 18:27
Apache is acting as the CGI host for Python. You need *something* to be a web server that can act as a CGI host. So, Apache, Nginx, etc. — Adrian, Apr 16 '19 at 18:29
@Adrian so I need to put nginx or apache in front of postgres and golang instead of serving directly with golang? — CommonSenseCode, Apr 16 '19 at 18:40
No, you don't need anything in front of Go. Your question appears to be about installing pgAdmin, which isn't a Go application. Maybe you can clarify what you're trying to do? — Adrian, Apr 16 '19 at 19:02
@Adrian, I have VPS with a golang webserver and a postgres db, I want a simple way to access postgres apart from ssh -> psql (CLI). Usually at my local env I use pgadmin 4 I want to do the same or something similar in my VPS (with go and postgres). Any tips appreciated I have been reading also of ssh tunnels might be a viable option? — CommonSenseCode, Apr 16 '19 at 19:05
If you want to access postgres directly from your PC, you either need to open Postgres' port or set up a tunnel or VPN or something - Postgres is not a web application, so Nginx/Apache won't help you. Go won't help you either unless you want to write your own Go version of pgAdmin. pgAdmin might help you but you'll need to run it behind an appropriate web server. — Adrian, Apr 16 '19 at 19:06
Docker is your friend. https://hub.docker.com/r/dpage/pgadmin4/ — Murtuza Z, Apr 17 '19 at 04:57

score 1 · Answer 1 · answered Apr 17 '19 at 00:22

Seeing your other answer I would like to offer a more secure alternative.

What's wrong with the current approach?

Your PostgreSQL instance is accessible from the internet. Generally you should try to limit access only where it is required. Especially if you are not using SSL to connect to PostgreSQL, an open port like this is a target for traffic interception and brute force attacks.

Alternative

Seeing that you are you using JetBrains IDE's you only need one other step to access your data - setting up a SSH tunnel.

This encrypts with SSH all your connections between development host and server without exposing PostgreSQL to the outside world.

In the connection settings for your database in the Jetbrains IDE select the SSH/SSL tab and "Use SSH tunnel". Input the information of your server and the SSH user + password/SSH key (use SSH keys for better security) into the relevant input fields.

Undo the settings changes you did to open the firewall and configure PostgreSQL to listen to all nodes.

Connections to your database are now possible over encrypted tunnels without exposing your database to any unwanted attacks.

I'll give it a look later thanks! How should the new line look like in `pg_hba.conf`? for letting it tunnel ssh connections? — CommonSenseCode, Apr 17 '19 at 05:00

score 0 · Answer 2 · answered Apr 16 '19 at 20:16

So this is what I did to achieve connection from my laptop to my ubuntu VPS, via webstorm (I suppose any intellij works also should work with other IDE's)

0 login to your server 1. Locate postgresql.conf usually under /etc/postgresql/10/main 2. sudo nano postgresql.conf 3. Locate and change line at connections

    listen_addresses = '*'

Then in same dir edit: sudo nano pg_hba.conf

#TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

Md5 means I connect with user and his password

5 Dont forget to allow ufw (firewall)

sudo ufw allow 5432/tcp

Open webstorm > Database (tab) > click + to add PostgtresSQL source (fill relevant info, user name, password, database name, host and port, etc...)
```
jdbc:postgresql://example.com:5432/my_database_name
```

Press on schemas and synchronize OR press:

Source > Settings > Schemas tab > [check] All Databases > refresh

Golang and accessing postgres via a client in an Ubuntu VPS?

2 Answers2