Back to Top

Add login roles to PostgreSQL

In this topic

  1. Use geoprocessing to add a user who can create database objects
  2. Create logins using SQL

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 user roles that will own objects in the geodatabase, the database administrator must also create a schema in the database where the geodatabase is stored. To use PostgreSQL with ArcGIS, the schema must have the same name as and be owned by the login role.

The database administrator 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 user 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 role.

Alternatively, the database administrator can use a PostgreSQL client application such as pgAdmin or PL/pgSQL to create login user roles. You must use these applications 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.

The database administrator can also create group roles to which login roles can be added, and specify permissions on the group that will apply to all associated login user roles. Use the Create Role tool or script to create group roles, or use SQL.

Note:

Each login user role in the group that will own objects in the geodatabase must have its own matching schema. You cannot create a schema for the group role.

Use geoprocessing to add a user who can create database objects

Run the Create Database User tool from ArcGIS Pro or call the management.CreateDatabaseUser ArcPy function in a Python script to create a login user role that can create tables, feature classes, and views.

You must connect 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 login user role in PostgreSQL using the Create Database User tool:

  1. Start ArcGIS Pro.
  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 parameter.
  5. Provide a name for the login user 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.

The database now has a user who can create tables, feature classes, and views.

The new user can connect to the database to create datasets. They can then grant privileges on them to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Run a Python script

To script login user role 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 Pro (Standard or Advanced) or ArcGIS Server is installed.

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

The database now has a login user role that can create tables, feature classes, and views.

The new user can connect to the database to create datasets. They can then grant privileges on them to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Create logins using SQL

You can use SQL to create roles. If you do this for a geodatabase in PostgreSQL, though, ensure that either the public group or specific roles have privileges to create temporary tables. This is required for all users, even those who will not create other database objects.

See Privileges for geodatabases in PostgreSQL for required privileges per user type.

Create roles

The following steps describe how to use psql to create group roles to simplify 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 login groups.

    In this example, group roles are created to grant privileges for data creators, data editors, and data viewers.

    CREATE ROLE owners 
NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

CREATE ROLE editors 
NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

CREATE ROLE viewers
NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

  3. Grant required privileges to each of the group roles based on what actions the group members need to perform.

    See Privileges for geodatabases in PostgreSQL when granting privileges to roles in a geodatabase or Privileges for using ArcGIS with a PostgreSQL database if the database does not contain a geodatabase.

  4. Create a login role with a matching schema to own database objects, and place that login role in the owners group role.

    CREATE ROLE geodataowner1 LOGIN 
ENCRYPTED PASSWORD '' 
NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT IN ROLE owners;

CREATE SCHEMA geodataowner1
  AUTHORIZATION geodataowner1;

    Change the login role and matching schema name and run the statements again for each login that will create database objects.

  5. 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 '' 
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.

  6. 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 '' 
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.

  7. Grant usage on the schemas you created in step 4 to the viewers and editors login groups.

    The usage privilege allows the members of the group roles to access data in the schemas. Without this, data owners would not be able to grant privileges on individual datasets to the members of the group roles.

    GRANT USAGE ON SCHEMA geodataowner1 TO viewers;
GRANT USAGE ON SCHEMA geodataowner1 TO editors;
  8. 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 owners, editors, and viewers groups.
    GRANT USAGE ON SCHEMA sde TO owners;
GRANT USAGE ON SCHEMA sde TO editors;
GRANT USAGE ON SCHEMA sde TO viewers;

The database now has one or more users who will create data, one or more users who will edit data, and one or more users who will view data.

Each user must create a connection to the database.

Once the data owners create datasets in the database or geodatabase, they 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.

Feedback on this topic?

In this topic
  1. Use geoprocessing to add a user who can create database objects
  2. Create logins using SQL