sqlalchemy-sotero 0.0.3

Python SQLAlchemy Dialect for Sotero JDBC driver.

Sotero JDBC Dialect for SQLAlchemy

Sotero JDBC Driver integrates with Sotero Protect Platform to selectively encrypt/decrypt sensitive data stored in relational databases.

Installation

Installing the dialect is straightforward::

python3 -m pip install sqlalchemy-sotero

Supported drivers

• PostgreSQL

Pre-Requisite

In order to authenticate the JDBC driver client application with Sotero Platform, you should have access to a Sotero Client Credentials file. If you don't have this file already, please follow the instructions below.

• Login to Sotero Main API URL using Sotero admin username and password to get an access token
• POST a request to <SOTERO_API_URL>/appclients/generate endpoint with the access token in the authorization header and the body containing the name of your application.

Authorization: Bearer <the-access-token>

• Request body should contain the name of the application:

{
    "name":"Enter Your JDBC Application Name"
}

• The response will contain client_id and private_key:

{
    "client_id": "6f5010fc-9e13-41f8-b483-88527658bc81",
    "private_key": "MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCyKiGZq1mzXyqMYTPDNXfxiDJSk/yvNJX58Cd9A5QSEh6MOOu5LEcBig1e9jROGFpn+TqrycjNp4jEckTvcC1UjCfZ+o8Lxh6lMMW9leV4cr1r6ONiRs5Vrisv1tOBVkz+m4AqARjqcgejcM/iV5dIhZkm2OH1s00gmsLmqf7LfAmJl6tTkw2P7CW0nCWg4RGjcUYKmr43vViX1oqnO5uwxyXoZiM1cam2c7KrjYWs52cSrInkWfgWwwcMNf6vnGykNIgZPz3jf64h+rsiMZRz3Havs8NKSy8kSVFAmA1sIvzhDgOD/jRyfP2zdXjuy5qXMPQsfEA0w1nTmSL9xvGRAgMBAAECggEATMNkWL6AVo2BWpyi3c/SzwlcjUHf1Gl22QqFKRL6oFKYQNhhkBYovdwKaMjxvlg106iJv7c="
}

• Save the above response json in a file. This credentials file is required while using the Sotero JDBC driver

Usage

Set a CLASSPATH environment variable for the Sotero driver and the driver for your database

export CLASSPATH=<path>/sotero-jdbc-driver-1.2.0.jar:<path>/postgresql-42.3.1.jar

or in Python application

os.environ['CLASSPATH'] = "<path>/sotero-jdbc-driver-1.2.0.jar:<path>/postgresql-42.3.1.jar"

PostgreSQL:

from sqlalchemy import create_engine

os.environ['CLASSPATH'] = "<path>/sotero-jdbc-driver-1.2.0.jar:<path>/postgresql-42.3.1.jar"

url = f'sotero+postgres://{username}:{password}@{sotero-api-url}?creds_file={creds-file-path}&dataset={sotero-dataset-id}'

conn = create_engine(url)

Note: In S4 platform should point to your tenant API URL instead of the main API URL.

Driver Options

Sotero driver options are specified as key=value pairs separated by &.

Basic options

• creds_file=<creds-file-path> => JSON file containing the client_id and private_key
• dataset=<sotero-dataset-id> => Id of a dataset configured in the Sotero platform. The target driver class and JDBC url will be derived based on the dataset configuration
• client_user=<dataset-userid> => check the decrypt permissions using this value instead of the target database user id

Controlling the driver behavior

By default, Sotero driver will encrypt the data inserting in to the database and optionally decrypt data retrieving from the database if the user has decrypt permissions. This behavior can be changed by specifying the optional mode parameter

• mode=encrypt  => perform encryption while querying non-encrypted database, no decryption is performed
• mode=none        => perform no encryption or decryption
• mode=protect => default behavior

Testing

Read more information on deploying the package here

python3 -m venv venv
source venv/bin/activate
pip install -U pip setuptools twine wheel JayDeBeApi

python setup.py sdist
twine upload --repository-url https://test.pypi.org/legacy/ dist/*
pip install -i https://testpypi.python.org/pypi  sqlalchemy-sotero --no-build-isolation --extra-index-url=https://test.pypi.org/simple/

Deploy the package

twine upload dist/sqlalchemy_sotero-0.0.2.tar.gz