Add login roles to PostgreSQL

Available with Standard or Advanced license.

PostgreSQL uses roles to log in to the database cluster and databases. Individual users are called login roles. For all login roles that will own objects in the geodatabase, you must also create a schema in that database. To use PostgreSQL with ArcGIS, the schema must have the same name as and be owned by the login role.

You can use the Create Database User tool to add a login role that can create tables and feature classes. The tool creates a database-authenticated login role in the PostgreSQL database cluster, creates a matching schema for the user in the database you specify, and grants usage privileges on the new schema to the public.

To create a login role that does not own a schema and, therefore, cannot create objects in the geodatabase, or to create a login role mapped to a Security Support Provider Interface (SSPI) or Lightweight Directory Access Protocol (LDAP) login, use a PostgreSQL client application such as pgAdmin or PL/pgSQL to create a role in the PostgreSQL database cluster.

You can also create group roles to which login roles can be added. Then you can specify permissions on the group that will apply to all associated login roles. You can use the Create Role tool or script to create group roles, or use SQL.

Note:

You must still create a matching schema for each login role in the group that will own objects in the geodatabase. You cannot create a schema for the group role.

Add a user who can create database objects

You can run the Create Database User tool from ArcGIS AllSource or call the management.CreateDatabaseUser ArcPy function in a Python script to create a database user who can create tables, feature classes, and views.

You must be connected to the database using a role with superuser status to run the Create Database User tool or management.CreateDatabaseUser ArcPy function.

Use the Create Database User tool

Follow these steps to create a database user in PostgreSQL using the Create Database User tool:

1. Start ArcGIS AllSource.
2. Connect to the database or geodatabase using a role with PostgreSQL superuser authority.
3. Open the Create Database User tool.

   The tool is in the Geodatabase Administration toolset of the Data Management toolbox.

4. Specify the database connection for the Input Database Connection.
5. Provide a name for the login role and schema that the tool will create.
6. Provide a password for the database user.
7. If you already have a group role that you want this user to be a member of, specify that group role.
8. Click Run.

Run a Python script

To script user creation, follow these steps:

1. Create a text file on an ArcGIS client machine and copy one of the following blocks of code into the file.

   Alter the code to use information specific to your site

   The examples assume you have an existing database connection file that connects as a user who has superuser authority in the PostgreSQL database cluster. If you do not have a connection file, create one before you run the script.

   # Name: createdatabaseuser_exampleW.py
   # Description: Uses existing database connection file
   # on a Windows computer to create a database user in PostgreSQL.
   
   # Import arcpy module
   import arcpy
    
   # Provide a database connection file for the PostgreSQL administrator user.
   connection = "C:\\ArcGIS\connection_files\<Connection file>.sde"
   
   # Process: Create database user that can create data.
   arcpy.CreateDatabaseUser_management(connection, "DATABASE_USER", "dbuser", "t3mpor@rypL@ceholder1")

   # Name: createdatabaseuser_exampleL.py
   # Description: Uses existing database connection file
   # on a Linux computer to create a database user in PostgreSQL.
   
   # Import arcpy module
   import arcpy
    
   # Provide a database connection file for the PostgreSQL administrator user.
   connection = "<user>/connections/<Connection_file>.sde"
   
   # Process: Create database user that can create data.
   arcpy.CreateDatabaseUser_management(connection, "DATABASE_USER", "dbuser", "t3mpor@rypL@ceholder1")

2. Save the file with a .py extension.
3. Run the script from a computer where ArcGIS AllSource (Standard or Advanced) or ArcGIS Server is installed.

   For information about running Python from an ArcGIS Server machine, see ArcGIS Server and ArcPy.

Your database now has a user who can create tables and feature classes.

Once the tables and feature classes exist, the data owner can grant privileges on the datasets to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Create logins to view and edit data

You can use SQL to create login roles for users who won't create data. If you do this for your geodatabase in PostgreSQL, though, be sure either the public group or the specific user has privileges to create temporary tables.

You can also create login groups to make it easier to grant privileges on individual datasets to these users.

Create roles

The following steps describe how to use psql to create group roles to simplify data privilege management and create login roles that belong to the group roles.

1. Sign in to psql as a user with permissions to create other roles in the database cluster.

   This can be a login with superuser status or one that has been granted the createrole privilege.

2. Use the create role command to create two login groups: one for users who can edit datasets (editors) and one for users who can only view data (viewers).

   CREATE ROLE editors 
   NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
   
   CREATE ROLE viewers
   NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

3. Next, create login roles that are members of the editors group.

   In this example, a login role (editor1) is created with an encrypted password. The role does not have superuser status, cannot create databases, and cannot create roles in the database cluster. However, it has been made a member of the group role editors and will inherit privileges from that group role.

   CREATE ROLE editor1 LOGIN 
   ENCRYPTED PASSWORD 'sooper.secret' 
   NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT IN ROLE editors;

   Change the login role name and run the statement again to create additional login roles that will be able to edit data in the geodatabase.

4. Now, create login roles that are members of the viewers group.

   In this example, a login role (reader1) is created with an encrypted password. The role does not have superuser status, cannot create databases, and cannot create roles in the database cluster. However, it has been made a member of the group role viewers and will inherit privileges from that group role.

   CREATE ROLE reader1 LOGIN 
   ENCRYPTED PASSWORD 'almostas.secret' 
   NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT IN ROLE viewers;

   Change the login role name and run the statement again to create additional login roles that can only view data in the geodatabase.

5. Grant usage on the geodata schema to each of the login groups.

   The usage privilege allows the members of editors and viewers to access data in the geodata's schema. Without this, geodata would not be able to grant privileges on individual datasets to the members of the viewers and editors group roles.

   GRANT USAGE ON SCHEMA geodata TO editors;
   GRANT USAGE ON SCHEMA geodata TO viewers;

6. If you are creating these logins for a geodatabase, and if you altered the sde schema privileges so that the public group does not have usage on it, grant usage on the sde schema to the editors and viewers groups.

   GRANT USAGE ON SCHEMA sde TO editors;
   GRANT USAGE ON SCHEMA sde TO viewers;

Your database now has one or more users who will edit data and one or more users who will view data.

Once datasets exist in the database or geodatabase, the owner can use ArcGIS tools to grant the select privilege on the datasets to the viewers group and select, insert, update, and delete privileges on the datasets to the editors group. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.