Skip To Content

Privileges for geodatabases in PostgreSQL

Privileges determine what someone is authorized to do with the data and the database. Privileges should be assigned based on the type of work the person does within the organization. Is this person involved with administration of the geodatabase? Does he or she need to edit or create data? Or would this person only need to query the data?

User privileges are set at different levels. This topic describes the required database and dataset privileges for common types of users: data viewers, data editors, data creators, and the geodatabase 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, and assign privileges to the group roles.

Note:

The CONNECT and TEMP database privileges are granted to the public group role by default. If you revoke these privileges from public, you need to explicitly grant CONNECT and TEMP privileges on databases to specific logins or group roles.

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

Privileges on datasets should be granted or revoked by the dataset owner using ArcGIS. See Grant and revoke dataset privileges for instructions.

Minimum privileges

The following table lists privileges to be granted to each of three common groups—data viewers, data editors, and data creators—and the minimum privileges needed by the geodatabase administrator (the sde login role) for day-to-day operations.

Type of userRequired privilegesPurpose

Data viewer

Grant USAGE on the sde schema.

This privilege allows access to the geodatabase.

Grant USAGE on all other schemas that contain data that data viewers need to access.

This privilege allows access to data in specific schemas.

If using the PostGIS geometry storage type in feature classes, grant SELECT privileges on the public.geometry_columns and public.spatial_ref_sys views to data viewers.

This privilege is required to access PostGIS geometry data.

If using the PostGIS geography storage in feature classes, grant SELECT privileges on the public.geography_columns and public.spatial_ref_sys views to data viewers.

This privilege is required to access PostGIS geography data.

Grant SELECT on specific datasets.

The data owner must grant the SELECT privilege on tables and feature classes to the data viewers so they can access the data.

Data editor

Data editors require the same privileges as data viewers plus these additional privileges.

SELECT, INSERT, UPDATE, and DELETE on other users' datasets

When you use ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a versioned feature class or table, those privileges are automatically granted on the associated versioned view. These privileges are required for the user to edit through versioned views.

Data owners must grant editors the privileges required to edit. Data owners can grant any combination of these privileges to editors.

Data creator

Data creators require the same privileges as data viewers plus this additional privilege.

Each login role that creates data requires AUTHORIZATION on its own schema. Note that the schema name must match the login role name and that group roles cannot share a schema.

AUTHORIZATION ensures that all the objects created in the schema are owned by that user.

Geodatabase administrator (the sde login role)

USAGE on all other user schemas.

This is required for the sde user to compress and upgrade the geodatabase.

PostgreSQL privilege table

Privileges required to create or upgrade a geodatabase

The sde login role must be granted superuser status to create a geodatabase. To upgrade a geodatabase, the sde login role must have the ability to access all other users' schemas and be able to select all the datasets in the geodatabase.

The sde user also requires superuser status to drop database connections using ArcGIS tools. Therefore, you can revoke superuser privileges after the geodatabase is created but only if you do not want the sde user to be able to drop connections.