Granting privileges to the Production Mapping workspace components in PostgreSQL

Available with Production Mapping license.

The geodatabase administrator must grant specific privileges to the tables for all users that will be accessing the Production Mapping workspace. This can be accomplished by creating database roles and assigning the roles to the individual users.

Granting permissions

In pgAdmin or psql grant permissions to the Production Mapping workspace tables using roles.

Note:

Copying and pasting the example 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 sqltext from pg_tables where tableowner = 'pm'
union all
select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tableowner || '.' || tablename || ' FROM pmviewer;' AS sqltext from pg_tables where tableowner = 'pm';

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

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

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

--Grant roles to editor and viewer users
GRANT role_pm_pm_editor TO pmeditor;
GRANT role_pm_pm_viewer TO pmviewer;