Francis Ndungu and Amy Negrette
The author selected Apache Software Foundation to receive a donation as part of the Write for DOnations program.
Authentication is the process of verifying users’ identity during login requests. In an authentication process, users submit their credentials as usernames and passwords. Then, the application matches those login credentials with stored database entries. The application grants users access to the system if there is a match.
Storing login credentials in a relational database like MySQL or PostgreSQL without a caching mechanism is still a common and practical approach, but it comes with the following limitations:
Overloading the database. The application must make a roundtrip to the database server to verify the users’ credentials from a database table every time a user submits a login request. Because the database might still serve other read/write requests, the entire process overloads the database and makes it slow.
Traditional disk-based databases have scalability issues. When your application receives thousands of requests per second, disk-based databases do not perform optimally.
To overcome the above challenges, you can use Redis to cache the users’ login credentials so that your application doesn’t have to contact the backend database during each login request. Redis is one of the most popular ultrafast data stores that utilizes your computer’s RAM to store data in key-value pairs. In this guide, you’ll use the Redis database to speed up session handling in your Python/MySQL application on the Ubuntu 22.04 server.
Before you begin this tutorial, you will need to setup following:
Switch to the new sudo
user account and install:
This application permanently stores users’ credentials, such as names and passwords, in a MySQL database server. When a user logs in to the application, a Python script queries the MySQL database and matches the details with stored values. Then, the Python script caches the user’s login credentials in a Redis database to serve other future requests. To complete that logic, your Python scripts require database drivers (Python modules) to communicate with the MySQL and Redis servers. Follow the steps below to install the drivers:
python3-pip
, a Python package manager allowing you to install additional modules not part of the Python standard library.sudo apt install python3-pip
pip install mysql-connector-python
pip install redis
After installing the necessary drivers for communicating with MySQL and Redis, proceed to the next step and initialize a MySQL database.
For this guide, you require one MySQL table. In a production environment, you can have dozens of tables that serve other requests. Set up a database and create the table by executing the following commands:
Log in to the MySQL database server as a root
user:
sudo mysql -u root -p
Enter your MySQL server’s root
password when prompted and press ENTER
to proceed. Then, run the following command to create a sample company
database and a company_user
account. Replace example-mysql-password
with a strong password:
- CREATE DATABASE company;
- CREATE USER 'company_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'example-mysql-password';
- GRANT ALL PRIVILEGES ON company.* TO 'company_user'@'localhost';
- FLUSH PRIVILEGES;
Ensure you receive the following output to confirm that the previous commands have run successfully:
OutputQuery OK, 1 row affected (0.01 sec)
Switch to the new company
database:
- USE company;
Confirm you’re connected to the new database by verifying the following output:
OutputDatabase changed
Create a system_users
table. The user_id
column serves as a PRIMARY KEY
to uniquely identify each user. The username
and password
columns are the login credentials that users must submit to log in to the application. The first_name
and last_name
columns store the users’ names:
custom_prefix(mysql>)
CREATE TABLE system_users (
user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
password VARCHAR(50)
) ENGINE = InnoDB;
Ensure you’ve created the new table by verifying the following output:
OutputQuery OK, 0 rows affected (0.03 sec)
Populate the system_users
table with sample data. Use the MySQL inbuilt MD5(...)
function to hash the password for security purposes:
- INSERT INTO system_users (username, first_name, last_name, password) VALUES ('john_doe', 'JOHN', 'DOE', MD5('password_1'));
- INSERT INTO system_users (username, first_name, last_name, password) VALUES ('mary_henry', 'MARY', 'HENRY', MD5('password_2'));
- INSERT INTO system_users (username, first_name, last_name, password) VALUES ('peter_jade', 'PETER', 'JADE', MD5('password_3'));
Verify the output below:
OutputQuery OK, 1 row affected (0.00 sec)
Query the system_users
table to ensure the data is in place:
- SELECT
- user_id,
- first_name,
- last_name,
- password
- FROM system_users;
Verify the following output:
Output+---------+------------+-----------+----------------------------------+
| user_id | first_name | last_name | password |
+---------+------------+-----------+----------------------------------+
| 1 | JOHN | DOE | 57210b12af5e06ad2e6e54a93b1465aa |
| 2 | MARY | HENRY | 259640f97ac2b4379dd540ff4016654c |
| 3 | PETER | JADE | 48ef85c894a06a4562268de8e4d934e1 |
+---------+------------+-----------+----------------------------------+
3 rows in set (0.00 sec)
Log out from the MySQL database:
- QUIT;
You’ve now set up the right MySQL database for your application. In the next step, you’ll build a Python module communicating with your sample database.
When coding any Python project, you should create a separate module for each task to promote code reusability. In this step, you’ll set up a central module that allows you to connect and query the MySQL database from a Python script. Follow the steps below:
Create a project
directory. This directory separates your Python source code files from the rest of the system files:
- mkdir project
Switch to the new project
directory:
- cd project
Use nano
text editor to open a new mysql_db.py
file. This file hosts the Python module that talks to the MySQL database:
nano mysql_db.py
Enter the following information into the mysql_db.py
file. Replace example-mysql-password
with the correct MySQL password for the company_user
account:
import mysql.connector
class MysqlDb:
def db_con(self):
mysql_con = mysql.connector.connect(
host = "localhost",
user = "company_user",
password = "example-mysql-password",
database = "company",
port = "3306"
)
return mysql_con
def query(self, username, password):
db = self.db_con()
db_cursor = db.cursor()
db_query = "select username, password from system_users where username = %s and password = md5(%s)"
db_cursor.execute(db_query, (username, password))
result = db_cursor.fetchone()
row_count = db_cursor.rowcount
if row_count < 1:
return False
else:
return result[1]
Save and close the mysql_db.py
file.
The mysql_db.py
module file has one class (MysqlDb:
) with two methods:
- db_con(self):
, connects to the sample company
database that you created earlier and returns a reusable MySQL connection using the return mysql_con
statement.
- query(self, username, password):
, a method that accepts a username
and password
and queries the system_users
table to find if there is a match. The conditional if row_count < 1: ... else: return result[1]
statement returns the boolean False
value if a user doesn’t exist in the table or the user’s password (result[1]
) if the application finds a match.
With the MySQL module ready, follow the next step to set up a similar Redis module that communicates to the Redis key-value store.
In this step, you’ll code a module that connects to the Redis server. Execute the following steps:
Open a new redis_db.py
file:
nano redis_db.py
Enter the following information into the redis_db.py
file. Replace example-redis-password
with the correct password for the Redis server:
import redis
class RedisDb:
def db_con(self):
r_host = 'localhost'
r_port = 6379
r_pass = 'example-redis-password'
redis_con = redis.Redis(host = r_host, port = r_port, password = r_pass)
return redis_con
Save and close the redis_db.py
file.
The above file has one class (RedisDb:
).
Under this class, the db_con(self):
method uses the provided credentials to connect to the Redis server and returns a reusable connection using the return redis_con
statement.
After setting up the Redis class, create the main file for your project in the next step.
Every Python application must have an entry point or the main file that executes when the application runs. In this file, you’ll create a code that shows the current server’s time for authenticated users. This file uses the custom MySQL and Redis modules you created to authenticate users. Follow the steps below to create the file:
Open a new index.py
file:
nano index.py
Enter the following information into the index.py
file:
from encodings import utf_8
import base64
from hashlib import md5
import json
import datetime
import http.server
from http import HTTPStatus
import socketserver
import mysql_db
import redis_db
class HttpHandler(http.server.SimpleHTTPRequestHandler):
def do_GET(self):
self.send_response(HTTPStatus.OK)
self.send_header('Content-type', 'application/json')
self.end_headers()
authHeader = self.headers.get('Authorization').split(' ');
auth_user, auth_password = base64.b64decode(authHeader[1]).decode('utf8').split(':')
mysql_server = mysql_db.MysqlDb()
redis_server = redis_db.RedisDb()
redis_client = redis_server.db_con()
now = datetime.datetime.now()
current_time = now.strftime("%Y-%m-%d %H:%M:%S")
resp = {}
if redis_client.exists(auth_user):
if md5(auth_password.encode('utf8')).hexdigest() != redis_client.get(auth_user).decode('utf8'):
resp = {"error": "Invalid username/password."}
else:
resp = {"time": current_time, "authorized by": "Redis server"}
else:
mysql_resp = mysql_server.query(auth_user, auth_password)
if mysql_resp == False:
resp = {"error": "Invalid username/password."}
else:
resp = {"time": current_time, "authorized by": "MySQL server"}
redis_client.set(auth_user, mysql_resp)
self.wfile.write(bytes(json.dumps(resp, indent = 2) + "\r\n", "utf8"))
httpd = socketserver.TCPServer(('', 8080), HttpHandler)
print("Web server is running on port 8080...")
try:
httpd.serve_forever()
except KeyboardInterrupt:
httpd.server_close()
print("Web server has stopped runing.")
Save and close the index.py
file.
In the index.py
file, the import...
section adds the following modules to your project:
utf_8
, base64
, md5
, and json
, text encoding and formatting modules.
http.server
, HTTPStatus
, and socketserver
, web server modules.
datetime
, time/date module.
mysql_db
and redis_db
, custom modules that you previously created to access the MySQL and Redis servers.
The HttpHandler(http.server.SimpleHTTPRequestHandler):
is a handler class for the HTTP server. Under the class, the do_GET(self):
method servers the HTTP GET requests and displays the system’s date/time for authenticated users.
In the if ... : else: ...
logic, the Python script runs the logical if redis_client.exists(auth_user):
statement to check if the user’s credentials exist in the Redis server. If the user details exist and the Redis stored password doesn’t match the user’s submitted password, the application returns the {"error": "Invalid username/password."}
error.
If the user details do not exist in the Redis server, the application queries the MySQL database server using the mysql_resp = mysql_server.query(auth_user, auth_password)
statement. In case the user’s supplied password doesn’t match the database stored value, the application returns the {"error": "Invalid username/password."}
error. Otherwise, the application caches the user’s details in the Redis server using the redis_client.set(auth_user, mysql_resp)
statement.
In all cases where the user’s credentials match the Redis/MySQL details, the application displays the system’s current date/time using the {"time": current_time, ...}
statement. The authorized by
entry in the output allows you to see the database server that authenticates the users in the application.
if redis_client.exists(auth_user):
if md5(auth_password.encode('utf8')).hexdigest() != redis_client.get(auth_user).decode('utf8'):
resp = {"error": "Invalid username/password."}
else:
resp = {"time": current_time, "authorized by": "Redis server"}
else:
mysql_resp = mysql_server.query(auth_user, auth_password)
if mysql_resp == False:
resp = {"error": "Invalid username/password."}
else:
resp = {"time": current_time, "authorized by": "MySQL server"}
redis_client.set(auth_user, mysql_resp)
You have now set up the main file for the application. In the next step, you’ll test the application.
In this step, you’ll run your application to see if the Redis caching mechanism works. Execute the commands below to test the application:
Use the following python3
command to run the application:
python3 index.py
Ensure the application’s custom web server is running:
OutputWeb server is running on port 8080...
Establish another SSH
connection to your server in a new terminal window and run the following curl
commands to send four GET request using john_doe's
credentials. Append [1-4]
at the end of the http://localhost:8080/
URL to send the four requests in a single command:
curl -X GET -u john_doe:password_1 http://localhost:8080/[1-4]
Verify the following outputs. The MySQL server only serves the first authentication request. Then, the Redis database serves the next three requests.
Output[1/4]
{
"time": "2023-11-07 10:04:38",
"authorized by": "MySQL server"
}
[4/4]
{
"time": "2023-11-07 10:04:38",
"authorized by": "Redis server"
}
Your application logic is now working as expected.
In this guide, you built a Python application that uses the Redis server to cache users’ login credentials. Redis is a highly available and scalable database server that can perform thousands of transactions per second. With the Redis caching mechanism in your application, you can highly reduce traffic in your backend database server. To learn more about Redis applications, refer to our Redis tutorials .
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.