Once again, I'm bringing some knowledge from my day-to-day work as a Developer Advocate at ScyllaDB, and this time I'm going to teach you about secure and encrypted connections ! It's a topic that can cause a lot of anxiety for newbies, but I'm going to make it easier for you.
If you're just getting started with databases in general or databases in particular, you might want to start by reading my first article, Database 101: Data Consistency for Beginners.
This article captures my own exploration of how many database paradigms exist as I look far beyond my previous experience with just SQL and MySQL. I'm keeping track of my studies in this Database 101 series.
Table of Contents
- 1. Prologue
- 2. What is SSL and TLS?
- 3. OpenSSL for Noobs
- 4. Database Authentication
- 5. Testing Encrypted Connections
- 6. Conclusion
1. Prologue
Working on a NoSQL database is challenging in the sense that I have to learn stuff that I'd NEVER touch or study as a regular web developer. I mean, mostly you build a CRUD application, try your best not to screw up the database indexes (right guys ??????) and paint a few buttons.
But if you've been following my Database 101 journey, you probably know that I came to Scylla knowing NOTHING about databases except what MySQL was and how to build things with Laravel, and now I'm into it:
- Building Highly Scalable Applications with ScyllaDB
- Still learning Rust - 1 year and 6 months (and I feel like I know shit about it).
- Started learning ShellScript, Go, Python, JavaScript, and anything that has a ScyllaDB driver
- Learned the basics of observability using Grafana/Prometheus
- and my new friend Transport Layer Security (a.k.a. TLS)
If you're setting up your own servers, you've probably already used certbot to install certificates to run HTTPS smoothly, and that's what I've been doing for the last 6~7 years. To be really honest, knowing how to use a tool is what we developers do.
It even seems like an omen, but I'm working on a browser extension whose backend is written in Rust, and I didn't manage to use certbot
there, so I had to create my certificate using weird commands and add a weird openssl
crate to my project and trust that it would work:
let mut certs_file = BufReader::new(File::open(cert_path).unwrap());
let mut key_file = BufReader::new(File::open(key_path).unwrap());
// load TLS certs and key to create a self-signed temporary cert for testing:
// `openssl req -x509 -newkey rsa:4096 -nodes -keyout key.pem -out cert.pem -days 365 -subj '/CN=localhost'`
let tls_certs = rustls_pemfile::certs(&mut certs_file)
.collect::<Result<Vec<_>, _>>()
.unwrap();
let tls_key = rustls_pemfile::pkcs8_private_keys(&mut key_file)
.next()
.unwrap()
.unwrap();
After a few hours of trying/catching, I finally managed to deploy the API with the certificate, but I didn't learn anything about it. THEN, a few days later, my boss asked me to extend the ScyllaDB Security Pages and learn how Certificated Based Connection works, since I'd never used it.
I spent 10 hours on it and managed to get it to work, but mostly it wasn't about the documentation, it was about my lack of knowledge in this area, which is no longer a problem and I will teach you everything I've learned so far!
2. What is SSL and TLS?
TLDR: this is that boring section which you can skip if you want.
SSL (Secure Sockets Layer) was introduced in 1995 by Netscape to provide privacy, authentication, and data integrity for internet communications, primarily in browsers. TLS (Transport Layer Security), which succeeded SSL, is now the standard security protocol used across the internet for applications like email, messaging, and VoIP.
Without SSL/TLS, data transmitted between a client and a server is unencrypted and vulnerable to interception and misuse, making it an easy target for phishing and other attacks. SSL/TLS ensures that a secure "handshake" occurs before data transmission, meaning that all data is encrypted for that specific session.
However, even though SSL/TLS provides encryption, it doesn’t guarantee that the server itself is fully trustworthy or secure. SSL, being deprecated since 1996 due to its vulnerabilities, is still often referenced alongside TLS by developers as "SSL/TLS" because of its historical significance.
With this understanding of SSL/TLS, let’s now dive into the tools that help manage secure connections.
3. OpenSSL for Noobs
There are many data encryption toolkits available today, but we're going to talk about a very specific one: the openssl-library.org.
This library, which had its first release in 1998, is attached to every single tutorial on data encryption, and is used by many other tools around the open/closed source environment - such as Let's Encrypt and Certbot - and the one we're going to use here.
The purpose of this tool is to create keychains and credentials to authorize someone to do whatever you want.
Imagine you work in a commercial building where you have to show your badge to security to get in:
- Your company creates a "master key" and generates a certificate for it.
- You also generate a key and certificate for the security guard and give them to him along with a list of all the employee's certificates.
- The guard has a truststore keychain with all sorts of tags on it.
- When you arrive at the entrance, he will check that your tag has the same root certificate as his to decide whether you can pass or not.
In matter of commands, we did something like this:
# Creating the root/master key and certificate
# -----
openssl genpkey -algorithm RSA -out root_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the root key
openssl req -x509 -new -key root_key.pem -days 3650 -out root_cert.pem -subj "/CN=administrator" # Generate the root certificate
# -----
# Creating the Employee Key and Certificate
# -----
openssl genpkey -algorithm RSA -out employee_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the employee base key
openssl req -new -key employee_key.pem -out employee.csr -subj "/CN=employee" # Generate the employee CSR
openssl x509 -req -in employee.csr -CA root_cert.pem -CAkey root_key.pem -CAcreateserial -out employee_cert.pem -days 365 # Sign and generate the employee certificate with the root/master key and certificate
cat root_cert.pem employee_cert.pem > employee_truststore.pem # Create a truststore (tag) to be used together with the key.
# -----
# Creating the Security (server) Key and Certificate
# -----
openssl genpkey -algorithm RSA -out security_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the security key
openssl req -new -key security_key.pem -out security.csr -subj "/CN=server" # Generate the security CSR
openssl x509 -req -in security.csr -CA root_cert.pem -CAkey root_key.pem -CAcreateserial -out security_cert.pem -days 365 # Sign and generate the security certificate with the root/master key and certificate
cat root_cert.pem security_cert.pem > security_truststore.pem # Create a truststore (tag) to be used together with the key.
# -----
# Creating the Malicious Root and Client Key and Certificate
# -----
openssl genpkey -algorithm RSA -out malicious_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the malicious root key
openssl req -x509 -new -key malicious_key.pem -days 3650 -out malicious_cert.pem -subj "/CN=fake-admin" # Generate the malicious root certificate
cat root_cert.pem malicious_cert.pem > malicious_truststore.pem # Combine root and malicious root certificates into a truststore
# -----
There are too many commands, but basically this is what happened:
- A
root_key.pem
androot_cert.pem
were generated. - These keys and certificates were used to sign other keys and certificates for employees:
-
security_key.pem
was signed byroot_key.pem
. -
employee_key.pem
was signed byroot_key.pem
. -
server_key.pem
has been signed byroot_key.pem
.
-
- All these keys are in a file called
security_truststore.pem
. - Someone tried to create a fake key called
malicious_key.pem
using theroot_cert.pem
.
Now let's do some validation. Theoretically, only the keys in security_truststore.pem
should be allowed to join:
# Validating the certificates with the truststores
# -----
openssl verify -CAfile security_truststore.pem employee_cert.pem # Check if the certificate is valid
# employee_cert.pem: OK
openssl verify -CAfile security_truststore.pem malicious_cert.pem # Check if the certificate is valid
# CN = fake-admin
# error 18 at 0 depth lookup: self-signed certificate
# error malicious_cert.pem: verification failed
# -----
And as expected, since the malicious key is not signed by the root user, authentication fails and that person is kicked out.
So now we know the basics of encryption. But where do you use it in the real world? Let me tell you more about my current task and then you will understand.
4. Database Authentication
Say you're running any database (e.g. MySQL, Postgres, ScyllaDB, Redis, etc.) in Docker. When you spin up these instances in a containerized environment, you usually don't need a password unless you set one. In any case, we have a number of options when we talk about authentication in any part of the modern web, and the same is true when we're talking about databases.
Were going to use ScyllaDB in this example however this flow is pretty common in many databases.
At this step, we're going to learn about:
- No Authentication: Literally no credentials required, just spin your instance and be happy;
- Username/Password Authentication: Some basic credentials are fine to keep people away from your data;
- Certified / Role Certified Authentication: This is where the big companies like to play and you should learn that.
After this brief introduction of encryption 101, we can get started with the content itself.
4.1 No Authentication
In Scylla, if you're running a node or cluster in the development environment (locally or using Docker), there is no password by default. Also, ScyllaDB uses the CQL protocol on port 9042 by default, which can be easily changed in the config files.
Why am I telling you this port information? Because this information will be important later, trust me.
You can spin up a Docker instance by running it:
# Running a ScyllaDB Node with Docker
docker run --name some_scylla -p 9042:9042 -p 9142:9142 -d scylladb/scylla:6.1.2 \
--overprovisioned 1 --smp 1
# Checking the node status -> Expect for UN (Up And Running)Cassandra
docker exec -it some_scylla nodetool status
# Datacenter: datacenter1
# =======================
# Status=Up/Down
# |/ State=Normal/Leaving/Joining/Moving
# -- Address Load Tokens Owns Host ID Rack
# UN 10.10.5.2 509.46 KB 256 ? 9f597eb5-a77f-493f-9835-85dd1e571fcc rack1
# --
After that, you can login at cqlsh (CQL Shell) by running:
docker exec -it some_scylla cqlsh
# Connected to at 10.10.5.5:9042
# [cqlsh 6.0.18 | Scylla 6.0.1-0.20240612.bc89aac9d017 | CQL spec 3.3.1 | Native protocol v4]
# Use HELP for help.
# cqlsh> select address, port, client_type, username from system.clients;
#
# address | port | client_type | username
# -----------+-------+-------------+-----------
# 10.10.5.5 | 50854 | cql | anonymous
# 10.10.5.5 | 50868 | cql | anonymous
# -----------+-------+-------------+-----------
Yeah, we're connected as anonymous users! Perfect for developing new stuff and testing locally. But then you decide to deploy the same configuration into production...
And OF COURSE that, in less than an hour you'll be owned by some random database crawler LOL.
And you can read more about this ransomware if you want. Anyway, let's add some base credentials and configure it properly in the next step.
Seriously, don't open your database to the Internet with the default configurations or without binding a unique IP address to access it.
4.2 Username/Password Authentication
While running any of these other databases, you have the ability to switch authentication types by changing a bunch of configuration files. At ScyllaDB, we're really proud of how modular and easy the configuration is.
If you look at /etc/scylla/scylla.yaml
in your docker (you can get a better look at the repository by clicking here (file: scylla.yaml#247), you will find these configuration flags:
# file: /etc/scylla/scylla.yaml
# ...
# ...
# Authentication backend, identifying users
# Out of the box, Scylla provides org.apache.cassandra.auth.{AllowAllAuthenticator,
# PasswordAuthenticator}.
#
# - AllowAllAuthenticator performs no checks - set it to disable authentication.
# - PasswordAuthenticator relies on username/password pairs to authenticate
# users. It keeps usernames and hashed passwords in system_auth.credentials table.
# Please increase system_auth keyspace replication factor if you use this authenticator.
# - com.scylladb.auth.TransitionalAuthenticator requires username/password pair
# to authenticate in the same manner as PasswordAuthenticator, but improper credentials
# result in being logged in as an anonymous user. Use for upgrading clusters' auth.
# authenticator: AllowAllAuthenticator
# ...
The default is the AllowAllAuthenticator
which allows anonymous connection with superpowers. Now, we'll uncomment the # authenticator:
line and replace with PasswordAuthenticator
:
# file: /etc/scylla/scylla.yaml
# ...
-#authenticator: AllowAllAuthenticator # line ~247
+authenticator: PasswordAuthenticator
Okay! Now we just need to tell our Scylla node that we have made this change. Since this is a "critical" change to our system environment, we'll need to drain
and then restart the node. Here's a step-by-step guide:
# Before Editing
docker exec -it some_scylla nodetool drain # Stop gossiping and preparing to shut down
docker exec -it some_scylla supervisorctl stop scylla # Stop ScyllaDB
docker exec -it some_scylla cat /etc/scylla/scylla.yaml | grep authenticator: # Check the current flag
# Edit /etc/scylla/scylla.yaml ...
docker exec -it some_scylla sed -i 's/# authenticator:.*/authenticator: PasswordAuthenticator/' /etc/scylla/scylla.yaml
# After Edit
docker exec -it some_scylla cat /etc/scylla/scylla.yaml | grep authenticator: # Check if the update is there
docker exec -it some_scylla supervisorctl start scylla # Run ScyllaDB
With that we're finally able to run cqlsh
and receives a huge "YOU NEED CREDENTIALS, BITCH":
docker exec -it some_scylla cqlsh
Connection error: ('Unable to connect to any servers', {'10.10.5.5:9042': AuthenticationFailed('Remote end requires authentication')})
and by default, at ScyllaDB, we can login using the username/password "cassandra" (superuser):
docker exec -it some_scylla cqlsh -u cassandra -p cassandra
# Connected to at 10.10.5.5:9042
# [cqlsh 6.0.18 | Scylla 6.0.1-0.20240612.bc89aac9d017 | CQL spec 3.3.1 | Native protocol v4]
# Use HELP for help.
# cassandra@cqlsh> select address, port, client_type, username from system.clients;
# address | port | client_type | username
# -----------+-------+-------------+-----------
# 10.10.5.5 | 56998 | cql | cassandra
# 10.10.5.5 | 57014 | cql | cassandra
# -----------+-------+-------------+-----------
... with that, we know the basics of how to configure authentication in ScyllaDB, and if you want to know why the user/password is "cassandra", you can read my first article from this series. Also, be sure to change your default credentials before deploying it.
The CQL protocol runs on port 9042 by default and is well known. In the same way that you can get owned by just letting your database run without authentication, you can get owned by letting the default credentials there.
At the moment, all connections still unencrypted
but at least we have a minimum security, but don't worry! We're about fix the encryption soon! Before that, let's create some users/roles inside our database.
4.3 Creating Users and Roles
Before creating new users for this database, we need to understand how it works. At Scylla we use Role Based Authentication
every day. So every user is placed in system.roles
as a role.
This is not a problem because you can set a PASSWORD
to a role. It looks weird, but I'll show you how it works. Check it out:
scylladb@cqlsh> desc system.roles;
CREATE TABLE system.roles (
role text,
can_login boolean,
is_superuser boolean,
member_of set<text>,
salted_hash text,
PRIMARY KEY (role)
);
scylladb@cqlsh> select * from system.roles;
role | can_login | is_superuser | member_of | salted_hash
-------------+-----------+--------------+---------------+--------------------
scylladb | True | True | null | $6$rAJ6FflUo8Chf...
employee | False | False | null | null
danielhe4rt | True | False | {'employee'} | $6$AF4F6CflAA8Cw...
-------------+-----------+--------------+---------------+--------------------
This output gives us some information about the authentication itself, such as
- A role becomes an authenticatable when the
can_login
flag is set totrue
; - A role must/can have a password after it becomes an authenticatable;
- A role can belong to
another role
, which will inherit all privileges from it.
This is just an environment created to give you an example of how the modeling and features work. Be sure to read the documentation.
In a matter of DCL (Data Control Language) and DML (Data Manipulation Language), we'll do basic commands like:
-- Create our user/roles
CREATE ROLE developer;
CREATE ROLE danielhe4rt WITH PASSWORD = 'some_cool_password' AND LOGIN = true;
-- Grant roles to our users
GRANT developer TO danielhe4rt;
... which we will be running at our ScyllaDB Docker Instance following the keys created in the beginning of the article:
docker exec -it some-scylla cqlsh -u cassandra -p cassandra -e "CREATE ROLE IF NOT EXISTS 'employee' WITH LOGIN = true;";
docker exec -it some-scylla cqlsh -u cassandra -p cassandra -e "CREATE ROLE IF NOT EXISTS 'server' WITH LOGIN = true;";
Note: You can only create new roles/users using an authenticated account
4.4 Certificated / Role Certificated Authentication
At the beginning of this tutorial we introduced the topic by talking about a story of someone with valid keys to enter a building and now the goal is to understand what is different from a regular authentication. As I said, without TLS/SSL, your data is in transit without any encryption, and that's BAD depending on the content you're sending/receiving.
Using a certificate means that before you send your data out, we have guarantees that if someone intercepts the data, it won't be a problem if they don't have the key to decrypt it.
- By default, ScyllaDB listens to the CQL protocol on port 9042, which can be configured using the
native_transport_port
configuration option. - Scylla also supports the CQL protocol via TLS/SSL encryption, which is disabled by default and can be enabled using the
native_transport_port_ssl
configuration option.
The traditional choice of port for secure connections is 9142, but if client_encryption_options
is specified and native_transport_port_ssl
is not, then native_transport_port
will only handle encrypted connections. The same thing happens if native_transport_port
and native_transport_port_ssl
are set to the same value.
I know, it seems a bit crazy with all these flags and options, but I'll try to make it simpler. Check out the rules that govern port assignment/encryption, which are summarized in the table below:
np := native_transport_port is set
nps := native_transport_port_ssl is set
ceo := client_encryption_options are enabled
eq := native_transport_port_ssl == native_transport_port
+-----+-----+-----+-----+
| np | nps | ceo | eq |
+-----+-----+-----+-----+
| 0 | 0 | 0 | * | => listen on native_transport_port, unencrypted
| 0 | 0 | 1 | * | => listen on native_transport_port, encrypted
| 0 | 1 | 0 | * | => don't listen
| 0 | 1 | 1 | * | => listen on native_transport_port_ssl, encrypted
| 1 | 0 | 0 | * | => listen on native_transport_port, unencrypted
| 1 | 0 | 1 | * | => listen on native_transport_port, encrypted
| 1 | 1 | 0 | * | => listen on native_transport_port, unencrypted
| 1 | 1 | 1 | 0 | => listen on native_transport_port, unencrypted + native_transport_port_ssl, encrypted
| 1 | 1 | 1 | 1 | => listen on native_transport_port(_ssl - same thing), encrypted
+-----+-----+-----+-----+
// More at: https://github.com/scylladb/scylladb/blob/master/docs/dev/protocols.md#cql-client-protocol
This tells us that if we want to enable encryption, we need to update a few more things in scylla.yaml
. Instead of using the PasswordAuthenticator
, we'll switch to the com.scylladb.auth.CertificateAuthenticator
feature.
Before we change anything, let's drain and stop our cluster:
docker exec -it some_scylla nodetool drain # Stop gossiping and preparing to shut down
docker exec -it some_scylla supervisorctl stop scylla # Stop ScyllaDB
In the previous step we created some roles with LOGIN enabled. And we added these "roles" to our OpenSSL commands at the top with a CN=server/blabla, right? Now it's time to make it useful and not use credentials anymore.
Private Keys + Certs can hold more information, like raw strings that can be used after the handshake is done. In this case, we have stored the user/role that will be used to log in inside our keys. With this, we can do a pattern match with the certificate content and check if there's any CN=something that matches our select * from system.roles where role = 'something'
.
Let's start the modifications by entering our ScyllaDB instance:
# Enter the ScyllaDB Instance
docker exec -it some_scylla shell
# root@c531i213hu:/#
# Install any editor of your choice
apt install nano
# Enter the ScyllaDB Config File
nano -l /etc/scylla/scylla.yaml
With your config file opened, let's work on the modifications needed.
4.4.1 Change the Authentication Type
This is the last time we'll open this file, I promise! The idea here is to change the authentication type from PasswordAuthenticator
to com.scylladb.auth.CertificateAuthenticator
. We're also going to set a rule that will extract the CN=
flag from each certificate and use the content for authentication purposes:
# file: /etc/scylla/scylla.yaml
-authenticator: PasswordAuthenticator # line ~247
+authenticator: com.scylladb.auth.CertificateAuthenticator
+auth_certificate_role_queries:
+ - source: SUBJECT
+ query: CN=([^,\s]+)
4.4.2 Enable the Encryption Port
We still haven't enabled TLS/SSL even after setting the authenticator. We still need to uncomment the native_transport_port_ssl
to get port 9142 (which is used to transport our encrypted data) into the game. So, back to the scylla.yaml
, lets change it:
# file: /etc/scylla/scylla.yaml
# ...
-# native_transport_port_ssl: 9142 # <- line ~131
+native_transport_port_ssl: 9142
4.4.3 Enable the Client Encryption Port
Step by step, we'll make it! Our final change is to the client_encryption_options
as defined in the table above. We need to uncomment everything and make sure everything matches in these configurations. Here's a brief explanation of each configuration:
- enabled: enables TLS/SSL encryption -> change to true (default: false)
-
certificate:
absolute path
to your server certificate (security_cert.pem / server_cert.pem) -
keyfile:
absolute path
your server key signed by the root_key (security_key.pem / server_key.pem) -
truststore:
absolute path
your server truststore containing server + root certificates (security_truststore.pem / server_truststore.pem) - require_client_auth: your server must receive a certificate to authenticate -> change to true
So, let's work on these changes:
# file: /etc/scylla/scylla.yaml
# ...
# enable or disable client/server encryption.
-# client_encryption_options:
-# enabled: true
-# certificate: /etc/scylla/certs/cert.pem
-# keyfile: /etc/scylla/certs/key.pem
-# truststore: /etc/scylla/certs/truststore.pem
-# require_client_auth: true
+client_encryption_options:
+ enabled: true
+ certificate: /etc/scylla/certs/server_cert.pem
+ keyfile: /etc/scylla/certs/server_key.pem
+ truststore: /etc/scylla/certs/server_truststore.pem
+ require_client_auth: true
Ok! Now we're good to go. Let's turn on our ScyllaDB cluster by running:
docker exec -it some_scylla supervisorctl start scylla # Start ScyllaDB
Let's see if our port 9142 is running and listening for TLS/SSL connections:
openssl s_client -connect localhost:9042
# CONNECTED(00000003)
# 40873B60D2750000:error:0A00010B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:354:
# no peer certificate available
# No client certificate CA names sent
openssl s_client -connect localhost:9142
# CONNECTED(00000003)
# Can't use SSL_get_servername
# depth=0 CN = server
# verify error:num=20:unable to get local issuer certificate
# verify return:1
# depth=0 CN = server
# verify error:num=21:unable to verify the first certificate
# verify return:1
# depth=0 CN = server
# verify return:1
# ---
# Certificate chain
# 0 s:CN = server
# i:CN = administrator
# a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
# v:NotBefore: Aug 1 20:09:38 2024 GMT; NotAfter: Aug 1 20:09:38 2025 GMT
# ---
# YAY IT'S ASKING FOR CERTIFICATES!!!
At this point, we are 100% sure that TLS is enabled and requesting the certificates that match our previously generated keys.
5. Testing Encrypted Connections
Too much stuff to configure, but how do we connect to it? Instead of using CQLSH (because it's a pain in the ass to set it up and I LITERALLY PREFERED TO MAKE IT WORK on Node than explain how to use it), we're going to use NodeJS for the sake of simplicity.
First, let's quick setup our driver by running:
npm install @lambda-group/scylladb
After that, you can already create a demo script pointing your nodes and don't forget to switch the ports at the connection string:
import { Cluster } from "@lambda-group/scylladb";
const cluster = new Cluster({
nodes: ["127.0.0.1:9142"],
ssl: {
enabled: true,
truststoreFilepath: "/your/path/to/certificates/developer_cert.pem",
privateKeyFilepath: "/your/path/to/certificates/developer_key.pem",
caFilepath: "/your/path/to/certificates/developer_truststore.pem",
verifyMode: VerifyMode.Peer,
}});
let result = await session.execute(
"SELECT address, port, username, driver_name, driver_version FROM system.clients"
);
console.log(result)
// [
// {
// address: '127.0.0.1',
// driver_name: 'scylla-js-driver',
// driver_version: '0.0.1',
// port: 58846,
// username: 'developer' // We're logged in as the role 'developer'
// }
// ]
As the output tells us, we managed to connect by only sending our key and certificate to the server!
6. Conclusion
This "article/tutorial" took me a long time to write for several reasons, but one of them was to make sure of the content by going through it a few times.
I also made a demo (gh: danielhe4rt/scylladb-role-tls-auth) where you can run a makefile
command and set up all these steps. So if you got here somehow, please don't forget to drop a star :D
Security is one of the topics that people are starting to pay more attention to nowadays and learning more about it was very exciting. Anyway, let me know what topics you would like to see in this series!
Stay safe and don't forget to drink water!