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 TEMPORARY database privileges are granted to the public group role by default. If you revoke these privileges from the public group role, you need to explicitly grant CONNECT and TEMPORARY 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 in geodatabases should be granted or revoked using ArcGIS clients and must be done by the dataset owner.

Privileges listed on this page apply to login roles in PostgreSQL and PostgreSQL database services supported by ArcGIS.

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.

PostgreSQL privilege table

Type of user	Required privileges	Purpose
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 your database uses the PostGIS geometry type for spatial data storage, roles require SELECT privileges on the PostGIS spatial_ref_sys table and the geometry_columns view.	This privilege is required to access PostGIS geometry data.
	If your database uses the PostGIS geography type for spatial data storage, roles require SELECT privileges on the PostGIS spatial_ref_sys table and the geography_columns view.	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 in a geodatabase 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 datasets owned by other users. When you use ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a feature class or table registered for traditional versioning, those privileges are automatically granted on the associated versioned view. These privileges are required for the user to edit using a SQL and a versioned view.	Data owners must grant editors the privileges required to edit data in a geodatabase. 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)	AUTHORIZATION on its own schema named sde. The sde login role requires USAGE on all other user schemas.	The geodatabase system tables, functions, and procedures are stored in the sde schema. USAGE on other user schemas is required for the sde user to compress the geodatabase.

Privileges required to create or upgrade a geodatabase

The privileges required by the sde login to create and upgrade a geodatabase, plus privileges required to perform other, optional administrative tasks, are described in the following table. The table lists privileges for each type of PostgreSQL implementation that ArcGIS supports.

	Create geodatabase	Upgrade geodatabase	Other administrator operations
PostgreSQL	Login and superuser status	Superuser status, have the ability to access all other users' schemas, and have SELECT privileges on all datasets in the geodatabase.	The sde user requires superuser status in PostgreSQL to drop database connections using ArcGIS tools. If you do not want the sde user to drop connections, you can revoke superuser status from sde after the geodatabase is created or upgraded.
Amazon Relational Database Service (RDS) for PostgreSQL	Login and rds_superuser status	Login and rds_superuser status	You can revoke rds_superuser from sde after the sde user creates or upgrades the geodatabase. However, if you want to use the Create Database User geoprocessing tool to create data owners in the database, do not revoke rds_superuser from sde and run this geoprocessing tool as the sde user.
Amazon Aurora (PostgreSQL-compatible edition)	Login and rds_superuser status	Login and rds_superuser status	You can revoke rds_superuser from sde after the sde user creates or upgrades the geodatabase. However, if you want to use the Create Database User geoprocessing tool to create data owners in the database, do not revoke rds_superuser from sde and run this geoprocessing tool as the sde user.
Microsoft Azure Database for PostgreSQL	Login GRANT pgazureadmin TO sde;	Login GRANT pgazureadmin TO sde;	You can revoke pgazureadmin from sde after the sde user creates or upgrades the geodatabase.
Google Cloud SQL for PostgreSQL	The sde user must be granted cloudsqlsuperuser.	The sde user must be granted cloudsqlsuperuser.	You can revoke cloudsqlsuperuser from sde after the sde user creates or upgrades the geodatabase.