4. Administration¶

The BI Explorer is a python web application that runs against a PostgreSQL server. The database connection parameters are set in the explorer configuration file.

4.1. Explorer Configuration¶

The configuration of the main Explorer application is stored in a YAML file. Separately from this file, there is a directory with filters, presets, projections and reports.

4.1.1. Program caches¶

The explorer uses program caches to store information that is used often.

The short_term cache is used to cache distinctlists, which are used to enumerate possible filter values found in the explorer table.

The platform_api cache is used to cache shared preset results typically used for dashboards. Using this cache data can already be calculated and stored before a user requests it.

4.1.2. User security¶

User passwords are salted, peppered and hashed using sha512 to the database. The salt is randomly generated and stored per user.

Make sure SECURITY_PEPPER is set differently per explorer installation.

The JSON Web Token settings (SECURITY_JWT_*) are part of the Platform API as used by the MGRID Dashboard, so these should match on both sides. The SECURITY_PLATFORM_USER should match the subject in the JWT.

User roles are obtained during login (e.g., using OAuth2). A role determines thw following access:

SECURITY_PII_ROLES: which roles are allowed to see personally identifiable information (PII).
SECURITY_ADMIN_ROLES: which roles are allowed to view the database query and configuration yaml behind a preset.
SECURITY_GROUP_MANAGEMENT_ROLES: which roles are allowed to create and manage group presets.
SECURITY_PRESET_MANAGEMENT_ROLES: which roles are allowed to create and manage public presets.

4.1.3. Read-Only Mode¶

The BI Explorer can be configured to run in read-only mode by setting the EXPLORER_READONLY_MODE configuration option to true. This mode is useful for production deployments where you want to prevent the application from making any changes to user accounts, groups, or schemas in the database.

Configuration

To enable read-only mode, add the following to your configuration file:

EXPLORER_READONLY_MODE: true

Behavior in Read-Only Mode

When read-only mode is enabled:

User Authentication: Users and groups must already exist in the database before the application starts. The application will only look up existing entities and will not create new ones.
Database Permissions: It’s recommended to use a database user with only SELECT permissions for enhanced security. This prevents any accidental write operations to the database.
Error Handling: If a user or group referenced during authentication is not found in the database, the application will raise an InvalidUserAccountError with a descriptive message.

Setting Up Read-Only Mode

Pre-populate Required Data: Before enabling read-only mode, ensure all necessary schemas, groups, and users are created using the setup tasks:

# Initialize the database
$ invoke app.setup.init

# Create schema
$ invoke app.setup.schema <schema_name>

# Create groups
$ invoke app.setup.group <schema_name> <group_name> [--external-id=<external_id>]

# Create users
$ invoke app.setup.user <schema_name> <user_name> <group_external_id> [--external-id=<external_id>] [--roles=<role1,role2>]

Optional: Create Read-Only Database User: For enhanced security, create a dedicated database user with only SELECT permissions:

-- Create read-only user
CREATE USER explorer_readonly WITH PASSWORD 'secure_password';

-- Grant connection privileges
GRANT CONNECT ON DATABASE your_database TO explorer_readonly;

-- Grant usage on schemas
GRANT USAGE ON SCHEMA your_schema TO explorer_readonly;
GRANT USAGE ON SCHEMA explorer TO explorer_readonly;

-- Grant select on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO explorer_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA explorer TO explorer_readonly;

-- Grant select on sequences
GRANT SELECT ON ALL SEQUENCES IN SCHEMA your_schema TO explorer_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA explorer TO explorer_readonly;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema GRANT SELECT ON TABLES TO explorer_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA explorer GRANT SELECT ON TABLES TO explorer_readonly;

Update Configuration: Update your database connection settings to use the read-only user and enable read-only mode:
```
DBPOOL_USER: explorer_readonly
DBPOOL_PASSWORD: secure_password
EXPLORER_READONLY_MODE: true
```

Use Cases

Read-only mode is particularly useful for:

Production environments where user management is handled externally
High-security deployments where database write access should be minimized
Disaster recovery scenarios where you want to ensure data integrity
Load balancing setups where multiple read-only instances serve requests

Troubleshooting

Common issues in read-only mode:

Schema 'schema_name' not found in read-only mode: The specified schema doesn’t exist. Create it first using invoke app.setup.schema.
Group 'group_external_id' not found in read-only mode: The specified group doesn’t exist. Create it first using invoke app.setup.group.
User 'external_id' not found in read-only mode: The specified user doesn’t exist. Create it first using invoke app.setup.user.

4.2. SSL configuration¶

The nginx software is used to expose the web application externally. By default the Python webcontainer is configured to only serve on 127.0.0.1. Additional configuration in /etc/nginx/nginx.conf:

user              nginx;
worker_processes  1;

error_log  /var/log/nginx/error.log;
pid        /var/run/nginx.pid;

events {
    worker_connections  1024;
}

http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  /var/log/nginx/access.log  main;

    sendfile        on;

    keepalive_timeout  65;

    include /etc/nginx/conf.d/*.conf;

    ssl_protocols  TLSv1 TLSv1.1 TLSv1.2;
}

With /etc/nginx/conf.d/explorer.conf:

upstream explorer-site {
    server 127.0.0.1:6543;
}

server {
    listen 443 ssl;
    server_name  explorer.outsidename.nl;
    ssl_certificate /etc/ssl/explorer/server.crt;
    ssl_certificate_key /etc/ssl/explorer/server.key;

    location / {
        proxy_set_header        Host $http_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;

        client_max_body_size    10m;
        client_body_buffer_size 128k;
        proxy_connect_timeout   60s;
        proxy_send_timeout      90s;
        proxy_read_timeout      90s;
        proxy_buffering         off;
        proxy_temp_file_write_size 64k;
        proxy_pass http://explorer-site;
        proxy_redirect          off;
    }
}

4.3. User and Schema Management¶

User and schema management is done using the command line tool invoke. This tool determines database location using a configuration file (set through the environment variable APP_CONFIG_FILE), and provides various tasks for setting up and managing the explorer environment. It should be run from the explorer application directory (/opt/mgrid/explorer).

4.3.1. Setup Tasks¶

The primary management tasks are organized under app.setup:

$ invoke --list app.setup
Available 'app.setup' tasks:

  .group     Create a group in the specified schema
  .init      Create extensions and functions
  .presets   Reset user presets
  .schema    Create and initialize user schema
  .user      Create a user in the specified schema and group

Default 'app.setup' task: .init

Database Initialization

Before creating users and groups, initialize the database:

$ invoke app.setup.init

Schema Management

Create and initialize a user schema:

$ invoke app.setup.schema <schema_name>

Group Management

Create a group in a specified schema:

$ invoke app.setup.group <schema_name> <group_name> [--external-id=<external_id>]

The external_id parameter is optional. If not provided, a random UUID will be generated.

User Management

Create a user in a specified schema and group:

$ invoke app.setup.user <schema_name> <user_name> <group_external_id> [--external-id=<external_id>] [--roles=<role1,role2>]

The external_id parameter is optional. If not provided, a random UUID will be generated. The roles parameter accepts a comma-separated list of roles.

Preset Management

Reset user presets for a schema:

$ invoke app.setup.presets <schema_name> [--force] [--userdefined]

4.4. Other Administrative Tasks¶

The explorer provides several other administrative task categories:

4.4.1. Backup and Restore¶

Backup and restore userdefined categories and presets:

$ invoke app.backup.backup     # Backup userdefined categories + presets
$ invoke app.backup.restore    # Restore userdefined categories + presets

4.4.2. Data Export¶

Export presets and data:

$ invoke app.export.presets    # Export presets
$ invoke app.export.data       # Export data for all presets

4.4.3. Validation¶

Validate various components of the explorer configuration:

$ invoke app.validate.all         # Validate filters, presets, projections and reports
$ invoke app.validate.filters     # Validate filters and filter values
$ invoke app.validate.presets     # Validate presets in configuration or database
$ invoke app.validate.projections # Validate projections in configuration
$ invoke app.validate.reports     # Validate report parameters
$ invoke app.validate.sql-presets # Validate presets in configuration

4.4.4. Configuration Generation¶

Generate configuration files and components:

$ invoke app.generate.columns     # Create csv file with column specs
$ invoke app.generate.filters     # Generate filters based on csv file
$ invoke app.generate.presets     # Generate starter preset
$ invoke app.generate.projections # Generate projections based on csv file

4.4.5. Translation¶

Manage translation strings:

$ invoke app.translate.extract    # Extract all ui strings from configuration
$ invoke app.translate.translate  # Translate all ui strings

4.4.6. Application Runtime¶

Run the application in different modes:

$ invoke app.run.run     # Run a production instance
$ invoke app.run.debug   # Run a development instance

4.4.7. Documentation¶

Generate API documentation:

$ invoke app.doc.swagger # Export swagger.json

4.4.8. Maintenance¶

Perform maintenance tasks:

$ invoke app.maintenance.remove-order-from-presets # Remove legacy order fields from presets