postgres-server setup

# based on digital ocean for 18.04

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

# make a new non-postgres user for the system without switching roles
$ sudo -u postgres createuser --interactive
$ sudo -u postgres createdb <your-user-name>

# setup a db password for user 
$ psql
#= \password <name-of-user>

######
# setup an SSH tunnel for client connections... from:
# https://www.digitalocean.com/community/tutorials/how-to-set-up-ssh-tunneling-on-a-vps
# https://www.digitalocean.com/community/questions/how-do-you-remote-connect-with-pgadmin-4-to-a-postgresql-on-a-droplet
1. On local machine 
$ ssh -L 65432:localhost:5432 <username>@<remote_ip> 

this should log you in... leave the terminal open to continue to use the tunnel (its running in foreground) 
you can see open tunnels:
$ sudo lsof -i -n | egrep '\<ssh\>'

# try ssh connection from local machine (NOTE: this assumes you have the psql client running locally)
$ psql -h localhost -p 65432 -U <username> <database-name>

### Using tunneling with pgadmin4 is weird to set up...
Under connection tab:  --> this is the tunneled connection
  hostname: localhost 
  port: 5432
  username: <username>
  password: <password>

Under ssh tunnel:  --> this configures the tunnel (it is closed automatically when disconnected)
  tunnel_host: <remote_ip>
  tunnel_port: 22
  username: <username>
  identity_file: <path to id_rsa (privkey)>

#NOTE If there is a paramiko module error, that means that you need to update python-bcrypt
# from: https://stackoverflow.com/questions/52012853/pgadmin4-error-module-paramiko-has-no-attribute-ed25519key
sudo apt install python3-bcrypt
wget https://launchpad.net/ubuntu/+archive/primary/+files/python3-paramiko_2.4.1-0ubuntu2_all.deb
sudo dpkg -i python3-paramiko_2.4.1-0ubuntu2_all.deb
-- followed by restarting pgadmin4

## Configuration for remote access: https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-against-automated-attacks 
# If the database is going to be used by webapps etc. via tcp you need to modify 
# two files and restart the service to allow remote access

1. Update pg_hba.conf on the db-server -- This acts as a firewall 
$ sudo nano /etc/postgresql/10/main/pg_hba.conf
# add a line at the bottom... database and username can be 'all' and ip can be 0.0.0.0:/32 if you want 
host  <database>  <username>   <client_ip_address>/cidr   md5

2. Update postgresql.conf 
$ sudo nano /etc/postgresql/10/main/postgresql.conf 
# add this line under listen addresses... can be listen_addresses = '*' to allow all ethernet or just use
# individual ips. 
listen_addresses = 'localhost, <server_ip_address>'

3. restart service and make sure its ok
$ sudo systemctl restart postgresql

4. Open port 5432 on the machine as seen fit via ufw...