User permissions for geodatabases in PostgreSQL

This topic describes the required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator.

Individual database users in PostgreSQL are referred to as login roles. To group together login roles based on the common tasks the users perform, you can create group roles, add the login roles to the group roles, then assign permissions to the group roles.

The following table lists permissions to be granted to each of three common groups—data viewers, data editors, and data creators—and the minimum permissions needed by the ArcSDE administrator (the sde login role) for day-to-day operations. Permissions needed by the ArcSDE administrator to upgrade the geodatabase are listed at the end of this topic.

Type of user	Database permissions	Dataset permissions	Notes
Data viewer	USAGE on sde schema USAGE on all other schemas that contain data to which data viewers need access	SELECT on specific datasets
Data editor	USAGE on sde schema USAGE on all other schemas that contain data that editors need to access	SELECT, INSERT, UPDATE, and DELETE on other users' datasets
Data creator	AUTHORIZATION on the user's own schema USAGE on sde schema USAGE on all other schemas that contain data that data creators need to access		If using the PostGIS geometry type, you must also grant SELECT, INSERT, UPDATE, and DELETE on the public.geometry_columns table (syntax = GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.geometry_columns TO <role_name>). Also grant SELECT on the public.spatial_ref_sys table (syntax = GRANT SELECT ON TABLE public.spatial_ref_sys TO <role_name>).
ArcSDE administrator	USAGE on all other user schemas		If this is not granted, the ArcSDE administrator will not be able to compress or upgrade the geodatabase.

PostgreSQL user permission table

Note:

The CONNECT database permission is granted to the public group role by default. If you revoke this privilege from public, you need to explicitly grant CONNECT on databases to specific login or group roles.

You can use one of the administrative applications that connect to PostgreSQL databases, such as pgAdmin III, to administer user privileges. Or you can use SQL statements to grant and revoke privileges and permissions.

Tip:

A SQL script, roles_schemas_privileges.sql, is installed in SDEHOME > tools > postgres. This script can be altered and used to create users and their schemas and grant permissions in the database.

Dataset privileges should be granted or revoked by the dataset owner using the Change Privileges geoprocessing tool that is available in ArcGIS Desktop. See Granting and revoking privileges on datasets and Change Privileges for instructions.

ArcSDE administrator installation/upgrade required permissions

To perform the ArcSDE component installation and create or upgrade a geodatabase, the ArcSDE administrator (the sde user) must be granted super user permission and have the ability to select all the datasets in the geodatabase. You can revoke this permission after installation, geodatabase creation, or upgrading is performed.