How to implement role based security for SQL queries

Description

The Atlassian folks came up with a way to implement role-based security based on user IDs to help them manage their data access better. This requires app version 5.2 and is simple to configure through data source profiles. This does require your database be set up so that roles are defined for each user in Confluence.

Steps

Ensure that the database administrator defines roles for each Confluence user by user ID and sets appropriate authority to data based on roles. A role for anonymous should also be added.
Go to UPM > Manage apps and find SQL for Confluence.
Click Configure.
Add a data source profile and:
- Add a dataSource parameter to reference an application server data source, if it has a different name that the profile.
- Add beforeSql and afterSql parameters similar to the example given below.

The new profile is available immediately - test it on a page to ensure expected behavior

Implementation without page updates

After testing, you can use data source alias support to change an existing (application server) data source to use the new role-based security model without requiring any page updates. Simply rename the data source profile to the same name as the existing (application server) data source already used in the SQL macros.

Cache macro

The Cache Macro must not be used around any content that is rendered differently for different users. The role-based model described here renders differently depending on the user, so the Cache Macro must not be used in this case.

Example

For Postgres, the following SQL works:

beforeSql=begin; SET LOCAL ROLE @user_id@; 
afterSql=; rollback;

The substitution variable @user_id@ is replaced in SQL by the SQL macros with the currently logged in user ID or anonymous (if not logged in).