database

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

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:

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:

  1. Update the package information index.$ sudo apt update
  2. 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
  3. 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
  4. Ensure you get the following password prompt.Password for user vultradmin:
  5. Enter the password for the managed PostgreSQL user and press ENTER to proceed. Then, verify the following output.defaultdb=>
  6. Enter the following command to create a sample my_company database.defaultdb=> CREATE DATABASE my_company; Output.CREATE DATABASE
  7. 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=>
  8. 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
  9. 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
  10. 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)
  11. 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
  12. 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
  13. 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
  14. 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)
  15. Log out from the managed PostgreSQL server.my_company=> \q
  16. 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:

  1. Create a project directory to separate your source code from system files.$ mkdir project
  2. Switch to the new project directory.$ cd project
  3. Open a new posgresql_gateway.py file in a text editor.$ nano postgresql_gateway.py
  4. 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
  5. Save and close the postgresql_gateway.py file.

The postgresql_gateway.py file explained:

  1. 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 ...
  2. The PostgresqlGateway class has three methods.class PostgresqlGateway: def __init__(self): ... def get_products(self): ... def authenticate_user(self, username, password): ...
  3. The _init_() method establishes a database connection to the PostgreSQL database when you instantiate the class.
  4. The get_products(...) method queries the products table to retrieve a list of products from the database.
  5. 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.
  6. 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.

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…

Leave a Reply

Your email address will not be published. Required fields are marked *