Teleport 6.0 : Database Access Made Easy!!

Photo by Markus Spiske on Unsplash

Teleport is an open source, identity-aware, access proxy with an integrated certificate authority. People have been using teleport for ssh-access, Kubernetes clusters and with Teleport 6.0 you get Database access as well (Postgress and MySQL).

In this tutorial, I will show you how you can do it all from scratch for a self-hosted MySQL Database(I will show the database install as well).

Prerequisites: 2 Ubuntu 20.04 instances with sudo access.

I have 2 machines called teleport and database

Complete setup

Step1: Login to the teleport instance and install teleport

curl https://deb.releases.teleport.dev/teleport-pubkey.asc | sudo apt-key add -
add-apt-repository 'deb https://deb.releases.teleport.dev/ stable main'
apt-get update
apt install teleport

Step2: Configure Teleport server

export IP={ip for the instance}
#in my case 
export IP=212.2.240.196

teleport configure --acme --acme-email=saiyam911@gmail.com --cluster-name=magic-$IP.nip.io --output="/etc/teleport.yaml"
Wrote config to file "/etc/teleport.yaml". Now you can start the server. Happy Teleporting!

Step3: Start teleport and add a user

systemctl start teleport
systemctl enable teleport
systemctl status teleport

tctl users add teleport-admin --roles=admin --logins=root

User "teleport-admin" has been created but requires a password. Share this URL with the user to complete user setup, link is valid for 1h:

https://magic-212.2.240.196.nip.io:443/web/invite/b39e7b128d3243c6c10407b06d27be26

NOTE: Make sure magic-212.2.240.196.nip.io:443 points at a Teleport proxy which users can access.

Go to the link to set up 2FA

You get the Web-UI Access

Step4: Install Mysql database on the database instance

apt-get update

sudo apt install mysql-server -y

Step5: Add MySQL address in the proxy service of teleport.yaml on the teleport instance

vi /etc/teleport.yaml

#Add below in bold
proxy_service:
  enabled: "yes"
  listen_addr: 0.0.0.0:3023
  web_listen_addr: :443
  public_addr: magic-212.2.240.196.nip.io:443
  mysql_listen_addr: 0.0.0.0:3036

Step6: certificate Key/pair creation

Below has to be run on teleport instance

# Export Teleport's certificate authority and generate certificate/key pair
# for host db.example.com with a one year validity period.
**tctl auth sign --format=db --host=localhost --out=server --ttl=8760h**

The credentials have been written to server.key, server.crt, server.cas

Copy over the files generated to the database instance

apt install sshpass
sshpass -p 'password' scp server.key server.crt server.cas user@{IP}:/etc/mysql/ssl

above will copy the files in /ssl directory of the database instance

cd /etc/mysql/ssl
ls | grep server
server.cas
server.crt
server.key

Step7: Configure MySQL on the database instance

cd /etc/mysql/
vi mysql.cnf

add below 

[mysqld]
require_secure_transport=ON
ssl-ca=/path/to/server.cas
ssl-cert=/path/to/server.crt
ssl-key=/path/to/server.key

#in my case it is 

[mysqld]
require_secure_transport=ON
ssl-ca=/etc/mysql/ssl/server.cas
ssl-cert=/etc/mysql/ssl/server.crt
ssl-key=/etc/mysql/ssl/server.key

Change ownership

chown -R mysql:mysql /etc/mysql/ssl/

Create User alice and configure with certificate

mysql> CREATE USER 'alice'@'%' REQUIRE X509;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'alice'@'%' REQUIRE X509;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON `%`.* TO 'alice'@'%';
Query OK, 0 rows affected (0.00 sec)

Step8: Create the token for database service to join (below command should run on teleport instance)

**tctl tokens add \
--type=db  \
--db-name=mysql \     
--db-protocol=mysql \     
--db-uri=localhost:3306**

The invite token: 30b936383ccd1d117d9315770c08a63c.
This token will expire in 60 minutes.

