Setting Up the Business Objects - Grafana Database User

Purpose

If you do not want the ability to run queries on the business_objects schema through grafana, you do not need to set this user up

The thingsHub Visualizer service requires dedicated PostgreSQL database credentials to perform its functions. This involves analyzing potentially large datasets from your thingsHub instance to generate dashboards and evaluate alerting rules.

To ensure security and system stability, the Visualizer service uses a separate PostgreSQL user with:

Read-Only Access: The user can only read data, not modify it.
Limited Scope: Access is restricted only to the specific business_objects schema and tables necessary for visualization and alerting.
Resource Constraints: Memory and temporary disk usage are limited to prevent analytical queries from negatively impacting overall database performance.

Prerequisites

You need administrative access (e.g., as the postgres user or another superuser) to your PostgreSQL database.

Configuration Steps

Follow these steps to create and configure the dedicated user. Run these commands using a tool like psql or any other PostgreSQL client connected as an administrator.

Create the User

Replace <read-user-name> with your desired username and <secure_password> with a strong, unique password for this user.

SQL

CREATE USER <read-user-name> WITH PASSWORD '<secure_password>';

Do not reuse passwords from other accounts.

Set Resource Limits

These settings control the resources allocated to potentially demanding analytical queries.

work_mem limits memory for operations like sorting or hashing, and temp_file_limit restricts temporary disk file usage if memory limits are exceeded.

Replace <visualizer-user-name> with the username created in Step 1.

SQL

-- Set maximum RAM per sort/hash operation
ALTER ROLE <read-user-name> SET work_mem = '256MB';

-- Set maximum temporary disk space per session
ALTER ROLE <read-user-name> SET temp_file_limit = '256MB';

Note on Values:
256MB is a recommended starting point that balances performance and resource control for typical analytical workloads on moderately sized servers.
You may need to adjust these values depending on your system’s available RAM, the complexity of your dashboards/alerts, and the data volume.
Monitor your server's performance and consult PostgreSQL documentation or support if queries are too slow (indicating potential disk spilling) or if memory usage is too high.

Grant Necessary Permissions:

Grant this user the minimum required permissions: USAGE on the relevant schema(s) and SELECT (read-only) access on the specific tables needed by the Visualizer.

Replace <read-user-name> with the chosen username.

SQL

GRANT CONNECT ON DATABASE <tenant-db-name> TO <read-user-name>;

-- Grant permission to access the schema
GRANT USAGE ON SCHEMA business_objects TO <read-user-name>;

-- Grant read-only access to the assets table
GRANT SELECT ON TABLE business_objects.assets TO <read-user-name>;

ALTER ROLE <read-user-name> SET search_path = 'business_objects';

Completion:

Once these steps are completed, the <read-user-name> user is ready.
Configure your tenant configuration file to use these credentials in the sql block of your tenant configuration file:

YAML

sql:
  ...
  read_users:
    business_objects:
      username: <read-user-name>
      password: <read-user-password>
  ...