-
-
Save fininhors/067f93882794e2013f2c62772e961d71 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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... | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment