Retrieving information from AWS Secrets Manager is currently on the roadmap, but in the meantime this technique can be used as a workaround. This approach can also be used to retrieve data using an application from various sources and return it to Data Virtuality extending its functionality.

Please note, at the time of this writing, this approach is not available for SaaS. Python can be executed from within SaaS, but for security reason, loading Python packages is prohibited. And this solution requires the external package AWS SDK Boto to be loaded.

This article assumes Data Virtuality Platform is installed on Linux, and the latest AWS CLI and AWS SDK are installed. 

The AWS SDK supports several programming languages, however, this article will use Python 3.10.

This article consists of four parts:

1. Prerequisites
2. Configuration of AWS credentials
3. Placement of Python script
4. Creation of view in Data Virtuality SQL
5. Conclusion

Please note, this article uses long term credentials for illustration purposes. AWS supports other methods. Please see the following articles for more information:

• AWS Command Line Interface

• Best practices for managing AWS access keys

1. Prerequisites

This article will not cover the installation of the prerequisites. Nor will it cover the configuration of AWS Secrets. These topics are covered on many sites on the Internet.

• AWS Secrets manager configured.
• Data Virtuality Platform installed on Windows or Linux.
• Installation of Python 3.10+
• Installation of AWS CLI and AWS SDK for Python 3.

2. Configuration of AWS credentials

When installing Data Virtuality Platform on Linux, the default location is /opt/datavirtuality/. /opt/datavirtuality/ is also the HOME directory for the datavirtuality user account.

Switch to the datavirtuality user account using

sudo su datavirtuality

Then run the AWS CLI to configure your credentials. This example will use AWS Access Key ID and AWS Secret Access Key.

aws configure

 The AWS CLI will prompt for the necessary values. The values below are for illustration purposes. Use the values provided when configuring the IAM account.

datavirtuality@ubuntu-22:~$ aws configureAWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY Default region name [None]: us-west-2 Default output format [None]: json

The AWS CLI will create a folder and files at the following location: /opt/datavirtuality/.aws

3. Placement of Python script

The first task is to create as new folder for the Python scripts and set the permissions to allow access to the datavirtuality user account.

sudo mkdir /opt/datavirtuality/python-scripts/sudo chown datavirtuality:datavirtuality /opt/datavirtuality/python-scripts/

The following Python script was generated when creating the secret in AWS Secrets. This file was placed inside the folder /opt/datavirtuality/python-scripts/.

# Use this code snippet in your app.# If you need more information about configurations# or implementing the sample code, visit the AWS docs:# https://aws.amazon.com/developer/language/python/import boto3from botocore.exceptions import ClientErrordef get_secret():    secret_name = "prod/App/Test"    region_name = "us-east-1"    # Create a Secrets Manager client    session = boto3.session.Session()    client = session.client(        service_name='secretsmanager',        region_name=region_name    )    try:        get_secret_value_response = client.get_secret_value(            SecretId=secret_name        )    except ClientError as e:        # For a list of exceptions thrown, see        # https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_GetSecretValue.html        raise e    # Decrypts secret using the associated KMS key.    secret = get_secret_value_response['SecretString']    # Your code goes here.    print(secret)get_secret()

The next step is to assure the file permissions are correct.

cd /opt/datavirtuality/python-scripts/sudo chmod 644 aws-secrets-example.py sudo chown datavirtuality:datavirtuality aws-secrets-example.py

The file permissions should look like this:

root@ubuntu-22:/opt/datavirtuality/python-scripts# ls -alFtotal 12drwxr-xr-x  2 datavirtuality datavirtuality 4096 jul 11 21:27 ./drwxr-xr-x 11 root root                     4096 jul 11 21:25 ../-rw-r--r--  1 datavirtuality datavirtuality  994 jul 11 21:27 aws-secrets-example.py

Test the script by switching to the datavirtuality user account and executing the script.

sudo su datavirtualitypython3 aws-secrets-example.py

The script if successful will print the following text:

{"test-key":"super-secret-value"}

4. Creation of view in Data Virtuality SQL

The next step is to execute the Python script and parse the JSON. Use the following script to achieve this:

create view views.python_example aswith cte as ( SELECT     x.exitCode,     x.stdOut,     JSONTOXML('root', x.stdOut) as stdOutXmlData,     x.stdErr FROM (         CALL SYSADMIN.execExternalProcess (             command => 'python3'             ,args => ARRAY ('/opt/python-scripts/aws-secrets-example.py')         )     ) as x)select --"xmlTable.idColumn", "xmlTable.test-key"from cte cross join XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root/test-key' PASSING JSONTOXML('root', cte.stdOut) COLUMNS  --"idColumn" FOR ORDINALITY, "test-key" STRING  PATH '.' ) "xmlTable";;

Executing this view will return the secret from AWS Secrets.

5. Conclusion

This article assumed several things for the sake of simplicity. The first being the use of the credentials to access AWS Secrets Manager. Long term credentials were used, but there are other more secure options. The reader is encouraged to review the article Best practices for managing AWS access keys.

The Python script was intentionally written to return JSON. This was an arbitrary choice. The data could have been returned in any format. The difference would have been in the SQL code needed to parse the data.

Another assumption made in this article is the use of Python. The AWS SDK supports many programming languages. The solution demonstrated here can be implemented in any of the languages supported by the AWS SDK.

This approach is not limited to accessing AWS Secrets Manager. This approach can be applied to a variety of situations. For example, an application that only has an SDK interface and does not have a Rest API.

I hope you enjoyed this article.

• aws-secrets-example.py

  1 KB Download

• views.python_example.sql

  38 Bytes Download