Each connection has an assigned cluster name
Clusters join up any number of databases
When a query is executed the cluster name is used to get a connection
A connection string defines how the Python database library finds and uses the connection. Certain environment variables can be used in the connection string and will be applied dynamically to establish the connection. Each type of database has a particular connection format. Use the connection builder button to format a connection string.
Database connections need to be defined to allow queries against remote databases. Database connection strings are used
to connect. When defining a connection string any value from the environment can be accessed by using
${ENV_NAME}
. Example:
postgresql://postgres:${DB_PASSWORD}@${DB_HOSTNAME}:5432/database
Engine options are specific configurations that is applied separate from the connection string. Probably you'll never use this.
All connections can be used by any report. Use a report to control access as there is not method for adding access options to a connection.
All reports are 'balance' strategy by default.
The 'balance' reporting option will pick a connection from a cluster to run a query allowing queries to be balanced. For this to be effective at least two connections for a cluster will be needed. The query will randomly pick an active connection from the cluster used in a report. In the long run each connection should be used equally. The connections do not need to be the same type.
Any failing connection to a database is removed from the selection rotation for a default of five minutes.
Prophund Reporting has a few SQL macros that allow easier ways to use the same SQL on different servers.
These are used by surrounding the macro with double curly braces.
For those macros with 'item' this is the column or value to be converted to a formatted string.
select ‘{s.current_date()}’ as example_current_date, ‘{s.current_time()}’ as example_current_time
Click on + Add
to start adding a connection.
Either enter the details if you know how or use the Connection Builder
button to be guided through the process.
Option | Usage |
---|---|
New name | name of the connection, must be unique |
Connection string | SQLAlchemy string that defines the connection details |
Connection Builder | Guided dialog for creating a connection string |
Type | Choose the database technology |
Cluster | Choose a cluster name that this connection is part of |
Submit | Creat the connection |
Cancel | Close the new connection window |
Test Connection | Test that the details connect to the database. |
Secure Reporter can be configured to hold all passwords and other credentials secret from report editors and admins.
Add any secret to an environment variable and then use that variable in the connection string of a database.
Example:
Environment:
- DB_PASSWORD=hello-secret
- DB_HOSTNAME=secret.server.com
postgresql://postgres:${DB_PASSWORD}@${DB_HOSTNAME}:5432/database
When a report raises any error the existing database connections are dropped and new ones generated. This behaviour can be used to automatically rotate secrets.
When a connection error happens you can run a Python
script to update any stored credentials. Either in the server
environment or as a file.
Set the name of the script file using the DB_ENVIRONMENT_UPDATER
environment variable.
The script will run in the server’s process environment.
Example updating from AWS secrets manager:
Update the DB_PASSWORD
environment variable of a DB connection string of:
postgresql://postgres:${DB_PASSWORD}@${DB_HOSTNAME}:5432/database
import boto3 # already installed
import os
import json
ssm = boto3.client('ssm')
secretsmanager = boto3.client('secretsmanager')
parameter = ssm.get_parameter(Name="/your/parameter/name", WithDecryption=True)
json_secret = secretsmanager.get_secret_value(SecretId=parameter['Parameter']['Value'])
secret = json.loads(json_secret['SecretString'])
os.environ['DB_PASSWORD'] = secret['password']
NOTE: the AWS secrets methods from
aws_secrets/get_aws_secrets.py
are used if noDB_ENVIRONMENT_UPDATER
is set.NOTE: boto3==1.34.49 package is installed into the Secure Reporter container
value | description |
---|---|
DB_CONNECTIONS_CACHE_SECONDS | Number of seconds to keep a connection in the cache, default 3600 |
DB_READY_RETRIES | number of times to retry a failing connection, default (5) |
DB_READY_SLEEP_SECONDS | number of seconds to wait before retry after a failed connection |
DB_ENVIRONMENT_UPDATER | file location of a Python environment updating script |