Connect to PostgreSQL

To connect your PostgreSQL database to Atlassian Analytics, you’ll need to do the following:

1. Allow Atlassian IP addresses for outbound connections.

2. Create a PostgreSQL read-only user.

3. Complete the connection form in Atlassian Analytics.

Allow Atlassian IP addresses

Modify the pg_bha config file

By default, PostgreSQL restricts connections to hosts and networks included in the pg_bha.conf file. You’ll need to add a line for each of the Atlassian outbound IP addresses to this file to allow connectivity to your database. Get the list of Atlassian IP addresses for outbound connections.

For example, these are lines we would add so our read-only user called atlassian_read_only can connect to our database called mydatabase:

Refer to the PostgreSQL documentation for more details on how to modify the pg_hba.conf file.

Modify the postgresql config file

You’ll also need to ensure that the listening_addresses in the postgresql.conf file is set to either '*' (all addresses) or includes all Atlassian IP addresses. For example: listen_addresses='13.52.5.96/28 13.236.8.224/28 18.136.214.96/28 18.184.99.224/28 18.234.32.224/28 18.246.31.224/28 52.215.192.224/28 104.192.136.240/28 104.192.137.240/28 104.192.138.240/28 104.192.140.240/28 104.192.142.240/28 104.192.143.240/28 185.166.143.240/28 185.166.142.240/28'

Create a PostgreSQL read-only user

This step isn’t required, but we highly recommend you create a specific read-only user that you’ll use to connect your PostgreSQL database to Atlassian Analytics.

There are two ways to create a PostgreSQL read-only user:

• Using psql

• Using pgAdmin

Create a read-only user using psql

To create a read-only user using psql:

To create a read-only user using psql:

1. Open a new terminal window, where you’ll input a series of commands to create the user.

2. Connect to your PostgreSQL instance: sudo -u postgres psql

3. Select the database you want to connect to Atlassian Analytics: \c database_name;

4. Create a new role for your read-only user: CREATE ROLE atlassian_read_only_user LOGIN PASSWORD 'secure_password';

5. Grant the necessary privileges for the new user to connect to your database:
   
   

   1 2 GRANT CONNECT ON DATABASE database_name TO atlassian_read_only_user; GRANT USAGE ON SCHEMA public TO atlassian_read_only_user;

6. Generate the GRANT statements that will grant SELECT access to the new user: SELECT 'GRANT SELECT ON '||schema_name||'."'||table_name||'" TO atlassian_read_only_user;' FROM pg_tables WHERE schema_name IN ('public') ORDER BY schema_name, table_name;

7. Copy the GRANT statements generated by the command in Step 6 then paste them into your terminal window. To restrict access to a subset of tables, only run the GRANT statements for those tables.
   
   Example GRANT statements:

Your new read-only user should now have the necessary permissions for Atlassian Analytics to connect to and query your database.

Create a read-only user using pgAdmin

Instead of using psql, you can use pgAdmin to create your read-only user.

To create a read-only user using pgAdmin:

1. Connect to your PostgreSQL server instance using pgAdmin.

2. In the server tree, right-click Login/Group Roles > Create > Login/Group Role.

3. Give the group role a descriptive name then select Save. You don’t need to create a password for the group role yet. You’ll create a password for the login role in a later step.

4. In the server tree, expand Databases, right-click on the database you want to connect to Atlassian Analytics, then select Properties.

5. In the Security tab, add a privilege for the newly created group role.

   1. Select the group role in the Grantee dropdown.

   2. Select Connect under Privileges.

   3. Select Save.

6. In the server tree, expand Databases > expand Schemas > right-click public > select Grant wizard.

7. In the Object selection step, select the tables you want to access, then select Next.

8. In the Privilege selection step:

   1. Select the group role you created in the Grantee dropdown.

   2. Check Select under Privileges.

   3. Select Next.

9. Review the SQL statements that will be executed on the database, then select Finish to run the statements and complete the process.
   
   Note: If your schema is not “public”, you’ll also need to grant USAGE permissions on the schema.

10. Next, create a login role. In the server tree, right-click Login/Group roles > Create > Login/Group role.

11. In the General tab, give the login role a descriptive name.

12. In the Definition tab, enter a secure password for the login role.

13. In the Membership tab, add the newly created group role from the previous steps to the Member of section, then select Save.

Your new read-only user should now have the necessary permissions for Atlassian Analytics to connect to and query your database. Use the credentials you created for the login role when you set up your PostgreSQL connection in Atlassian Analytics.

Add PostgreSQL to Atlassian Analytics

After creating a read-only database user and allowing our outbound IP addresses, you can complete the connection form in Atlassian Analytics to add your PostgreSQL database.

To add PostgreSQL to Atlassian Analytics:

1. Select Data from the global navigation.

2. Select Add data source > PostgreSQL.

3. Fill out the required fields of the connection form:

   1. Hostname or IP - Where your database is hosted.

   2. Port:- Default port number for PostgreSQL is 5432.

   3. Username - Username of the PostgreSQL read-only user you created.

   4. User password - Password of the PostgreSQL read-only user you created.

   5. Database name - Name of your PostgreSQL database.

   6. Schema - You must select at least one.

Atlassian Analytics will connect with your database to get your schema, then your data source will be ready to use.