Supported Databases

  • Any recent version of PostgreSQL
  • Any recent version of MySQL
  • Any recent version of MS SQL
  • Certain SQL based features of Dynamo DB
  • Any recent versions of SQL Lite

Clusters

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

Report cluster strategy

Choose a cluster strategy when designing a report

  • The 'balance' option will pick a connection from a cluster to run a query allowing queries to be balanced.
  • The 'consolidate' option will run the query on every connection in a cluster and append the results.
  • The 'distribute' option will run the query on every connection in a cluster show each result separate.

Connection string

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

Engine options are specific configurations that is applied separate from the connection string. Probably you'll never use this.

Access groups

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.

Balance queries

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.

Cross platform SQL macros

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.

  • s.current_time() - 'HH24:MI:SS'
  • s.current_date() - 'YYYY-MM-DD'
  • s.datetime(item) - 'YYYY-MM-DD HH24:MI:SS'
  • s.time(item) - 'HH24:MI:SS'
  • s.now() - Database current date and time representation

Example select statement

select ‘{s.current_date()}’ as example_current_date, ‘{s.current_time()}’ as example_current_time

NOTE the use of curly braces and quotes.

Adding a connection

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.

Secrets

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:

Docker config:

  Environment:
    - DB_PASSWORD=hello-secret
    - DB_HOSTNAME=secret.server.com

Connection string:

postgresql://postgres:${DB_PASSWORD}@${DB_HOSTNAME}:5432/database

Errors and rotating secrets

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.

DB_ENVIRONMENT_UPDATER

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

Python program

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 no DB_ENVIRONMENT_UPDATER is set.

NOTE: boto3==1.34.49 package is installed into the Secure Reporter container

Database connection configuration settings

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