Introduction

The basic provision of the plugin are twin macros that store sql and generate a table of information from a database when viewed. Sending SQL and building a table of data from the result is the trivial part of the task. The more difficult part is controlling access and monitoring usage so the plugin doesn't generate performance problems either on the Confluence server or the database.

As a word of caution, providing the ability to place SQL on a wiki page that uses a profile with saved credentials to interact with a database is convenient, however, it is inherently riskier compared to using a dedicated application. While we've taken a lot of precautions to protect the connection, if the database contains sensitive information it is not recommended setup a profile to it from Confluence. At a minimum, it is strongly recommended to use a database account with read only permissions.

At the time of this development, there are several commercially available plugins that do the same thing. There are two reasons for authoring this work. First is cost. We presently have an unlimited user license for our Confluence instance and are looking to try out a few experiments that need to pull data from a database. It was a rather expensive proposition to buy an unlimited user license to one of the commercial SQL plugins as we experiment with how we want to to use the functionality. The second rationale for making yet another SQL pluging was wanting a different security paradigm. This plugin allows the Confluence administrators to set some limits to protect Confluence and then delegate access to others to manage the connection profiles. When creating a profile, there is a provision to specify which users are allowed to use the profile so that only users who know how to safely write SQL against the database can.

At present, the plugin should be able to talk to DB2, Derby, Microsoft SQL Server, MongoDB, MySQL, Oracle, PostgreSQL and Sybase; however, only Microsoft SQL Server, MySQL, Oracle and PostgreSQL have been tested at this time.

Vulnerability Alerts

CVE-2020-35121: Javascript Injection Attack possible with versions prior to 1.5.0.

Prior to 1.5.0, it was possible by using various techniques to insert a javascript tag and code into the stored data of the macro. When another person viewed the page, the javascript would run. In 1.5.0 code was added to escape html special characters so what gets displayed will no longer be executed in the browser.

CVE-2020-35122: SQL Replacement Attack possible with versions prior to 1.5.0

Prior to 1.5.0, it was possible by using various techniques to either place a macro on a space where the user had write permissions or edit a macro if the user had write access and send arbitrary SQL to a connection profile for which the user was not authorized to access. In 1.5.0 the profile can be locked to particuar spaces insuring that only people with permissions to the profile and the space can define the SQL sent to the database.

Features at a Glance

Features in Detail

Database Connection Configuration Page

The Database Configuration page is accessed from the Configure button for Keysight's Database Connector plugin in the Manage Add-ons screen. There are several things for the Confluence Administrator to do there. Set the path to the jdbc jar files and or the content delivery network (CDN) urls to where they can be downloaded from. Grant permissions to access the Database Connection Profiles administration page to users or groups. Set various query limits to protect Confluence from bad SQL queries.

Set the path to the jdbc jar files

First, a Confluence administrator is expected to provide place the necessary jdbc JAR files on the server and then in the plugin configuration page specify the paths to them. This is necessary as in order to communicate with an external relational database system such as mysql, the jdbc drivers need to be provided to the plugin. For licensing reasons, these jdbc jar files cannot be distributed with the plugin. This plugin expects the drivers to be located on the local file system in specifically named folders inside a user defined folder. The inner child folders need to be one of the following names (including case and spaces): DB2, Derby, jTDS SQL Server, Microsoft SQL Server, MongoDB, MySQL, Oracle, PostgreSQL, Sybase. Inside of each folder there should be the associated jdbc jar file. If a database connection is not needed, then the child folder can simply be omitted. For example, if the driver folder is \JdbcDrivers, this plugin will be looking for the MySQL jdbc driver in any jar file found in the directory \JdbcDrivers\MySQL ( i.e. \JdbcDrivers\MySQL\mysql-connector-java-5.1.40.bin.jar).

Set the content delivery network url to the jar files

It is highly recommended to download the jdbc jar files, place them in an appropriate directory and point the plugin to that folder as described above because it will be much faster. The plugin cannot provide the jar files directly due to licensing constraints, but it can download them on the fly from a CDN. This is really useful when trying the plugin out as the configuration step above can be skipped. In order to get the correct version of the driver, a Confluence administrator can update the default url set in the plugin.

Grant permissions to use the Database Connector Profiles administration page

A Confluence administrator can provide the names or groups that can access the Database Connector Profiles administration page. This is helpful when organizations have divided responsibility for Confluence and the Database administration to two different teams. The Confluence administrators don't want the DB administrators to have Confluence administration privileges which then forces the DB administrators to have to work through the Confluence administrators to setup and maintain the connections. This can be a headache. With this feature, Confluence administrators can delegate management of the connection profiles to the people who own the databases. Confluence administrators will always have access to the connection profile administration page.

Query Limits

Confluence administrators can set limits on the number of rows to be received or the time it will take a query to run. If the Confluence administrator sets a hard row limit, then the plugin will add that limit into the SQL query and it's possible data truncation may silently occur. If there is a soft row limit and query pulls back more rows than the limit, then an email will be sent to the configured address. If the admin sets a hard timeout, the query will exit at that time returning an error to the user. If the admin sets a soft timeout and the query takes longer than that time, an email will be sent to the configured address.

Cache Configuration

The Confluence Administrator can set a threshold for the maximum lifetime for cache entries. This value can be overridden with a smaller value per instance of the Database Query, Database Query Compact and Macro Results Cache macros. The macros cannot increase cache lifetime beyond this value. The default is seven days.

Audit Logging

It can be useful to log every query being sent to a database. The plugin provides three methods to create a log. First is to set the Atlassian Log Level to Warn, that will cause every query to insert an entry into the atlassian-confluence.log file. Another method for creating a log it to send an email. The emails could be just archived in a generic email account, or processed by an automated job. The last approach is to put an entry into a database. The first step is to setup the database and create a profile to the audit log database. The database needs to have a table with the schema below:

            CREATE TABLE newLogEntry (
                newLogEntryId   INT(11)      NOT NULL AUTO_INCREMENT,
                fullName        VARCHAR(255) NOT NULL,
                userName        VARCHAR(255) NOT NULL,
                pageUrl         VARCHAR(255) NOT NULL,
                pageName        VARCHAR(255) NOT NULL,
                spaceName       VARCHAR(255) NOT NULL,
                databaseProfile VARCHAR(255) NOT NULL,
                rowCount        INT(11)      NOT NULL,
                queryDuration   INT(11)      NOT NULL,
                sqlQuery        TEXT         NOT NULL,
                timestamp       DATETIME     NOT NULL,
                CONSTRAINT pk_newLogEntryId PRIMARY KEY (newLogEntryId),
                CONSTRAINT uk_newLogEntryId UNIQUE (newLogEntryId)
            ) ENGINE=InnoDB;

Within Keysight, we use a schema that has some built in normalization. The schema can be downloaded from this wiki..

Database Connection Profile Administration Page

The Database Connection Profile Administration Page can be accessed from two places. First, by clicking on Database Connector Profiles in the Confluence Administration sidebar. Second, for users with rights to manage profiles, a link can be found in the macro properties for either of the two database macros. Once on the pages, a user can add, delete or update profiles. Passwords are never sent back to the browser for security reasons. Most of the parameters are self explanatory, but a few are worth describing.

The Authorized Users and Authorized Groups are used to set which users can use the profile. When a Confluence user inserts a Database Connector macro, they will only be able to see the profiles they have been given access to. Confluence administrators have access to all profiles by default.

In 1.5.1, a new field, Authorized Spaces, was introduced. It's important to set this field for security. When a profile specifies one or more authorized users and or groups, only those users will see the profile in the macro dropdown. However, once a plugin is placed on a page, any user with permissions to edit the pages can change the sql. With Confluence, the user based authorization should be considered more of a usability feature rather than a true security feature. The core problem is a malicious user can inspect a page to get the profile ID for a database profile and then use the Confluence REST API to create a page with the database connector macro using that profile. This allows them to have a database macro with the restricted profile and submit arbitrary sql to the database. The more secure approach is to restrict the profile to one or more spaces. This allows us to use the Confluence permissions layer to protect write access to that space and while a user may be able to create a counterfeit macro in another space with the desired profile id, it will be preveted from making a connection as the query is coming from the wrong space.

The Description field is not used except to contain some text about the profile. It is recommended to list the primary contact for the database should something go wrong.a

It is a best practice to only setup profiles using read only database accounts. This is because it would be a very easy mistake to not put a page restriction on a page with the Database Connector macro which could then be edited and used as a way to submit any sql against the database using the saved credentials in the profile.

Database Connection Macro

Description

The Database Connection macro provides a way for end users to insert SQL into the body of the macro and have it generate a table of data. The tricky part with database connections is not in establishing the pipeline but rather in the security protections. The approach of this plugin is to have either the Confluence administrators or Database Plugin Administrators define a connection and the users or groups that may access that profile.

Note, any user with rights to edit the page can change the SQL - and the profile has the credentials saved so it can be used as a means to execute any arbitrary SQL against a database. It is therefore recommended to put a page restriction on the page to limit which users can update the SQL.

Usage

To use the macro, insert the macro using the normal Confluence methods and adjust the parameters as needed.

If the user has permissions to access the Database Profile Connections administration page, a link to the page will be inserted into the macro browser just below the macro description.

Purpose

To allow safe connections to databases allowing data from the database to be displayed on a Confluence pages.

Database Connection Compact Macro

Description

The Database Connection Compact macro is a variant of the Database Connection macro. The only differentiation is the SQL is provided in the body of the Database Connection macro and as the value for a macro parameter for the Database Connection Compact.

Note, any user with rights to edit the page can change the SQL - and the profile has the credentials saved so it can be used as a means to execute any arbitrary SQL against a database. It is therefore recommended to put a page restriction on the page to limit which users can update the SQL.

Usage

To use the macro, insert the macro using the normal Confluence methods and adjust the parameters as needed.

If the user has permissions to access the Database Profile Connections administration page, a link to the page will be inserted into the macro browser just below the macro description.

Purpose

Both the Database Connection and Database Connection Compact macros are provided by the plugin as depending on the circumstances one may be a better fit than the other. They do the exact same thing - which is guaranteed as they exercise the same code. The primary difference is the Database Connection provides a lot more space to insert and view the SQL as the SQL is placed in the body of the macro. The down side to this approach is that to preview the SQL, the entire page must be previewed and then returned to edit mode. That is quite a slow operation. It also means that anybody editing the page can see the sql and therefore it's easier to make unintended changes. The Database Connection Compact macro keeps the SQL in the macro parameter so it's harder to make unintended changes and it's easy to preview changes to the SQL from the macro browser. It has the downside of being a small text area to enter and view the SQL.

Macro Results Cache Macro

Description

The Macro Results Cache macro is used to cache the output of it's body. The maximum cache lifetime is set in the Database Connector plugin configuration properties.

Usage

To use the macro, insert the macro using the normal Confluence methods and adjust the parameters as needed.

Purpose

The Macro Results Cache was created by taking the logic developed for caching results in the Database Query macro and generalizing it. One of the intended use cases is to allow users to cache a graph generated from a database generated table improving performance.

A note about the Macro Results Cache with Confluence Data Center

When used in Confluence Data Center, each of the nodes will have it's own cached copy of the results. This is because the temp folder where some macro such as the Chart macro create and server their images from are on the cluster node file system rather than the shared file system. Thus, other sibling nodes can't access the images created by a node. By having a cache for each node, the cache works mostly as expected. The one unexpected result is the cached information may differ. For example, if a chart wrapped with a Macro Results Cache macro is viewed on one node, then the data changes and then it's viewed on a different node. The view from the two nodes will be different until either the cache for the first node expires or somebody manually presses the Refresh link causing the cache for both nodes to be reset.

Home