Author: Francis Ndungu
Last Updated: Thu, Dec 1, 2022Authentication is the process of verifying the user's credentials before granting access to an application. To log in to an application, end-users enter their usernames and passwords. Under the hood, a background process compares the users' credentials with database values to check if there is a match.
The whole authentication process requires a round-trip to a disk-based database like PostgreSQL every time a user accesses the application. When the application's user base grows, the disk-based databases encounter scalability issues. To overcome the challenge, this is where an in-memory database like Redis comes to play.
You can use the Redis database to cache authentication details when a user logs in to an application for the first time. Then, during the following requests, you can query the Redis server to check the authentication status instead of hitting the disk-based database. Redis is several times faster than disk-based databases. This approach makes your application faster and more scalable in the end.
This guide describes the process of authenticating a Python application with managed PostgreSQL and Redis databases from the Vultr platform. Vultr provides a secure and highly scalable managed database that works right out of the box to automate all the difficult tasks of your database administration.
To follow this guide:
Provision one PostgreSQL and one Redis managed database cluster. Use the same location for both clusters.
Locate the Connection Details for each database under the Overview tab. This guide uses the following sample connection details:
Redis server:
username: default
password: EXAMPLE_REDIS_PASSWORD
host: SAMPLE_REDIS_DB_HOST_STRING.vultrdb.com
port: 16752
PostgreSQL server:
username: vultradmin
password: EXAMPLE_POSTGRESQL_PASSWORD
host: SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com
port: 16751
This guide uses the managed PostgreSQL database to store data permanently on a disk. For this sample application, you require a database and two tables. The first table stores products. Then, a Python script queries the table to return the products in JSON format when users send requests to the application. The second table stores users and their authentication credentials. Follow the steps below to set up the database:
Update the package information index.
$ sudo apt update
Install the postgresql-client package. Because this application uses the PostgreSQL-managed database from Vultr, you only require the PostgreSQL command-line client to query the database.
$ sudo apt install -y postgresql-client
Use the psql command to log in to the managed PostgreSQL database. Replace SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com with the correct name of the host.
$ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com -p 16751 -U vultradmin defaultdb
Ensure you get the following password prompt.
Password for user vultradmin:
Enter the password for the managed PostgreSQL user and press ENTER to proceed. Then, verify the following output.
defaultdb=>
Enter the following command to create a sample my_company database.
defaultdb=> CREATE DATABASE my_company;
Output.
CREATE DATABASE
Switch to the new my_company database.
defaultdb=> \c my_company;
Output.
You are now connected to database "my_company" as user "vultradmin".
my_company=>
Create a products table. This guide uses a single table. In a production environment, you might have tens or hundreds of tables depending on the complexity of your application.
my_company=> CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR (50),
retail_price NUMERIC(5, 2)
);
Output.
CREATE TABLE
Populate the products table.
my_company=> INSERT INTO products (product_name, retail_price) VALUES ('1L FOUNTAIN DRINKING WATER', 2.55);
INSERT INTO products (product_name, retail_price) VALUES ('PINK COTTON BUDS', 4.85);
INSERT INTO products (product_name, retail_price) VALUES ('WINE GLASS', 9.75);
Output.
...
INSERT 0 1
Query the products table to ensure the data is in place.
my_company=> SELECT
product_id,
product_name,
retail_price
FROM products;
Output.
product_id | product_name | retail_price
------------+----------------------------+--------------
1 | 1L FOUNTAIN DRINKING WATER | 2.55
2 | PINK COTTON BUDS | 4.85
3 | WINE GLASS | 9.75
(3 rows)
Create a users table. The users table stores users' information such as user_id, username, and pwd (password).
my_company=> CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR (50),
pwd VARCHAR (255)
);
Output.
CREATE TABLE
Issue the following command to enable the pgcrypto extension. You require this extension to hash passwords before inserting them into the users table.
my_company=> CREATE EXTENSION pgcrypto;
Output.
CREATE EXTENSION
Populate the users table with sample data. This guide uses EXAMPLE_PASSWORD and EXAMPLE_PASSWORD_2. Remember to use strong passwords to prevent brute-force attacks in a production environment.
my_company=> INSERT INTO users (username, pwd) VALUES ('john_doe', crypt('EXAMPLE_PASSWORD', gen_salt('bf')));
INSERT INTO users (username, pwd) VALUES ('mary_smith', crypt('EXAMPLE_PASSWORD_2', gen_salt('bf')));
Output.
...
INSERT 0 1
Query the users table to verify the records and the workings of the pgcrypto extension.
my_company=> SELECT
user_id,
username,
pwd
FROM users;
Output.
user_id | username | pwd
---------+------------+--------------------------------------------------------------
1 | john_doe | $2a$06$spijfwl34nCdBpApp1C68OWa//j0buReiQ4SHAJVCV4sm627iyyZW
2 | mary_smith | $2a$06$g6FjH7PXSCMT75uIKB94ZOUWHbeth0SsHebOqcykjXM4Dq6mtlxtG
(2 rows)
Log out from the managed PostgreSQL server.
my_company=> \q
Proceed to the next step to create a database class for the PostgreSQL server.
This step shows you how to create a central PostgreSQL class that you can use from your application to access database functions. Follow the steps below to create the class:
Create a project directory to separate your source code from system files.
$ mkdir project
Switch to the new project directory.
$ cd project
Open a new posgresql_gateway.py file in a text editor.
$ nano postgresql_gateway.py
Enter the following information into the postgresql_gateway.py file. Replace the db_pass and db_host values with the correct host and password for the managed PostgreSQL database.
import psycopg2
import bcrypt
class PostgresqlGateway:
def __init__(self):
db_host = 'SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com'
db_port = 16751
db_name = 'my_company'
db_user = 'vultradmin'
db_pass = 'EXAMPLE_POSTGRESQL_PASSWORD'
self.postgresql_client = psycopg2.connect(host = db_host, database = db_name, user = db_user, password = db_pass, port = db_port)
def get_products(self):
sql_string = 'select product_id, product_name, retail_price from products'
cur = self.postgresql_client.cursor()
cur.execute(sql_string)
rows = cur.fetchall()
products = []
dt_columns = list(cur.description)
for row in rows:
row_data = {}
for i, col in enumerate(dt_columns):
row_data[col.name] = str(row[i])
products.append(row_data)
return products
def authenticate_user(self, username, password):
sql_string = "select username, pwd from users where username = %s"
cur = self.postgresql_client.cursor()
cur.execute(sql_string, (username,))
if cur.rowcount < 1 :
return False
else:
row = cur.fetchone()
if bcrypt.checkpw(password.encode('utf8'), row[1].encode('utf8')):
self.hashed_password = row[1].encode('utf8')
return True
else:
return False
Save and close the postgresql_gateway.py file.
postgresql_gateway.py file explained:The import section declares two libraries. The psycopg2 is a popular Python library for the PostgreSQL database. The bcrypt is a password-hashing library.
import psycopg2
import bcrypt
...
The PostgresqlGateway class has three methods.
class PostgresqlGateway:
def __init__(self):
...
def get_products(self):
...
def authenticate_user(self, username, password):
...
The _init_() method establishes a database connection to the PostgreSQL database when you instantiate the class.
The get_products(...) method queries the products table to retrieve a list of products from the database.
The authenticate_user(...) method queries the users table to find a match when a user tries to log in to the application. If a user's credentials match a record in the users table, the authenticate_user method returns True.
The if bcrypt.checkpw(password.encode('utf8'), row[1].encode('utf8')): statement compares the user's password with the database value using the bcrypt library.
The postgresql_gateway.py class is now ready. To use it in other Python files, use the following syntax:
import postgresql_gateway
pg = postgresql_gateway.PostgresqlGateway()
... = pg.get_products()
... = pg.authenticate_user(username, password)
Follow the next step to create a Redis database class.
This step focuses on creating a Redis database class. The class provides Redis functionalities for creating and retrieving keys. Execute the steps below to create the class:
Open a new redis_gateway.py file in a text editor.
$ nano redis_gateway.py
Enter the following information into the redis_gateway.py file. Replace the db_host and db_pass values with the correct host and password from your managed Redis server.
import redis
import bcrypt
class RedisGateway:
def __init__(self):
db_host = 'SAMPLE_REDIS_DB_HOST_STRING.vultrdb.com'
db_port = 16752
db_pass = 'EXAMPLE_REDIS_PASSWORD'
self.redis_client = redis.Redis(host = db_host, port = db_port, password = db_pass, ssl = 'true')
def cache_user(self, username, password):
self.redis_client.set(username, password)
def authenticate_user(self, username, password):
if self.redis_client.exists(username):
hashed_password = self.redis_client.get(username)
if bcrypt.checkpw(password.encode('utf8'), hashed_password):
return True
else:
return False
Save and close the redis_gateway.py file.
redis_gateway.py file explained:The import section declares two Python libraries. The redis library provides an interface between Python and the managed Redis server. The bcrypt library compares the plain-text password provided by a user and the hashed pass from Redis.
...
import redis
import bcrypt
The RedisGateway class has three methods.
...
class RedisGateway:
def __init__(self):
...
def cache_user(self, username, password):
...
def authenticate_user(self, username, password):
...
The _init_() method establishes a connection to the managed Redis database.
The cache_user() method saves the user's authentication details to the Redis server using the self.redis_client.set(username, password) function. Each user has a unique username that acts as a Redis key while the password is a Redis value.
The authenticate_user(...) method queries the Redis server to check if a key (hashed_password) named with the given username exists using the if self.redis_client.exists(username): statement. If the user's password is available from the Redis server, the authenticate_user(...) function returns True. Otherwise, the function returns False.
The RedisGateway class is now ready. You can import and use the class in other Python files using the following syntax:
import redis_gateway
rg = redis_gateway.RedisGateway()
... = pg.authenticate_user(username, password)
rg.cache_user(username, pg.hashed_password)
Follow the next step to finish coding your application.
The final step is creating an entry point to the sample application. This guide uses a main.py file as the application's start-up file. Follow the steps below to create the file:
Open a new main.py file in a text editor.
$ nano main.py
Enter the following information into the main.py file.
import http.server
from http import HTTPStatus
import socketserver
import json
import base64
import postgresql_gateway
import redis_gateway
class httpHandler(http.server.SimpleHTTPRequestHandler):
def do_GET(self):
authHeader = self.headers.get('Authorization').split(' ');
username, password = base64.b64decode(authHeader[1]).decode('utf8').split(':')
self.send_response(HTTPStatus.OK)
self.send_header('Content-type', 'application/json')
self.end_headers()
pg = postgresql_gateway.PostgresqlGateway()
rg = redis_gateway.RedisGateway()
data = dict()
if rg.authenticate_user(username, password) == True:
products = pg.get_products()
data = {'authenticated_by' : 'Redis Server', 'data': products}
else:
if pg.authenticate_user(username, password) == True:
rg.cache_user(username, pg.hashed_password)
products = pg.get_products()
data = {'authenticated_by' : 'PostgreSQL Server', 'data': products}
else:
data = {'error': 'Authentication failed.'}
resp = json.dumps(data, indent = 4, separators = (',', ': '))
self.wfile.write(bytes(resp + '\r\n', "utf8"))
httpServer = socketserver.TCPServer(('', 8080), httpHandler)
print("HTTP server started at port 8080...")
try:
httpServer.serve_forever()
except KeyboardInterrupt:
httpServer.server_close()
print("The server is stopped.")
Save and close the main.py file.
main.py file explained:The import section declares the HTTP server (http.server, HTTPStatus, and socketserver), json, base64, postgresql_gateway, and redis_gateway libraries.
import http.server
from http import HTTPStatus
import socketserver
import json
import base64
import postgresql_gateway
import redis_gateway
...
The httpHandler is an HTTP handler class for the application with one do_GET(self) method. This method fires when a user sends a GET request to the application. The do_GET method outputs a JSON output.
class httpHandler(http.server.SimpleHTTPRequestHandler):
def do_GET(self):
...
resp = json.dumps(data, indent = 4, separators = (',', ': '))
self.wfile.write(bytes(resp + '\r\n', "utf8"))
The do_GET() method declares the two custom PostgreSQL and Redis libraries that you created earlier using the following syntax.
pg = postgresql_gateway.PostgresqlGateway()
rg = redis_gateway.RedisGateway()
The main logic of the application lies in the following code.
...
if rg.authenticate_user(username, password) == True:
products = pg.get_products()
data = {'authenticated_by' : 'Redis Server', 'data': products}
else:
if pg.authenticate_user(username, password) == True:
rg.cache_user(username, pg.hashed_password)
products = pg.get_products()
data = {'authenticated_by' : 'PostgreSQL Server', 'data': products}
else:
data = {'error': 'Authentication failed.'}
...
The rg.authenticate_user(username, password) == True: logic queries the Redis server to check whether the user's details are already cached. If the function returns True, the logic calls the products = pg.get_products() to output the products from the PostgreSQL database.
If the user's details are not found on the Redis server, the if pg.authenticate_user(username, password) == True: logic looks for the user's credentials from the PostgreSQL database. If the user details are correct, the logic calls the rg.cache_user(username, pg.hashed_password) to cache the user's details to the Redis server for other calls and then runs the pg.get_products() function to output the products from the PostgreSQL database.
The statements {'authenticated_by' : 'Redis Server', 'data': products} and {'authenticated_by' : 'PostgreSQL Server', 'data': products} allow you to identify how the user authenticates to the application. This is for demonstration purposes only, and you can remove the authenticated_by values in a production environment.
The statement below starts a web server that listens for incoming connections on port 8080 and declares the httpHandler function as the handler function.
...
httpServer = socketserver.TCPServer(('', 8080), httpHandler)
print("HTTP server started at port 8080...")
try:
httpServer.serve_forever()
except KeyboardInterrupt:
httpServer.server_close()
print("The server is stopped.")
Your application is now ready for testing.
The final step is installing all the third-party libraries required by the application and testing the authentication logic. Follow the steps below to complete those steps:
Install the Python pip package.
$ sudo apt install -y python3-pip
Use the pip package to install the psycopg2 module. For testing and development, use the binary package (psycopg2-binary). However, in a production environment, consider using the psycopg2 package.
$ pip install psycopg2-binary
Output.
...
Successfully installed psycopg2-binary-2.9.5
Install the redis module for Python.
$ pip install redis
Output.
...
Successfully installed async-timeout-4.0.2 packaging-21.3 pyparsing-3.0.9 redis-4.3.5
Install the bcrypt module for Python.
$ pip install bcrypt
Output.
...
Successfully installed bcrypt-4.0.1
Use the python3 command to run the application.
$ python3 main.py
Output.
HTTP server started at port 8080...
Establish another SSH connection to your server and issue the following Linux curl commands to send two GET requests to the application.
john_doe:
$ curl -X GET -u john_doe:EXAMPLE_PASSWORD http://localhost:8080/
$ curl -X GET -u john_doe:EXAMPLE_PASSWORD http://localhost:8080/
mary_smith:
$ curl -X GET -u marysmith:EXAMPLEPASSWORD_2 http://localhost:8080/
$ curl -X GET -u marysmith:EXAMPLEPASSWORD_2 http://localhost:8080/
Note the following outputs. In the first output, the authenticated_by value reads PostgreSQL Server. However, in the second request, the authenticated_by value reads Redis Server.
Output 1.
...
{
"authenticated_by": "PostgreSQL Server",
"data": [
{
"product_id": "1",
"product_name": "1L FOUNTAIN DRINKING WATER",
"retail_price": "2.55"
},
{
"product_id": "2",
"product_name": "PINK COTTON BUDS",
"retail_price": "4.85"
},
{
"product_id": "3",
"product_name": "WINE GLASS",
"retail_price": "9.75"
}
]
}
Output 2.
...
{
"authenticated_by": "Redis Server",
"data": [
{
"product_id": "1",
"product_name": "1L FOUNTAIN DRINKING WATER",
"retail_price": "2.55"
},
{
"product_id": "2",
"product_name": "PINK COTTON BUDS",
"retail_price": "4.85"
},
{
"product_id": "3",
"product_name": "WINE GLASS",
"retail_price": "9.75"
}
]
}
Your application logic is working as expected.
This guide uses Vultr's managed Redis and PostgreSQL databases to speed up authenticating a Python application on Ubuntu 20.04 server. Use this guide's sample source code files to scale your application on your next Python project.
Read more guides about the Redis server by following the links below: