Server configuration

There are several configuration files in CloudBeaver.

Main server configuration

The primary configuration file is cloudbeaver.conf. By default, it is placed in the /etc/cloudbeaver/ folder.
But in most cases it is redefined for each server by the command line parameter, -web-config <config-file-path>.
The server configuration is in the JSONC format (JSON with comments and without redundant quotes). It can be parsed by most of the JSON parsers in lenient mode.
Additionally, configuration parameters can be specified in the file workspace/.data/.cloudbeaver.runtime.conf. It is convenient because the workspace can be deployed as a shared docker volume. .cloudbeaver.runtime.conf has the same structure as cloudbeaver.conf. However it has a higher priority than cloudbeaver.conf.

Typical configuration:

{
    server: {
        serverPort: 8978,
        serverHost: "localhost",
        serverName: "CloudBeaver Sample Server",

        // Paths are absolute or relative to the server root folder
        workspaceLocation: "workspace",
        contentRoot: "web",
        driversLocation: "drivers",

        rootURI: "/",
        serviceURI: "/api/",

        // Webapp configuration file
        productConfiguration: "conf/product.conf",

        expireSessionAfterPeriod: 600000,

        develMode: false,

        database: {
            url: "jdbc:h2:${workspace}/.data/cb.h2.dat",
            initialDataConfiguration: "conf/initial-data.conf",
            pool: {
                minIdleConnections: 4, 
                maxIdleConnections: 10,
                maxConnections: 100,
                validationQuery: "SELECT 1"
            }
        }
    },
    app: {
        anonymousAccessAllowed: true,
        anonymousUserRole: "user",
        supportsCustomConnections: false,
        publicCredentialsSaveEnabled: true,
        adminCredentialsSaveEnabled: true,
        resourceManagerEnabled: true,
        resourceQuotas: {
            dataExportFileSizeLimit: 10000000,
            resourceManagerFileSizeLimit: 500000,
            sqlMaxRunningQueries: 100,
            sqlResultSetRowsLimit: 100000,
            sqlResultSetMemoryLimit: 2000000,
            sqlTextPreviewMaxLength: 4096,
            sqlBinaryPreviewMaxLength: 261120
        },
        defaultNavigatorSettings: {
            showSystemObjects: true,
            showUtilityObjects: false,
            showOnlyEntities: false,
            mergeEntities: false,
            hideFolders: false,
            hideSchemas: false
        },
        plugins: {

        },
        enabledAuthProviders: [
            "local"
        ],
        enabledDrivers: [
            
        ],
        disabledDrivers: [
            "sqlite:sqlite_jdbc",
            "h2:h2_embedded"
        ]

    }

}

All paths can be absolute or are relative to the server start directory (or current directory).

Server parameters:

Name Description
serverPort Port CloudBeaver server listens on
serverHost The network interface CloudBeaver server binds to as an IP address or a hostname. If null or 0.0.0.0, then bind network interface to all available interfaces.
serverURL Server address (full URL). Used to generate links and for third-party services integration.
workspaceLocation Root folder for projects
contentRoot Path to directory with static content
driversLocation Optional path for driver jar files
rootURI Web application URI prefix. / by default
serviceURI Services API URI prefix (relative to rootURI). /api/ by default.
productConfiguration Path to product (web interface) configuration file (json)
develMode When set to true extra debug, the information is printed in logs and the GraphQL console is enabled on the server.
expireSessionAfterPeriod Maximum idle time after which the user’s session will be closed.

Database configuration

Configures CloudBeaver database where it keeps users, credentials and permission.
In the section server.database:

Name Description
driver Database driver (e.g. sqlite, h2_embedded, postgres-jdbc, etc)
url Database JDBC URL (e.g. jdbc:postgresql://localhost:5432/cb
user Database user name
password Database user password
initialDataConfiguration Path to the initial data file (json) that will be loaded on the first time the server is run

Database connection pool configuration

Configures connection pool to be used by the CloudBeaver database.
In the section server.database.pool:

Name Description
validationQuery Query that will check the successful connection to the database
minIdleConnections Minimum number of idle connections that should be kept in the pool
maxIdleConnections Maximum number of idle connections that should be kept in the pool
maxConnections Maximum number of idle and active connections that should be kept in the pool

Database Initial Data

Configures initial data containing administrator credentials and a list of roles and their permission.
Stored in a separate file. The path to which is specified in the server.database.initialDataConfiguration section.

Name Description
adminName Username for administrator
adminPassword Password for administrator
roles List of initial roles

Roles schema

Name Description
roleId Id for the role
name Name for the role
description Role description
permissions Set of available permissions for the role

Configuration example:

{
    adminName: "cbadmin",
    adminPassword: "cbadmin20",
    roles: [
        {
            roleId: "admin",
            name: "Admin",
            description: "Administrative access. Has total and full authority.",
            permission: ["public", "admin"]
        },
        {
            roleId: "user",
            name: "User",
            description: "Standard user",
            permission: ["public"]
        }
    ]
}

Application parameters:

In the section app:

Name Description
anonymousAccessEnabled Allows anonymous access. Anonymous users have a role anonymousUserRole.
anonymousUserRole A role that will be assigned to the anonymous user, user by default.
authenticationEnabled Enables users’ authentication. If disabled, then only anonymous access is allowed.
supportsCustomConnections Allows users to create custom connections to any databases. Otherwise only the CB administrator can create/edit connections.
publicCredentialsSaveEnabled Allows you to save user database credentials in a local cache.
adminCredentialsSaveEnabled Allows you to save global database credentials in a local cache.
redirectOnFederatedAuth If there is only one federation authentication configuration, then login attempt will automatically be made when the application is opened.
forwardProxy Identifies the originating IP address and other headers of a client connecting to a web server through an HTTP proxy.
enabledDrivers List of drivers that are allowed to be used. If the list is empty, all drivers are allowed.
disabledDrivers List of drivers that are prohibited for use. If the list is empty, all drivers are allowed.
enableReverseProxyAuth Enabling reverse proxy authorization mechanism for more details can be found here.
defaultAuthProvider The provider that will be used for authorization by default.
enabledAuthProviders List of allowed authorization providers. If the property is absent, all providers are allowed.
defaultNavigatorSettings Default database navigator settings.

Resource quotas

You can configure the following resource quotes in the section app.resourceQuotas:

Name Description
dataExportFileSizeLimit Maximum file size for data export operation (in bytes)
resourceManagerFileSizeLimit Maximum file size saved in the resource manager (in bytes)
sqlMaxRunningQueries Maximum number of simultaneous queries for a single user session. Includes data read queries (i.e. table data view)
sqlResultSetRowsLimit Maximum number of rows to select from a table or query
sqlTextPreviewMaxLength Maximum size for text file shown in value panel (in bytes)
sqlQueryTimeout Maximum time (in seconds) for SQL query execution (including table data read)
sqlBinaryPreviewMaxLength Maximum size for binary file (also affects JSON in the SQLite) shown in value panel (in bytes)

Navigator settings

You can configure the following properties in the section app.defaultNavigatorSettings:

Name Description
showSystemObjects Show system objects.
showUtilityObjects Show “utility” objects.
showOnlyEntities Only show schemas and tables.
mergeEntities Show all types of database objects in one list (tables, sequences, etc.).
hideFolders Hide folders like “Tables”, “Schemas”, “Columns”, etc.
hideSchemas Do not show schemas (all tables in one list).

Simple view mode properties example:

    defaultNavigatorSettings: {
        showOnlyEntities: true,
        hideFolders: true,
        hideVirtualModel: true
    }

Automatic server configuration

When you start the CloudBeaver server for the first time you will see the administrator interface for
server configuration.
In some cases the server must be configured automatically (e.g. when it is run in the Kubernetes environment).
The following parameters must be specified in the configuration:

Name Description Example
CB_SERVER_NAME Server name Test Server
CB_SERVER_URL Server base URL https://cloudbeaver.domain.com:10000/
CB_ADMIN_NAME Administrator user name admin
CB_ADMIN_PASSWORD Administrator user password S0mePazzworD

These parameters can be specified in:

  • OS environment variables
  • configuration file .cloudbeaver.auto.conf which must be placed in the same location as the cloudbeaver.conf file.

Datasources configuration

You can find a detailed description at here

Using environment variables

You can use references on environment variables in most server configuration properties.
For example:

{
    server: {
        serverPort: ${cb.port},
        serverHost: "${cb.host}",
        serverName: "CloudBeaver Server",

        rootURI: "${cb.prefix}",
        serviceURI: "/api/",

        expireSessionAfterPeriod: ${cb.expire-time},
    }
}

Visual Query Builder

Note: This feature is available in Enterprise and Enterprise for AWS editions only.

Overview

The Visual Query Builder is a user-friendly visualization tool that can help you to create queries to the database and see results. You do not need to know SQL language to work in it.
The Visual Query Builder may be useful for:

  • building queries;
  • complex queries analysis;
  • easy query editing.

vqb view

To open the Visual Query Builder, click the Query Builder tab in the SQL Editor right toolbar.

Creating a Visual Query

  1. Select tables in the Navigator tree and drag-and-drop them into the Visual Query Builder area. The existing connections between the tables will automatically be displayed. The tables will also be added to the SQL expression which can be found in the field to the right of the diagram.

vqb dnd

  1. To create a new join between tables, connect their columns holding the left mouse button. The connection between the selected columns of the tables will appear in the diagram and the Inner Join will be added to the SQL script.

vqb create_join

  1. You can change a join type clicking the join label on the connection line.
  2. To remove a join between tables, click on the line, then press the Delete button. The connection will be removed from the diagram and the join will disappear from the SQL script.
  3. By default all tables’ columns are included in the query. If you only want to see certain columns in your query result, select the checkbox near the column name.

vqb column checkbox

Filtering

  1. WHERE condition with the filter value is used for filtering. To add a filter, write it in the top filter field.
Column name Operation Sign Value
A table column name. You have to write a table alias before if another column has the same name The most common signs: =, >, <, <>, LIKE, ILIKE, BETWEEN A column value, used as a parameter. Text and time values must be rounded by single quotes, numeric values do not need any quotes

Filter example:

vqb_filter

Sorting

  1. To apply a sorting condition to a column, press the sorting icon next to a column name on the diagram. The column will be sorted in ascending order and the conditional expression ORDER BY will be added to the SQL script.
    To sort the column in descending order, press the sorting icon again to select the down arrow.
    If you want to remove a condition, continue to click the sorting icon to deactivate it.
    Sorting can be applied to multiple columns in different tables. First, apply sorting on the first column you wish to sort, and then on the second, third and so on.
    You can sort numbers, texts, dates, time and other values.

vqb sorting

Executing a Visual Query

Use the Execute SQL statement button vqb Execute SQL statement button on the left pane to execute a query and get the results in the same tab. If you want to see the result in a new tab, press the Execute SQL statement in a new tab button vqb Execute SQL statement in a new tab button.

Shortcuts

You can use the same shortcuts as in the SQL Editor to execute the Visual Query.

Key Description
Ctrl+Enter Execute the SQL statement
Ctrl+\ or Ctrl+Shift+Enter Execute the SQL statement in a new tab

The Visual Query Builder symbols

The Visual Query Builder uses the following visual tools to display queries on the diagram:

Table symbols

Symbol Description
vqb pk Table Primary Key is bold and displayed at the top of the table.
vqb table alias Table Alias is used to shorten your Join Statement. vqb sorting
vqb coloured header Colored table header marks the first table in your Join Statement.
vqb colourless header Colorless header marks a joined table in your Join Statement.
vqb line Line goes from the joined table to the first table.

Join symbols

Available Join types are described in the table below. The Visual Query Builder can show results only for those types of Joins that are supported by your database.

Symbol Description
vqb Inner Join Inner Join
vqb Left Join Left Join
vqb Left Outer Join Left Outer Join
vqb Right Join Right Join
vqb Right Outer Join Right Outer Join
vqb Full Join Full Join
vqb Full Outer Join Full Outer Join
vqb Cross Join Cross Join

Settings

You can customize the diagram view using the bottom toolbar to make the work with the diagram easier.

vqb_settings_menu

  • Layout updates the diagram view to display all of its objects in the most optimal way.

  • Zoom in and Zoom out enlarges or shrinks the diagram view.

  • Settings menu contains additional settings of the Visual Query Builder. Press the Settings button at the bottom toolbar to open it.

    • Layout on update enables Auto-layout feature. As soon as you add a new object to the diagram, the diagram view will automatically be updated to display all of its objects in the most optimal way.
    • Show join type on entities moves Join labels from lines into headers of joined tables.
    • Show Type adds information about column types into entities.
    • Show Icons adds icons of column types into entities.
    • Notation changes the representation of connection lines. Simple notation is set by default. You can change it to the IDEF1X language type.

Visualization of an existing SQL query

If you write a JOIN statement by yourself and then want to convert it to the diagram view, just switch the SQL Editor with your statement to the Visual Query Builder.

Note: the Visual Query Builder can transform the syntax of your query, but it does not affect the query result in the Result set.

SSO

Single Sign-On

CloudBeaver Enterprise supports federated authentication for Single Sign-On (SSO) access into the application. 

SSO is an authentication service which permits a user to log in with single credentials to multiple applications.

SSO in Cloudbeaver allows to:

  • log in to the application by users who have been given rights.

  • get access to databases according to users’ roles.

Cloudbeaver supports SAML and OpenID  authentication methods for SSO.

SSO for AWS 

You can configure SSO access for AWS. In order to provide users permission to your AWS cloud resources (RDS, DynamoDB, etc.) you need to configure AWS federated access proxy user. You can find more information here: configuring SAML assertions for the authentication response:

  1. Go to the AWS Settings tab and enable the Federated authentication. 

administration_aws_settings.png

  1. Add the Proxy User on the same page. You can set the current user or add a new one.

  2. Create SAML configuration. You can find more information here: SAML Authentication

When an AWS user is logged into CloudBeaver using SSO, it has the Proxy User and the IAM user’s identity-based permissions.

Actual permission set and user role are configured in attribute mappings of SAML integration.

Proxy user permissions

Proxy use must have permissions to access you databases. Besides that it must have permission to generate federated tokens for end-users based on requested roles.
Make sure it have following AWS policies:
Policy name | Description
—|—
arn:aws:iam::aws:policy/service-role/AWSQuickSightListIAM | Allows to list IAM policies and permissions

Also make sure it has following STS permissions:
Permission | Description
—|—
sts:GetSessionToken |
sts:TagSession |
sts:GetFederationToken |
sts:GetAccessKeyInfo |
sts:GetCallerIdentity |
sts:GetServiceBearerToken |

More info at GetFederationToken policy

Notes: 

CloudBeaver does not keep your authentication information on the server-side and in configuration files.

Once your session expires, you will need to authenticate again. When a user logs out from the application, CloudBeaver also performs a session logout from Id Provider.

The latest CloudBeaver 21.1.5 2021 09 30

CloudBeaver 21.1.5 – 2021-09-30

  • Objects can be deleted and renamed via UI.
  • SQL Scripts generation is available in the objects’ context menu.
  • Connected and disconnected databases are divided into 2 groups in the SQL Editor.
  • Other UI improvements have been made.

Working with spatial/GIS data

Spatial data is a geometry or geography value that can be represented on a map or a graph. A geometry object consists of a series of points. Please find more details here.

CloudBeaver’s support of spatial data covers the following databases:

  • PostgreSQL (PostGIS)
  • MySQL
  • SQLite (GeoPackage)
  • H2GIS
  • SAP HANA
  • Oracle
  • SQL Server

Spatial data viewer

Data viewer GIS

If you click on an object on the map, the following data (strings, numbers, dates etc.) from every other column in the corresponding row will be displayed.

GIS Object info

SAML authentication

SAML configuration

If your Identity Provider uses SAML (Security Assertion Markup Language), follow this guide.

Enabling SAML authentication

Go to the Administration menu and enable SAML in the Server configuration tab.

server_configuration_saml_switcher

Configuring an external identity provider

  1. Go to the Identity Providers tab and create a new configuration using the SAML IdP details.

identify_providers_saml_configuration_creation

  1. Add details from your SAML IdP into the new configuration in CloudBeaver.

Configuring CloudBeaver integration in an external identity provider

  1. Open the created configuration in CloudBeaver and download the metadata file.

identify_providers_saml_configuration_metadata_file

  1. Go to the SAML IdP website and add the metadata parameters from the file (entityID and Location) to the SSO access settings, assign users and add the attribute mappings according to the SAML IdP requirements.

Each identity provider has its own configuration procedure, we will show how to do it in AWS in the next chapter.

AWS SSO configuration

Configuration

  1. Go to the Identity Providers tab and create a new configuration using the SAML IdP details as it is described above.

  2. Add details from your SAML IdP into the new configuration in CloudBeaver. 

Configuration in Amazon Configuration in CloudBeaver
AWS SSO sign-in URL IDP signon URL
AWS SSO sign-out URL IDP logout URL
AWS SSO issuer URL IDP Entity ID
  1. You can upload the metadata file to fill parameters automatically. 

  2. Or you can specify parameters manually:

Parameter Value
Application ACS URL https://HOST_NAME/api/saml/CONFIG_ID/acs
Application SAML audience https://HOST_NAME/api/saml/CONFIG_ID/metadata

Where HOST_NAME is the host name of your CloudBeaver installation, CONFIG_ID is the identifier of your SAML configuration.

Attributes

Attributes explanation:

Attribute Value Meaning
Subject ${user:email} User unique identifier (nameId). It is usually an email address.
https://aws.amazon.com/SAML/Attributes/SessionDuration 1800 Session duration in seconds. 1800 (30 minutes) is the default value
https://aws.amazon.com/SAML/Attributes/Role roleARN, idpARN IAM role identifier

Role is the most important attribute, it defines which IAM role will be used for user federation session. Role format: roleARN, idpARN. You can get role ARN in AWS IAM section https://console.aws.amazon.com/iamv2/home#/roles. Role ARN looks like this: arn:aws:iam::123678087624:role/RoleForSAMLAccess.

You can get IDP ARN in AWS identity providers page https://console.aws.amazon.com/iamv2/home#/identity_providers. IDP ARN looks like this: arn:aws:iam::123678087624:saml-provider/GSuiteSAML.

Testing SAML authentication

The Federated tab becomes available in the CloudBeaver authentication dialog after creating the configuration. The user can select the configuration and thereafter login into the application using SSO.

authetication_dialog_federated_saml

SQL Editor

Features

Overview

Description

SQL Editor supports autocomplete, syntax highlight, statement execution, script execution, and execution plan for some databases.

SQL Editor

Shortcuts

Shortcut Description
Ctrl+Enter Execute SQL statement
Ctrl+\ or Ctrl+Shift+Enter Execute SQL statement in new tab
Alt+X Execute script
Shift+Ctrl+E Show exectution plan
Alt+T Open SQL Editor in separate browser tab
Shift+Ctrl+F Format script

Statement Execution

Place the cursor on the line with the statement or select part of the script to execute the statement. Click on the Run icon in the left toolbar or use the Ctrl+Enter shortcut. The result of the statement execution will be shown under the script editor area. Results will be grouped (Result - 1 (1), Result - 1 (2)) if statement execution is finished with more than one result.

Statement Execution

Script Execution

Click on the Script icon in the left toolbar or use the Alt+X shortcut to execute the script. The summary result will be shown in the Statistics tab, and results will be shown in separate Result tabs.

Script Execution

The latest CloudBeaver 21.1.3 2021 08 30

CloudBeaver 21.1.3

  • Table rows can be created and deleted from the Data Editor.
  • It is possible to preview scripts in the Data Editor.
  • The dialog to enter the credentials appears when you test connections.
  • Different UI bugs have been fixed.

Server configuration

CloudBeaver offers different settings that allow configuring the server. The administrator can set the Server configuration settings when configuring the app for the first time, or it can be done later in the Administration Menu.

Server Configuration

Server information

Basic settings such as Server name and Session lifetime.

Configuration

Custom connections

Whether users can create connections by themselves or it can be done only from the Administration Menu.

Navigator simple view

Defines how the Database navigator structure will look like.
You can read more about Simple and Advanced mode here.

Services

AWS

Enables AWS Services.

Authentication settings

Define different authentication methods.
You can read more about authentication methods here.

Security

Save credentials

Allow saving credentials for the pre-configured database.

Save users credentials

Allow saving credentials for non-admin users.

Value Panel

The Value panel provides additional space in the Data editor in which you can manipulate data. The panel is handy if you work with complex types (structures, arrays), long text data or BLOBs.

To open the panel, click the Value button on the right hand side of the Data tab. Alternatively, you can open the Value panel by clicking Show in value panel on a cell context menu.

To close the panel, click the Value button again.

Value Panel Buttons

The Value viewer panel displays just one value that is currently selected or in focus and allows editing.

At the top of the Value panel, you can find several tabs. The tabs depend on the current value type. For example, if your current value is a string, you will find 4 tabs (Plain text, HTML, XML, JSON), each representing a format the string can be shown in.

Value Panel Open