Implement role based security for PostgreSQL database - 11.x

Overview

This article describes in detail how to implement a role based security.

Step-by-step process

Define roles in the database for a Confluence user

Use the following commands to create user, role and grant the privileges to the role:

• create user bob; /* bob is a Confluence user whose user ID/username in Confluence is bob
• create role dbrole;
• grant dbrole  to bob;
• grant SELECT ON cwd_group to dbrole; /* you are granting the SELECT operation for the table cwd_group to the role DB_role

Set up the data source for SQL

Let's say you have an application data source already defined in server.xml (as shown in the screenshot), for which you want to implement this role based security. 

Follow the steps as:  

1. Go to UPM > Manage apps and find the SQL for Confluence app.
2. Click Configure.
3. Select the View and modify data source profiles tab.
4. Click Add profile. To add a role based security data source, select the data source as required. Refer to the screenshot below:

   

The new profile (in this case, "rolebased") is available immediately. Test it on a page to ensure expected behavior.

• If the user bob tries to access the page: 
  Since SELECT on cwd_group table is granted to bob, only the respective data is visible but not cwd_user table data.

• If any other user (let's say random user who has no database role associated with their own account/userid) tries to access the page: 
  Since there is no role associated with this user, an error message is shown as: