--- date: Generated on 2026-10-06 title: Sysadmin Guide --- # Sysadmin Welcome to the installation guide for BAO Analytics Platform (AP from now on). This document is intended for system administrators who will be setting up and maintaining the environment required to run AP. AP requires a Linux operating system. An Ubuntu LTS version is the recommended Linux distribution. The installation guide assumes *Ubuntu Linux* as the operating system and the availability of the *systemd* process and service manager. AP supports a variety of public cloud providers, data storage and data warehouses. It can be deployed in a public cloud environment, and on Linux-based, on-premise server environments. ## Data platform AP uses a data storage provider to ingest and store raw data files from multiple sources in their native format. The following data infrastructure environments are supported. AP can be fully installed and operated in on-premise environments as well as in leading public cloud environments. Infrastructure Data storage Data warehouse ----------------------------- ---------------------- ----------------- Amazon Web Services (AWS) Amazon S3 ClickHouse Amazon Web Services (AWS) Amazon S3 Amazon Redshift Amazon Web Services (AWS) Amazon S3 Snowflake Microsoft Azure Azure Blob Storage Snowflake Microsoft Azure Azure Blob Storage SQL Database Microsoft Azure Azure Blob Storage Synapse Google Cloud Platform (GCP) Google Cloud Storage BigQuery On-premise Local filesystem ClickHouse On-premise Local filesystem PostgreSQL On-premise Local filesystem SQL Server ![AP infrastructure providers](../assets/images/sysadmin/ap_infrastructure_providers.png) ### Data storage AP supports and integrates with the following providers for data storage. - Amazon S3 - Azure Blob Storage - Google Cloud Storage - Local filesystem *Amazon S3* and *Azure Blob Storage* are scalable, highly durable and cost-effective public cloud storage services that allow users to store and retrieve any amount of data from anywhere on the web. These services integrate well with the vast ecosystem of data services in the AWS and Azure public clouds respectively. *Local filesystem* refers to using a regular server with attached disk storage. This approach leverages the file system of the server and data files are stored in regular directories. As high-speed reading is not a priority, HDDs (Hard Disk Drives) are a cost-effective and feasible option, as opposed to more expensive and faster SSDs (Solid State Drives). ### Data warehouses AP supports and integrates with the following data warehouses. - ClickHouse - Amazon Redshift - Azure SQL Database - Azure Synapse - Google BigQuery - Microsoft SQL Server - PostgreSQL - Snowflake In on-premise environments, *ClickHouse* is the preferred data warehouse due to its open source license, well-documented server installation and high-performance data ingestion and data querying. ![AP data warehouses](../assets/images/sysadmin/ap_data_warehouses.png) ## Middleware Below is a summary of the necessary middleware components that your system needs to ensure optimal performance and compatibility. AP is built and deployed on open source licensed middleware only. When using ClickHouse or PostgreSQL as the data warehouse, no proprietary middleware technology is required. - **OpenJDK 17:** A robust and widely-used open-source implementation of the Java Platform which provides the runtime environment necessary for running Java applications. The AP backend microservices are written in Java 17. - **PostgreSQL:** Version 14 or later. A powerful, open-source relational database management system that offers advanced features such as complex queries, foreign keys, triggers, and up-to-date compliance with SQL standards. The AP backend microservices use PostgreSQL databases for persistence of metadata and operational data. - **nginx:** A high-performance, open-source HTTP server and reverse proxy that is essential for handling web traffic, load balancing, and serving static content efficiently. AP uses nginx for secure routing of HTTP requests. - **Redis:** An in-memory, open-source key-value store that provides lightning-fast data retrieval, making it ideal for caching and supporting real-time analytics, session management, and message brokering. AP uses redis for caching of expensive operations, such as creating data table profiles and data previews. - **Apache Pulsar:** An open-source distributed messaging and streaming platform that enables reliable, scalable, and low-latency data streaming and message queueing, suitable for event-driven applications. AP uses Apache Pulsar for asynchronous event communication between microservices. - **ClickHouse:** A high-performance, open-source columnar database management system designed for online analytical processing (OLAP) and real-time data analytics at scale. AP utilizes ClickHouse as the preferred data warehouse for analytical data processing and querying. The AP software architecture follows the microservice architectural pattern, where the platform is made up of several microservices, each with their own key responsibilities. - **API Gateway:** The API gateway service is responsible for routing API requests to the appropriate backend service. It manages authentication and user sessions. - **Identity:** The identity service is responsible for security, authentication, authorization, and for user and client management. - **Data pipeline:** The data pipeline service is the main component of AP and is responsible for data catalog, data pipelines, data quality checks, views, destinations and workflows. - **Web UI:** The UI is composed of two web apps written in React and JavaScript: The analytics platform web app and the user management web app. AP is extended by several optional microservices. - **DHIS2 Superset gateway:** Authentication, dashboard management and gateway between DHIS2 and Apache Superset. - **DHIS2 text query:** Natural text query translation to DHIS2 API communication. - **Pyserve:** Python code execution and formatting. AP is deployed as JAR files, managed by the systemd system and process manager. ## Operating system AP runs on a Linux-based, open source licensed operating system. The recommended Linux distribution is Ubuntu. Using the latest LTS version is recommended. ## Deployment The following options are available for automated deployment of AP. - **Ansible:** Deployment with Ansible playbook and Runbook automation. - **Docker Compose:** Container-based deployment with Docker containers and Docker Compose. The following deployment environments are supported for AP. - On-premise servers - Amazon Web Services (AWS) - Microsoft Azure - Google Cloud Platform (GCP) AP can be deployed both on virtual machines and physical servers, also referred to as *bare metal*. The on-premise support means that AP can be deployed and hosted in government or private local data centers in a country, without a dependency on cloud technology. The multi-cloud support means that AP can be deployed in any of the major public cloud provider environments, allowing organizations to utilize their existing cloud subscriptions. AP can also be deployed in a hybrid model, for example where the AP microservices are deployed in the cloud, while the data store and data warehouse are deployed on on-premise servers. # Hosting requirements AP can be deployed on-premise or in the AWS and Azure public clouds. It can be deployed entirely on a single virtual machine (VM), or by deploying the various services, such as the AP software services, ClickHouse data warehouse and Apache Superset data exploration tool, on separate, individual VMs. For an on-premise deployment on a single VM, the following minimum requirements apply. ## Hardware Minimum hardware specifications are described below. - Virtual machine or physical server - 32 GB RAM - 16 CPU - 500 GB SSD storage ## Operating system - Linux operating system - Ubuntu LTS version is strongly recommended ## Network - 300 Kbps network bandwidth per user - 1 Gbps network transfer between servers (if more than one) ## Components The following aspects are required for a production-grade installation. - Internet connection with public IP address - Remote SSH server access - Terminal root access - Domain name - SSL certificate - SMTP email server - Backup strategy for local and off-site backups - Monitoring of uptime and alerting for downtime - Vulnerability scan and security hardening ## Considerations - A fast SSD is required, and NVMe is preferred for the ClickHouse data warehouse, with read/write throughput of 250 MB/s or more. - The storage requirements depend on the data size. Allocate 2x to 3x the size of the raw data. - ClickHouse scales well with hardware resources, so the more provisioned memory and CPU the better. - All middleware and services can either be installed on a single VM, or individually on separate VMs. ## High-availability setup The following specifications are recommended for large-scale workloads to ensure high availability and high processing throughput. The various AP middleware and services will be deployed on multiple virtual machines within a private network. ------------------------------------------------------------------------------ VM Services RAM CPU Storage ---- --------------------------------------------------- ----- ----- --------- 1 AP backend services, nginx, PostgreSQL, redis 16 GB 8 256 GB 2 ClickHouse 32 GB 16 512 GB 3 Apache Superset 8 GB 2 64 GB 4 Python, pyserve, R, RServe 4 GB 2 32 GB ------------------------------------------------------------------------------ # Middleware installation This guide covers installation of required middleware for the BAO Analytics Platform (AP). This guide assumes Ubuntu Linux 22.04 LTS is used as the operating system and that the reader has some familiarity with Linux and terminals. The text editor used is `nano`. Please consider the following: - There are many approaches to hosting a Java-based application such as AP. This guide outlines one of them. - Topics including security hardening and backup strategy are important but beyond the scope of this guide. - There may be several managed cloud middleware offerings available. This guide is focused on the on-premise installation scenario. ## OpenJDK 17 Start by updating the operating system packages. ``` sh sudo apt update && sudo apt upgrade -y ``` Install OpenJDK version 17. ``` sh sudo apt install -y openjdk-17-jdk ``` ## PostgreSQL 14 Install PostgreSQL version 14 or later. Note that version 14 and later versions of PostgreSQL are supported. The installation of PostgreSQL is well covered in online installation guides. ``` sh sudo apt install -y postgresql postgresql-client ``` !!! note The `bao-data-pipeline` application service requires the `postgresql-client` package to be available in the server environment. The PostgreSQL service is enabled on boot by default after installation. Verify the status of the PostgreSQL process. ``` sh sudo systemctl status postgresql ``` Set the PostgreSQL authentication method to `md5`. ``` sh sudo nano /etc/postgresql/14/main/pg_hba.conf ``` Make sure the authentication method is set to `md5` for localhost connections, typically by modifying the last two lines. ``` properties host all all 127.0.0.1/32 md5 host all all ::1/128 md5 ``` Adjust performance settings by creating a new configuration file. ``` sh nano 10-perf.conf ``` ``` properties # PostgreSQL performance settings max_connections = 100 shared_buffers = 768MB work_mem = 16MB maintenance_work_mem = 256MB temp_buffers = 16MB effective_cache_size = 2GB checkpoint_completion_target = 0.8 wal_writer_delay = 1s random_page_cost = 1.1 max_locks_per_transaction = 1024 track_activity_query_size = 8192 ``` Set owner and permissions for the configuration file, and move it to the PostgreSQL configuration directory. ``` sh sudo chown postgres:postgres 10-perf.conf ``` ``` sh sudo chmod 644 10-perf.conf ``` ``` sh sudo mv 10-perf.conf /etc/postgresql/14/main/conf.d ``` Restart PostgreSQL to have changes take effect. ``` sh sudo systemctl restart postgresql ``` ## nginx Install nginx. ``` sh sudo apt install -y nginx ``` The nginx service is enabled on boot by default after installation. Verify the status of the nginx process. ``` sh sudo systemctl status nginx ``` Configure nginx by creating a file `analytics-platform.conf` and placing it in the nginx `sites-available` directory. ``` sh sudo nano /etc/nginx/sites-available/analytics-platform.conf ``` Configure nginx with SSL and static web app UI served from Amazon S3. - SSL and certificate configuration are left out, and should be configured appropriately. - The `apigateway`, `web` and `identity` services are defined as *upstreams* and referred to later in the config. - The manager and user web apps are served from Amazon S3. - Additional security hardening may be appropriate in a production environment. - Update `server_name` from `ap.mydomain.org` to match your environment. ``` nginx # Upstream upstream apigateway { server 127.0.0.1:8085; } upstream web { server 127.0.0.1:8081; } upstream identity { server 127.0.0.1:8086; } # Redirect HTTP to HTTPS server { listen [::]:80; listen 80; server_name ap.mydomain.org; return 301 https://$host$request_uri; } # HTTPS server server { listen [::]:443 ssl; listen 443 ssl; server_name ap.mydomain.org; # Compression gzip on; gzip_types application/json application/javascript text/javascript text/css text/plain; # Includes for the default hostname include default.d/*.conf; # Includes for the default hostname under HTTPS include default.d/*-https.inc; # https://developer.mozilla.org/en-US/docs/Web/HTTP/X-Frame-Options add_header X-Frame-Options DENY; # https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP add_header Content-Security-Policy "frame-ancestors 'none';"; # Enable Strict Transport Security (HSTS) for https add_header Strict-Transport-Security "max-age=31536000" always; # Root URL rewrite to login page location = / { return 301 http://$host/manager/; } # Proxy settings proxy_set_header host $http_host; proxy_set_header x-forwarded-host $host; proxy_set_header x-real-ip $remote_addr; proxy_set_header x-forwarded-for $proxy_add_x_forwarded_for; proxy_set_header x-forwarded-proto $scheme; proxy_set_header x-forwarded-port $server_port; proxy_buffer_size 128k; proxy_buffers 8 128k; proxy_busy_buffers_size 256k; # Proxy forwards # Login check and logout location /login_check { proxy_pass http://identity/login_check; } location /session_logout { proxy_pass http://identity/session_logout; } # App location ~* ^/(app|doc|node_modules) { rewrite ^/(.*) /$1 break; proxy_pass http://web; } # Manager web app to Amazon S3 location /manager { proxy_intercept_errors on; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_hide_header x-amz-id-2; proxy_hide_header x-amz-request-id; proxy_pass http://bao-cloud-manager-prod.s3-website-us-east-1.amazonaws.com/manager; } # User web app to Amazon S3 location /users { proxy_intercept_errors on; proxy_set_header x-real-ip $remote_addr; proxy_set_header x-forwarded-for $proxy_add_x_forwarded_for; proxy_hide_header x-amz-id-2; proxy_hide_header x-amz-request-id; proxy_pass http://bao-cloud-manager-prod.s3-website-us-east-1.amazonaws.com/users; } # Increased max upload size and timeout for file upload API endpoints location /api/dataPipelines { proxy_pass http://apigateway/api/dataPipelines; client_max_body_size 2048M; proxy_read_timeout 600; proxy_connect_timeout 600; proxy_send_timeout 600; } # API requests to API gateway service location /api { proxy_pass http://apigateway/api; } } ``` Enable the server configuration by creating a symlink to the nginx `sites-enabled` directory. ``` sh sudo ln -s /etc/nginx/sites-available/analytics-platform.conf \ /etc/nginx/sites-enabled/analytics-platform.conf ``` Remove the default server configuration file. ``` sh sudo rm /etc/nginx/sites-enabled/default ``` Restart nginx to make changes take effect. sudo systemctl restart nginx ## Redis Install redis server. ``` sh sudo apt install -y redis-server ``` The redis service is enabled on boot by default after installation. Verify the status of the redis process. ``` sh sudo systemctl status redis ``` Edit the redis configuration file. ``` sh sudo nano /etc/redis/redis.conf ``` ## Apache Pulsar ### Installation Install Apache Pulsar using the binary distribution. First, download and extract Pulsar using `wget`. Alternatively, visit the Apache Pulsar downloads [page](https://pulsar.apache.org/download/). You may want to check for a later version of Apache Pulsar. ``` sh PULSAR_VER="3.3.3" wget https://archive.apache.org/dist/pulsar/pulsar-${PULSAR_VER}/apache-pulsar-${PULSAR_VER}-bin.tar.gz tar xvfz apache-pulsar-${PULSAR_VER}-bin.tar.gz mv apache-pulsar-${PULSAR_VER} apache-pulsar ``` Set root as owner and make binary files executable. ``` sh sudo chown root:root -R apache-pulsar ``` ``` sh sudo chmod +x apache-pulsar/bin/pulsar* ``` ### Configuration *Optional:* Adjust memory usage by modifying `pulsar_env.sh`. ``` sh sudo nano apache-pulsar/conf/pulsar_env.sh ``` Set the `PULSAR_MEM` variable and specify memory usage, adjusted to available server resources. ``` sh PULSAR_MEM=${PULSAR_MEM:-"-Xms2g -Xmx2g -XX:MaxDirectMemorySize=2g"} ``` *Optional:* Set a new port for the HTTP server to avoid occupying port 8080. ``` sh sudo nano apache-pulsar/conf/standalone.conf ``` Set the `webServicePort` property to `8098`. ``` sh webServicePort=8098 ``` Move the directory to a suitable installation location. ``` sh sudo mv apache-pulsar /var/lib/apache-pulsar ``` Create a `systemd` service file called `apache-pulsar.service` for running Pulsar in standalone mode. ``` sh nano apache-pulsar.service ``` ``` properties [Unit] Description = Apache Pulsar [Service] ExecStart = /var/lib/apache-pulsar/bin/pulsar standalone -nss [Install] WantedBy = multi-user.target ``` !!! note The `-nss` flag is set due to Pulsar bug [5668](https://github.com/apache/pulsar/issues/5668). Set owner and permissions for the init script. ``` sh sudo chown root:root apache-pulsar.service ``` ``` sh sudo chmod 644 apache-pulsar.service ``` Move the init script to the `systemd` directory. ``` sh sudo mv apache-pulsar.service /etc/systemd/system/ ``` Reload the `systemd` daemon. ``` sh sudo systemctl daemon-reload ``` Enable Pulsar on startup. ``` sh sudo systemctl enable apache-pulsar ``` Start Pulsar. ``` sh sudo systemctl start apache-pulsar ``` Verify that the Pulsar service is running. ``` sh sudo systemctl status apache-pulsar ``` View the Pulsar log. ``` sh sudo journalctl -f -u apache-pulsar -n 400 ``` You should now have Pulsar running on port 6650. To run Pulsar manually. ``` sh sudo /var/lib/apache-pulsar/bin/pulsar standalone ``` *Troubleshooting:* If Apache Pulsar fails to start due to local data corruption, a solution is to stop the service, delete the local data directory and start the service. Local data will be lost, however, Apache Pulsar topics are not persisted and the data directory will be recreated on next start. /var/lib/apache-pulsar/data ## Compression Install the Zstandard compression tool. ``` sh sudo apt install zstd ``` ## Extra Shorthand notation for installing packages in standard Ubuntu repositories. ``` sh sudo apt update && \ sudo apt upgrade -y && \ sudo apt install -y openjdk-17-jdk postgresql-14 nginx redis-server zstd unzip ``` # Analytics Platform installation This guide covers the installation of the BAO Analytics Platform (AP) software. The AP backend server is composed of the following services. - API gateway - Identity - Data pipeline The *key* and *port* of each service are described below. The *key* refers to the name used in configuration directories and files. The *port* refers to the default port on which the service will listen for incoming requests. Name Key Port --------------- ------------------- ------ API gateway bao-api-gateway 8085 Identity bao-identity 8086 Data pipeline bao-data-pipeline 8084 ## User Create an operating system user for running the AP services. This guide uses `bao-admin` as username, though any valid username can be used. The user has no password. For security reasons, avoiding password-based login and instead using SSH key-based login is strongly recommended. ``` sh sudo adduser --disabled-password --shell /bin/bash bao-admin ``` For security reasons, the AP services should not run as a privileged user. It may however be practical to allow *sudo without password*: ``` sh sudo usermod -aG sudo bao-admin sudo echo "bao-admin ALL=(ALL) NOPASSWD:ALL" > /etc/sudoers.d/50-ap-users ``` Create the SSH directory and add the authorized keys file. Add public keys for users which should have access. ``` sh mkdir ~/.ssh touch ~/.ssh/authorized_keys ``` Set directory and file permissions. ``` sh chmod 700 ~/.ssh chmod 600 ~/.ssh/authorized_keys ``` ## SSH Carefully confirm that public key based authentication to the server is successful, i.e. login without specifying a password. Disable password-based authentication for enhanced security. Create an SSH daemon config file. ``` sh sudo nano /etc/ssh/sshd_config.d/90-no-passwd-auth.conf ``` Add the following properties. ``` properties PubkeyAuthentication yes PasswordAuthentication no PermitRootLogin prohibit-password ``` Restart the SSH daemon to have the changes take effect. ``` sh sudo systemctl restart ssh ``` ## JAR files Each service is available as a JAR file. The JAR files should be installed at the following locations. ---------------------------------------------------------------------------- JAR file File location ------------------------- -------------------------------------------------- bao-api-gateway.jar /var/lib/bao-api-gateway/bao-api-gateway.jar bao-identity.jar /var/lib/bao-identity/bao-identity.jar bao-data-pipeline.jar /var/lib/bao-data-pipeline/bao-data-pipeline.jar ---------------------------------------------------------------------------- Create the directories manually and make `bao-admin` the owner. ``` sh sudo mkdir /var/lib/bao-api-gateway sudo mkdir /var/lib/bao-identity sudo mkdir /var/lib/bao-data-pipeline ``` ``` sh sudo chown bao-admin:bao-admin /var/lib/bao-api-gateway sudo chown bao-admin:bao-admin /var/lib/bao-identity sudo chown bao-admin:bao-admin /var/lib/bao-data-pipeline ``` Place the JAR files in the respective directories and make `bao-admin` the owner. ``` sh sudo cp bao-api-gateway.jar /var/lib/bao-api-gateway sudo cp bao-identity.jar /var/lib/bao-identity sudo cp bao-data-pipeline.jar /var/lib/bao-data-pipeline ``` ``` sh sudo chown bao-admin:bao-admin /var/lib/bao-api-gateway/bao-api-gateway.jar sudo chown bao-admin:bao-admin /var/lib/bao-identity/bao-identity.jar sudo chown bao-admin:bao-admin /var/lib/bao-data-pipeline/bao-data-pipeline.jar ``` ## Systemd The *systemd* service manager is used to manage the service processes. Each service has a corresponding systemd service file and a configuration file. The systemd service files are specified below. The memory allocations should be adjusted to the available server resources. The systemd service files should be located in the `/etc/systemd/system` directory. ---------------------------------------------------------------------------------- Systemd file File location ---------------------------------- ----------------------------------------------- bao-api-gateway.service /etc/systemd/system/bao-api-gateway.service bao-identity.service /etc/systemd/system/bao-identity.service bao-data-pipeline.service /etc/systemd/system/bao-data-pipeline.service ---------------------------------------------------------------------------------- The `bao-api-gateway.service` systemd service file. ``` sh sudo nano /etc/systemd/system/bao-api-gateway.service ``` ``` sh [Unit] Description = AP API Gateway [Service] Environment = "JAVA_HOME=/usr/lib/jvm/java-1.17.0-openjdk-amd64" Environment = "JAVA_OPTS=-Xms512M -Xmx1G" ExecStart = /bin/bash -c '${JAVA_HOME}/bin/java ${JAVA_OPTS} -jar /var/lib/bao-api-gateway/bao-api-gateway.jar' User = bao-admin [Install] WantedBy = multi-user.target ``` The `bao-identity.service` systemd service file. ``` sh sudo nano /etc/systemd/system/bao-identity.service ``` ``` sh [Unit] Description = AP Identity [Service] Environment = "JAVA_HOME=/usr/lib/jvm/java-1.17.0-openjdk-amd64" Environment = "JAVA_OPTS=-Xms1G -Xmx2G" ExecStart = /bin/bash -c '${JAVA_HOME}/bin/java ${JAVA_OPTS} -jar /var/lib/bao-identity/bao-identity.jar' User = bao-admin [Install] WantedBy = multi-user.target ``` The `bao-data-pipeline.service` systemd service file. ``` sh sudo nano /etc/systemd/system/bao-data-pipeline.service ``` ``` sh [Unit] Description = AP Data Pipeline [Service] Environment = "JAVA_HOME=/usr/lib/jvm/java-1.17.0-openjdk-amd64" Environment = "JAVA_OPTS=-Xms2G -Xmx6G" ExecStart = /bin/bash -c '${JAVA_HOME}/bin/java ${JAVA_OPTS} -jar /var/lib/bao-data-pipeline/bao-data-pipeline.jar' User = bao-admin LimitNOFILE = 65536 LimitNPROC = 65536 [Install] WantedBy = multi-user.target ``` !!! note "GCP deployments" For deployments using Google Cloud Platform (GCP BigQuery), add the following `Environment` line to the `[Service]` section of `bao-data-pipeline.service`. It points to a Workload Identity Federation (WIF) external account configuration. ```sh Environment = "GOOGLE_APPLICATION_CREDENTIALS=/opt/bao-data-pipeline/gcp-credentials.json" ``` Place the credentials file at the path above and set the correct ownership and permissions. ```sh sudo cp gcp-credentials.json /opt/bao-data-pipeline/gcp-credentials.json sudo chown bao-admin:bao-admin /opt/bao-data-pipeline/gcp-credentials.json sudo chmod 600 /opt/bao-data-pipeline/gcp-credentials.json ``` Reload systemd and restart the service after updating the service file. ```sh sudo systemctl daemon-reload sudo systemctl restart bao-data-pipeline ``` To enable the services on boot, invoke the following commands. ``` sh sudo systemctl enable bao-api-gateway sudo systemctl enable bao-identity sudo systemctl enable bao-data-pipeline ``` To start a service using systemd, after the JAR files and configuration files are installed, invoke the following command. ``` sh sudo systemctl start bao-data-pipeline ``` To stop a service using systemd, invoke the following command. ``` sh sudo systemctl stop bao-data-pipeline ``` ## PostgreSQL The AP identity and data pipeline services use PostgreSQL for persistence. Note that PostgreSQL contains metadata for data pipelines, views and more, while analytical data is stored in a data warehouse such as ClickHouse. Note that the names given to the databases and users can be adjusted as preferred, and the following names are suggestions. Database name Database user Encoding ----------------- ----------------- ---------- baoidentity baoidentity UTF-8 baodatapipeline baodatapipeline UTF-8 ### Users Create the required users. Switch to the `postgres` user. Connect to PostgreSQL with the `psql` CLI. ``` sh sudo su postgres ``` ``` sh psql ``` Create users for the identity and data pipeline services. Replace `mypassword1` and `mypassword2` with strong passwords and take note of them in a secure manner. ``` sql create user baoidentity with password 'mypassword1'; ``` ``` sql create user baodatapipeline with password 'mypassword2'; ``` ### Databases Create databases for the identity and data pipeline services. Set encoding to UTF-8. ``` sql create database baoidentity with owner baoidentity encoding 'utf8'; ``` ``` sql create database baodatapipeline with owner baodatapipeline encoding 'utf8'; ``` Exit the CLI with `Ctrl+D` and then return to the `bao-admin` user with `exit`. ## Configuration Each service has a corresponding configuration file. -------------------------------------------------------------------------------- Config file File location -------------------------------- ----------------------------------------------- bao-api-gateway.conf /opt/bao-api-gateway/bao-api-gateway.conf bao-identity.conf /opt/bao-identity/bao-identity.conf bao-data-pipeline.conf /opt/bao-data-pipeline/bao-data-pipeline.conf -------------------------------------------------------------------------------- ### API gateway Create the `bao-api-gateway.conf` configuration file for the API gateway service with `chmod 600`. ``` sh sudo mkdir /opt/bao-api-gateway ``` ``` sh sudo nano /opt/bao-api-gateway/bao-api-gateway.conf ``` ``` properties # ---------------------------------------------------------- # Service to URI mapping # ---------------------------------------------------------- # Identity service URI service.identity = http://localhost:8086/ # Data pipeline service URI service.datapipeline = http://localhost:8084/ # ---------------------------------------------------------- # CORS # ---------------------------------------------------------- # Allowed origins for CORS cors.allowed_origins = https://localhost:3000, \ https://localhost:9000 ``` ``` sh sudo chown bao-admin:bao-admin /opt/bao-api-gateway/bao-api-gateway.conf ``` ``` sh sudo chmod 600 /opt/bao-api-gateway/bao-api-gateway.conf ``` ### Identity Create the `bao-identity.conf` configuration file for the identity service. Adjust usernames and passwords to your environment. ``` sh sudo mkdir /opt/bao-identity ``` ``` sh sudo nano /opt/bao-identity/bao-identity.conf ``` ``` properties # ---------------------------------------------------------- # Database connection # ---------------------------------------------------------- # JDBC connection URL connection.url = jdbc:postgresql://127.0.0.1/baoidentity # JDBC connection username connection.username = baoidentity # JDBC connection password (confidential) connection.password = xxxx # ---------------------------------------------------------- # Redis # ---------------------------------------------------------- # Redis hostname / IP address redis.hostname = 127.0.0.1 # Redis port, optional, default: 6379 redis.port = 6379 # Redis password, optional redis.password = # ---------------------------------------------------------- # Apache Pulsar # ---------------------------------------------------------- # Pulsar hostname / IP address pulsar.service_url = pulsar://127.0.0.1:6650 # Pulsar TLS authentication plugin, optional, TLS only # pulsar.tls.auth.plugin = # Pulsar TLS certificate path, optional, optional, TLS only # pulsar.tls.trusts.certs.file.path = # Pulsar TLS certificate file, optional, TLS only # pulsar.tls.cert.file = # Pulsar TLS key file, optional, TLS only # pulsar.tls.key.file = # ---------------------------------------------------------- # System # ---------------------------------------------------------- # System hostname / base URL system.base_url = https://analytics.mydomain.org # System application title system.application_title = Analytics Platform # Log email invitation URLs, disable in prod (debugging only) # system.user_invite.logging = off # Name of issuer for MFA entries system.mfa_issuer = Analytics Platform # ---------------------------------------------------------- # Email # ---------------------------------------------------------- # From address for outgoing emails email.from.address = noreply@mydomain.org # ---------------------------------------------------------- # SMTP # ---------------------------------------------------------- # SMTP hostname or IP address smtp.host = 127.0.0.1 # SMTP port, default: 587 smtp.port = 587 # SMTP TLS smtp.tls = true # SMTP username smtp.user = myuser # SMTP password smtp.password = xxxx ``` ``` sh sudo chown bao-admin:bao-admin /opt/bao-identity/bao-identity.conf ``` ``` sh sudo chmod 600 /opt/bao-identity/bao-identity.conf ``` ### Data pipeline Create the `bao-data-pipeline.conf` configuration file for the data pipeline service. Adjust usernames and passwords to your environment. ``` sh sudo mkdir /opt/bao-data-pipeline ``` ``` sh sudo nano /opt/bao-data-pipeline/bao-data-pipeline.conf ``` ``` properties # ---------------------------------------------------------- # Database connection # ---------------------------------------------------------- # JDBC connection URL connection.url = jdbc:postgresql://127.0.0.1/baodatapipeline # JDBC connection username connection.username = baodatapipeline # JDBC connection password (confidential) connection.password = xxxx # ---------------------------------------------------------- # Redis # ---------------------------------------------------------- # Redis hostname / IP address redis.hostname = 127.0.0.1 # Redis port, optional, type: int4, default: 6379 redis.port = 6379 # Redis password, optional redis.password = # ---------------------------------------------------------- # Apache Pulsar # ---------------------------------------------------------- # Pulsar hostname / IP address pulsar.service_url = pulsar://127.0.0.1:6650 # Pulsar TLS authentication plugin, optional, TLS only # pulsar.tls.auth.plugin = # Pulsar TLS certificate path, optional, optional, TLS only # pulsar.tls.trusts.certs.file.path = # Pulsar TLS certificate file, optional, TLS only # pulsar.tls.cert.file = # Pulsar TLS key file, optional, TLS only # pulsar.tls.key.file = # ---------------------------------------------------------- # System # ---------------------------------------------------------- # System hostname / base URL system.base_url = https://analytics.mydomain.org # Retain temporary data files, default: 'off', values: 'off' or 'on' (debugging only) # system.retain_temp_files = off # Sample size for dataset column type detection, type: int4, default: 500k system.max_sample_size = 500000 # Email address to send alert messages for errors system.error.alert_email = alerts@mydomain.org # Scheduling of data load jobs, default: 'on', values: 'on' or 'off' # system.data_load.scheduling = on # ---------------------------------------------------------- # Blobstore (local filesystem only) # ---------------------------------------------------------- # Root directory for local file system blob storage blobstore.root_dir = /var/lib/bao-data-pipeline/data # ---------------------------------------------------------- # Data loading # ---------------------------------------------------------- # Minimum number of rows to enable partitioning of data files, type: int8, default: 100m # dataload.min_partition_rows = 100000000 # Number of data partitions for data loading, type: int4, default: 10 # dataload.partitions = 10 # Maximum number of concurrent tasks for data loading per workflow, type: int4, default: 4 # dataload.max_concurrent_tasks = 4 # ---------------------------------------------------------------------- # Google Gemini [Optional] # ---------------------------------------------------------------------- # Google Gemini API key google.gemini.api_key = # ---------------------------------------------------------------------- # OpenAI [Optional] # ---------------------------------------------------------------------- # OpenAI API key openai.api_key = ``` ``` sh sudo chown bao-admin:bao-admin /opt/bao-data-pipeline/bao-data-pipeline.conf ``` ``` sh sudo chmod 600 /opt/bao-data-pipeline/bao-data-pipeline.conf ``` ### GenAI AP uses generative AI providers for conversion of natural text to SQL and scripts. The following providers are supported and can be configured in the `bao-data-pipeline.conf` configuration file. #### Google Gemini To enable Google Gemini, provide a Google Gemini API key. ``` properties # Google Gemini API key google.gemini.api_key = ``` #### OpenAI GPT To enable OpenAI, provide an OpenAI API key. ``` properties # OpenAI API key openai.api_key = ``` #### Local engines Local GenAI providers which provide an OpenAI completions compatible API are supported, including Ollama and vLLM. To enable a local GenAI engine, specify `local` as provider and provide base URL (required) and API key (optional). ``` properties # GenAI platform provider, values: 'google', 'openai', 'local' genai.provider = local # GenAI base URL genai.base_url = http://localhost:11434 # GenAI API key, optional genai.api_key = ``` ## Encryption The data pipeline service encrypts all secrets at the database level, and requires an encryption key to be provided. !!! tip "Note" Store the encryption key in a secure manner! The encryption key should be stored in a secure and confidential way. If the key is lost, the encrypted database content cannot be recovered. If the key is exposed, an attacker could use the key to decrypt the database secrets. The *Tink* Java library is used for encryption. An encryption key can be generated using the Tink CLI called *Tinkey*. The encryption key file name is `bao-data-pipeline-key.json` and the content is in JSON format. Download Tinkey from the following URL. https://developers.google.com/tink/tinkey-overview Uncompress the tarball in a suitable location. Generate the key with the following command. The key will be printed to the console in JSON format. ``` sh ./tinkey create-keyset --key-template AES128_GCM --out-format json ``` Create and store the encryption key file in the data pipeline configuration directory. ``` sh sudo nano /opt/bao-data-pipeline/bao-data-pipeline-key.json ``` Example encryption key file. ``` json { "primaryKeyId": 0000000000, "key": [ { "keyData": { "typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey", "value": "{secret}", "keyMaterialType": "SYMMETRIC" }, "status": "ENABLED", "keyId": 0000000000, "outputPrefixType": "TINK" } ] } ``` ``` sh sudo chown bao-admin:bao-admin /opt/bao-data-pipeline/bao-data-pipeline-key.json ``` ``` sh sudo chmod 600 /opt/bao-data-pipeline/bao-data-pipeline-key.json ``` ## Data cache When AP ingests data from various data sources, it caches data in the form of data files, which are temporarily stored on the filesystem of the server where AP is deployed. Depending on the data sources, significant storage capacity is required. However, data is deleted when a data load process completes, meaning the data volume will not grow over time. The data cache directory name is `data-pipeline`, and it is located below the configuration directory. /opt/bao-data-pipeline/data-pipeline Create the directory manually. ``` sh CACHE_DIR="/opt/bao-data-pipeline/data-pipeline" sudo mkdir $CACHE_DIR sudo chown bao-admin:bao-admin $CACHE_DIR sudo chmod 755 $CACHE_DIR ``` ## Data storage !!! tip "Note" This section applies only to on-premise server data storage environments. When deploying AP in on-premise server environments, take care to provision a storage device (disk or SSD) with appropriate capacity. 500GB is a reasonable starting point. Separate storage devices may be provisioned for the AP software and for the data storage. The configuration property `blobstore.root_dir` in `bao-data-pipeline.conf` defines the root directory for data storage on the local filesystem. It allows for storing data on a dedicated storage device (disk or SSD). The default location is `/var/lib/bao-data-pipeline/data`. Create the `data` directory manually. ``` sh DATA_DIR="/var/lib/bao-data-pipeline/data" sudo mkdir $DATA_DIR sudo chown bao-admin:bao-admin $DATA_DIR sudo chmod 755 $DATA_DIR ``` In the following configuration section, the blob store *container name* will be specified per client (tenant). In an on-premise environment, create a directory manually to represent the container using the specified container name below the root data directory. This guide uses `bao-ap-client-main` as the container name for the default client, though any container name can be used. The directory should be created in the following location. /var/lib/bao-data-pipeline/data/bao-ap-client-main Create the directory manually. The data and client directories should be located on a storage medium with appropriate capacity. ``` sh CLIENT_DIR="/var/lib/bao-data-pipeline/data/bao-ap-client-main" sudo mkdir $CLIENT_DIR sudo chown bao-admin:bao-admin $CLIENT_DIR sudo chmod 755 $CLIENT_DIR ``` The data storage location can be defined with the `blobstore.root_dir` property in the `bao-data-pipeline.conf` configuration file. ## Read me The following content is convenient to maintain in a `readme.md` file. ``` sh nano readme.md ``` ``` sh # Analytics Platform ## Redis redis-cli -h 127.0.0.1 ## Apache Pulsar sudo systemctl status apache-pulsar sudo systemctl restart apache-pulsar sudo journalctl -n 500 -f -u apache-pulsar ## Nginx sudo systemctl status nginx sudo systemctl restart nginx sudo tail -f /var/log/nginx/access.log ## Apache Superset sudo systemctl status apache-superset sudo systemctl restart apache-superset sudo journalctl -n 500 -f -u apache-superset ## AP service status sudo systemctl status bao-api-gateway sudo systemctl status bao-identity sudo systemctl status bao-data-pipeline ## AP service restart sudo systemctl restart bao-api-gateway sudo systemctl restart bao-identity sudo systemctl restart bao-data-pipeline ## AP service logging sudo journalctl -n 500 -f -u bao-api-gateway -u bao-identity -u bao-data-pipeline -o cat sudo journalctl -n 500 -f -u bao-data-pipeline ``` ## Debug To adjust the log level for the Java services, append the following parameter to the `ExecStart` property in the appropriate systemd service file. The `com.bao` part of the parameter value refers to the package of the classes for which the logging level will apply. ``` properties ExecStart = /bin/bash -c '${JAVA_HOME}/bin/java ${JAVA_OPTS} -jar /var/lib/bao-identity/bao-identity.jar --logging.level.com.bao=debug' ``` # ClickHouse installation ## Requirements ClickHouse version 25.9 or later is required. ClickHouse version 26.2 or later is recommended. ## Installation Consult the official ClickHouse production installation documentation for Ubuntu / Debian Linux [here](https://clickhouse.com/docs/en/install). ClickHouse features a client-server architecture, where the client and server are installed as separate packages. - ClickHouse provides a default user named `default` with a password set during the installation process. This guide uses `admin` as the password. - ClickHouse provides a default database named `default`. - The main directory for ClickHouse server is `/etc/clickhouse-server`. Set up the Debian package repository. ``` sh sudo apt-get install -y apt-transport-https \ ca-certificates curl gnupg curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | \ sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] \ https://packages.clickhouse.com/deb stable main" | \ sudo tee /etc/apt/sources.list.d/clickhouse.list sudo apt-get update ``` Install the `deb` packages for ClickHouse server and ClickHouse client. Provide a password for the `default` user during installation. ``` sh sudo apt install -y clickhouse-server clickhouse-client ``` Enable ClickHouse server on startup. ``` sh sudo systemctl enable clickhouse-server ``` Start ClickHouse server using systemd. ``` sh sudo systemctl start clickhouse-server ``` Verify the status of the ClickHouse process. ``` sh sudo systemctl status clickhouse-server ``` ## Access control By default, SQL-driven access control and account management is disabled in ClickHouse. Access control can be configured by adding a new configuration file to the directory `/etc/clickhouse-server/users.d`. To enable SQL-driven access control for the `default` user, and to add the `default` and `baoanalytics` users to the default profile, add the following configuration file. sudo nano /etc/clickhouse-server/users.d/users.xml Specify the following content in XML format. Note that the `access_management` property enables SQL-driven access control. ``` xml 1 1 1 1 3 default ``` Set file ownership. ``` sh sudo chown clickhouse:clickhouse /etc/clickhouse-server/users.d/users.xml ``` Restart the service to have the changes take effect. ``` sh sudo systemctl restart clickhouse-server ``` ### Password type Verify that the password type is set to `sha256_password`. ``` sh sudo cat /etc/clickhouse-server/config.xml | grep "" ``` ### Database and admin user To create a new database called `baoanalytics`, enter the ClickHouse client. ``` sh clickhouse-client ``` Execute the following SQL statement to create the database. ``` sql create database baoanalytics; ``` Create a new admin user called `baoanalytics` with password `mypassword`, associated with the default profile, with the following SQL statement. Replace `mypassword` with a strong password, and take note of it securely. ``` sql create user baoanalytics identified by 'mypassword' profile default; ``` ``` sql grant all on *.* to baoanalytics with grant option; ``` Verify that the user is associated with the default profile. ``` sql show create user baoanalytics; ``` Note the importance of associating the `baoanalytics` user with the `default` profile, which ensures that the configuration settings in a following section take effect. After an admin user is created, SQL-driven access control can be removed from the default user for security purposes. ## Performance tuning Memory allocations, caching and concurrency should be adjusted to the available server resources. Set server-wide global settings by adding the following file and content. ``` sh sudo nano /etc/clickhouse-server/config.d/performance.xml ``` Specify the following content in XML format. The size unit is bytes. Audit logging is disabled as it typically generates very large amounts of data. ``` xml 4294967296 104857600 100 100 0 1073741824 ``` Set file ownership. ``` sh sudo chown clickhouse:clickhouse /etc/clickhouse-server/config.d/performance.xml ``` Set profile-specific settings by editing the existing `users.xml` file previously created and adding the following content. sudo nano /etc/clickhouse-server/users.d/users.xml ``` xml 4294967296 134217728 134217728 2 100000 100000 134217728 1 LZ4 ``` A convenience GB-to-byte mapping table is found below. GB Bytes ---- ------------- 1 1073741824 2 2147483648 4 4294967296 6 6442450944 8 8589934592 10 10737418240 12 12884901888 16 17179869184 Restart the service for changes to take effect. ``` sh sudo systemctl restart clickhouse-server ``` ## Logging View ClickHouse log file. sudo tail -n 500 -f /var/log/clickhouse-server/clickhouse-server.log View ClickHouse error log file. This log file is useful to inspect if ClickHouse fails to start after making changes to settings. sudo tail -n 500 -f /var/log/clickhouse-server/clickhouse-server.err.log View ClickHouse journal log. sudo journalctl -f -u clickhouse-server ## Query log Query log configuration is specified in the main ClickHouse config file `config.xml`. To enable query logging, modify the performance configuration file previously created. ``` sh sudo nano /etc/clickhouse-server/config.d/performance.xml ``` Replace `` with the following `` element to configure ClickHouse to only keep query log entries generated in the last 2 days. ``` xml .. event_date + interval 2 day .. ``` ClickHouse writes query log entries to table `query_log` in database `system`. If the `query_log` table already exists, invoke the following SQL command to update the time to live (TTL). ``` sql alter table system.query_log modify ttl event_date + interval 2 day; ``` Retrieve all information from the query log with a SQL query. ``` sql select * from system.query_log; ``` Retrieve relevant columns from the query log, ordered by query start time most recent first with a SQL query. ``` sql select query_start_time, user, type, query_duration_ms, read_rows, result_rows, memory_usage, query from system.query_log where type not in ('QueryStart') order by query_start_time desc limit 50 format vertical; ``` # Apache Superset installation This guide explains how to install Apache Superset using the `pip` package installer for Python on Ubuntu 22.04. The source installation is efficient in terms of required server memory resources, and is preferable in an on-premise environment. The guide assumes that a `bao-admin` user exists, and that PostgreSQL, Redis and nginx are installed. ## PostgreSQL Create a user and database for Superset. Switch to the postgres user and connect to PostgreSQL with the psql command-line tool. sudo su postgres psql Create the PostgreSQL user for Superset with a strong password. Take note of the password. ``` sh create user superset with password 'mypassword'; ``` Create the PostgreSQL database for Superset and exit the command-line tool. ``` sh create database superset with owner superset encoding 'utf8'; ``` *Optional:* Verify that PostgreSQL is running and the database is accessible as the `superset` user. ``` sh psql -d superset -U superset -h 127.0.0.1 ``` ## Python Install Python 3 and OS dependencies from the Ubuntu package repository. sudo apt update && sudo apt upgrade -y ``` sh sudo apt-get install build-essential libssl-dev libffi-dev python3-dev \ python3-venv python3-pip libsasl2-dev libldap2-dev libpq-dev ``` Verify the Python version. ``` sh python3 -V ``` Upgrade `pip`. ``` sh pip3 install --upgrade pip ``` Create an installation directory. ``` sh sudo mkdir -p /var/lib/apache-superset ``` ``` sh sudo chown bao-admin:bao-admin /var/lib/apache-superset ``` Create a virtual environment in the installation directory. ``` sh cd /var/lib/apache-superset ``` ``` sh python3 -m venv venv ``` Activate the virtual environment. ``` sh source venv/bin/activate ``` Verify the Python and pip versions in the virtual environment. ``` sh python -V ``` ``` sh pip -V ``` !!!note Do not change the location of the virtual environment after installation. ## Apache Superset ### Installation Install Apache Superset, Python packages and the ClickHouse database driver. ``` sh pip install apache-superset psycopg2 gunicorn gevent flask_cors pillow rich cachetools ``` ``` sh pip install clickhouse-connect ``` Other relevant driver packages, like Redshift and MS SQL, can also be installed if required. ``` sh pip install sqlalchemy-redshift pymssql ``` ### Configuration Generate a secret encryption key for the Superset config file. ``` sh openssl rand -base64 42 ``` Create a config file named `superset_config.py` in the root Superset installation directory with the following configuration content. Note that the config file should be located in the root directory, *not* the `venv` directory. ``` sh nano superset_config.py ``` Adjust the required configuration values to the environment. - Set `SECRET_KEY` to the previously generated secret key. - Set `` for the `SQLALCHEMY_DATABASE_URI` property to the previously created PostgreSQL password. ``` properties # Number of worker threads SUPERSET_WORKERS = 4 # Gunicorn server port SUPERSET_WEBSERVER_PORT = 8089 # Enable logging to file ENABLE_TIME_ROTATE = True # Encryption key SECRET_KEY = 'mykey' # Superset PostgreSQL metadata database connection SQLALCHEMY_DATABASE_URI = 'postgresql://superset:@localhost/superset' # Caching with Redis CACHE_CONFIG = { 'CACHE_TYPE': 'redis', 'CACHE_DEFAULT_TIMEOUT': 3600, 'CACHE_KEY_PREFIX': 'superset_results', 'CACHE_REDIS_URL': 'redis://localhost:6379/0', } # Flask-WTF flag for CSRF WTF_CSRF_ENABLED = False CSRF_ENABLED = False # Add endpoints that need to be exempt from CSRF protection WTF_CSRF_EXEMPT_LIST = [] # A CSRF token that expires in 1 year WTF_CSRF_TIME_LIMIT = 60 * 60 * 24 * 365 # Set this API key to enable Mapbox visualizations MAPBOX_API_KEY = '' # Enable CORS to allow embedded dashboards ENABLE_CORS = True ALLOW_ORIGINS = ['http://localhost:8089'] CORS_OPTIONS = { 'supports_credentials': True, 'allow_headers': ['*'], 'resources':['*'], 'origins': ALLOW_ORIGINS } # Disable CSP due to bug in Superset TALISMAN_ENABLED = False # Enable proxy headers to support nginx ENABLE_PROXY_FIX = True # Enable embedded dashboards FEATURE_FLAGS = { "EMBEDDED_SUPERSET": True, "DASHBOARD_RBAC": True, "EMBEDDABLE_CHARTS": True } # Dashboard embedding GUEST_ROLE_NAME = "Gamma" ``` Set ownership and permissions. ``` sh sudo chown bao-admin:bao-admin superset_config.py ``` ``` sh sudo chmod 644 superset_config.py ``` Initialize the PostgreSQL database and create an initial Superset admin user by invoking the following commands. Take note of the provided username and password. ``` sh # Set required environment variables export FLASK_APP=superset export SUPERSET_CONFIG_PATH=/var/lib/apache-superset/superset_config.py # Run database migrations superset db upgrade # Initialize database superset init # Create admin user superset fab create-admin ``` Take note of and store the admin password securely. *Optional:* Run Superset with Gunicorn to verify that the installation is working. ``` sh gunicorn -w 10 -k gevent -t 120 -b 127.0.0.1:8089 "superset.app:create_app()" ``` Deactivate the virtual environment. ``` sh deactivate ``` Superset will listen on localhost on port `8089`. ### Reset password The following commands can be used in the event of needing to reset the password for a user, e.g. the `bao-admin` user. ``` sh # Set required environment variables export FLASK_APP=superset export SUPERSET_CONFIG_PATH=/var/lib/apache-superset/superset_config.py # Reset password for admin user superset fab reset-password --username bao-admin ``` ### Upgrade To upgrade Superset when a new version is available, navigate to the Superset installation directory and activate the virtual environment. ``` sh source venv/bin/activate ``` Upgrade the Superset version by executing the command below. ``` sh pip install apache-superset --upgrade ``` Upgrade the database schema by running required migrations, if any. ``` sh # Set required environment variables export FLASK_APP=superset export SUPERSET_CONFIG_PATH=/var/lib/apache-superset/superset_config.py # Run database migrations superset db upgrade ``` Deactivate the virtual environment. ``` sh deactivate ``` Restart the `systemd` Superset service. ### Systemd Create a `systemd` service file for Superset called `apache-superset.service`. ``` sh nano apache-superset.service ``` ``` sh [Unit] Description = Apache Superset After = network.target [Service] Type = simple User = bao-admin Environment = 'FLASK_APP=superset' Environment = 'SUPERSET_CONFIG_PATH=/var/lib/apache-superset/superset_config.py' ExecStart = /var/lib/apache-superset/venv/bin/gunicorn -w 10 -k gevent -t 120 -b 127.0.0.1:8089 "superset.app:create_app()" Restart = on-failure RestartSec = 5s [Install] WantedBy=multi-user.target ``` Set ownership, permissions and move the init script to the `systemd` service directory. ``` sh sudo chown root:root apache-superset.service ``` ``` sh sudo chmod 644 apache-superset.service ``` ``` sh sudo mv apache-superset.service /etc/systemd/system/ ``` Reload the systemd daemon. ``` sh sudo systemctl daemon-reload ``` Enable Superset on boot. ``` sh sudo systemctl enable apache-superset ``` Start Superset. ``` sh sudo systemctl start apache-superset ``` View status. ``` sh sudo systemctl status apache-superset ``` View the logs. ``` sh sudo journalctl -n 500 -f -u apache-superset ``` ## nginx Configure nginx by creating a configuration file `apache-superset.conf`. Requests will be proxied to Gunicorn, which was set up previously. This guide assumes that SSL and certificates are configured. ``` sh nano apache-superset.conf ``` Configure a Superset server. - SSL and certificate configuration are left out, and should be configured appropriately. - Additional security hardening may be appropriate in a production environment. - Update `server_name` from `superset.mydomain.org` to match your environment. ``` nginx # Redirect HTTP to HTTPS server { listen [::]:80; listen 80; server_name superset.mydomain.org; access_log off; log_not_found off; return 301 https://$host$request_uri; } # HTTPS server server { listen [::]:443 ssl; listen 443 ssl; server_name superset.mydomain.org; # Proxy requests to Gunicorn on port 8089 location / { proxy_pass http://127.0.0.1:8089/; proxy_redirect off; proxy_set_header host $host; proxy_set_header x-real-ip $remote_addr; proxy_set_header x-forwarded-for $proxy_add_x_forwarded_for; proxy_set_header x-forwarded-proto $scheme; proxy_set_header x-forwarded-port $server_port; } } ``` Set ownership, permissions and move the config file to the correct location. ``` sh sudo chown root:root apache-superset.conf ``` ``` sh sudo chmod 644 apache-superset.conf ``` ``` sh sudo mv apache-superset.conf /etc/nginx/sites-available/ ``` Enable the server configuration by creating a symlink to the nginx `sites-enabled` directory. ``` sh sudo ln -s /etc/nginx/sites-available/apache-superset.conf \ /etc/nginx/sites-enabled/apache-superset.conf ``` Restart nginx to make changes take effect. ``` sh sudo systemctl restart nginx ``` ## Setup This section provides various tips for how to set up Apache Superset. ### ClickHouse setup This section assumes that ClickHouse has been installed on the same machine and configured per the instructions in the middleware installation guide. To set up a ClickHouse data warehouse connection: - Click **Database** in the top-right corner. - Under **Supported databases**, select **ClickHouse Connect (Superset)**. - In the basic form, specify the following values. - *Host:* 127.0.0.1 - *Port:* 8123 - *Database name:* baoanalytics - *Username:* baoanalytics - *Password:* *Use password from installation* - *Display name:* Analytics Platform - ClickHouse - In the advanced form, in the **Performance** tab, specify the following values. - *Chart cache timeout:* 900 (or adjust as preferred) - *Schema cache timeout:* 1 - *Table cache timeout:* 1 - Click **Finish**. ### Apache Superset setup Various considerations when configuring Apache Superset are described below. - **Database connection cache:** For databases, database schema and table metadata will by default be cached indefinitely. This means that if you add new schemas, tables and columns in the database after creating the database connection, they will not be reflected in the UI. Hence, the schema and table cache timeout values may be set to 1. - **Cache values:** For caching, do not specify a negative value such as -1 as Apache Superset will crash due to an internal bug. - **Chart cache:** For databases, charts will by default be cached indefinitely, which means it is important to set an appropriate chart cache timeout. - **Data sets cache:** When creating datasets, in the add new dataset screen, the list of schemas and tables in the respective drop-downs may not refresh properly. To force a refresh, click the **Force** icon next to each drop-down. - **User roles:** For user roles, ensure that the *Public* role does not have any permissions, and importantly does not have the *can read on dashboard* and *can read on chart* permissions. If granted, API requests to the `/api/v1/dashboards/` endpoint will return no dashboards, which prevents embedded dashboards from working properly. # DHIS2 Superset Gateway installation This guide covers the installation of the DHIS2 Superset Gateway service. The DHIS2 Superset Gateway is a backend service and API for connecting DHIS2 and Apache Superset. The gateway provides access control for DHIS2 external dashboards and access and guest tokens for embedded dashboards in Superset. External dashboards are stored in the DHIS2 data store using the DHIS2 data store API. The service *key* is `dhis2-superset-gateway`. The service *port* is `8092`. This guide assumes that a dedicated user for running the service called `bao-admin` exists. ## JAR file The service is available as a JAR file. The filename is `dhis2-superset-gateway.jar`. The JAR file should be installed in the following location. /var/lib/dhis2-superset-gateway/dhis2-superset-gateway.jar Create the directory manually and make `bao-admin` the owner. ``` sh sudo mkdir /var/lib/dhis2-superset-gateway ``` sudo chown bao-admin:bao-admin /var/lib/dhis2-superset-gateway Place the JAR file in the previously created directory and make `bao-admin` the owner. sudo cp dhis2-superset-gateway.jar /var/lib/dhis2-superset-gateway sudo chown bao-admin:bao-admin /var/lib/dhis2-superset-gateway/dhis2-superset-gateway.jar ## Systemd The *systemd* service manager is used to manage the service process. The systemd service file should be located in the `/etc/systemd/system` directory. /etc/systemd/system/dhis2-superset-gateway.service Create the systemd service file with the following content. ``` sh sudo nano /etc/systemd/system/dhis2-superset-gateway.service ``` ``` sh [Unit] Description = DHIS2 Superset Gateway service [Service] Environment = "JAVA_HOME=/usr/lib/jvm/java-1.17.0-openjdk-amd64" Environment="JAVA_OPTS=-Xms512M -Xmx1024M" ExecStart = /bin/bash -c '${JAVA_HOME}/bin/java ${JAVA_OPTS} -jar /var/lib/dhis2-superset-gateway/dhis2-superset-gateway.jar' User = bao-admin [Install] WantedBy = multi-user.target ``` To enable the service on boot, invoke the following command. ``` sh sudo systemctl enable dhis2-superset-gateway ``` ## Configuration The service is configured with a properties file called `dhis2-superset-gateway.conf`. The file should reside in the following location. /opt/dhis2-superset-gateway/dhis2-superset-gateway.conf Create a configuration file with the following content. ``` sh sudo mkdir /opt/dhis2-superset-gateway ``` ``` sh sudo nano /opt/dhis2-superset-gateway/dhis2-superset-gateway.conf ``` ``` properties # ---------------------------------------------------------- # DHIS2 # ---------------------------------------------------------- # Base URL to DHIS2 dhis2.base_url = https://dhis2.mydomain.org # ---------------------------------------------------------- # Superset # ---------------------------------------------------------- # Base URL to Superset superset.base_url = https://superset.mydomain.org # Username for Superset user account superset.username = myusername # Password for Superset user account (confidential) superset.password = xxxx # Enable instance-level row-level security for org unit hierarchy superset.rls.org_unit = off # Enable instance-level row-level security for specific datasets, none means all datasets superset.rls.datasets = # Enable require authority to manage Superset dashboards [temporary] superset.superset.auth.create_dashboard = off # ---------------------------------------------------------- # CORS # ---------------------------------------------------------- # Origins from which to allow CORS cors.allowed_origins = http://localhost:3000,\ http://localhost:9000, ``` ## Proxy The service provides API endpoints. To make the API endpoints accessible, an HTTP proxy must be set up. This is typically done by specifying a *location* block in *nginx*. In the nginx configuration file for DHIS2, immediately before the location block for DHIS2 itself, specify a location block for the service. ``` nginx server { # .. # DHIS2 Superset gateway location /superset-gateway/ { proxy_pass http://127.0.0.1:8092/superset-gateway/; proxy_set_header Host $host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto $scheme; proxy_set_header X-Forwarded-Port $server_port; } } ``` ## Logging The service uses the *journalctl* tool to view logs. To view log output, invoke the following command. ``` sh sudo journalctl -n 500 -f -u dhis2-superset-gateway ``` ## Start The service is started by invoking the following command. ``` sh sudo systemctl start dhis2-superset-gateway ``` ## Stop The service is stopped by invoking the following command. ``` sh sudo systemctl stop dhis2-superset-gateway ``` ## Status The service status is observed by invoking the following command. ``` sh sudo systemctl status dhis2-superset-gateway ``` # DHIS2 Text Query installation This guide covers the installation of the DHIS2 Text Query service. The DHIS2 Text Query is a backend service and API for conversational analytics and visualization for DHIS2. The service *key* is `dhis2-text-query`. The service *port* is `8094`. This guide assumes that a dedicated user for running the service called `bao-admin` exists. ## JAR file The service is available as a JAR file. The filename is `dhis2-text-query.jar`. The JAR file should be installed in the following location. /var/lib/dhis2-text-query/dhis2-text-query.jar Create the directory manually and make `bao-admin` the owner. ``` sh sudo mkdir /var/lib/dhis2-text-query ``` sudo chown bao-admin:bao-admin /var/lib/dhis2-text-query Place the JAR file in the previously created directory and make `bao-admin` the owner. sudo cp dhis2-text-query.jar /var/lib/dhis2-text-query sudo chown bao-admin:bao-admin /var/lib/dhis2-text-query/dhis2-text-query.jar ## Systemd The *systemd* service manager is used to manage the service process. The systemd service file should be located in the `/etc/systemd/system` directory. /etc/systemd/system/dhis2-text-query.service Create the systemd service file with the following content. ``` sh sudo nano /etc/systemd/system/dhis2-text-query.service ``` ``` sh [Unit] Description = DHIS2 Text Query service [Service] Environment = "JAVA_HOME=/usr/lib/jvm/java-1.17.0-openjdk-amd64" Environment="JAVA_OPTS=-Xms512M -Xmx1024M" ExecStart = /bin/bash -c '${JAVA_HOME}/bin/java ${JAVA_OPTS} -jar /var/lib/dhis2-text-query/dhis2-text-query.jar' User = bao-admin [Install] WantedBy = multi-user.target ``` To enable the service on boot, invoke the following command. ``` sh sudo systemctl enable dhis2-text-query ``` ## PostgreSQL The service requires a PostgreSQL database. The minimum PostgreSQL version is 14. ``` sh sudo su postgres ``` ``` sh psql ``` Create user. ``` sql create user dhis2textquery with password 'mypassword'; ``` Create database. ``` sql create database dhis2textquery with owner dhis2textquery encoding 'utf8'; ``` ## Configuration The service is configured with a properties file called `dhis2-text-query.conf`. The file should reside in the following location. /opt/dhis2-text-query/dhis2-text-query.conf Create a configuration file with the following content. ``` sh sudo mkdir /opt/dhis2-text-query ``` ``` sh sudo nano /opt/dhis2-text-query/dhis2-text-query.conf ``` ``` properties # ---------------------------------------------------------------------- # DHIS2 Text Query # ---------------------------------------------------------------------- # ---------------------------------------------------------------------- # Database connection # ---------------------------------------------------------------------- # Database connection URL connection.url = jdbc:postgresql:dhis2textquery # Database username connection.username = dhis2textquery # Database password (sensitive) connection.password = mypassword # ---------------------------------------------------------------------- # DHIS2 # ---------------------------------------------------------------------- # Base URL dhis2.base_url = https://dhis2.mydomain.org # ---------------------------------------------------------------------- # Google Gemini # ---------------------------------------------------------------------- # API key (confidential) google.gemini.api_key = xxxx # ---------------------------------------------------------------------- # CORS # ---------------------------------------------------------------------- # Origins from which to allow CORS cors.allowed_origins = http://localhost,\ http://localhost:3000 ``` ## Proxy The service provides API endpoints. To make the API endpoints accessible, an HTTP proxy must be set up. This is typically done by specifying a *location* block in *nginx*. In the nginx configuration file for DHIS2, immediately before the location block for DHIS2 itself, specify a location block for the service. ``` nginx server { # .. # DHIS2 text query location /text-query/ { proxy_pass http://127.0.0.1:8094/text-query/; proxy_set_header Host $host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto $scheme; proxy_set_header X-Forwarded-Port $server_port; } } ``` ## Logging The service uses the *journalctl* tool to view logs. To view log output, invoke the following command. ``` sh sudo journalctl -n 500 -f -u dhis2-text-query ``` ## Start The service is started by invoking the following command. ``` sh sudo systemctl start dhis2-text-query ``` ## Stop The service is stopped by invoking the following command. ``` sh sudo systemctl stop dhis2-text-query ``` ## Status The service status is observed by invoking the following command. ``` sh sudo systemctl status dhis2-text-query ``` # Analytics Platform configuration This guide covers the configuration of the BAO Analytics Platform (AP) software. ## Default client and user account AP features a multi-tenant architecture, which means at least one *client* (tenant) must be created in order to use the software. The first time AP is deployed and started, a default client and default user account will be inserted into the database. ### Default client Property Value ---------- ------------- UID WxMvtZb9eNP Code ADMIN Name Admin The default client can be renamed. Alternatively, a new client can be created and the default client can be removed. ### Default user account Property Value ---------- --------------- UID Xk6Gfr24Rj7 Username administrator Password Admin_1234 Name Admin !!! note After logging in, change the password of the default user from the profile menu using **Change password**. It is required to change the password of the default user account after logging in for the first time, and before making AP available on the network, as the default password is publicly known. ## Data pipeline configuration The configuration of the infrastructure/cloud provider, the data storage and the data warehouse for AP is referred to as the *data pipeline config*. The data pipeline config can be specified using the API or using the web UI. ### Environments The following infrastructure environments are supported. Infrastructure Data storage Data warehouse ---------------- ---------------------- ----------------- AWS Amazon S3 ClickHouse AWS Amazon S3 Amazon Redshift AWS Amazon S3 Snowflake Azure Azure Blob Storage SQL Database Azure Azure Blob Storage Synapse GCP Google Cloud Storage BigQuery On-prem Local filesystem ClickHouse On-prem Local filesystem SQL Server On-prem Local filesystem PostgreSQL Configuration of AWS and Azure cloud environments can be done in several ways and is well covered in online guides. It is hence considered outside the scope of this guide, which will address on-premise deployments using the local filesystem as blob store (data storage) and ClickHouse or PostgreSQL as the data warehouse. !!! note "GCP deployments" For GCP deployments, AP authenticates to Google Cloud Storage and BigQuery using Application Default Credentials (ADC). Set the `GOOGLE_APPLICATION_CREDENTIALS` environment variable in the `/etc/systemd/system/bao-data-pipeline.service` service file to the path of a Workload Identity Federation (WIF) external account configuration file. See the [installation guide](analytics-platform-installation.md) for details. In the data pipeline config, set `provider` to `GCP_BIGQUERY`. The `blobStoreConfig.identity` and `blobStoreConfig.credential` fields are not used for GCP and should be set to `"na"`. The `projectId` field must be set to the GCP project identifier. ### UID The UID format specification is as follows: - Is exactly 11 characters long - Contains only uppercase letters, lowercase letters and digits - Starts with a letter ### Properties The following properties are required for the data pipeline config. The `blobStoreConfig` and `dataWarehouseConfig` objects are required. The `publicHostname` property is optional and can be used in situations where AP should connect to the data warehouse using a local IP address, while external clients should connect using a public hostname. The `supersetConfig` object is optional, and refers to an integrated instance of Apache Superset. ------------------------------------------------------------------------------------------------ Property Description Value --------------------- ---------------------------------------------------- --------------------- client Client identifier UID provider Infrastructure provider, cloud provider/on-premise `AWS_REDSHIFT`, environment and data warehouse platform. `S3_CLICKHOUSE`, `AZURE_SQL_SERVER`, `AZURE_SYNAPSE`, `GCP_BIGQUERY`, `LOCAL_CLICKHOUSE`, `LOCAL_SQL_SERVER`, `LOCAL_POSTGRESQL` blobStoreConfig Configuration for blob store, i.e. data storage Object environment identity AWS: Access key. Azure: Storage account name. String On-prem: NA. credential AWS: Secret key. Azure: Storage account key. String On-prem: NA. container AWS: Bucket name. Azure: Container name. On-prem: String Root directory name. account Azure: Storage account name. AWS and on-prem: NA. String dataWarehouseConfig Data warehouse configuration Object hostname Data warehouse hostname String publicHostname Data warehouse public hostname (optional) String database Database name String username Data warehouse admin account username String password Data warehouse admin account password String iamRoleArn AWS: Redshift IAM role ARN. Azure and on-prem: NA. String supersetConfig Apache Superset configuration (optional) Object url Superset domain name String username Superset username String password Superset password String databaseId Superset AP database identifier Integer ------------------------------------------------------------------------------------------------ ### API configuration The data pipeline config can be configured using the API: ``` http POST /api/dataPipelineConfig ``` ``` properties Content-Type:application/json ``` The configuration payload in JSON format using local filesystem for data storage and ClickHouse as data warehouse can be defined as below. Value `na` refers to "not applicable". Under `blobStoreConfig`, field `bao-ap-client-main` refers to the directory below the data storage directory on the local file system. ``` json { "client": "TKcmL3RbA3I", "provider": "LOCAL_CLICKHOUSE", "blobStoreConfig": { "identity": "na", "credential": "na", "container": "bao-ap-client-main" }, "dataWarehouseConfig": { "hostname": "127.0.0.1", "publicHostname": null, "database": "baoanalytics", "username": "baoanalytics", "password": "{secret}" }, "supersetConfig": { "url": "https://superset.mydomain.org", "username": "admin", "password": "{secret}", "databaseId": 1 }, "pythonScriptConfig": { "url": "http://127.0.0.1:8000", "identity": "na", "credential": "na", }, "rScriptConfig": { "hostname": "127.0.0.1", "port": 6311, "identity": "na", "credential": "na" } } ``` ### Web UI configuration The data pipeline can be configured using the web UI. The steps assume that you are logged in as the relevant client. - From the top-right context menu, click **Clients**. - Select the relevant client name. - Click **Manage data pipeline config**. - Click **Update**. - Enter the required values using the information above. - Click **Update**. An example of values to use with the local server filesystem as data storage and ClickHouse as data warehouse in the configuration form is found below. Field Value --------------------------- ------------------------------- Client Prefilled Provider Local - ClickHouse **Blob store config** Identity na Credential na Container bao-ap-client-main Account **Data warehouse config** Hostname 127.0.0.1 Public hostname Username baoanalytics Password {secret} IAM role ARN **Superset config** URL https://superset.mydomain.org Username admin Password {secret} Database ID 1 **Python script config** URL http://127.0.0.1:8000 Credential na **R script config** Hostname 127.0.0.1 Port 6311 Credential na The Superset database ID can be retrieved from the `/api/v1/database/` Superset API endpoint. ### Connection test In the web UI, the data pipeline config page allows for testing the blob store, data warehouse and Apache Superset connections. - To test the blob store connection, click **Test blob store connection**. - To test the data warehouse connection, click **Test data warehouse connection**. - To test the Apache Superset connection, click **Test Superset connection**. A window will open and indicate the test outcome. ### Initialize data warehouse The supported data warehouses may require initial setup. - To perform the work to initialize the data warehouse, click **Initialize data warehouse**. If the initialization is done multiple times, the operation will fail, but will not cause an invalid state. ## User admin Several user authorities are considered internal and grant permissions to manage all clients (tenants) of the platform. These authorities should only be granted to administrators of the entire AP instance installation, and **not** to users that belong to a specific client. !!! note Grant internal authorities only to AP instance administrators. The internal authorities can be granted to a specific username using the SQL statement below. Replace `my_username` with the username of the appropriate user. ``` sql insert into user_auths (user_id, auths) select u.id, a.auth from useraccount u cross join (values ('ROLE_MANAGE_CLIENT'), ('ROLE_MANAGE_HOSTING_PLAN'), ('ROLE_SEE_DATA_PIPELINE_CONFIG'), ('ROLE_MANAGE_DATA_PIPELINE_CONFIG'), ('ROLE_MANAGE_SSO_PROVIDER_CONFIG'), ('ROLE_MANAGE_SSO_CLIENT_CONFIG'), ('ROLE_MANAGE_PUBLIC_DATASET_CATALOG')) as a(auth) where u.username = 'my_username'; ``` # Single Sign-On (SSO) Configuration AP supports a comprehensive authentication system based on OpenID Connect (OIDC), a protocol built on top of the OAuth 2.0 protocol. The system features a dual-role architecture that allows AP to function both as an OpenID Connect client and as an authorization server. The OIDC support allows AP to participate in Single Sign-On authentication in both a client and an authorization server role. ## OpenID Connect (OIDC) AP leverages Spring Security's OAuth2/OpenID Connect (OIDC) support with the following key components. **Client implementation** - Supports multiple identity providers per client through a multi-tenant architecture. - Handles standard OpenID Connect scopes: `openid`, `profile`, and `email`. **Authorization Server implementation** - Provides OAuth2 authorization server capabilities through Spring Authorization Server - Supports standard OAuth2 grant types: - Authorization Code - Refresh Token - Client Credentials - Implements OpenID Connect endpoints: - Authorization endpoint: `/oauth2/authorize` - Token endpoint: `/oauth2/token` - UserInfo endpoint: `/oauth2/userinfo` - JWKS endpoint: `/.well-known/jwks.json` - Supports dynamic client registration ## AP as an OAuth2 client In this role, AP authenticates users against external identity providers (e.g., Okta, Azure AD). This section explains how to set up an SSO configuration using Okta as the identity provider. Okta is a popular identity and access management platform which supports the OpenID Connect protocol. The following describes the steps to set up AP as an OAuth2 client. ### Create Okta app integration In this step we will create an Okta web app integration. - Navigate to **Applications** \> **Applications**. - Click **Create app integration**. - Under **Sign-in Method**, select *OIDC - OpenID Connect*. - Under **Application type**, select **Web application**, and click *Next*. - Under **App integration name**, enter a descriptive name like: **BAO Analytics Platform Integration**. - Under **Grant type** \> **Client acting on behalf of itself**, enable *Client Credentials*. - Under **Client acting on behalf of a user**, make sure *Authorization Code* is enabled and other options are disabled. - For **Sign-in redirect URIs**, enter `https://manager.baosystems.com/oauth2/code/OCC4TI2Fdwl/okta`. The pattern is `https://{ap-base-url}/oauth2/code/{ap-client-id}/{provider-key}`. The `ap-base-url` is `manager.baosystems.com`. Make sure to replace `ap-client-id` with the id of your organization in the AP. The `provider-key` is the provider name in lowercase, e.g., `okta` for Okta. - Leave **Trusted Origins** blank. - For **Assignments**, select *Allow everyone in your organization to access*. - Click **Save**. - In the **Assignments** tab in the application overview, ensure that the relevant people are assigned. ### Record Okta settings securely In this step we will take note of the relevant Okta settings and credentials. - Go to the **General** tab in the application overview screen. - Take note of the following settings by clicking the *Copy to clipboard* buttons. These settings will be used to create an SSO configuration in AP. The settings should be considered secrets and stored in a secure way. - Client ID - Client secret - Okta domain - Log out of the Okta portal, to allow logging back in later. ### Create AP user An AP user must be created for every Okta user that needs to log in to AP. The AP user is mapped to the Okta identity via the AP user **SSO Authentication ID** field. User roles and user groups can be granted to the AP user as usual. - Log in to AP using a regular user with authority to create users. - Go to **Users**. - Create a new AP user, or update an existing one. - Check **Enable SSO** and enter your Okta username (**email**) in the Authentication ID field that is displayed. - Select appropriate user roles. - Click **Save**. ### Configure SSO in AP In this step we will configure AP to authenticate with Okta by creating an SSO provider configuration. - Log in to AP as an admin user (with `MANAGE_CLIENT` and `MANAGE_SSO_PROVIDER_CONFIG` authorities). - At the top-right of the screen, click on your username and select **Clients** from the dropdown. - Click on your client name. - Select **Manage SSO Provider config**. - Enter the OKTA credentials recorded previously: - **Provider:** Select OKTA from the dropdown. - **SSO Client ID:** Enter the OKTA **Client ID**. - **SSO Client Secret:** Enter the OKTA **Client Secret**. - **SSO Client Domain:** Enter the OKTA **domain URL**. - **Mapping Claim:** Enter **email**. - Click **Save**. ### Nginx configuration Update the nginx configuration as follows. ``` bash sudo nano /etc/nginx/sites-available/analytics-platform.conf ``` Add the following location block. ``` bash location /oauth2 { proxy_pass http://identity/oauth2; } ``` Restart nginx to make changes take effect. ``` bash sudo systemctl restart nginx ``` ## AP as authorization server In this role, AP provides authentication services to other applications. This section describes the process for configuring SSO clients for the AP Authorization Server service. The section covers: 1. Adding SSO client configurations 2. Configuring nginx for the authorization flow requests 3. Example: Configuring DHIS2 as a client for SSO 4. Example: Configuring Apache Superset as a client for SSO ### Add SSO client configurations The `/api/ssoClientConfig` endpoint manages client configuration details for SSO clients. This requires the `ROLE_MANAGE_SSO_CLIENT_CONFIG` role. **Create new SSO Client** POST /api/ssoClientConfig Content-Type: application/json **Body** ``` json { "clientName": "data.baosystems.com", "redirectUris": [ "https://data.baosystems.com/oauth2/code/ap" ], "scopes": [ "openid", "email" ] } ``` **Response** ``` json { "data": { "clientId": "OfNwxFCVvNZN2QBFHuQv", "clientSecret": "ZfC0UyTxLZmRwa5iDYM0IQDmC2spCWFu4i8aARM9oetKsnbzLfJORZFIkj2M2ncx", "id": "EYwkcaMMZrg" } } ``` !!! tip "Note" The `clientId` and `clientSecret` values are generated by the AP server. The `clientSecret` is only shown once when the configuration is first created. ### Configure nginx for the authorization flow requests Nginx needs to be configured to allow authorization flow requests. As a multi-tenant application, auth requests need to contain the tenant id in the request. For each tenant (AP client), an OpenID Provider Configuration Request will be: https://manager.baosystems.com/{ap-client-id}/.well-known/openid-configuration where `ap-client-id` is the AP tenant (AP client) id. Update the nginx configuration as follows to allow this and other oauth requests: ``` nginx location ~ "^/([a-zA-Z]{1}[a-zA-Z0-9]{10})/(oauth2|\.well-known/openid-configuration)(.*)$" { set $client_id $1; set $sub_path1 $2; set $sub_path2 $3; proxy_pass http://identity/$client_id/$sub_path1$sub_path2$is_args$args; } ``` ### Example: Configuring DHIS2 as SSO client Setting up a DHIS2 instance for SSO is covered in detail in the [DHIS2 documentation](https://docs.dhis2.org/en/manage/reference/openid-connect-oidc.html#generic-providers). In summary, the `dhis.conf` file needs to be updated as follows: ``` properties oidc.oauth2.login.enabled = on oidc.provider.ap.client_id = dkhY3Z6zO1qDkdL7dMBg oidc.provider.ap.client_secret = bcSfmL8KjzvM1CTt2VmeUC1IEzxPiP0LPzGab1jr5J4rVtHtmZZ59piFBkweyi36 oidc.provider.ap.mapping_claim = email oidc.provider.ap.authorization_uri = https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/authorize oidc.provider.ap.token_uri = https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/token oidc.provider.ap.user_info_uri = https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/userinfo oidc.provider.ap.jwk_uri = https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/jwks oidc.provider.ap.end_session_endpoint = https://manager.baosystems.com/OCC4TI2Fdwl/logout oidc.provider.ap.scopes = openid oidc.provider.ap.redirect_url = https://{dhis2-server-url}/oauth2/code/ap oidc.provider.ap.display_alias = BAO Analytics Platform oidc.logout.redirect_url = https://{dhis2-server-url}/ ``` !!! tip "Note" 1. This change requires a Tomcat restart. In addition, a DHIS2 user needs to be created to match the AP user. In particular, the **OIDC mapping value** field of the DHIS2 user must match the email of the user in AP. Find more details in [Okta for DHIS2 guide](https://docs.dhis2.org/en/topics/tutorials/configure-oidc-with-okta.html#create-dhis-2-user). 2. The `redirect_uri` should be `https://{dhis2-server-url}/oauth2/code/ap` and it must match the redirect URI used to create the SSO client config in AP. ### Example: Configuring Apache Superset as SSO client Setting up Apache Superset for SSO is described in the [Apache Superset documentation](https://superset.apache.org/docs/configuration/configuring-superset/#custom-oauth2-configuration). **Install `Authlib`** Install the `Authlib` pip package by adding an entry in the `superset/docker/requirements-local.txt` file: ``` text ... authlib ... ``` **Configure `superset_config.py`** Edit the file `superset/docker/pythonpath_dev/superset_config.py` and add the following lines: ``` python from flask_appbuilder.security.manager import AUTH_OAUTH from custom_sso_security_manager import CustomSsoSecurityManager CUSTOM_SECURITY_MANAGER = CustomSsoSecurityManager # Set the authentication type to OAuth AUTH_TYPE = AUTH_OAUTH OAUTH_PROVIDERS = [ { 'name': 'BAO-Analytics-Platform', 'token_key': 'access_token', # Name of the token in the response of access_token_url 'icon': 'fa-address-card', 'remote_app': { 'client_id': 'NHNy6W9ggLJbC89INtw6', # Created via /api/ssoClientConfig 'client_secret': 'l8mOLS8jPNxs4L0JQpsvWQVeqpPK5foBNK8MyFiKqobvgKr4DWavSyTkxVNwLCQL', # Created via /api/ssoClientConfig 'client_kwargs': { 'scope': 'openid' # Scope for the Authorization }, 'jwks_uri': 'https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/jwks', # Uri for token creation 'access_token_method': 'POST', # HTTP Method to call access_token_url 'access_token_params': { # Additional parameters for calls to access_token_url 'client_id': 'NHNy6W9ggLJbC89INtw6' }, 'access_token_headers': { # Additional headers for calls to access_token_url 'Authorization': 'Basic TkhOeTZXOWdnTEpiQzg5SU50dzY6bDhtT0xTOGpQTnhzNEwwSlFwc3ZXUVZlcXBQSzVmb0JOSzhNeUZpS3FvYnZnS3I0RFdhdlN5VGt4Vk53TENRTA==', 'Content-Type': 'application/x-www-form-urlencoded', }, 'api_base_url': 'https://manager.baosystems.com/OCC4TI2Fdwl/', 'access_token_url': 'https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/token', 'authorize_url': 'https://manager.baosystems.com/OCC4TI2Fdwl/oauth2/authorize' } } ] # Will allow user self registration, allowing to create Flask users from Authorized User AUTH_USER_REGISTRATION = True # The default user self registration role AUTH_USER_REGISTRATION_ROLE = "Gamma" ``` !!! tip "Note" 1. The `remote_app.access_token_headers.Authorization` value is the base64 encoding of the `client_id:client_secret`. 2. The redirect URL will be `https://{superset-webserver}/oauth-authorized/{provider-name}`. For the BAO demo instance, that will be: `https://analytics.demo.baosystems.com/oauth-authorized/BAO-Analytics-Platform` **Configure `custom_sso_security_manager.py`** Create a `superset/docker/pythonpath_dev/custom_sso_security_manager.py` file with the following content: ``` python import logging from superset.security import SupersetSecurityManager class CustomSsoSecurityManager(SupersetSecurityManager): def oauth_user_info(self, provider, response=None): logging.debug("Oauth2 provider: {0}.".format(provider)) if provider == 'BAO-Analytics-Platform': userinfo = self.appbuilder.sm.oauth_remotes[provider].get('oauth2/userinfo').json() # check the values of user_name, mail and others values in me variable return { 'name': userinfo.get('name', ''), 'email': userinfo.get('sub', ''), 'id': userinfo.get('id', ''), 'username': userinfo.get('sub', ''), 'first_name': '', 'last_name': '' } def auth_user_oauth(self, userinfo): # Extract the username or fallback to email username = userinfo.get("username") or userinfo.get("email") if not username: logging.error("OAUTH userinfo does not have username or email %s", userinfo) return None username = username.strip() email = (userinfo.get("email") or username).strip() # Try to find an existing user (by username or email) user = self.find_user(username=username) if not user and email: user = self.find_user(email=email) logging.debug("User found in auth_user_oauth: %s", user) if user and (not user.is_active): return None if (not user) and (not self.auth_user_registration): return None if user and self.auth_roles_sync_at_login: user.roles = self._oauth_calculate_user_roles(userinfo) logging.debug("Calculated new roles for user='%s' as: %s", username, user.roles) if (not user) and self.auth_user_registration: user = self.add_user( username=username, first_name=userinfo.get("first_name", ""), last_name=userinfo.get("last_name", ""), email=email or f"{username}@email.notfound", role=self._oauth_calculate_user_roles(userinfo), ) logging.debug("New user registered: %s", user) if not user: logging.error("Error creating a new OAuth user %s", username) return None if user: self.update_user_auth_stat(user) return user else: return None ``` !!! tip "Note" Overriding the `auth_user_oauth` method enables Superset to find existing users by either their username or email address. This is particularly useful when the identity provider uses an email as the username. ### Troubleshooting Common issues and solutions are found below. **Invalid Redirect URI** - Ensure the configured redirect URI exactly matches the callback URL. - Check for trailing slashes and correct protocol (`http` vs `https`). **Token Validation Failures** - Check if tokens have expired. - Validate signature keys are properly configured. **User Attribute Mapping Issues** - Check that the identity provider is sending the required claims. - Verify attribute mapping configuration. - Check scopes include necessary permissions. # PyServe installation This guide provides instructions for system administrators to install, configure, deploy, and manage the PyServe backend service. ## Overview PyServe is a lightweight Python backend for code execution and formatting, designed for integration with other services. ## Requirements - Python version 3.11 or newer - `pip` package manager ## Installation Clone the [repository](https://bitbucket.org/baosystems/pyserve) and navigate to the project directory. Move into the `/opt` directory and change ownership to `bao-admin`. ``` bash mv pyserve /opt/pyserve chown -R bao-admin:bao-admin /opt/pyserve ``` Create a virtual environment. ``` bash python -m venv venv source venv/bin/activate ``` ### Install dependencies ``` sh pip install -r requirements.txt ``` ### Deployment Run the installation script to set up dependencies and environment. ``` sh sh div/script/install.sh ``` ### Deploy and start the service ``` sh sh div/script/deploy.sh ``` The application listens at the following address. http://localhost:8097 ### Systemd Copy the file at `div/conf/pyserve.service` to `/etc/systemd/system/` and enable the service. ``` sh sudo cp div/conf/pyserve.service /etc/systemd/system/ sudo systemctl daemon-reload sudo systemctl enable pyserve sudo systemctl start pyserve ``` ## API endpoints Format Python code. POST /pyserve/format Execute Python code. POST /pyserve/execute ### Example API request Format code. ``` sh curl -X POST http://localhost:8097/pyserve/format \ -H "Content-Type: application/json" \ -d '{"code": " print (\"hello world\") "}' ``` ## nginx (optional) Configure Nginx as a reverse proxy for PyServe. Example configuration is provided in the [repository](https://bitbucket.org/baosystems/pyserve). ``` sh div/conf/nginx.conf ``` ## Logs For troubleshooting and logs, check the *systemd* journal. ``` sh sudo journalctl -u pyserve tail -f /var/log/pyserve.log tail -f /var/log/pyserve.err ``` # Rserve installation This guide provides instructions for system administrators to install, configure, deploy, and manage R and the Rserve backend service. ## Overview Rserve is a lightweight R backend for code execution and integration, designed for use with other services. ## Requirements - R version 4.0 or newer - Ubuntu/Linux operating system with admin access ## Installation Install R. ``` bash sudo apt update sudo apt install -y r-base ``` Start R and install Rserve and other R packages. ``` bash sudo R install.packages("Rserve") install.packages("knitr") install.packages("pander") install.packages("dbplyr") q() ``` Install the relevant data warehouse driver to allow Rserve to connect to the data warehouse. **Azure SQL Database / SQL Server:** Install the `Microsoft ODBC driver for SQL Server (Linux)` driver by following the official [guide](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server). Install the `odbc` package. ``` bash install.packages("odbc") ``` **ClickHouse:** Install the `RClickhouse` driver package. ``` bash install.packages("RClickhouse") ``` **PostgreSQL:** Install the `RPostgres` driver package. ``` bash install.packages("RPostgres") ``` ## Deployment Run the following command to start Rserve in daemon mode: ``` bash R CMD Rserve --no-save --RS-enable-remote ``` ## Systemd Create a systemd file. ``` sh cd /etc/systemd/system sudo nano rserve.service ``` Add the following content to the `rserve.service` file: ``` text [Unit] Description=Rserve daemon [Service] User = bao-admin ExecStart=/usr/bin/R CMD Rserve --no-save Restart=always RestartSec=3 [Install] WantedBy=multi-user.target ``` Enable the service on startup and start the service. ``` sh sudo systemctl daemon-reload sudo systemctl enable rserve sudo systemctl start rserve ``` # SSH tunnel for PostgreSQL connection This guide covers how to set up an SSH tunnel to a remote server running PostgreSQL. ## Overview The DHIS2 data pipeline in AP benefits from a database connection to the DHIS2 PostgreSQL database for efficient loading of huge amounts of data. The database connection must however be set up in a secure way, and exposing the PostgreSQL port to the outside is not recommended. Using an SSH tunnel has several security benefits. It adds a layer of encryption to the connection and uses public-private key authentication, which is more secure than password-based login. Since using an SSH tunnel requires that the public key of the AP server is installed on the remote PostgreSQL server, it is important to create a dedicated user with minimal authority which only has access to the PostgreSQL service, not admin or root access. ## Configuration The local port forwarding approach for SSH tunneling is the most appropriate type for remote connections to PostgreSQL. ### SSH Set up the tunnel with the following command. ``` sh ssh -fN -L 5001:localhost:5432 bao-admin@my.domain.org ``` The `-f` flag ensures the process is run in the background. The `-N` flag prevents SSH from executing a remote command, i.e. avoids logging in and opening a shell. The `-L` flag defines the local port number to be forwarded and the remote host and port number. The local hostname is omitted and defaults to `localhost`. Using localhost as the remote host leads traffic to arrive on the `localhost` address at the remote server, meaning no changes to the PostgreSQL `pg_hba.conf` configuration should be necessary. ### systemd To manage the SSH tunnel, in terms of starting, stopping and enabling it on server boot, the *systemd* process manager can be used. Create a systemd service file with a descriptive name. ``` sh nano bao-ssh-tunnel.service ``` ``` properties [Unit] Description = SSH Tunnel for PostgreSQL at my.domain.org After = network.target [Service] ExecStart = /usr/bin/ssh -N -L 5001:localhost:5432 bao-admin@my.domain.org User = bao-admin Restart = always RestartSec = 3 [Install] WantedBy = multi-user.target ``` ``` sh sudo chown root:root bao-ssh-tunnel.service ``` ``` sh sudo mv bao-ssh-tunnel.service /etc/systemd/system ``` !!! note The `-f` flag must be omitted in the systemd file to avoid the SSH process being deactivated. To enable the SSH tunnel on server boot. ``` sh sudo systemctl daemon-reload ``` ``` sh sudo systemctl enable bao-ssh-tunnel ``` To start and stop the SSH tunnel. ``` sh sudo systemctl start bao-ssh-tunnel ``` ``` sh sudo systemctl stop bao-ssh-tunnel ``` ## Testing To test, first ensure that the public key of the local server is installed at the remote server and that you can SSH into the remote server. ``` sh ssh user@my.domain.org ``` Exit the remote server. Now verify that you can connect to the remote PostgreSQL instance on localhost using the port defined by the SSH tunnel and the regular `psql` CLI syntax, where the PostgreSQL database name is `dhis2` and the user is `dhis`. ``` sh psql -h localhost -p 5001 -d dhis2 -U dhis ``` If the `psql` CLI returns with a password prompt, the connection is valid. View current SSH connections with the following command. ``` sh sudo netstat -tpln | grep ssh ``` View current SSH processes with the following command. ``` sh ps aux | grep ssh ``` # Read-only user for PostgreSQL To adhere to the principle of least privilege, it may be desirable to use a PostgreSQL user with read-only permissions for the AP connection. The DHIS2 database contains both standard and dynamic tables. Dynamic tables are tables which are generated by DHIS2 at runtime and include resource tables and analytics tables. As a result, when creating a new read-only user it is important to grant access both to existing database tables and to tables created in the future. This guide describes the commands to create a read-only user with the following assumptions: - DHIS2 database name: `dhis2` - DHIS2 database main/owner user: `dhis` - DHIS2 read-only user: `dhis_ro` - DHIS2 schema: `public` Adjust the script to match the environment as necessary. Connect with `psql` to database `dhis2` as `postgres` superuser. ``` sh psql -d dhis2 -U postgres ``` As the `postgres` superuser, create the read-only `dhis_ro` user with the SQL statements below. ``` sql -- -- Create the read-only user -- create user dhis_ro with password 'your_secure_password'; -- -- Grant `connect` to database and `usage` on schema -- grant connect on database dhis2 to dhis_ro; grant usage on schema public to dhis_ro; -- -- Grant `select` on current tables, views and sequences -- grant select on all tables in schema public to dhis_ro; grant select on all sequences in schema public to dhis_ro; -- -- Grant `select` on future tables, views and sequences -- alter default privileges for role dhis in schema public grant select on tables to dhis_ro; alter default privileges for role dhis in schema public grant select on sequences to dhis_ro; ``` Note that the default privileges are altered for the user which creates future tables, which is the `dhis` user, to grant read access to the read-only user, which is the `dhis_ro` user. You can now use the `dhis_ro` user when connecting to PostgreSQL with an AP data pipeline. # Client authentication for PostgreSQL This guide covers how to open a port to a remote server and configure client authentication for PostgreSQL. ## Overview The DHIS2 data pipeline in AP benefits from a database connection to the DHIS2 PostgreSQL database for efficient loading of huge amounts of data. The database connection must however be set up in a secure way, and exposing the PostgreSQL port to the outside is not recommended. Opening a port involves a security risk, and the port should only be opened for specific and trusted IPs. If deploying in a public cloud, the PostgreSQL connection may have to be specifically opened. In the AWS cloud, a security group with inbound rules for the relevant ports and IPs must be created. This guide covers Linux firewall and PostgreSQL client authentication configuration. ## IP addresses The IP addresses for the prod and test environments of AP are described below. Environment Hostname IP address ------------- ----------------------------- --------------- Prod datapipeline.baosystems.com 3.93.131.28 Test test.manager.baosystems.com 54.173.36.156 ## Linux firewall The PostgreSQL port must be accessible in the **iptables** (firewall) of the Linux operating system. This can be achieved using the **firewalld** management tool and the `firewall-cmd` command. Create a dedicated zone `bao-ap` for the analytics platform, add the various source IP addresses and add the PostgreSQL port. Invoke as `root` user. ``` sh sudo su root ``` firewall-cmd --new-zone=bao-ap --permanent firewall-cmd --reload firewall-cmd --zone=bao-ap --add-source=3.93.131.28/32 --permanent firewall-cmd --zone=bao-ap --add-source=54.173.36.156/32 --permanent firewall-cmd --zone=bao-ap --add-port=5432/tcp --permanent firewall-cmd --reload firewall-cmd --list-all --zone=bao-ap ## PostgreSQL authentication PostgreSQL must be configured for authentication from external clients. This is done in the `pg_hba.conf` configuration file. Invoke as postgres user. Note that the PostgreSQL version might be different (e.g. `14` instead of `16`). ``` sh sudo su postgres nano /var/lib/pgsql/16/data/pg_hba.conf ``` Under address, `/32` refers to the CIDR notation for a single host, not a port. This will enable access for the `dhis2` database and `dhis` user for the specific IP addresses only. Add the lines below to the end of the file. # Analytics platform connections # host database user ip-address/mask auth-method host dhis2 dhis 3.93.131.28/32 md5 host dhis2 dhis 54.173.36.156/32 md5 ## PostgreSQL listen port PostgreSQL must be configured to listen for external connections. In the PostgreSQL configuration file, ensure `listen_addresses` is set to `'*'`. The file is typically located at `/var/lib/pgsql/16/data/postgresql.conf`. Listening addresses are not restricted, since the multiple layers of security described above are in place. ``` properties listen_addresses = '*' ``` ## Restart services PostgreSQL must be restarted for changes to take effect. Invoke as `root` user. ``` sh systemctl restart postgresql ``` The DHIS 2 instance might need to be restarted because of the PostgreSQL restart. Invoke as `root` user. ``` sh systemctl restart apache-tomcat ``` # Security This page covers the security principles and security posture for BAO Analytics Platform. ## Software security This section describes various security aspects of the AP software. - **Encryption of secrets:** Secrets including API keys and passwords are encrypted using the [Tink](https://developers.google.com/tink) cryptographic library for Java from Google, a high-assurance framework which provides industry-standard security primitives. AP employs *Authenticated Encryption with Associated Data* (AEAD) key types to ensure confidentiality of secrets and the cryptographic integrity required to prevent unauthorized tampering. - **Encryption at rest:** Full disk encryption (FDE) using LUKS (Linux Unified Key Setup) is the recommended approach for encrypting data at rest. As modern CPUs support AES-NI hardware acceleration, the performance hit for ClickHouse high-throughput reads is negligible. - **Encryption in transit:** Data pipelines (connectors) communicate securely with source systems through the HTTPS protocol, reinforced by industry-standard strong cipher suites. This approach ensures that data is fully encrypted in transit, providing high-assurance security as data moves across the network. - **Security framework:** User and client authentication in AP is managed through the industry-standard [Spring Security](https://spring.io/projects/spring-security) framework, ensuring that all access controls and authentication and authorization workflows meet modern enterprise standards. - **Hashing of user passwords:** AP user account passwords are secured with one-way cryptographic hashing using the industry-standard BCrypt algorithm. This approach ensures that credentials are never stored in a reversible or human-readable format, providing protection against brute-force and rainbow table attacks. - **Secure file validation:** AP enforces validation for file uploads, including CSV and Parquet data files. The validation includes verifying file extensions and MIME types against an allow-list to mitigate spoofing attacks, and structural integrity checks where files that fail to parse correctly according to the format specification are automatically rejected. - **Secure variables:** Variables in AP allow for centralized, encrypted, secure storage of secrets such as API keys and passwords. Variables can be securely referenced from data pipelines, views and scripts, removing the need for specifying secrets in plain text. - **Single Sign-On (SSO):** AP supports the [OIDC](https://openid.net/) authentication protocol standard, and with that the major identity providers including Keycloak, Google Identity Platform and Azure Entra ID. AP supports Single Sign-On authentication flows across applications through the OIDC standard. - **Multi-Factor Authentication (MFA):** AP supports multi-factor authentication using *Time-Based One-Time Passwords* (TOTP) and authenticator apps including *Google Authenticator* and *Authy*, providing a strong, second layer of protection for user accounts. - **Secure by default:** AP follows the *Secure by default* and *Least privilege* principles. The software is designed and configured with strict security settings out of the box. Users are encouraged to grant only the permissions necessary to perform primary functions. This reduces the attack surface and the risk of misconfiguration. - **Static Application Security Testing (SAST):** The AP software is developed using CI/CD where *Static Application Security Testing* (SAST) with SonarCloud is integrated in the build pipeline. The SAST tool automatically scans for critical security flaws, including the OWASP Top 10 security risks. - **Software Composition Analysis (SCA):** AP integrates an automated process using Mend Renovate which identifies and manages third-party components and dependencies, scans the codebase and detects vulnerabilities. The SCA tool automatically creates PRs and triggers CI/CD pipelines, ensuring vulnerabilities are patched immediately and software components are kept up-to-date. ## Managed hosting security This section describes various security aspects of the AP managed hosting offering from BAO Systems. - **Web Application Firewall (WAF):** The WAF stops attacks at the doorstep by inspecting incoming traffic against the latest OWASP Core Rule Set, blocking malicious behavior and abnormal patterns before they reach your application. The WAF continuously evolves with the threat landscape, helping ensure that only legitimate traffic is allowed through. - **Encryption at rest:** Data is encrypted at rest using *Amazon EBS Encryption* for full disk encryption. The entire EBS volume (virtual disk), where the database files, logs, and buffer data files reside, is encrypted, protecting against unauthorized access to the data storage. - **Proactive patching:** A structured schedule for patching of packages and libraries ensures staying ahead of threats. BAO Systems continuously monitors for zero-day vulnerabilities and proactively applies patches to ensure the system stays secure, while providing the flexibility to specify patching windows to balance uptime and security. - **Performance monitoring:** BAO Systems ensures platform stability through continuous, real-time infrastructure monitoring. Grafana, Prometheus and Zabbix are leveraged to track performance trends and resource utilization to detect anomalies. This allows for proactive intervention before users are impacted, maintaining a controlled and reliable environment. - **Infrastructure as code:** The hosting server fleet is fully managed via Infrastructure as Code, ensuring strict consistency across all environments. By automating configuration with Ansible and Rundeck, BAO Systems enforces consistency and security standards and enables rapid disaster recovery through complete rebuilds of entire environments. - **Dynamic Application Security Testing (DAST):** BAO Systems employs continuous automated scanning and *Dynamic Application Security Testing* (DAST) with Tenable Web App Scanning to identify weaknesses across infrastructure and applications. Issues are prioritized by severity, tracked and remediated according to industry best practice. This proactive cycle ensures that vulnerabilities are neutralized before they pose a risk. - **Single Sign-On:** BAO Systems helps streamline access and login across DHIS2, Analytics Platform, Apache Superset and the entire ecosystem through Single Sign-On. AP is integrated with your existing identity provider, including Google Identity Platform and Microsoft Entra ID. Alternatively, BAO Systems can host a dedicated Keycloak instance to provide secure authentication. - **Application level security:** The managed hosting offering follows the [Mozilla web security guidelines](https://infosec.mozilla.org/guidelines/web_security) and standard web security recommendations, providing protection against cross-site request forgery, cross-site scripting, session hijacking and other common threats. # Artificial Intelligence (AI) This page describes the use of AI in BAO Analytics Platform (AP). ## AI and LLMs AP integrates AI, Large Language Models (LLMs) and vector search capabilities to transform natural-language instructions into production-grade data analytics. The supported AI and LLM providers are listed below. - **Google Gemini** (Flash and Flash-Lite) - **OpenAI ChatGPT** (GPT) - **Local model engines** (Ollama and vLLM) By incorporating foundational AI models, AP translates user intent into optimized SQL queries and executable Python or R code across both web interfaces and automated API agents. ![LLM providers](../assets/images/sysadmin/ap_llm_providers.png) - **Text-to-SQL engine:** Non-technical users can execute complex data queries within the Data browser or through the Model Context Protocol (MCP) server by asking questions in plain language. The system feeds schema metadata and prompt context to LLMs to instantly generate, validate and execute precise SQL queries. - **Text-to-script editor:** Users can describe desired data science outcomes in conversational text, such as running outlier detection via modified Z-score statistical algorithms, performing linear regression forecasting, or creating lightweight API connectors. The AI-powered editor translates the prompt into fully executable Python or R code that can be run on-demand or embedded directly into automated workflows. - **Vector embeddings:** AP processes natural-language prompts by generating vector embeddings and performing cosine-similarity search against specialized metadata embedding tables. This step identifies and resolves terms within user prompts, such as facility names, organizational units and health indicators, in a scalable and precise manner. - **Adaptive LLM reasoning levels:** Users can toggle between *Fast mode* (using e.g. Gemini Flash-Lite for low-latency queries) and *Thinking mode* (using Gemini Flash for multi-table joins and complex statistical logic) to optimize runtime performance. - **Enterprise AI agents:** AP exposes a native Model Context Protocol (MCP) server that allows external LLM agents, such as Claude and ChatGPT, to securely discover database schemas, resolve metadata terms and run analytics queries as a native extension of their reasoning processes. The integrated AI architecture bridges the gap between complex database operations and non-technical stakeholders, driving rapid data-driven decision-making without requiring expertise in SQL query writing or Python coding. ### Cloud frontier models AP supports frontier models provided as a service in the cloud. These include the Google Gemini and OpenAI GPT families. In a multi-tenant configuration, each client (tenant) can provide their own API key and manage their own customer relationship with the cloud LLM provider, including accepting the terms of use and managing billing. ### Local model engines AP supports local model engines which provide REST APIs compatible with the OpenAI completions API. These include [Ollama](https://ollama.com) and [vLLM](https://vllm.ai). A range of LLMs are supported by these engines and by AP, including Qwen3-Coder-30B, Qwen3-32B, GPT-OSS-20B and Mistral-Small-3.2-24B. These models can be deployed in on-premise server environments, connected to AP and used as the backend for the AP conversational analytics functions. ### Managed cloud models The OpenAI completions API has become the de facto standard for LLM interaction and most cloud model providers, such as [Together AI](https://www.together.ai) and [DeepInfra](https://deepinfra.com), offer an OpenAI compatible API. AP offers a generic, OpenAI compatible model connector with configurable model, base URL and API key which allows for utilizing the majority of cloud model providers. ## AI and data privacy AP supports Google Gemini and OpenAI ChatGPT as AI LLM providers. AP transmits only non-sensitive metadata to LLM providers, such as data warehouse table names, column names and data types, and semantic definitions, such as names of data elements and organizational units. No data records are transmitted to the AI/LLM provider. Metadata is included in system instructions for content prompts, for purposes such as generation of SQL queries and Python code. Generated queries are executed locally within the platform using the integrated data warehouse, which means that data records never leave the hosting environment. Examples of metadata which are **transmitted** to the LLM provider: - Data warehouse table and column names - Metadata items such as names of DHIS2 data elements and indicators - The natural text query provided by the user Examples of data points which are **NOT** transmitted to the LLM provider: - Aggregate data values - Individual data records - System user information ## AI and governance AP supports reliable AI governance through transparency, traceability, logging and access control. ### Explainability AP provides full query and code transparency and plain-language interpretations, allowing users to understand the output generated by LLMs before executing it. - **Inspected script code generation:** Natural language queries processed by the LLM produce human-readable Python or R code. Generated scripts are populated in the web-based script editor in AP, allowing analysts to inspect, audit, and modify the code before running it. - **Inspected SQL query generation:** Natural language queries converted to SQL queries by the LLM can be inspected directly in the web-based data browser. Generated SQL queries can only read data, not make modifications or delete data, to prevent unintended changes. - **Automated query explanation:** To assist non-technical stakeholders, AP includes a built-in explanation engine in the data browser that breaks down complex generated SQL queries into plain-text descriptions detailing underlying joins, aggregations and filtering logic. - **Semantic phrase search:** To eliminate generative hallucinations, AP performs cosine-similarity searches using vector embeddings against a data warehouse embedding table, resolving natural text terms directly to metadata items, such as DHIS2 data elements, prior to query generation. ### Traceability AP offers end-to-end lineage and immutable audit logs, allowing system administrators to audit and monitor use of AI features and interaction with LLM providers. - **Query history log:** AP automatically writes every AI-generated query to the data browser query history log, recording the requesting user, exact execution timestamp, generated SQL statement, natural text query, execution duration and output data row count. - **Workflow task audit trail:** When AI-generated views or scripts are included in multi-step AP automated workflows, every run generates a permanent change log containing a unique job ID, table-level execution metrics, row counts and error statuses for complete data lineage. - **MCP lineage:** AI interactions handled via the Model Context Protocol (MCP) server capture the full execution lineage, from initial prompt input and metadata search output to the returned query payload. ### Governance AP provides enforced role-based access control (RBAC), sovereign LLM provider account, sandboxing and zero model training. - **Context-aware access control:** AI responses strictly respect the object-level sharing and data warehouse security model of AP. Users can only generate queries against schemas, datasets and organization unit hierarchies for which they have explicit permissions. - **Sovereign LLM account and terms ownership:** For on-premise deployments, implementing organizations retain full operational and legal sovereignty by creating and managing their own enterprise accounts directly with provider platforms (e.g., Google Cloud or OpenAI). The organization manages its own billing, accepts the terms of service and maintains direct control over API key lifecycles and data processing agreements without vendor intermediary lock-in. - **Data privacy and confidentiality:** All API requests sent to the LLM are transmitted via encrypted HTTPS/TLS channels. Under enterprise managed hosting policies, customer data, prompts, and metadata are never retained or used by foundation model providers for model training. - **Sandboxed compute and encrypted secrets:** AI-generated Python and R scripts run in isolated backend execution environments (PyServe and RServe) with strictly managed package libraries. All platform secrets and API credentials referenced in AI-generated scripts are encrypted at rest using Google Tink (AES-128 GCM) and injected securely via AP variables, preventing exposure in plain text. ## Text-to-SQL architecture This section describes the flow of the text-to-SQL solution in AP. 1. User makes a data request in natural text through the AP web user interface. 2. The API layer receives and deserializes the request. 3. AP retrieves the data warehouse schema directly from the data warehouse. This includes schema, table and column names and data types. 4. AP retrieves metadata about the relevant part of the schema. This metadata is generated from the data source application and provided by the data pipeline system. An example is data program, data element, attribute and option set metadata from a DHIS2 data source instance. 5. AP composes the LLM content request with system instruction, data warehouse schema, data source metadata and user query. 6. AP makes a content generation request to the LLM API and receives a SQL query. 7. AP executes the SQL query. 8. If the generated SQL query execution fails, AP retries the generation once. The error message is included in a new LLM content generation request. 9. AP returns the data result set and the generated SQL query in an API response. 10. The AP data browser user interface displays the result set and the generated SQL query to the user. ![Text-to-SQL architecture](../assets/images/sysadmin/ap_text_to_sql_architecture.png) # Use cases This page describes common use cases for data analytics within AP. ## Natural language queries and analytics AP provides the following main capabilities for natural language analytics, also referred to as conversational analytics. - **Convo web app:** Convo is a web app for DHIS2 for conversational analytics which allows users to ask questions about their data using natural text. Convo understands natural text questions and uses DHIS2 API calls to retrieve the relevant information. See the "Convo" documentation page. - **Data browser:** The data browser in AP allows users to ask questions about their data using natural text. The text-to-SQL engine converts questions into SQL queries which retrieve the relevant data. - **Script editor:** The script editor supports Python and R scripting and allows users to declare what type of data analytics and data science they want to achieve in natural text. The text-to-script engine converts the natural text input into Python and R scripts. ## Geospatial visualization and maps AP does not provide a native geospatial visualization and maps (GIS) component. The following systems are recommended to be used with AP for geospatial analysis: - **DHIS2 Maps:** The DHIS2 platform features a web-based and powerful thematic mapping and geospatial visualization tool. DHIS2 is integrated with AP through the DHIS2 data pipeline. - **ArcGIS**: ArcGIS from Esri is a comprehensive geographic information system (GIS) platform that enables users to create, manage, analyze, and map spatial data to gain actionable insights. ArcGIS is integrated through the DHIS2 to ArcGIS Connector app. ## Audit trail AP includes audit records when loading data from source systems with data pipelines, assuming that the source system stores audit information. The main entities in AP, including workflows, data pipelines, data quality check groups, materialized views and destinations, create and store a detailed change log for every task. For example, every time a workflow runs, either scheduled or ad-hoc, a change log with detailed logs is stored. The changelog includes the time the task started and completed. For ad-hoc runs, the changelog includes information about which user started the task. This ensures auditability of the system, both for source system data changes and for operations within AP including data loading and transformation. ## Project-based configuration model AP supports stand-alone deployment in on-premise server environments. AP is designed with a multi-tenant architecture. These features allow for setting up AP in a way where multiple projects or teams within the same organization can work independently and isolated from each other. To achieve this, separate clients for each team are created. This allows each team to create data pipelines, views, scripts and workflows, as well as a dedicated data warehouse, independently of each other. ## Unified and integrated data layer AP allows for importing data from data sources into a central data warehouse through the data pipeline system. Each data pipeline creates and loads data into several data tables in a dedicated data warehouse schema. SQL views and Python and R scripts are used to join the data tables, either by specifying SQL and script code directly, or generated from natural text, where AP converts the natural text input to SQL and script code with the use of LLM/AI technology. ## High-performance query execution on large datasets AP utilizes ClickHouse as the recommended data warehouse. ClickHouse is an open-source, column-oriented database management system specifically designed for high-performance Online Analytical Processing (OLAP). It allows users to generate analytical reports in real-time using SQL queries on trillions of rows and petabytes of data with sub-second latency. ClickHouse is designed for lightning fast queries through a range of features. - **Column-oriented data storage:** Reduces disk reads by only reading columns specified in queries. - **Vectorized query execution:** Processes data in large blocks. - **Massive parallelism:** Utilizes many CPUs and compute nodes for a single query. - **High data compression:** Reduces disk usage and speeds up data reading. - **Specialized storage engines:** Allows for high ingestion rates and fast data range scans. - **Data skipping indexes:** Allows for skipping over ranges of blocks while reading data. ## Data capture AP does not provide a built-in data capture app or module. Instead, AP provides no-code, easily configured connectors (data pipelines) for a variety of data capture solutions. The following software systems are integrated and recommended for data capture. - **DHIS2:** A highly flexible configurable information system with support for data capture of aggregate data, survey and event data, and individual data. Supports desktop web clients and Android mobile clients. Data is ingested into AP using the DHIS2 data pipeline. - **ODK:** Flexible and easy-to-configure software for survey data collection through an Android mobile app. Data is ingested into AP using the ODK data pipeline. - **CSV:** Data can be collected in CSV and Excel files. Data is ingested into AP using the CSV file upload data pipeline. ## Data governance AP supports the key aspects of data governance through its extensive feature set. - **Data quality:** The data quality checks, SQL views, scripting environment, workflows and notification system in AP are useful for ensuring that data is accurate, complete, and consistent. - **Data security and privacy:** The multi-dimensional sharing and access control system, secure-by-default approach and compliance are useful for managing the data security aspect. - **Role based access control:** User groups in AP allow for grouping users by their role within the organization, allowing access and permission to be granted at the role level. - **Metadata management:** The data catalog in AP allows for creating a detailed inventory of data sources and datasets through its rich metadata model. - **Auditing:** All main entities in AP including data pipelines, data quality checks, views and workflows provide changelogs with detailed logs for every task, ensuring all activity is audited. - **Data lifecycle management:** In addition to data loading, AP supports data archiving and data deletion, allowing for managing the lifecycle of datasets and data records. - **Data retention and versioning:** AP retains and stores the raw data files for every data load operation, allowing for comparing the current data with historical versions. ## Role based access control (RBAC) AP supports a Role-Based Access Control (RBAC) security model that restricts system access and permissions based on roles within an organization, rather than individual user identities. It simplifies administration by granting permissions to roles, such as *data owner*, *data steward*, *data analyst* and *data viewer*, instead of directly to individual users. AP supports user groups, users can be assigned to groups depending on their role in the organization, after which user groups can be granted access to datasets, workflows, views and other entities and objects. ## Data archiving and removal AP supports archiving of data. A "frozen" data pipeline can be used to ingest data from a source system and then prohibit the data pipeline from subsequently being refreshed or deleted. This allows for archival of historical datasets, and means the historical data can be deleted from the source system, and instead be accessed with a data exploration or BI tool in AP. Data can be permanently removed based on policies and time thresholds. This is achieved through SQL statements with filters and workflows. Automated data lifecycle management can be enforced through scheduled workflows running on a fixed interval. The data archiving and removal operations are audited through the workflow changelog system. ## Permanent removal of data AP allows permanent and complete removal of the data associated with a data source (data pipeline). When a data pipeline is removed, associated data is by default deleted in its entirety, including data files stored in the data lake, data tables in the warehouse and change log records. Note that AP stores data files for every run of data pipelines, and that data files for all runs are deleted as part of deleting a data pipeline. ## ETL and ELT AP follows the *ELT* approach for data integration. The ELT (Extract, Load, Transform) approach is a modern data integration methodology designed to leverage the high-performance processing capabilities of modern cloud data warehouses. Unlike traditional ETL frameworks, ELT shifts the transformation layer to the end of the pipeline, allowing for more agile and scalable data management. - **Extract:** Raw data is ingested from disparate source systems, such as databases and APIs. AP features a range of no-code data connectors for this purpose. - **Load:** Data is moved directly into the destination storage in its raw format. In AP this is a cloud-native or on-premise data lake and data warehouse like ClickHouse. - **Transform:** Once the data is stored in the data warehouse, it is processed, cleaned, modeled and transformed using the AP and data warehouse native compute power. This includes SQL for scalable data processing, and integrated Python and R scripts for complex algorithmic logic and machine learning. The ELT process has several advantages over the traditional ETL approach. - **Scalability:** Utilizes the elastic, parallel-processing power of cloud environments to handle massive datasets without dedicated staging hardware. - **Flexibility:** By loading raw data before transformation, organizations maintain a single source of truth that can be re-processed as business requirements evolve. - **Low latency:** Minimizes the time between extraction and availability, as data does not need to wait for complex transformation cycles before being stored. ## New connector development AP features two main approaches for development of new connectors (data pipelines). - **Framework:** AP has a *connector development framework* which avoids boiler-plate code and makes connector development more efficient. The AP team leverages [agent skills](https://agentskills.io/) and *agentic coding* for rapid development of new connectors. - **Python scripting:** The integrated Python scripting environment is suitable for rapid development of light-weight and customized API connectors for local and bespoke software applications. Python scripts can be used for data loading and be integrated in AP workflows together with built-in connectors. ## API access AP is designed with the *API first* principle in mind. The data model and all platform operations are exposed in the API. This means that clients and third-party tools, such as CLIs, HTTP clients, web apps and BI tools, can utilize the comprehensive API to communicate, integrate and extend the platform. AP provides complete API documentation based on the *OpenAPI* (Swagger) standard. The API first design allows for extending the platform to meet specific needs and requirements. ## OWASP Top 10 standard AP maintains a rigorous security posture which embeds Static Application Security Testing (SAST) directly into the CI/CD pipeline, ensuring every build is automatically vetted against the OWASP Top 10 security risks. The managed hosting environment reinforces this by deploying a Web Application Firewall (WAF) that leverages the latest OWASP Core Rule Set to stop malicious traffic and abnormal patterns at the doorstep. Finally, the platform's architecture provides native protection against high-risk vulnerabilities like cross-site request forgery (CSRF), cross-site scripting (XSS), and session hijacking, strictly adhering to industry-standard web security guidelines for comprehensive application-level defense. ## Outlier detection AP supports data outlier detection using advanced statistical methods including the *Modified Z-score* and *Inter-quartile range* (IQR) algorithms. These checks are implemented with SQL queries to achieve high performance. For more complex data scenarios such as seasonal diseases, the integrated Python scripting environment is used to identify outliers using advanced time series decomposition, unsupervised machine learning and statistical profiling. ## Azure deployment The Microsoft Azure cloud is a popular environment for deploying AP. AP integrates well with the Azure cloud stack and services. ### Deployment - **Self-hosted model:** AP can be provisioned within a user managed Azure Account leveraging Azure Virtual Machines for compute and Azure Database for PostgreSQL for metadata storage. - **Hybrid model:** The multi-tenant managed service establishes secure connections to Azure SQL Database and other cloud services residing within the user managed Azure environment. ### Connectivity - **Database:** AP connects to and can load data from Azure SQL Database (SQL Server). - **Object storage:** AP connects to and can load data files from Azure Blob Storage. - **Data warehousing:** AP utilizes Azure SQL Database and Azure Synapse as primary data warehouses and destinations for data loading. - **LLM:** AP utilizes Azure OpenAI for LLM content generation, such as text-to-SQL conversion. - **Business intelligence:** AP is purpose-built for MS Power BI integration, allowing for streamlined data modelling and visualization. ## Versioning The AP version format: MAJOR.MINOR.BUILD Example: 1.0.1829 - **MAJOR:** Increments for large new features. - **MINOR:** Increments for small fixes and enhancements. - **BUILD:** Represents the total commit count in the Git master branch and increments with new commits. # Software architecture This page covers the software architecture for BAO Analytics Platform. ## Overview AP is a multi-tenant and web-based software. Multi-tenancy is an application architecture where a single instance of the software serves multiple "tenants", also known as clients or organizations. Each tenant's data and configuration are isolated, ensuring security and privacy, but they all share the same underlying infrastructure and codebase. This approach allows for efficient use of resources, as the software instance can be maintained and updated centrally while still catering to the unique needs of different tenants. For an on-premise installation scenario used by a single organization, a single tenant can be configured, or alternatively, individual tenants for development, testing and production. The high-level architecture of AP is described in the diagram below. ![AP software architecture](../assets/images/sysadmin/ap_software_architecture.png) ## Network architecture AP network architecture for on-prem hosting environments is described in the diagram below, which shows a typical example with a DHIS2 instance as data source, AP multi-tenant service and tenant-specific data storage and data warehouse. ![AP network architecture](../assets/images/sysadmin/ap_network_architecture.png) ## Tech stack The AP software is built using a client-server architecture, where the client (front-end) communicates with the server (backend) over a REST HTTP API. ![AP tech stack](../assets/images/sysadmin/ap_tech_stack.png) ### Database - **PostgreSQL:** Transactional data and metadata. - **Redis:** In-memory caching and session store. - **ClickHouse:** Data warehouse for data analytics. ### Backend services Backend services are written in Java. Runtime - **Java:** Java OpenJDK / JRE 17. Core frameworks - **Spring Boot:** Application framework with dependency injection container, transaction management and REST API support. - **Spring Cloud Gateway:** Reactive routing, filtering, and rate-limiting engine for the API gateway. - **Spring Security:** Authentication and authorization, including the OAuth2/OIDC authorization server. - **Spring Data JPA (Hibernate):** ORM and repository layer for entity persistence and queries. - **Spring Session:** Distributes HTTP session state via Redis across backend services. - **Apache Commons:** General-purpose collection helpers, string utilities and validation logic. Data & persistence - **Flyway:** Versioned and predictable SQL schema migrations. - **Spring JDBC:** Connections and queries against external data stores. Messaging & integration - **Apache Pulsar:** Event bus for events published and consumed across services. - **Apache HttpClient:** HTTP transport and communication with external APIs. - **Jackson:** JSON, XML, CSV, and YAML serialization and deserialization. API docs - **Springdoc OpenAPI:** Generates Swagger UI and OpenAPI docs. Testing - **JUnit Jupiter:** Test framework with test lifecycle handling and declarative assertion methods. - **Mockito:** Mocking support in unit tests. - **Testcontainers:** Spins up real database containers for integration tests. AI and LLM - **Spring AI:** Integrates LLM calls and embeddings generation and exposes an MCP server. - **LLM:** Google Gemini or OpenAI ChatGPT for LLM content generation and embeddings. ### Front-end web apps Front-end web apps are written in JavaScript/TypeScript. Core frameworks - **React:** Web UI library framework for building reusable components and rendering data. - **Vite:** Build tool and dev server which compiles TypeScript and bundles the app for production. - **TypeScript:** Static typing across the codebase. - **Yarn:** Package and dependency manager. Routing & state - **React Router:** Client-side request routing. - **Redux:** Global app state management, async action handling and local storage persistence. - **TanStack Query:** Server-state management with caching, fetching and synchronization of API data. UI & styling - **Ant Design:** Primary component library, including for buttons, tables, forms and layout. - **Apache ECharts:** Charts and data visualization library. Forms - **Formik:** Form state management, including values, submission and dirty tracking. - **Yup:** Schema-based form validation. Editor - **CodeMirror:** In-browser code editor component supporting Python, SQL and R syntax. Testing - **Cypress:** End-to-end, integration and component testing, directly in the browser. - **Sentry:** Application error tracking and performance monitoring for Redux actions.