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

Updated on December 1, 2022

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

5. Test the Application Logic

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 mary_smith:EXAMPLE_PASSWORD_2  http://localhost:8080/
 $ curl -X GET -u mary_smith:EXAMPLE_PASSWORD_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.

Conclusion

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:

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

Introduction

Prerequisites

1. Set Up a Sample Database

2. Create a PostgreSQL Database Class

The `postgresql_gateway.py` file explained:

3. Create a Redis Database Class

The `redis_gateway.py` file explained:

4. Create the Application's Entry Point

The `main.py` file explained:

5. Test the Application Logic

Conclusion

Products

Features

Marketplace

Resources

Company

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

Introduction

Prerequisites

1. Set Up a Sample Database

2. Create a PostgreSQL Database Class

The postgresql_gateway.py file explained:

3. Create a Redis Database Class

The redis_gateway.py file explained:

4. Create the Application's Entry Point

The main.py file explained:

5. Test the Application Logic

Conclusion

The `postgresql_gateway.py` file explained:

The `redis_gateway.py` file explained:

The `main.py` file explained: