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 in the organization. Is this person involved with administration of the geodatabase? Does this person need to edit or create data? Or would this person only need to query the data?

User privileges are set at different levels and for different purposes. The first section below describes the minimum required database and dataset privileges for common types of users: data viewers, data editors, data creators, and the geodatabase administrator. The second section lists privileges required by the geodatabase administrator to create and upgrade a geodatabase in PostgreSQL. The last section provides links to other information about possible required privileges.

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 must be granted or revoked using ArcGIS, and must be done by the dataset owner.

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

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 the database uses the PostGIS geometry type for spatial data storage, roles require the `SELECT` privilege on the PostGIS spatial_ref_sys table and the geometry_columns view.	This privilege is required to access PostGIS geometry data.
	If the database uses the PostGIS geography type for spatial data storage, roles require the `SELECT` privilege 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, as well as these additional privileges.	`INSERT`, `UPDATE`, `DELETE` on other users' tables 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 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.	Granting `AUTHORIZATION` on the user's schema 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 are described in the following table. The table lists privileges for each type of PostgreSQL implementation that ArcGIS supports.


	Create geodatabase	Upgrade geodatabase	Notes
PostgreSQL	`Login` `superuser` status	`Superuser` status Have the ability to access all other users' schemas, and have the `SELECT` privilege on all datasets in the geodatabase.	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` `rds_superuser` status	`Login` `rds_superuser` status The sde user must also be granted all roles that own data in the geodatabase. For example, if roles gisdata1 and gisdata2 own data in the geodatabase, you must grant these roles to sde before upgrading. `grant gisdata1 to sde;` `grant gisdata2 to sde;` Revoke the roles from sde after the geodatabase is upgraded.	You can revoke `rds_superuser` from sde after the sde user creates or upgrades the geodatabase.
Amazon Aurora (PostgreSQL-compatible edition)	`Login` `rds_superuser` status	`Login` `rds_superuser` status	You can revoke `rds_superuser` from sde after the sde user creates or upgrades the geodatabase.
Microsoft Azure Database for PostgreSQL	`Login` `GRANT azure_pg_admin TO sde;`	`Login` `GRANT azure_pg_admin TO sde;`	You can revoke `azure_pg_admin` 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.

Other privileges

The following are additional privileges required to perform other, optional tasks in ArcGIS:

The sde user requires superuser status in PostgreSQL to drop database connections using ArcGIS tools.
Additional privileges may be required for ArcGIS Monitor to access and monitor the geodatabase. See Register PostgreSQL databases for more information.