Authenticate a Python Application with Vultr Managed Databases for PostgreSQL and Redis

by cobra_admin
January 2, 2023
113

Introduction

Authentication 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.

Prerequisites

To follow this guide:

Deploy an Ubuntu 20.04 server.
Create a non-root sudo user.
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

1. Set Up a Sample Database

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.

2. Create a PostgreSQL Database Class

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.

The `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.

3. 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.

The `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.

4. Create the Application’s Entry Point

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.

The `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

Introduction Authentication 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…

Introduction Authentication 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…

Authenticate a Python Application with Vultr Managed Databases for PostgreSQL and Redis

AI Powered Search with Python and Milvus Vector Database

Cobra Softwares Blog