Fill out and run this command on a node to start proxying the database:

> teleport start \
   --roles=db \
   --token=30b936383ccd1d117d9315770c08a63c \
   --ca-pin=sha256:0cff619a5e66cb079cbb6110ec884424e5a5d47f16e13b8855344889568b5770 \
   --auth-server=magic-212.2.240.196.nip.io:443 \
   --db-name=mysql \
   --db-protocol=mysql \
   --db-uri=localhost:3306

You can either use the above token and create a config file on the database instance for the database service using just the token (https://goteleport.com/docs/ver/6.1/database-access/guides/mysql-self-hosted/#start-database-service-with-config-file)

OR you can just use the output of the above command and run it on the database instance.

Step9: Install teleport on Database instance and then run the output from the above command

curl https://deb.releases.teleport.dev/teleport-pubkey.asc | sudo apt-key add -
add-apt-repository 'deb https://deb.releases.teleport.dev/ stable main'
apt-get update
apt install teleport

Run the command to start database service

teleport start \
>    --roles=db \
>    --token=30b936383ccd1d117d9315770c08a63c \
>    --ca-pin=sha256:0cff619a5e66cb079cbb6110ec884424e5a5d47f16e13b8855344889568b5770 \
>    --auth-server=magic-212.2.240.196.nip.io:443 \
>    --db-name=mysql \
>    --db-protocol=mysql \
>    --db-uri=localhost:3306
INFO [PROC:1]    Connecting to the cluster magic-212.2.240.196.nip.io with TLS client certificate. service/connect.go:128
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log/upload. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log/upload/sessions. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log/upload/sessions/default. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log/upload. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log/upload/streaming. service/service.go:1895
INFO [AUDIT:1]   Creating directory /var/lib/teleport/log/upload/streaming/default. service/service.go:1895
INFO [DB:SERVIC] Database service has successfully started: [DatabaseServer(Name=mysql, Version=6.0.2, Labels=map[])]. service/db.go:205

Step10: Login to MySQL instance from Teleport:

Start the terminal session after login into the teleport server from the UI. You can also use tsh login on the teleport instance to login as a particular user.

It will open a terminal session

Now let’s try to access the MySQL database running on the database instance :

root@teleport-9cd889ac:~# **tsh db ls**
Name       Description Labels Connect
---------- ----------- ------ ------------------------------------------------------------------------------------------------------
mysql

root@teleport-9cd889ac:~# tsh db login mysql

Connection information for MySQL database "magic-212.2.240.196.nip.io-mysql" has been saved.

You can now connect to the database using the following command:

$ mysql --defaults-group-suffix=_magic-212.2.240.196.nip.io-mysql

Or configure environment variables and use regular CLI flags:

$ eval $(tsh db env)
  $ mysql

root@teleport-9cd889ac:~# tsh db ls
Name       Description Labels Connect
---------- ----------- ------ ------------------------------------------------------------------------------------------------------
> mysql                       mysql --defaults-group-suffix=_magic-212.2.240.196.nip.io-mysql --user=<user> --database=<database>

root@teleport-9cd889ac:~# mysql --defaults-group-suffix=_magic-212.2.240.196.nip.io-mysql --user=alice --database=mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10027
Server version: 8.0.0-Teleport (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

As you can see a user can establish a Mysql server connection from teleport. how cool is that !!!!

Conclusion:

In simple 10 steps, I was able to set up teleport, create users, create a database and connect to the database from the teleport UI. Optionally you can also create RBAC as teleport supports RBAC so you can create a db role for the database and create users with that role accordingly. docs to create RBAC: https://goteleport.com/docs/ver/6.1/database-access/guides/mysql-self-hosted/#create-role-and-user

Saiyam Pathak CNCF Ambassador Youtube: https://youtube.com/c/saiyam911 Twitter: https://twitter.com/saiyampathak Twitch: https://twitch.tv.saiyampathak Kubernetes CKS book: https://gumroad.com/l/cksbook