ArcGIS Server Banner

User permissions for geodatabases in PostgreSQL

User permissions for geodatabases in PostgreSQL
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note:This topic was updated for 9.3.1.

The following tables list the minimum required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator.

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.

In most cases, owners of individual datasets should use ArcCatalog to set other users' permissions on these datasets. For information on this, see Granting and revoking privileges on datasets.

Note that to perform an upgrade, the ArcSDE administrator must have write permission to either the SDEHOME/geocode directory on the server or the directory to which the TEMP environment variable is set. This is because during an upgrade, ArcSDE attempts to make backups of the locator files in the geocode directory. If that fails, ArcSDE attempts to write the backups to the TEMP directory. If the ArcSDE administrator does not have write permission to either of these directories, a warning is generated and new locators are loaded.

Individual database users in PostgreSQL are referred to as roles. You can create groups based on the common tasks the users perform, add the roles to the groups, then assign permissions to the groups.

The table below lists permissions to be granted to each of three common groups—data viewers, data editors, and data creators—and permissions needed by the ArcSDE administrator (the SDE role) for day-to-day operations.


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 editors need to access
 SELECT, INSERT, UPDATE, and DELETE on other users' datasets

Data creator
  • USAGE on sde schema

  • USAGE on all other schemas that contain data that data creators need to access

  • CREATEDB

If using 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 <user_name>)
ArcSDE administrator USAGE on all other user schemas

If this is not granted, the ArcSDE administrator will not be able to compress the database.

NOTE: CONNECT on database is granted to PUBLIC by default. If you revoke this privilege from PUBLIC, you will need to explicitly grant CONNECT on databases to specific groups and/or roles.

ArcSDE administrator installation/upgrade required permissions

To perform the ArcSDE component installation and geodatabase upgrades, the ArcSDE administrator (the sde user) must be granted super user permission in the database. You can revoke this permission after installation or upgrade is performed.