Setup secure database access with SSH Tunnel
source link: https://dzlab.github.io/gcp/2022/01/16/gcp-sshtunnel/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Setup secure database access with SSH Tunnel
16 Jan 2022 by dzlab
This article walks through how to setup a VM on GCP, a Postgres database and SSH tunneling from local machine to the database on the remove VM.
Setup VM
Create a new instance with Google Cloud CLI, a machine type n1-standard-2 is enough but you can try other types like n1-highmem-4 or n1-highcpu-4.
$ gcloud compute instances create databases --machine-type n1-standard-2 --zone us-central1-a
From the Google Console, Create a Firewall rule that will enable remote access to Postgres port.
Once the VM is running, connect to it via SSH from Google Console and then create a user (e.g. myadmin with password mypass).
$ sudo adduser myadmin
Adding user `myadmin' ...
Adding new group `myadmin' (1002) ...
Adding new user `myadmin' (1001) with group `myadmin' ...
Creating home directory `/home/myadmin' ...
Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
Changing the user information for myadmin
Enter the new value, or press ENTER for the default
Full Name []:
Room Number []:
Work Phone []:
Home Phone []:
Other []:
Is the information correct? [Y/n]
For the user to be able to connect via SSH either manually upload a public key of this user or enabling connection with password by setting PasswordAuthentication to yes in /etc/ssh/sshd_config:
$ sudo vi /etc/ssh/sshd_config
PasswordAuthentication yes
$ sudo systemctl restart sshd
You can test the connection establishment from local machine with user myadmin with password mypass
$ ssh myadmin@external-ip
myadmin@external-ip's password:
Setup Postgres
First, install Postgres with apt-get
user@databases:~$ sudo apt-get -y install postgresql
user@databases:~$ sudo pg_ctlcluster 11 main start
user@databases:~$ sudo -u postgres psql -c "SELECT version();"
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.14 (Debian 11.14-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
After installing postgres an admin user postgres should be created, you can confirm with the following
user@databases:~$ sudo su - postgres
postgres@databases:~$ psql
psql (11.14 (Debian 11.14-0+deb10u1))
Type "help" for help.
postgres=# \q
postgres@databases:~$ exit
logout
We should not be using this admin user but instead create another user and give configure its privilege. For instance, create a user with admin privilege on a specific databse
user@databases:~$ sudo su - postgres -c "createuser myadmin"
user@databases:~$ sudo su - postgres -c "createdb database01"
user@databases:~$ sudo -u postgres psql
psql (11.14 (Debian 11.14-0+deb10u1))
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE database01 TO myadmin;
GRANT
postgres=# ALTER USER myadmin WITH PASSWORD 'mypass';
ALTER ROLE
We need to allow new user to authenticate to Postgres with a password by editing pg_hba.conf
user@databases:~$ sudo cat 'local all myadmin md5' >> /etc/postgresql/11/main/pg_hba.conf
sudo service postgresql restart
user@databases:~$ psql --port=5432 --username=myadmin --dbname=database01 --password
Password:
psql (11.14 (Debian 11.14-0+deb10u1))
Type "help" for help.
database01=> \q
Now we need to make Postgres accept connections from local machine, and also optionally from remote ones. The former, can be done by adding listen_addresses = '*' to postgresql.conf then restarting the service:
$ sudo vi /etc/postgresql/11/main/postgresql.conf
$ sudo /etc/init.d/postgresql restart
[ ok ] Restarting postgresql (via systemctl): postgresql.service.
We also edit pg_hba.conf file
$ sudo vi /etc/postgresql/11/main/pg_hba.conf
Add host all all 0.0.0.0/0 md5 to pg_hba.conf to allow access to all databases for all users with an encrypted password:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 0.0.0.0/0 md5
After that restart service with service postgresql restart
SSH Tunneling
Before anything check that the VM is reachble on the external IP and the SSH port accessible
$ nc -zv external-ip 22
Connection to external-ip port 22 [tcp/ssh] succeeded!
From local machine establish tunnel with user myadmin and password mypass
$ ssh -L 63333:localhost:5432 myadmin@external-ip -N
myadmin@external-ip's password:
Connect to the database from a different shell tab with user myadmin with password mypass
$ psql -h localhost -p 63333 --username=myadmin --dbname=database01 --password
Password:
psql (14.0, server 11.14 (Debian 11.14-0+deb10u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
database01=> \q
Note: how we are connecting to the database using ‘localhost’ as if the database is running locally thanks to the established SSH Tunnel.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK