Granting privileges to the Workflow Manager (Classic) workspace components in PostgreSQL

Available with Workflow Manager license.

The geodatabase administrator must grant specific privileges to the tables for all users who will be accessing the Workflow Manager (Classic) workspace. This can be accomplished by creating database roles and assigning the roles to the individual users.

Note:

Copying and pasting the examples may cause syntax errors.

In pgAdmin or psql grant permissions to the Workflow Manager (Classic) workspace tables using roles.

Note:

Copying and pasting the examples may cause syntax errors.

Grant privileges:

--This SQL query creates a script to revoke permissions granted directly to pmeditor, pmviewer users.

select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON' || tableowner ||'.'|| tablename || 'FROM
pmeditor;' AS sqltxt from pg_tables where tableowner = 'wmx'
union all
select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON' || tableowner ||'.'|| tablename || 'FROM 
pmviewer;' AS sqltext from pg_tables where tableowner = 'wmx';

--This SQL query creates a script to grant permission to the roles.

--role_wmx_wmx_editor
select 'GRANT SELECT, INSERT, UPDATE, DELETE ON' || tableowner ||'.'|| 'TO
role_wmx_wmx_editor;' AS sqltext
from pg_tables
where tableowner = 'wmx';

--role_wmx_wmx_viewer
select 'GRANT SELECT ON' || tableowner ||'.'|| tablename || 'TO role_wmx_wmx_viewer;' AS sqltext
from pg_tables
where tableowner = 'wmx';

--Grant roles to editor and viewer users
GRANT role_wmx_wmx_editor TO pmeditor;
GRANT role_wmx_wmx_viewer TO pmviewer;