1 Overview
- - 1.1.1 Step 1 – Create a Script to Perform Grant Operations
    - 1.1.1.1 Option 1: Version with RAISE NOTICE (preferred)
    - 1.1.1.2 Options 2: Version without RAISE NOTICE
  - 1.1.2 Step 2 – Create Changelog Properties for Replacement Variables
  - 1.1.3 Step 3 – Add the ChangeSet to the ChangeLog
    - 1.1.3.1 Option 1: Using psql (preferred)
    - 1.1.3.2 Option 2: Using JDBC
  - 1.1.4 Step 5 – Test the New Change Set
- 1.2 Sample Execution Output

Overview

There are situations where grants may need to be created for all objects that meet a particular criteria such as belonging to a specific schema. This can be accomplished by creating a changeset that is executed at the end of every deployment to apply the appropriate grants.

Step 1 – Create a Script to Perform Grant Operations

Create a script that will generate the desired grant or alias statements. The script can utilize change log properties (Setting Properties in the Project Changelog ) to customize values for different steps in the pipeline. The following is an example of a script to grant privileges to an Application User for all tables in a specified schema. The criteria for APPUSER will be specified by properties from the project changelog.

Save the script to a location outside the SQL SCM for the Datical Project since we will manually add the changeset to the changelog.

Option 1: Version with RAISE NOTICE (preferred)

Adding RAISE NOTICE to the script will allow for logging the output to the Execution Output

DO $$
DECLARE
    t record;
    table_count integer := 0;
BEGIN
    RAISE NOTICE 'Starting permission grant process...';
    
    FOR t IN
        SELECT table_schema, table_name 
        FROM information_schema.tables 
        WHERE table_schema NOT IN ('pg_catalog','information_schema')
    LOOP
        -- Log each table being processed
        RAISE NOTICE 'Granting permissions on %.% to ${APPUSER}', t.table_schema, t.table_name;
        
        EXECUTE format('GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE %I.%I TO postgres', 
                      t.table_schema, t.table_name);
        
        table_count := table_count + 1;
    END LOOP;
    
    RAISE NOTICE 'Permission grant process completed. Processed % tables.', table_count;
END;
$$;

Options 2: Version without RAISE NOTICE

DO $$
  declare
    t record;
BEGIN
  FOR t IN
      SELECT table_schema, table_name from information_schema.tables where table_schema not in ('pg_catalog','information_schema')
  LOOP
      execute format('GRANT INSERT, SELJ

Step 2 – Create Changelog Properties for Replacement Variables

If the script requires different values based upon context or labels, create the required properties. Go the Project Settings in the Datical GUI and select Manage Properties.

Setting up Property Substitution

Add the properties to be used by the script along with the Context or Label where the property value should be applied. Within the script specify the Property Key using the syntax ${property_key}.

Step 3 – Add the ChangeSet to the ChangeLog

From the Datical GUI, Select “Add a Change Set”.

Add a Change Set

Option 1: Using psql (preferred)

This option allows the RAISE NOTICE Execution Output to display.

Scroll down and select the “Execute with psql for Postgres Databases” change type.

psql Change Type

Browse to select the file.

On the next screen select Finalize
On the Create Change Set screen:
Enter the ChangeSet ID (any text string) and the Author.
Set the Execution Settings to:

“Redeploy this Change Set every time the contents are modified” – Allows modification of the SQL file saved to the resources directory. Without this there will be “checksum” errors if the file is modified.

“Deploy this Change Set every time a deployment is performed” – Changeset will be run for every Deployment.

Set Deploy Order to “After Other Change Sets”.

Save changes by selecting: File >> Save.
Add, Commit, and Push the changes to the SCM.

Option 2: Using JDBC

Note: You will not receive the RAISE NOTICE Execution Output using this method.

Scroll down and select the “Execute a SQL script file using JDBC”/“Custom SQL (External File)” change type. Note that “Execute a SQL script file using JDBC” is the newer name, and “Custom SQL (External File)” is the older name for the same change type.

JDBC Change Type

Browse to select the file. Deselect Split Statements.

On the next screen select Finalize
On the Create Change Set screen:
Enter the ChangeSet ID (any text string) and the Author.
Set the Execution Settings to: