In the previous Part I, we introduced PostgreSQL as a viable Enterprise Geodatabase alternative.  When we left off, we had a connection to an ArcGIS 10.2.1 database, and were able to create a feature class.  In this post, we are going continue discussing the use of PostgreSQL for ArcGIS, but we will take it to the next level by adding a schema owner to receive and own feature datasets (plus contents).  We lastly want to create permission roles and assign users to those roles.

Creating a New Schema Owner

We first want to create a new schema owner that will own the data.  Municipalities with multiple infrastructure types have schema owner names reflecting the type of infrastructure (i.e. Electric, Fiber, Water, etc).  In the example below, we will create a schema owner titled “Fiber”.

• To start, we open pgAdmin III and connect to the server as the postgres user.  We now see our sde-owned ssp database created in the previous blog post (Figure 1).

• Right-click on ‘Login Roles’ and choose ‘New Login Role’.  The ‘New Login Role’ GUI appears.
• Under the ‘Properties’ tab and ‘Role Name’, enter “Fiber” (Figure 2).

• Under the ‘Definition’ tab and ‘Password’/’Password (again)’, enter “Fiber” (Figure 3).

At this point, we can then connect from ArcCatalog to the “ssp” database as the “Fiber” schema owner (Figure 5).

At this point, we can copy data from an empty database for a data structure.  Any data copied will be placed as the Fiber owner (Figure 6).

Creating a New Database Role

Database roles can be considered a group of users with common privileges.  Once created, roles are assigned to the applicable data.  They are convenient because to add (or remove) a user’s privileges, one only needs to interact with the role instead of assigning that user to all the applicable data.

To create a new Database role, conduct the following procedure:

• Log into pgAdmin as postgres.
• Right-click on ‘Group Roles’ and choose ‘New Group Role’.
• In the ‘New Group Role’ GUI (Figure 7), supply the name (Fiber_Editor in this case).  It is not necessary to assign a password to the group role because we do not intend to login with the role name.

• Right-click on the ssp database and choose ‘Properties’.
• Select the ‘Privileges’ tab (Figure 8) and conduct the following tasks.  (1) Choose group Fiber_Editor for the role.  (2) ‘ALL’ for the privilege.  (3) Click the ‘Add/Change’ button.  (4) Lastly, click the ‘OK’ button.

• Right-click on the desired schema (Fiber in this case) and choose ‘Grant Wizard’.
• In the GUI that appears (Figure 9) on the ‘Selection’ tab, click the ‘Check all’ button.

• On the ‘Privileges’ tab (Figure 10), choose the ‘Role’ (group Fiber_Editor in this case), ‘ALL’ privileges, click the ‘Add/Change’ button, and lastly the ‘OK’ button.

At this point, the role and its assigned privileges are assigned to the Fiber schema.  This can be verified in ArcCatalog by logging in a Fiber, right-clicking on the FiberNetwork feature dataset, choosing Manage–>Privileges.  As shown in Figure 11, the applicable privileges have been assigned to the Fiber_Editor role.

Assignment of Users to Roles

The assignment users to a specific role is a piece of cake.  To add a new user, conduct the following procedure:

• Right-click on ‘Login Roles’ and choose ‘New Login Role’.  The ‘New Login Role’ GUI appears (Figure 12).

• On the ‘Properties’ tab, we enter the ‘Role Name’ which is my friend Josh (in this case).
• On the ‘Definition’ tab (Figure 13), enter a password.

At this point, Josh should be able to establish a connection to the data and have the privileges of the Fiber_Editor role.  We establish a connection in ArcCatalog as shown in Figure 15.

As shown in Figure 16, we are not quite there.  We connect to the database fine, but observe that the feature classes are not visible within the FiberNetwork feature dataset.  To be honest, this completely perplexed me because ALL privileges were supplied at the schema level.  After a few hours of digging, I found that the USAGE permission had to be specifically applied.  I guess the word “ALL” didn’t mean what I thought it meant.

To grant the Usage Privilege to the appropriate schemas, execute SQL statement as shown in Figure 17.  It should also be noted that there appears to be some issues with mixed case in PostgreSQL.  This issue forced me to put the SQL statement in double quotes.

Following this granting of Usage Privilege, Josh can now see the feature classes.

Lastly, we will want to create a Fiber_User role and grant Select privileges only.  Conduct the following procedure:

• Create Fiber_User Group Role as illustrated in Figure 7 above.
• Grant CONNECT privileges to the new role on the ssp database (Figure 18).

Are You Interested in Discussing PostgreSQL for ArcGIS?

Reach out to the veteran GDB team at SSP on our contact page, and we’ll discuss your GDB options.