Implementing PostgreSQL for ArcGIS, PostgreSQL for ArcGIS

PostgreSQL for ArcGIS Part II: Schema Owner and Permissions

June 27, 2017 — Brian Higgins

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).
Figure 1. Connection to Database - PostgreSQL for ArcGIS
  • 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).
Figure 2. New Login Role - PostgreSQL for ArcGIS
  • Under the ‘Definition’ tab and ‘Password’/’Password (again)’, enter “Fiber” (Figure 3).
Figure 3. Login Password - PostgreSQL for ArcGIS
  • Right-click on ‘Schemas’, and choose ‘New Schema’.  The ‘New Schema’ GUI appears (Figure 4).
  • Under the ‘Properties’ tab, enter “Fiber” for the Name and select “Fiber” from the list of options for Owner.  It should be noted that PostgreSQL is the same as SQL Server in that to paste data from a source database, the schema and user name must be the same.
Figure 4.  Creation of New Schema - PostgreSQL for ArcGIS


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

Figure 5. ArcCatalog Connection as the Fiber User - PostgreSQL for ArcGIS


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

Figure 6.  Population of Empty Fiber Schema - PostgreSQL for ArcGIS

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.

Figure 7.  Creation of new Group Role - PostgreSQL for ArcGIS

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

Figure 8. Assignment of New Role to Database - PostgreSQL for ArcGIS

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

Figure 9. Assigned of Privileges for  - PostgreSQL for ArcGIS

  • 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.
Figure 10.  Assignment of Specific Privileges - PostgreSQL for ArcGIS

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.

Figure 11.  Verification of Privileges - PostgreSQL for ArcGIS

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

Figure 12. Addition of New User - PostgreSQL for ArcGIS

  • 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.
Figure 13. Password Specification - PostgreSQL for ArcGIS
  • We lastly want to give Josh the Fiber_Editor role we created above.  On the ‘Role membership’ tab (Figure 14), make Josh a Fiber_Editor.
  • Click the ‘OK’ button.
Figure 14. Assignment to Role - PostgreSQL for ArcGIS

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.

Figure 15. Connection as User - PostgreSQL for ArcGIS

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.

Figure 16. ArcCatalog View of Data - PostgreSQL for ArcGIS

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.

Figure 17. Assignment of Usage Privilege - PostgreSQL for ArcGIS

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).
Figure 18. Assignment of CONNECT Privileges - PostgreSQL for ArcGIS
  • Grant CONNECT privileges to the applicable schemas for the Fiber_User as shown above in Figures 9 and 10.
  • Add applicable user(s) to the Login Role(s) as previously shown in Figures 12 and 13.  I am adding Zach Jannasch as a fiber user, hence read-only privileges.
  • Assign new Login Role(s) the Fiber_User Group Role as shown in Figure 14.
  • Specific grant USAGE privileges to appropriate schemas as shown in Figure 17.
At this point, Zach should be able to login and see data, BUT NOT EDIT.  After creating a connection as Zach, we login to ArcMap, and try to edit the fiber data.  As shown in our final Figure 19, we cannot edit.  Mission accomplished.  Time to go buy some bubble gum.
Figure 19.  Inability to Edit based upon Permissions - PostgreSQL for ArcGIS

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.

We Wrote the Book

The Indispensible Guide to ArcGIS Online

Download It for Free

Brian Higgins

Brian Higgins is a Senior Consultant at the Utility & Telecommunications GIS consulting company SSP Innovations in Centennial, Colorado.  He is a certified Geographic Information Systems Professional (GISP) with 22 years of experience in the design and development of GIS systems for the management of municipal infrastructure.

What do you think?

Leave a comment, and share your thoughts

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>