The PTC Datasource Settings can be found in the General Configuration of your Confluence Instance in the Page Tree Creator Section.

Security Tip: Create a special user in your DBMS with

  • No update, create or delete permission

  • With view permission on the desired tables

Use Cases

Configure a Datasource

Both a JDBC driver and a connection string are needed to connect an external database. The correct JDBC Driver needs to be copied into the classpath of the running Confluence server. Have a look at Atlassian Documentation - Database JDBC Drivers for more information .

Confluence already comes with some drivers bundled. If compatible with your DBMS, you don’t need to copy your own drivers 🙂

Extra Security with Password Encryption

The ‘password’ field for your DBMS is stored in the Confluence DB. To mitigate some risks, the password is encrypted in this process. Per default, we use our own encryption key. As additional security measurement, you can (and probably should) supply your own encryption key. For this, you need to provide the PTC_DATASOURCE_KEY environment variable when starting your Confluence instance.

  • Your Confluence instance needs to be restarted for the changes to take effect

  • Set the PTC_DATASOURCE_KEY environment variable to provide your custom encryption key

  • The encryption key needs to be 16, 24 or 32 bytes long

  • Ask your system administrator or solution partner for more information on this topic

Connection Examples

Below are Examples for PostgreSQL, Microsoft SQL Server, Oracle, MySQL JDBC Driver and Connection String.

PostgreSQL

JDBC Driver

org.postgresql.Driver

Connection String

jdbc:postgresql://[host]:[port]/[database_name]

Microsoft SQL Server

Microsoft JDBC Driver for SQL Server

JDBC Driver

com.microsoft.sqlserver.jdbc.SQLServerDriver

Connection String

jdbc:sqlserver://[host]:[port];database=[database_name]

jTDS 1.3.1 driver

JDBC Driver

net.sourceforge.jtds.jdbc.Driver

Connection String

jdbc:jtds:sqlserver://[host]:[port]/[database_name]

Oracle

JDBC Driver

oracle.jdbc.driver.OracleDriver

Connection String

jdbc:oracle:thin:@//[host][:port]/[SID]

MySQL

JDBC Driver

com.mysql.jdbc.Driver

Connection String

jdbc:mysql://[host]:[port]/[database_name]

Test the connection

To make sure that the JDBC Driver and the Connection String can connect to your DBMS click on test:

Configure a Query

The configured queries can be selected in the PTC Placeholder Macro.

Parameter

Example

Name

Identification for the query. The name will be shown in the Placeholder Macro.

Project ID

Query

SQL Select query to be used. A query needs to have two columns in the result. The first column will be the displayed text in the replace dialog and the second column will be the replacement.

SELECT CONCAT(project_name, ' ', project_id) AS Text, 
  project_id
  FROM Projects
CODE

Test a query

Use the run button to check if a query is valid. If the query is valid the first ten results are returned.