> ## Documentation Index
> Fetch the complete documentation index at: https://developer.upsun.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Give your BI tool read-only database access on Upsun

> Give your BI tool read-only database access through a small SSH app on Upsun. Combine Viewer permissions, API tokens, and database endpoints. Follow the setup.


Have you ever needed to give a business intelligence (BI) tool access to your database, without giving it access to your application?

This comes up with customers every now and then. The reporting team needs to read data. You want that to happen through a secure connection. But handing over SSH access to the application also exposes its files, environment variables, and database credentials.

That's quite a lot of access for a dashboard.

On Upsun, you can put together a small application whose only job is to provide that connection. Give the BI user permission to SSH into that app, and give the app a read-only relationship to MariaDB or PostgreSQL.

It's a slightly funny setup: an application that doesn't serve an application. But it combines native features, and there's very little to maintain.

Start in a [preview environment](https://developer.upsun.com/docs/environments.md) cloned from the intended parent. Verify its environment type and parent, and review inherited external connections before testing. The CLI examples use a preview named `bi-preview` and an example project ID. Replace them with your verified identifiers.

## Start with a dedicated BI user

Create a machine user with its own Upsun account. Give it the **Project viewer** role and the **Viewer** role for the environment type it needs. Leave other environment types unassigned unless they're needed.

These are [environment type permissions](https://developer.upsun.com/docs/administration/users.md#environment-type-roles), so a Staging role applies to all Staging environments. Check inherited team permissions, too. The account shouldn't acquire broader access through another membership.

A Viewer can't push code. By default, it can't SSH into application containers either. You'll make an exception for 1 dedicated app later.

Create an [API token for the machine user](https://developer.upsun.com/cli/api-tokens.md). I prefer this for automation because it gives you a credential you can rotate independently of anyone's personal account. An SSH key attached to that user is another option, particularly if your BI tool only supports key-based SSH authentication.

With the CLI, authentication uses [SSH certificates that cycle every hour](https://developer.upsun.com/docs/development/ssh#1-authenticate-with-the-cli). The CLI obtains the certificate using your authenticated session, including when you authenticate with an API token.

You get a temporary SSH credential without manually distributing a permanent SSH key for the BI integration.

> [!WARNING]
>
> The API token remains a long-lived secret and can obtain fresh certificates. Store it in the secret manager on your
> tunnel host, rotate it, and revoke it if compromised. Certificate expiration doesn't terminate an established SSH
> tunnel.

## Give your database a read-only endpoint

Both databases support endpoints with different permissions. Each relationship can use a specific endpoint and receive the corresponding database credentials.

Choose the configuration for your database:

> [tab] MariaDB
>
> Suppose your service is called `mariadb` and your database schema is `main`. Add a `reporting` endpoint with `ro` privileges:
>
>       services:
>         mariadb:
>           type: mariadb:\{\{version:mariadb:latest}}
>           configuration:
>             schemas:
>               \- main
>             endpoints:
>               mysql:
>                 default\_schema: main
>                 privileges:
>                   main: admin
>               reporting:
>                 default\_schema: main
>                 privileges:
>                   main: ro
>       
>
> Merge this into your existing service configuration. Keep your current MariaDB version, schema names, and application endpoint permissions. The `mysql` endpoint above preserves the usual default endpoint for the application.
>
> Once you explicitly configure schemas or endpoints, Upsun no longer supplies their defaults. Include every existing schema and endpoint you still need, as described in the [multiple databases documentation](https://developer.upsun.com/docs/add-services/mysql#multiple-databases).
>
> > [!WARNING]
> >
> > Removing a schema from the configuration deletes that schema on deployment. Preserve your existing schema list when
> > adding the reporting endpoint.
>
> The `ro` permission allows reads, views, and temporary tables. It doesn't allow changing the application's stored tables. Setting `default_schema` also lets CLI tools identify which database to connect to.
>
> See the [MariaDB endpoint permissions](https://developer.upsun.com/docs/add-services/mysql#define-permissions) for details.

> [tab] PostgreSQL
>
> Suppose your service is called `postgresql` and your database is `main`. Add a `reporting` endpoint with `ro` privileges:
>
>       services:
>         postgresql:
>           type: postgresql:\{\{version:postgresql:latest}}
>           configuration:
>             databases:
>               \- main
>             endpoints:
>               postgresql:
>                 default\_database: main
>                 privileges:
>                   main: admin
>               reporting:
>                 default\_database: main
>                 privileges:
>                   main: ro
>       
>
> Merge this into your existing service configuration. Keep your current PostgreSQL version, service name, database names, extensions, and application endpoint permissions. The `postgresql` endpoint preserves the usual default endpoint for the application.
>
> PostgreSQL uses `databases` and `default_database` where MariaDB uses `schemas` and `default_schema`. The `ro` endpoint grants read access to `main`. Setting `default_database` lets CLI tools identify the database.
>
> Preserve all existing databases and endpoints when adding the reporting endpoint. See the [PostgreSQL endpoint configuration](https://developer.upsun.com/docs/add-services/postgresql#multiple-databases) for details.

This uses your existing database. You haven't created a second copy of the data or a replication setup.

## Add an application that does very little

Add a separate app called `bi-access` under `applications` in the same configuration file:

The example below uses the MariaDB service. For PostgreSQL, change `service: mariadb` to `service: postgresql` in the `reports` relationship. Use your existing service name if it differs.


  applications:
    bi-access:
      type: python:\{\{version:python:latest}}
      source:
        root: bi-access
      access:
        ssh: viewer
      relationships:
        reports:
          service: mariadb
          endpoint: reporting
      mounts: \{}
      web:
        commands:
          start: sleep infinity
        locations:
          "/":
            allow: false
            passthru: false
  

Keep your existing applications alongside this entry. Create a `bi-access` directory in the repository with a tracked file, such as a README, so Git preserves it.

The separate [source root](https://developer.upsun.com/docs/configure-apps/multi-app/project-structure.md) keeps the main application's files out of this app's build. Python supplies a runtime here, but you aren't writing Python code. The [start command](https://developer.upsun.com/docs/configure-apps/image-properties/web.md#web-commands) does nothing beyond keeping the process alive. Don't add an HTTP route to this app.

The [`access.ssh` setting](https://developer.upsun.com/docs/configure-apps/image-properties/access) lowers the minimum SSH role to Viewer for this app. Other apps keep their own access settings, whose default minimum is Contributor. This permits any Viewer with the relevant environment access to enter `bi-access`, rather than naming 1 particular user.

The only [relationship](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md) is `reports`, pointing to the `reporting` endpoint. The main application can retain its normal database permissions in its own container.

> [!WARNING]
>
> This grants a shell inside `bi-access`. Keep application secrets, shared mounts, and privileged relationships out of
> it. Review project and environment variables exposed at runtime, and scope application secrets to the apps that need
> them. A separate source root doesn't hide variables shared across apps.

I recommend starting this container at **0.1 CPU**. Its workload is forwarding an SSH connection, while the database executes the queries. Check throughput and memory usage if you move large amounts of data.

After the preview deployment completes, use the [resource configuration command](https://developer.upsun.com/docs/manage-resources/adjust-resources.md#vertical-scaling) to size this app. Run this as a project administrator, before switching to the BI account:

```bash theme={null}
  upsun resources:set --project abcdefgh1234567 --environment bi-preview --size bi-access:0.1
```

There's no reason to pay for a large container to sit there waiting for your next report.

## Connect the BI tool through the app

Deploy the configuration in a preview environment first. If you've changed user permissions, [redeploy to apply SSH access changes](https://developer.upsun.com/docs/administration/users.md#add-a-user-to-a-project).

On the machine running the tunnel, authenticate the CLI as the BI user. For an interactive check, this command prompts for its token:

```bash theme={null}
  upsun auth:api-token-login
```

For unattended operation, inject `UPSUN_CLI_TOKEN` through that machine's secret manager instead. Keep the BI token on the tunnel host; the Upsun app doesn't need it.

Open a [single relationship tunnel](https://developer.upsun.com/docs/development/ssh#use-a-direct-tunnel), explicitly selecting the dedicated app:

```bash theme={null}
  upsun tunnel:single --project abcdefgh1234567 --environment bi-preview --app bi-access --relationship reports --port 3307
```

Leave that command running. In your BI tool, choose the MySQL driver for MariaDB or the PostgreSQL driver for PostgreSQL. Use `127.0.0.1`, port `3307`, database `main`, and the credentials from the `reports` relationship.

The local port is your choice: `3307` works for either database because the tunnel forwards it to the relationship's remote port.

You can inspect those credentials as the BI user with:

```bash theme={null}
  upsun relationships --project abcdefgh1234567 --environment bi-preview --app bi-access
```

Use the relationship's username and password, rather than credentials from the main application's relationship.

The BI process must run on the machine that owns this local tunnel. For a hosted BI service, check its connection options. It may require its own SSH tunnel support or a connector you run alongside the CLI.

If the tool handles SSH itself, use the connection details for `bi-access` and the [app tunnel instructions](https://developer.upsun.com/docs/development/ssh#use-an-app-tunnel). A tool using certificates needs a way to refresh them. Attaching an SSH key to the same restricted user may fit that tool better.

## Check the permissions with the BI account

Before connecting production reporting, test the setup using the machine user's credentials in your preview environment:

* Confirm a `SELECT` against a known table succeeds through `reports`.
* On a disposable test table (or a preview environment), confirm an `INSERT` fails with a permissions error.
* Confirm SSH into `bi-access` succeeds, while SSH into the main application is denied.
* Check the BI user's roles deny code pushes and that no privileged credentials appear inside `bi-access`.

Once these checks pass, apply the configuration to the environment you intend to use for reporting. Verify its identity before deploying or opening a tunnel, and assign the BI user the corresponding environment type role.

Read-only access still permits reading everything granted by that endpoint. It also permits expensive queries. If reporting competes with application traffic, consider [MariaDB read-only replicas](https://developer.upsun.com/docs/add-services/mysql/mysql-readonly-replication.md) or [PostgreSQL read-only replicas](https://developer.upsun.com/docs/add-services/postgresql/postgresql-readonly-replication.md).

The existing [service access tutorial](https://developer.upsun.com/docs/core-concepts/common-tasks/restrict-service-access.md) shows a worker variant of this pattern. A separate application gives you a separate source directory as well.

You end up with a Viewer account, a small SSH app, and permissions enforced by the database. All that for a dashboard... which can now get on with being a dashboard.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.