How To: Generate Auto Permissions for PostgreSQL
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 (https://datical-cs.atlassian.net/wiki/spaces/DDOC/pages/896569871 ) 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.
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, SELECT, UPDATE, DELETE ON TABLE %I.%I TO ${APPUSER}', t.table_schema, t.table_name);
END LOOP;
end;
$$;
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.
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”.
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.
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:
“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.
Step 5 – Test the New Change Set
Package and deploy a simple “CREATE TABLE” script. Verify that the grants are applied to the new table.
Copyright © Datical 2012-2020 - Proprietary and Confidential