Create datasource based profiles

Owned by Smita Nair
Last updated: Mar 16, 2021
5 min read

Profiles provide the means to connect, query, and retrieve relevant data to be displayed on pages. Using profiles ensures that sensitive information such as database connection details and credentials of the database user can be hidden from others using the macro on Confluence pages. 

Important information:

Administrators must ensure to use credentials of database users with read-only privileges for profiles intended for use with the SQL Query macro. We recommend creating a read-only database user to be used specifically with the SQL for Confluence - SQL Query macro.

Steps

To create profiles, administrators must:

  1. Navigate to Manage apps > SQL Configuration > Data source profiles.
  2. Click the Add profile button to open the Add profile wizard.

    1. Select your database. See here to learn more.

    2. Provide connection and user credentials to access the specified database. Click this for more details.

      About Macro Security:

      Administrators can specify trusted users and restrict access to specific macro parameters or parameter values in SQL Configuration > Macro security and add another layer of security. Refer to this documentation to learn more.

  3. Save your profile.

The newly created profile is now listed in the Data source profiles page.

Add profile wizard - Step 1

Select the database that the macro must access, query, and retrieve data from.

Add profile wizard - Step 2

After selecting the relevant SQL database, provide details such as the database connection details, user authentication method, and more. 

The following table lists the parameters to be provided:

SectionParameterDefault valueDescription
Profile name
Enter the name to be used for the profile. Users can then select from the list of profiles displayed in the macro editor. This field is mandatory. 
Database connectionHostname

Enter the host name or the IP address of the database server to be accessed. This field is mandatory. 

Configuration note

Ensure that the database host allows connections from the SQL for Confluence app. This requires updating the host's firewall settings to allow these IP addresses:

  • 184.73.28.18
  • 52.45.207.55

If these addresses are not allowed, errors are generated and relevant messages are shown.  

Port
Enter the port number required to access the relevant database on the server. This field is mandatory.
Database
Enter the database name the macros must query for information. This field is mandatory.

Authentication type

Important information:

Administrators must ensure to use credentials of database users with read-only privileges for profiles intended for use with the SQL Query macro.

Authentication typeBasic

Select the authentication method to be used to connect to the specified database. This field is mandatory.

The options are as follows:

  • Basic - This is the default authentication method. This method uses the basic user authentication (user name and password) to validate access to the required database.

  • AWS IAM - This authentication method is used to manage access to Amazon Web Services (AWS) and resources securely with access keys. 

    The Amazon Relation Database Service (RDS) documentation specifies the databases supported with IAM authentication as follows:

    • Amazon RDS for MySQL
    • Amazon RDS for PostgreSQL

    For more information about authenticating to a database instance using IAM, see IAM database authentication for MySQL and PostgreSQL.

    Refer to this article for more information about IAM, and this article to know more about AWS access keys used to authenticate users. 

User

Enter the user name to be used to log in the required database or AWS account. Based on the value of Authentication type, provide the following:

  • Basic: Enter the user name or ID to be used to log into the required database. 
  • AWS IAM: Enter the user ID of the AWS account used to log into the required database.

This field is mandatory.

Password

Enter the password to be used along with the user name to access the required database. This field is mandatory.

  • This field appears only if Authentication type is Basic.
  • All profile sensitive information (such as passwords) are encrypted to provide enhanced security.
AWS region

Enter the AWS region (specified when creating the AWS account) where the relevant database is hosted.

An AWS region is a physical location that contains clusters of data centers where cloud based services are accessed over the internet. For more information about regions, refer to the AWS global infrastructure documentation. 

This field is displayed only if Authentication type is AWS IAM.

Key ID

Enter the AWS key ID of the user to log in the required database. This field is mandatory.

This field is displayed only if Authentication type is AWS IAM.

Access keys are long-term credentials for an IAM user or the AWS account root user. For more information about AWS access keys, refer to the AWS IAM documentation.

Secret access key

Enter the AWS secret access key (part of a user's AWS access key) to be used to log in the required database. This field is mandatory.

This field is displayed only if Authentication type is AWS IAM.

For more information about AWS access keys, refer to the AWS IAM documentation.

Test connection

Click this link to test whether a connection can be established with the required database using the specified authentication details. After the test is completed, either of the following indicators are shown:

IndicatorDefinition

Indicates that the connection information supplied for the data source profile enabled a successful validation. Thus, further connections can be made from Confluence to the targeted database.

Note that this indicator does not imply that the profile is actually being used on a Confluence page.

Indicates that the connection specific information is incorrect, and thus, the connection to the targeted database is not established.

A relevant error message about the connection failure is displayed along with this indicator. If this indicator is displayed, update the profile details with the relevant changes.

Extended parametersLimit rows processed250

Select the maximum number of rows to be processed and displayed on Confluence pages. This setting prevents queries from using excessive resources that can result in a large number of rows.

Individual queries can use the Limit rows processed parameter in the macro editor to override this value.

The following options are available from a selection list:

  • 250
  • 500
  • 1000
  • 2500
  • 5000
  • 10000
  • 25000


Refer to this note to know more about the default behavior for common parameters available in the configuration as well as macro editor.

Limit query time120

Select the time in seconds that a query can take before a forced timeout. This prevents queries that take too long, from impacting other users.

Individual users can use the Limit query time parameter in macro editor to override this value.

The following options are available from a selection list:

  • 1
  • 5
  • 10
  • 30
  • 60
  • 120

Refer to this note to know more about the default behavior for common parameters available in the configuration as well as macro editor.

Priority used for common parameters in configuration and macro editor:

There are some parameters, such as Limit rows processed or Limit query time, that are available in Global configuration, profile configuration, as well as macro editor. Any parameter defined at the macro level overrides the values set at the Global or profile configuration levels. The order of overriding the default setting is:

Macro level parameter > Profile configuration > Global configuration

Add profile wizard - Step 3

Click Save profile to create the profile, or, Cancel to go back to the Data source profiles screen. The newly created profile is now displayed along with the connection status on the Data source profiles screen.

Other resources

Click any of the given links to learn more about the app:

Log a request with our support team.

Confluence®, Jira®, Atlassian Bamboo®, Bitbucket®, Fisheye®, and Atlassian Crucible® are registered trademarks of Atlassian®
Copyright © 2005 - 2024 Appfire | All rights reserved. Appfire™, the 'Apps for makers™' slogan and Bob Swift Atlassian Apps™ are all trademarks of Appfire Technologies, LLC.