Add login roles to PostgreSQL—ArcGIS AllSource

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 tool 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.

Use the Create Database User tool

Start ArcGIS AllSource.
Connect to the database or geodatabase using a role with PostgreSQL superuser authority.
Open the Create Database User tool.
The tool is in the Geodatabase Administration toolset of the Data Management toolbox.
Specify the database connection for the Input Database Connection.
Type a name for the login role and schema that the tool will create.
Type a password for the database user.
If you already have a group role that you want this user to be a member of, specify the role.
Click Run.

Run a Python script

To script user creation, follow these steps:

Create a text file on an ArcGIS client machine and copy the following script into the file.

"""
Name: create_database_user.py
Description: Provide connection information to a database user.
Type create_database_user.py -h or create_database_user.py --help for usage
"""

# Import system modules
import arcpy
import os
import optparse
import sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for 10.1 release")

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")                   
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("--auth", dest="Account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-U", dest="Dbms_admin", type="string", default="", help="DBMS administrator user")
parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
parser.add_option ("--utype", dest="user_type", type ="choice", choices=['DATABASE_USER', 'OPERATING_SYSTEM_USER'], default='DATABASE_USER', help="Authentication type options (case-sensitive):  DATABASE_USER, OPERATING_SYSTEM_USER.  Default=DATABASE_USER")
parser.add_option ("-u", dest="dbuser", type="string", default="", help="database user name")
parser.add_option ("-p", dest="dbuser_pwd", type="string", default="", help="database user password")
parser.add_option ("-r", dest="role", type="string", default="", help="role to be granted to the user")
parser.add_option ("-t", dest="Tablespace", type="string", default="", help="Tablespace name")
# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
		parser.print_help()
		sys.exit(3)

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	database = options.Database.lower()	
	account_authentication = options.Account_authentication.upper()
	dbms_admin = options.Dbms_admin
	dbms_admin_pwd = options.Dbms_admin_pwd
	dbuser = options.dbuser
	dbuser_pwd = options.dbuser_pwd	
	tablespace = options.Tablespace
	user_type = options.user_type
	role = options.role

	
	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
	
	if( database_type ==""):	
		print(" \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified."))
		parser.print_help()
		sys.exit(3)		
	
	if(database_type == "SQL_SERVER"):
		if( account_authentication == "DATABASE_AUTH" and dbms_admin == ""):
			print("\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified with database authentication"))
			sys.exit(3)
		if( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != ""):
			print("\nWarning: %s\n" % ("Ignoring DBMS administrator specified when using operating system authentication..."))
	else:		
		if( dbuser.lower() == ""):
			print("\n%s: error: %s\n" % (sys.argv[0], "Database user must be specified."))
			sys.exit(3)		
		if( dbms_admin == ""):
			print("\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!"))
			sys.exit(3)

	if ( user_type == "DATABASE_USER" and (dbuser =="" or dbuser_pwd =="")):
		print(" \n%s: error: \n%s\n" % (sys.argv[0], "To create database authenticated user, user name and password must be specified!"))
		parser.print_help()
		sys.exit(3)	

	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print("\n" + product_license + " license found!" + "  Creating a user in an enterprise geodatabase or database requires an ArcGIS Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS Server license.")
		sys.exit("Re-authorize ArcGIS before creating a database user.")
	else:
		print("\n" + product_license + " license available!  Continuing to create...")
		arcpy.AddMessage("+++++++++")

	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database + "_" + dbms_admin   

	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  

	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)

	try:
		print("\nCreating Database Connection File...\n")
		# Process: Create Database Connection File...
		# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
		#arcpy.CreateDatabaseConnection_management(temp , Connection_File_Name, database_type, instance, database, account_authentication, dbms_admin, dbms_admin_pwd, "TRUE")
		arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=dbms_admin, password=dbms_admin_pwd, save_user_pass="TRUE")
	        for i in range(arcpy.GetMessageCount()):
			if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
				arcpy.AddReturnMessage(i)
				arcpy.AddMessage("\n+++++++++")
				arcpy.AddMessage("Exiting!!")
				arcpy.AddMessage("+++++++++\n")
				sys.exit(3)            
			else:
				arcpy.AddReturnMessage(i)
				arcpy.AddMessage("+++++++++\n")

		print("Creating database user...\n")
		arcpy.CreateDatabaseUser_management(input_workspace=Connection_File_Name_full_path, user_authentication_type=user_type, user_name=dbuser, user_password=dbuser_pwd, role=role, tablespace_name=tablespace)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print("\n")
		parser.print_help()   
		parser.exit(2)

You can run the script from a computer where ArcGIS AllSource (ArcGIS Desktop Standard or Advanced) or ArcGIS Server is installed.

Save the file with a .py extension.
Run the script, providing options and information specific to your PostgreSQL database cluster and the login role you want to create.
In the following example run from a Microsoft Windows server, the script name is create_database_user.py. A database authenticated login role (geodata) is created in the PostgreSQL database cluster pgdb7, and a corresponding schema is created in the database gisdata. The user is not added to a group role.
```
create_database_user.py --DBMS POSTGRESQL -i pgdb7 -D gisdata -U postgres -P $hHhH --utype DATABASE_USER -u geodata -p 0wn1t
```
This is an example of running the script on a Linux machine:
```
./create_database_user.py --DBMS POSTGRESQL -i pgdb7 -D gisdata -U postgres -P $hHhH --utype DATABASE_USER -u geodata -p 0wn1t
```
Tip:
Type -h or --help at the command prompt to get syntax help.

Your database now has a user that can create tables.

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.

Log 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.
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;
```
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.
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.
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;
```
If you are creating these logins for a geodatabase, and if you altered the sde schema privileges so that public 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 for instructions.

Feedback on this topic?