2024 Secure Infrastructure Access Report: Key Insights and Trends
Oct 30
Virtual
Register Today
Teleport logo

Home - Teleport Blog - Production MySQL SSL with Teleport Machine ID - Jul 25, 2022

Production MySQL SSL with Teleport Machine ID

Securing TLS

In the first part of this blog post, we setup SSL/TLS for a MySQL database, using the built in self-signed certificates. The main problem using out of the box self-signed certificates is that clients can't verify that they're talking to the right database host, and it's not possible to verify the certificate chain.

In this post we'll cover upgrading the client connection to VERIFY_IDENTITY and how to use Machine ID to continuously renew certificates. Teleport is an open-source access platform that was initially focused on providing human access to infrastructure. Teleport 9 introduced Machine ID to make it easier for developers to secure machine-to-machine communications based on X.509 and SSH certificates.

Key capabilities of Teleport Machine ID include:

  • Automatic generation and renewal of short-lived certificates for any custom-built application, infrastructure resource or other machine user
  • Automatic service discovery through integration into Teleport inventory management
  • Routing of secure, encrypted communications between machines using mTLS or SSH
  • Protocol-level RBAC (e.g. different levels of access for SSH, Kubernetes, databases, Windows)
  • Instant session termination of machine users without the hassle of deleting keys from servers
  • Enhanced logging and session recordings of all machine sessions

Upgrading MySQL SSL Mode to VERIFY_IDENTITY

One problem of using mysql_ssl_rsa_setup and MySQL default self-signed certificate is that while the connection is encrypted, the server hostname is not verified. This opens the possibility for MITM attacks for another database using self-signed certs.

To fix this issue, you'll need to issue certificates from a central certificate authority (CA). Depending on where you're hosted, you have a few options. For AWS users:

  1. AWS Certificate Manager Private Certificate Authority.
    • Pro: A completely managed CA allowing for different CA hierarchies.
    • Con: Start at $400 per month.
  2. Leverage Teleport Certificate Authority using Machine ID
    • Pro: Open-source Certificate Authority designed for infrastructure access.
    • Con: Expanding Database support, might not support obscure databases.

1. Install Teleport

Teleport requires a central cluster. Comprising of a Proxy and Auth service. To install Teleport follow our Getting Started Guide or the MySQL Database Setup Guide.

Along with an optional Teleport Database Service, service should be installed on the same host as the MySQL service. When adding this service. The Teleport Database Service will be set up on item on step 4.

2. Create a certificate/key pair

Teleport uses mutual TLS authentication with self-hosted databases. These databases must be configured with Teleport's certificate authority to be able to verify client certificates. They also need a certificate/key files that Teleport can verify.

# The --host must match the FQDN of the database, this could be localhost or --host=db.example.com
tctl auth sign --format=db --host=localhost --out=server --ttl=2190h

3. Configure MySQL

To configure MySQL to accept TLS connections, add the following to your MySQL configuration file, mysql.cnf:

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

Additionally, your MySQL database user accounts must be configured to require a valid client certificate. If you're creating a new user:

CREATE USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice';
# If you're updating an existing user:
# ALTER USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice';

By default, the created user may not have access to anything and won't be able to connect, so let's grant it some permissions:

GRANT ALL ON `%`.* TO 'alice'@'%';

4. Start Teleport Database Service

Teleport recommends starting a Teleport Database service on the same host as the MySQL Instance.

Teleport MachineID
Teleport MachineID

Start the database service

teleport db start \
   --token=/tmp/token \
   --auth-server=teleport.example.com:443 \
   --name=test \
   --protocol=mysql \
   --uri=localhost:3306 \
   --labels=env=dev

5. Use Machine ID for Clients and tsh for end-users

Once the Database Service has joined the cluster, log in manually to see the available databases:

tsh login --proxy=teleport.example.com --user=testuser
tsh db ls
#Name    Description   Labels
#
#------- ------------- --------
#
#example Example MySQL env=dev

To connect to a particular database server, first retrieve credentials from Teleport using the tsh db login command:

tsh db login example
# Once logged in, connect to the database:
tsh db connect example

6. Install Machine ID on the application host

The last stage of securing MySQL database is to update clients to use the new SSL/TLS certificates. Teleport has created a small program tbot to help clients obtain new short-lived certificates.

7. Start tbot and tbot proxy on the application host.

Make sure Teleport has been installed. Create a bot user and join token

Start by creating a configuration file for Machine ID at /etc/tbot.yaml:

auth_server: "auth.example.com:3025"
onboarding:
  join_method: "token"
  token: "abcd123-insecure-do-not-use-this"
  ca_pins:
  - "sha256:abdc1245efgh5678abdc1245efgh5678abdc1245efgh5678abdc1245efgh5678"
storage:
  directory: /var/lib/teleport/bot
destinations:
  - directory: /opt/machine-id
    
    database:
      service: example-server
      username: alice
      database: example
    
    # If using MongoDB, be sure to include the Mongo-formatted certificates:
    configs:
      - mongo

Machine ID needs two services, one for obtaining the certificates and another to create a database proxy. Once these two services are created, start the two systemd services.

8. Update application to use Machine ID

The final stage is to update your application or API to use the certificates obtained by Machine ID. At this final stage we'll make the application use sslmode=verify-full, providing the strongest integrity checks against the database. The standard TLS credentials may be found in your configured destination directory, which in this example is /opt/machine-id. The certificate may be found at /opt/machine-id/tlscert along with the private key /opt/machine-id/key and CA at /opt/machine-id/teleport-database-ca.crt. These are compatible with most database clients.

Below is an example Go Program, that includes the certificates as part of sql.Open and sets the sslmode to verify-full.

// This example program demonstrates how to connect to a Postgres database
// using certificates issued by Teleport Machine ID.

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// Open connection to database.
	db, err := sql.Open("mysql", fmt.Sprint(
		"host=localhost ",
		"port=1234 ",
		"dbname=example ",
		"user=alice ",
		"sslmode=verify-full",
		"sslrootcert=/opt/machine-id/teleport-database-ca.crt ",
		"sslkey=/opt/machine-id/key ",
		"sslcert=/opt/machine-id/tlscert ",
	))
	if err != nil {
		log.Fatalf("Failed to open database: %v.", err)
	}

	defer db.Close()

	// Call "Ping" to test connectivity.
	err = db.Ping()
	if err != nil {
		log.Fatalf("Failed to Ping database: %v.", err)
	}

	log.Printf("Successfully connected to MySQL.")
}

You are all set. You have provided your application with an encrypted TLS connection in 'VERIFY-FULL' mode, using short-lived certificates tied to a machine identity that can access your database — with the bonus of being audited and easily rotated.

If you would like to learn more, check out our Machine ID Guides.

Tags

Teleport Newsletter

Stay up-to-date with the newest Teleport releases by subscribing to our monthly updates.

background

Subscribe to our newsletter

PAM / Teleport