{"id":1160,"date":"2026-10-16T06:51:18","date_gmt":"2026-10-16T06:51:18","guid":{"rendered":"https:\/\/helpwithmygisassignment.com\/blog\/?p=1160"},"modified":"2026-10-16T06:51:18","modified_gmt":"2026-10-16T06:51:18","slug":"multi-user-spatial-databases-in-postgresql","status":"publish","type":"post","link":"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/","title":{"rendered":"Building Multi-User Spatial Databases: Managing User Roles &#038; Permissions in PostgreSQL"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_89 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Building_Multi-User_Spatial_Databases_Managing_User_Roles_Permissions_in_PostgreSQL\" >Building Multi-User Spatial Databases: Managing User Roles &amp; Permissions in PostgreSQL<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#1_Introduction_Managing_a_Shared_Spatial_Database\" >1. Introduction: Managing a Shared Spatial Database<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Why_use_a_central_PostgreSQLPostGIS_database\" >Why use a central PostgreSQL\/PostGIS database?<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#2_Designing_the_Role_Architecture\" >2. Designing the Role Architecture<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#21_Administrator_Role\" >2.1 Administrator Role<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#22_Data_Editor_Role\" >2.2 Data Editor Role<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#23_Read-Only_Viewer_Role\" >2.3 Read-Only Viewer Role<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#24_Recommended_Architecture\" >2.4 Recommended Architecture<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#3_Step-by-Step_SQL_Scripting_Guide\" >3. Step-by-Step SQL Scripting Guide<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_1_Create_Custom_Database_Roles\" >Step 1: Create Custom Database Roles<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_2_Assign_Users_to_Group_Roles\" >Step 2: Assign Users to Group Roles<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_3_Grant_Database_and_Schema_Access\" >Step 3: Grant Database and Schema Access<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_4_Create_a_Spatial_Table_with_Data_Integrity_Constraints\" >Step 4: Create a Spatial Table with Data Integrity Constraints<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_5_Grant_Editing_Privileges\" >Step 5: Grant Editing Privileges<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_6_Make_Permissions_Work_for_Future_Tables\" >Step 6: Make Permissions Work for Future Tables<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#Step_7_Configure_Read-Only_Viewer_Access\" >Step 7: Configure Read-Only Viewer Access<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#4_Enforcing_Row-Level_Security_and_Spatial_Constraints\" >4. Enforcing Row-Level Security and Spatial Constraints<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#41_Create_a_Table_with_User-Specific_Records\" >4.1 Create a Table with User-Specific Records<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#42_Enable_Row-Level_Security\" >4.2 Enable Row-Level Security<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#43_Restrict_Editors_to_Their_Assigned_Records\" >4.3 Restrict Editors to Their Assigned Records<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#44_Allow_Designated_Administrators_to_Access_All_Work_Orders\" >4.4 Allow Designated Administrators to Access All Work Orders<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#45_Enforcing_Spatial_Layer_Constraints\" >4.5 Enforcing Spatial Layer Constraints<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#5_Connecting_User_Roles_from_QGIS_and_ArcGIS_Pro\" >5. Connecting User Roles from QGIS and ArcGIS Pro<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#51_Connect_from_QGIS\" >5.1 Connect from QGIS<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#52_Connect_from_ArcGIS_Pro\" >5.2 Connect from ArcGIS Pro<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#53_Test_Each_Role_Using_Its_Own_Credentials\" >5.3 Test Each Role Using Its Own Credentials<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#6_Troubleshooting_Common_Permission_Problems\" >6. Troubleshooting Common Permission Problems<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#7_Summary_Checklist_for_GIS_Systems_Administrators\" >7. Summary Checklist for GIS Systems Administrators<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/#8_Conclusion\" >8. Conclusion<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"Building_Multi-User_Spatial_Databases_Managing_User_Roles_Permissions_in_PostgreSQL\"><\/span>Building Multi-User Spatial Databases: Managing User Roles &amp; Permissions in PostgreSQL<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"1_Introduction_Managing_a_Shared_Spatial_Database\"><\/span>1. Introduction: Managing a Shared Spatial Database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Building Multi-User Spatial Databases in PostgreSQL is essential for organizations sharing geospatial datasets securely across teams. Imagine a county government maintaining a central spatial database containing land parcels, roads, water pipelines, building footprints, and zoning boundaries. Several GIS analysts use QGIS or ArcGIS Pro to access the same datasets.<\/p>\n<p>One analyst updates parcel boundaries, another edits road attributes, a planning officer reviews zoning maps, and students access selected layers for coursework.<\/p>\n<p>Without a structured permission system, users might accidentally modify restricted datasets, delete important records, or gain access to information beyond their responsibilities.<\/p>\n<p><strong>Multi-user spatial database administration<\/strong> involves designing a PostgreSQL environment in which multiple users can access shared geographic data while maintaining appropriate security, data integrity, and editing controls.<\/p>\n<p>PostgreSQL manages this through <strong>roles, privileges, ownership, and row-level security<\/strong>. PostGIS adds spatial data types and functions, while desktop applications such as QGIS and ArcGIS Pro connect to the database using individual database credentials.<\/p>\n<p>The objective is to establish a centralized system in which each user can perform authorized tasks without receiving unnecessary privileges.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Why_use_a_central_PostgreSQLPostGIS_database\"><\/span>Why use a central PostgreSQL\/PostGIS database?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li><strong>Concurrent access:<\/strong> Multiple users can read and edit shared spatial datasets through database transactions.<\/li>\n<li><strong>Access control:<\/strong> Permissions determine which schemas, tables, and operations users can access.<\/li>\n<li><strong>Data integrity:<\/strong> Database constraints can enforce geometry types, coordinate reference systems, and other rules.<\/li>\n<li><strong>Centralized administration:<\/strong> Administrators can manage data, permissions, indexes, backups, and maintenance centrally.<\/li>\n<li><strong>Application flexibility:<\/strong> Different GIS clients can connect to the same database without maintaining separate copies of every dataset.<\/li>\n<\/ul>\n<p>PostgreSQL&#8217;s role system provides the foundation for this architecture. Roles can represent individual logins, groups of users, or ownership identities. See the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/user-manag.html\" target=\"_blank\" rel=\"noopener\">official PostgreSQL role management documentation<\/a>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Designing_the_Role_Architecture\"><\/span>2. Designing the Role Architecture<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before writing SQL permissions, determine who needs access and what each type of user should be allowed to do.<\/p>\n<p>A practical enterprise GIS system separates administrative responsibilities, data ownership, editing, and viewing.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"21_Administrator_Role\"><\/span>2.1 Administrator Role<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Administrators maintain database security, schemas, database objects, and operational settings.<\/p>\n<p>Typical responsibilities include:<\/p>\n<ul>\n<li>Creating and managing database roles.<\/li>\n<li>Granting and revoking permissions.<\/li>\n<li>Managing schemas, tables, indexes, and constraints.<\/li>\n<li>Monitoring database performance.<\/li>\n<li>Configuring backups, maintenance, and database connectivity.<\/li>\n<\/ul>\n<p>A critical distinction is that a <strong>GIS data administrator does not automatically need PostgreSQL superuser privileges<\/strong>.<\/p>\n<p>Superusers can bypass normal database permission controls. Reserve superuser access for trusted database administrators who need cluster-level responsibilities, rather than giving it to ordinary GIS analysts.<\/p>\n<p>A non-login ownership role can own schemas and tables, while designated administrators receive controlled membership in that role.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"22_Data_Editor_Role\"><\/span>2.2 Data Editor Role<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Editors work with approved spatial datasets in QGIS or ArcGIS Pro.<\/p>\n<p>They commonly require:<\/p>\n<ul>\n<li><code>SELECT<\/code> to view records.<\/li>\n<li><code>INSERT<\/code> to add records.<\/li>\n<li><code>UPDATE<\/code> to modify records.<\/li>\n<li><code>DELETE<\/code> to remove records when authorized.<\/li>\n<\/ul>\n<p>Editors usually should not have <code>CREATE<\/code> privileges on shared schemas or ownership of production tables. Otherwise, routine editing could become unrestricted database administration.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"23_Read-Only_Viewer_Role\"><\/span>2.3 Read-Only Viewer Role<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Viewers might include planning officers, students, researchers, and members of the public using approved datasets.<\/p>\n<p>They typically require only <code>SELECT<\/code> privileges on the information they are permitted to access.<\/p>\n<p>A viewer should not be able to:<\/p>\n<ul>\n<li>Insert new features.<\/li>\n<li>Change existing geometry or attributes.<\/li>\n<li>Delete records.<\/li>\n<li>Create or modify database objects.<\/li>\n<\/ul>\n<p>For public or student access, granting <code>SELECT<\/code> on curated views may be safer than granting access to every table in the database.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"24_Recommended_Architecture\"><\/span>2.4 Recommended Architecture<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1176\" src=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_m244elm244elm244-1024x559.jpg\" alt=\"Multi-User Spatial Databases in PostgreSQL architecture diagram showing role hierarchy and permission levels.\" width=\"1024\" height=\"559\" srcset=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_m244elm244elm244-1024x559.jpg 1024w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_m244elm244elm244-300x164.jpg 300w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_m244elm244elm244-768x419.jpg 768w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_m244elm244elm244.jpg 1408w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p>&lt;box gap={3}&gt; &lt;box border radius=&#8221;lg&#8221; padding={3} gap={1}&gt; &lt;row align=&#8221;center&#8221; gap={2}&gt; &lt;icon name=&#8221;shield-check&#8221; size=&#8221;xl&#8221; \/&gt; **Database administrator** &lt;\/row&gt; &lt;text color=&#8221;secondary&#8221; size=&#8221;sm&#8221;&gt;Manages cluster settings, roles, security, backups, and privileged operations.&lt;\/text&gt; &lt;text size=&#8221;sm&#8221;&gt;Uses a dedicated administrative login.&lt;\/text&gt; &lt;\/box&gt; &lt;box align=&#8221;center&#8221;&gt; &lt;icon name=&#8221;arrow-down&#8221; size=&#8221;lg&#8221; color=&#8221;secondary&#8221; \/&gt; &lt;\/box&gt; &lt;box border radius=&#8221;lg&#8221; padding={3} gap={1}&gt; &lt;row align=&#8221;center&#8221; gap={2}&gt; &lt;icon name=&#8221;database&#8221; size=&#8221;xl&#8221; \/&gt; **GIS data owner** &lt;\/row&gt; &lt;text color=&#8221;secondary&#8221; size=&#8221;sm&#8221;&gt;A non-login role owns the GIS schema and production tables.&lt;\/text&gt; &lt;text size=&#8221;sm&#8221;&gt;Separates object ownership from individual users.&lt;\/text&gt; &lt;\/box&gt; &lt;box align=&#8221;center&#8221;&gt; &lt;icon name=&#8221;arrow-down&#8221; size=&#8221;lg&#8221; color=&#8221;secondary&#8221; \/&gt; &lt;\/box&gt; &lt;grid columns={2} gap={2}&gt; &lt;grid-item&gt; &lt;box border radius=&#8221;lg&#8221; padding={3} gap={1} height=&#8221;100%&#8221;&gt; &lt;icon name=&#8221;edit-3&#8243; size=&#8221;lg&#8221; \/&gt; **Editor group** &lt;text size=&#8221;sm&#8221; color=&#8221;secondary&#8221;&gt;Read and modify authorized spatial records.&lt;\/text&gt; &lt;\/box&gt; &lt;\/grid-item&gt; &lt;grid-item&gt; &lt;box border radius=&#8221;lg&#8221; padding={3} gap={1} height=&#8221;100%&#8221;&gt; &lt;icon name=&#8221;eye&#8221; size=&#8221;lg&#8221; \/&gt; **Viewer group** &lt;text size=&#8221;sm&#8221; color=&#8221;secondary&#8221;&gt;Read approved tables or views only.&lt;\/text&gt; &lt;\/box&gt; &lt;\/grid-item&gt; &lt;\/grid&gt; &lt;\/box&gt;<\/p>\n<p>The central principle is <strong>least privilege<\/strong>: every account receives only the permissions necessary for its work.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"3_Step-by-Step_SQL_Scripting_Guide\"><\/span>3. Step-by-Step SQL Scripting Guide<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The following examples use a database named <code>spatialdb<\/code> and a schema named <code>gis<\/code>. Run administrative statements using an appropriately privileged account.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_1_Create_Custom_Database_Roles\"><\/span>Step 1: Create Custom Database Roles<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>PostgreSQL has one role system for both users and groups. A role with <code>LOGIN<\/code> can authenticate to the database; a role with <code>NOLOGIN<\/code> can act as a group or ownership identity.<\/p>\n<p>Create the ownership and permission groups:<\/p>\n<pre><code>-- Run once using a privileged administrative account.\r\n\r\nCREATE ROLE gis_owner NOLOGIN;\r\n\r\nCREATE ROLE gis_admin_group NOLOGIN;\r\n\r\nCREATE ROLE gis_editor NOLOGIN;\r\n\r\nCREATE ROLE gis_viewer NOLOGIN;<\/code><\/pre>\n<p>These roles establish the ownership, administration, editing, and viewing layers.<\/p>\n<p>Next, create individual login accounts:<\/p>\n<pre><code>-- Individual administrator and GIS user accounts.\r\n-- These roles do not receive passwords in this script.\r\n\r\nCREATE ROLE gis_admin_login LOGIN;\r\n\r\nCREATE ROLE alice_editor LOGIN;\r\n\r\nCREATE ROLE student_viewer LOGIN;<\/code><\/pre>\n<p>Set each account&#8217;s password securely. In the interactive <code>psql<\/code> terminal, for example, use:<\/p>\n<pre><code>\\password alice_editor<\/code><\/pre>\n<p>Repeat for the other login roles. The command prompts for the password rather than requiring a password literal in a SQL script.<\/p>\n<p>In production, consider your organization&#8217;s password policy, secret-management tools, and approved authentication methods.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_2_Assign_Users_to_Group_Roles\"><\/span>Step 2: Assign Users to Group Roles<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Grant membership in the appropriate permission group:<\/p>\n<pre><code>-- Assign the editor's permissions to Alice.\r\nGRANT gis_editor TO alice_editor;\r\n\r\n-- Give the student read-only group membership.\r\nGRANT gis_viewer TO student_viewer;\r\n\r\n-- Assign controlled administrative privileges.\r\nGRANT gis_admin_group TO gis_admin_login;\r\nGRANT gis_owner TO gis_admin_login;<\/code><\/pre>\n<p>The last statement gives the designated administrator membership in the non-login ownership role. This is a powerful grant because the account can exercise ownership-related authority.<\/p>\n<p>In routine workflows, use an owner membership only for trusted accounts and deliberately assume the owner role when creating or changing owned objects:<\/p>\n<pre><code>-- Run in the intended database session.\r\nSET ROLE gis_owner;\r\n\r\n-- Create or administer objects that should be owned by gis_owner.\r\n\r\nRESET ROLE;<\/code><\/pre>\n<p>The <code>SET ROLE<\/code> statement requires appropriate role membership and role-switching permission. Membership and inheritance options can vary with PostgreSQL version and configuration.<\/p>\n<p><strong>Security recommendation:<\/strong> Do not give ordinary editors membership in <code>gis_owner<\/code>, and do not use a PostgreSQL superuser account for routine editing from QGIS or ArcGIS Pro.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_3_Grant_Database_and_Schema_Access\"><\/span>Step 3: Grant Database and Schema Access<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>A user generally needs permission to connect to the database and <code>USAGE<\/code> on a schema before accessing objects within it.<\/p>\n<p>Assuming <code>spatialdb<\/code> already exists:<\/p>\n<pre><code>-- Allow the user groups to connect.\r\nGRANT CONNECT ON DATABASE spatialdb\r\nTO gis_admin_group, gis_editor, gis_viewer;<\/code><\/pre>\n<p>Connect to <code>spatialdb<\/code> before executing the following statements. Create the schema if it does not already exist:<\/p>\n<pre><code>-- Run in the spatialdb database.\r\nCREATE SCHEMA gis AUTHORIZATION gis_owner;<\/code><\/pre>\n<p>If the schema already exists, inspect its owner rather than trying to create it again.<\/p>\n<p>Grant schema access:<\/p>\n<pre><code>-- Allow the groups to resolve objects in the GIS schema.\r\nGRANT USAGE ON SCHEMA gis\r\nTO gis_admin_group, gis_editor, gis_viewer;<\/code><\/pre>\n<p><code>USAGE<\/code> does not grant permission to read or modify tables. Those permissions are granted separately.<\/p>\n<p>Also note that PostgreSQL requires appropriate network access, authentication configuration, and database privileges. A SQL <code>GRANT CONNECT<\/code> does not by itself configure the server&#8217;s <code>pg_hba.conf<\/code> rules or open a firewall port.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_4_Create_a_Spatial_Table_with_Data_Integrity_Constraints\"><\/span>Step 4: Create a Spatial Table with Data Integrity Constraints<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>A geometry column can enforce a specific geometry type and spatial reference system.<\/p>\n<p>For example, create a parcels table:<\/p>\n<pre><code>-- Execute as the designated owner role.\r\nSET ROLE gis_owner;\r\n\r\nCREATE TABLE gis.parcels (\r\n    parcel_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\r\n    parcel_code TEXT NOT NULL UNIQUE,\r\n    land_use TEXT NOT NULL,\r\n    is_public BOOLEAN NOT NULL DEFAULT FALSE,\r\n\r\n    -- Store only multipolygons in EPSG:4326.\r\n    geom geometry(MultiPolygon, 4326) NOT NULL,\r\n\r\n    -- Reject invalid polygon geometry.\r\n    CONSTRAINT parcels_geom_valid\r\n        CHECK (ST_IsValid(geom))\r\n);\r\n\r\nRESET ROLE;<\/code><\/pre>\n<p>This table illustrates several useful database controls:<\/p>\n<ul>\n<li>The primary key uniquely identifies each parcel.<\/li>\n<li><code>NOT NULL<\/code> prevents required attributes from being omitted.<\/li>\n<li><code>UNIQUE<\/code> prevents duplicate parcel codes.<\/li>\n<li>The geometry type and SRID restrict the spatial data structure.<\/li>\n<li>The validity check rejects geometries that fail the selected validity test.<\/li>\n<\/ul>\n<p>The geometry declaration does not verify that every parcel lies within your jurisdiction. If that is required, add a suitable spatial constraint or validate against an authoritative boundary.<\/p>\n<p>PostGIS geometry types and spatial metadata are described in the <a href=\"https:\/\/postgis.net\/docs\/en\/using_postgis_dbmanagement.html\" target=\"_blank\" rel=\"noopener\">PostGIS data management documentation<\/a>.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_5_Grant_Editing_Privileges\"><\/span>Step 5: Grant Editing Privileges<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Editors need permissions on the tables they are authorized to change.<\/p>\n<p>For an existing GIS dataset, the basic grants are:<\/p>\n<pre><code>-- Allow existing editors to read and change approved tables.\r\nGRANT SELECT, INSERT, UPDATE, DELETE\r\nON ALL TABLES IN SCHEMA gis\r\nTO gis_editor;<\/code><\/pre>\n<p>If your tables use traditional <code>SERIAL<\/code> columns or sequences that the application accesses directly, grant the required sequence privileges as well:<\/p>\n<pre><code>-- Permit normal sequence usage for editor workflows.\r\nGRANT USAGE, SELECT\r\nON ALL SEQUENCES IN SCHEMA gis\r\nTO gis_editor;<\/code><\/pre>\n<p>For identity columns, generated values are managed differently from traditional serial defaults, so direct sequence privileges may not always be necessary. Grant them where the application design requires them.<\/p>\n<p>Notice what is <em>not<\/em> included: <code>CREATE<\/code> on the schema, table ownership, and superuser privileges. This lets editors manage approved records without automatically granting authority to redesign the database.<\/p>\n<p>These grants apply to existing objects. They do not automatically grant equivalent permissions on every table created in the future.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_6_Make_Permissions_Work_for_Future_Tables\"><\/span>Step 6: Make Permissions Work for Future Tables<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>In enterprise databases, new datasets are regularly added. PostgreSQL supports <strong>default privileges<\/strong> to control permissions on objects created in the future by a particular role.<\/p>\n<p>For example:<\/p>\n<pre><code>-- Run as a role permitted to manage gis_owner's defaults.\r\n-- These defaults apply to future tables created by gis_owner.\r\n\r\nALTER DEFAULT PRIVILEGES\r\nFOR ROLE gis_owner\r\nIN SCHEMA gis\r\nGRANT SELECT, INSERT, UPDATE, DELETE\r\nON TABLES TO gis_editor;\r\n\r\nALTER DEFAULT PRIVILEGES\r\nFOR ROLE gis_owner\r\nIN SCHEMA gis\r\nGRANT USAGE, SELECT\r\nON SEQUENCES TO gis_editor;<\/code><\/pre>\n<p>These default privileges apply when <code>gis_owner<\/code> creates future objects in the <code>gis<\/code> schema. If another role creates a table, it needs its own applicable default privilege settings or explicit grants.<\/p>\n<p>Do not apply broad default <code>SELECT<\/code> privileges to all future tables if the schema will contain confidential or restricted datasets. Use a separate schema or curated views for the data that viewers are allowed to access.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_7_Configure_Read-Only_Viewer_Access\"><\/span>Step 7: Configure Read-Only Viewer Access<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>There are two common designs for viewer permissions.<\/p>\n<p><strong>Option A: The viewer can read every table in a designated read-only schema.<\/strong><\/p>\n<p>If all existing tables in a schema are approved for viewing, grant:<\/p>\n<pre><code>-- Allow read-only access to existing approved tables.\r\nGRANT SELECT\r\nON ALL TABLES IN SCHEMA gis\r\nTO gis_viewer;<\/code><\/pre>\n<p>For future tables, a default privilege can be used:<\/p>\n<pre><code>-- Use only if all new tables in this schema are approved\r\n-- for viewer access.\r\n\r\nALTER DEFAULT PRIVILEGES\r\nFOR ROLE gis_owner\r\nIN SCHEMA gis\r\nGRANT SELECT ON TABLES TO gis_viewer;<\/code><\/pre>\n<p>This pattern is convenient, but it can unintentionally expose future tables if the schema contains mixed public and confidential data.<\/p>\n<p><strong>Option B: The viewer can read only approved layers or columns.<\/strong><\/p>\n<p>For public or student access, a curated view is often safer.<\/p>\n<pre><code>-- Create a view containing only approved public parcels.\r\n-- Run as the trusted object owner.\r\n\r\nSET ROLE gis_owner;\r\n\r\nCREATE VIEW gis.public_parcels AS\r\nSELECT\r\n    parcel_id,\r\n    parcel_code,\r\n    land_use,\r\n    geom\r\nFROM gis.parcels\r\nWHERE is_public = TRUE;\r\n\r\nRESET ROLE;<\/code><\/pre>\n<p>Remove direct access to the underlying table and grant access to the view:<\/p>\n<pre><code>-- Ensure viewers cannot query the underlying parcel table.\r\nREVOKE ALL PRIVILEGES\r\nON gis.parcels\r\nFROM gis_viewer;\r\n\r\n-- Expose only the curated public layer.\r\nGRANT SELECT\r\nON gis.public_parcels\r\nTO gis_viewer;<\/code><\/pre>\n<p>A normal PostgreSQL view uses its owner&#8217;s privileges to access underlying objects. Therefore, ensure that the view is created by a trusted owner and that its filter exposes only the intended information. Test the view using the actual viewer account.<\/p>\n<p>Views and row-level security serve different purposes. A view is useful for publishing a safe subset of the data; row-level security is useful when permitted rows vary by user.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"4_Enforcing_Row-Level_Security_and_Spatial_Constraints\"><\/span>4. Enforcing Row-Level Security and Spatial Constraints<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Table-level permissions determine which operations are allowed on a table. Sometimes, however, two editors should be able to access different records within the same table.<\/p>\n<p>For example, a road-maintenance organization might want each employee to update only the work orders assigned to them.<\/p>\n<p>PostgreSQL <strong>Row-Level Security (RLS)<\/strong> supports these restrictions through policies.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"41_Create_a_Table_with_User-Specific_Records\"><\/span>4.1 Create a Table with User-Specific Records<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Consider a table of maintenance work orders:<\/p>\n<pre><code>-- Run as gis_owner.\r\nSET ROLE gis_owner;\r\n\r\nCREATE TABLE gis.work_orders (\r\n    work_order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\r\n\r\n    assigned_to NAME NOT NULL,\r\n\r\n    status TEXT NOT NULL\r\n        CHECK (status IN ('open', 'in_progress', 'closed')),\r\n\r\n    -- Point location stored in WGS 84.\r\n    geom geometry(Point, 4326) NOT NULL,\r\n\r\n    CONSTRAINT work_orders_geom_valid\r\n        CHECK (ST_IsValid(geom))\r\n);\r\n\r\nRESET ROLE;<\/code><\/pre>\n<p>The <code>assigned_to<\/code> column stores the database login name responsible for each work order.<\/p>\n<p>The geometry declaration requires point geometry in EPSG:4326. Additional constraints could enforce the permitted geographic extent or other business-specific rules.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"42_Enable_Row-Level_Security\"><\/span>4.2 Enable Row-Level Security<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>First, grant the editor group the table-level permissions it requires:<\/p>\n<pre><code>GRANT SELECT, INSERT, UPDATE, DELETE\r\nON gis.work_orders\r\nTO gis_editor;\r\n\r\nGRANT SELECT, INSERT, UPDATE, DELETE\r\nON gis.work_orders\r\nTO gis_admin_group;<\/code><\/pre>\n<p>Now enable row-level security:<\/p>\n<pre><code>-- Require applicable policies for normal table access.\r\nALTER TABLE gis.work_orders\r\nENABLE ROW LEVEL SECURITY;<\/code><\/pre>\n<p>When RLS is enabled, users must have both the necessary table privileges and an applicable row policy to perform an operation.<\/p>\n<p>Without an applicable policy, access is denied by default.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"43_Restrict_Editors_to_Their_Assigned_Records\"><\/span>4.3 Restrict Editors to Their Assigned Records<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Create a policy for the editor group:<\/p>\n<pre><code>-- Editors may view, modify, or delete only their own\r\n-- assigned records. New or updated records must remain\r\n-- assigned to their own database login.\r\n\r\nCREATE POLICY work_orders_editor_scope\r\nON gis.work_orders\r\nFOR ALL\r\nTO gis_editor\r\nUSING (\r\n    assigned_to = current_user\r\n)\r\nWITH CHECK (\r\n    assigned_to = current_user\r\n);<\/code><\/pre>\n<p>The two clauses serve different purposes:<\/p>\n<ul>\n<li><code>USING<\/code> determines which existing rows can be accessed by applicable operations.<\/li>\n<li><code>WITH CHECK<\/code> determines whether new or updated rows satisfy the policy.<\/li>\n<\/ul>\n<p>For example, if Alice connects as <code>alice_editor<\/code>, she can access rows whose <code>assigned_to<\/code> value equals <code>alice_editor<\/code>. She cannot change a record&#8217;s assignment to another login if that would violate the policy.<\/p>\n<p>This example assumes that database login names are the correct identifiers for user assignments. More complex organizations may require a separate user-to-area or user-to-project authorization model.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"44_Allow_Designated_Administrators_to_Access_All_Work_Orders\"><\/span>4.4 Allow Designated Administrators to Access All Work Orders<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>An administrative policy can permit the administrator group to access every work order:<\/p>\n<pre><code>-- Only members of the trusted administration group\r\n-- should receive this privilege.\r\n\r\nCREATE POLICY work_orders_admin_all\r\nON gis.work_orders\r\nFOR ALL\r\nTO gis_admin_group, gis_owner\r\nUSING (TRUE)\r\nWITH CHECK (TRUE);<\/code><\/pre>\n<p>To apply policies to the table owner as well, force row-level security:<\/p>\n<pre><code>ALTER TABLE gis.work_orders\r\nFORCE ROW LEVEL SECURITY;<\/code><\/pre>\n<p>This is useful where the owner should also be subject to policies. The explicit owner policy allows that role access to the rows.<\/p>\n<p><strong>Important:<\/strong> PostgreSQL superusers and roles with the <code>BYPASSRLS<\/code> attribute bypass row-level security. Table owners normally bypass RLS unless <code>FORCE ROW LEVEL SECURITY<\/code> is enabled. Carefully control these privileged accounts.<\/p>\n<p>For more details, consult the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/ddl-rowsecurity.html\" target=\"_blank\" rel=\"noopener\">official PostgreSQL row-level security documentation<\/a>.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"45_Enforcing_Spatial_Layer_Constraints\"><\/span>4.5 Enforcing Spatial Layer Constraints<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>RLS controls which rows a user may access; it does not inherently restrict the geographic area covered by a geometry.<\/p>\n<p>Use spatial column definitions and constraints to maintain data integrity. For example:<\/p>\n<pre><code>-- Example: a layer for points in a known projected CRS.\r\nCREATE TABLE gis.inspection_sites (\r\n    site_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\r\n\r\n    site_name TEXT NOT NULL,\r\n\r\n    geom geometry(Point, 32737) NOT NULL,\r\n\r\n    CONSTRAINT inspection_sites_geom_valid\r\n        CHECK (ST_IsValid(geom))\r\n);<\/code><\/pre>\n<p>This definition requires valid point geometry with the specified SRID. Replace EPSG:32737 with the correct projected coordinate system for your study area.<\/p>\n<p>For an application that must restrict features to a defined geographic extent, an additional constraint or validation process can check whether each geometry lies within the approved boundary. Choose that boundary from an authoritative dataset and ensure it uses a compatible CRS.<\/p>\n<p>The best design combines separate controls:<\/p>\n<ul>\n<li><strong>Table privileges<\/strong> determine whether the user can read or modify records.<\/li>\n<li><strong>Row-level security<\/strong> determines which records the user can access.<\/li>\n<li><strong>Geometry constraints<\/strong> enforce spatial type, SRID, validity, and other selected data rules.<\/li>\n<li><strong>Views<\/strong> expose curated layers or attributes to less-privileged users.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"5_Connecting_User_Roles_from_QGIS_and_ArcGIS_Pro\"><\/span>5. Connecting User Roles from QGIS and ArcGIS Pro<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once the PostgreSQL roles and privileges are configured, desktop GIS clients can connect using the corresponding login accounts.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"51_Connect_from_QGIS\"><\/span>5.1 Connect from QGIS<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1177\" src=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_5qawqk5qawqk5qaw-1024x559.jpg\" alt=\"Multi-User Spatial Databases in PostgreSQL connection setup and layer browsing interface in QGIS.\" width=\"1024\" height=\"559\" srcset=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_5qawqk5qawqk5qaw-1024x559.jpg 1024w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_5qawqk5qawqk5qaw-300x164.jpg 300w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_5qawqk5qawqk5qaw-768x419.jpg 768w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_5qawqk5qawqk5qaw.jpg 1408w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p>In QGIS:<\/p>\n<ol start=\"1\">\n<li>Open the <strong>Browser<\/strong> panel.<\/li>\n<li>Expand <strong>PostgreSQL<\/strong> and select <strong>New Connection<\/strong>.<\/li>\n<li>Enter the database host, port, database name, and username.<\/li>\n<li>Authenticate using the assigned login credentials.<\/li>\n<li>Connect and expand the schemas.<\/li>\n<li>Add the authorized spatial tables or views to the map.<\/li>\n<\/ol>\n<p>Confirm that the connection uses the intended account. If an administrator account is used for testing, you may incorrectly conclude that editors or viewers can access more data than their own permissions allow.<\/p>\n<p>For production, use QGIS&#8217;s <strong>Authentication<\/strong> system to store and reference credentials securely. Avoid storing unprotected passwords in shared project files.<\/p>\n<p>The <a href=\"https:\/\/docs.qgis.org\/latest\/en\/docs\/user_manual\/auth_system\/auth_overview.html\" target=\"_blank\" rel=\"noopener\">QGIS documentation on authentication configurations<\/a> explains credential management and connection authentication.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"52_Connect_from_ArcGIS_Pro\"><\/span>5.2 Connect from ArcGIS Pro<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>In <a href=\"https:\/\/helpwithmygisassignment.com\/blog\/geodatabase-topology-rules-arcgis-pro\/\">ArcGIS Pro<\/a>:<\/p>\n<ol start=\"1\">\n<li>Open the <strong>Catalog<\/strong> pane.<\/li>\n<li>Right-click <strong>Databases<\/strong> and select <strong>New Database Connection<\/strong>.<\/li>\n<li>Choose PostgreSQL as the database platform.<\/li>\n<li>Enter the database server, database name, and login information.<\/li>\n<li>Test the connection.<\/li>\n<li>Expand the database connection to browse available schemas and datasets.<\/li>\n<\/ol>\n<p>For ordinary PostgreSQL\/PostGIS access, the account needs the relevant database, schema, and table privileges. If you are using an ArcGIS enterprise geodatabase, additional Esri-specific permissions and administration requirements may apply.<\/p>\n<p>ArcGIS Pro creates a database connection file (<code>.sde<\/code>). Such files can contain credentials when the save-password option is enabled. Protect these files and use appropriately restricted accounts.<\/p>\n<p>Esri&#8217;s guidance on <a href=\"https:\/\/pro.arcgis.com\/en\/pro-app\/latest\/help\/data\/databases\/connect-postgresql.htm\" target=\"_blank\" rel=\"noopener\">connecting to PostgreSQL<\/a> explains database connections, authentication, and the implications of saving login information.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"53_Test_Each_Role_Using_Its_Own_Credentials\"><\/span>5.3 Test Each Role Using Its Own Credentials<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>After connecting, perform simple verification tests.<\/p>\n<p>For the viewer account:<\/p>\n<pre><code>-- This should succeed if SELECT access was granted.\r\nSELECT COUNT(*)\r\nFROM gis.public_parcels;<\/code><\/pre>\n<p>Attempting to modify a restricted table should fail if the account has no relevant write privilege:<\/p>\n<pre><code>-- This should fail for a genuinely read-only account.\r\nUPDATE gis.parcels\r\nSET land_use = 'Residential'\r\nWHERE parcel_id = 100;<\/code><\/pre>\n<p>For the editor account, test an authorized operation against an approved table. If RLS is enabled, also confirm that the editor cannot access another user&#8217;s restricted records.<\/p>\n<p>Testing with actual login accounts is one of the most important steps in verifying a multi-user permission architecture.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"6_Troubleshooting_Common_Permission_Problems\"><\/span>6. Troubleshooting Common Permission Problems<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<table>\n<tbody>\n<tr>\n<th>Symptom<\/th>\n<th>Likely cause<\/th>\n<th>What to check<\/th>\n<\/tr>\n<tr>\n<td>Connection is rejected<\/td>\n<td>Authentication, network, or database access problem<\/td>\n<td>Credentials, server access rules, <code>CONNECT<\/code> privilege, and firewall<\/td>\n<\/tr>\n<tr>\n<td>Schema appears empty<\/td>\n<td>Missing schema or table privileges<\/td>\n<td><code>USAGE<\/code> on the schema and required table privileges<\/td>\n<\/tr>\n<tr>\n<td>Layer is visible but cannot be edited<\/td>\n<td>Missing write privilege, unsuitable table structure, or an applicable RLS policy<\/td>\n<td><code>INSERT<\/code>, <code>UPDATE<\/code>, <code>DELETE<\/code>, primary key, and row policy<\/td>\n<\/tr>\n<tr>\n<td>Editor can see too many records<\/td>\n<td>Missing or incorrect row-level security<\/td>\n<td>RLS enablement and <code>USING<\/code> policies<\/td>\n<\/tr>\n<tr>\n<td>New tables are not accessible<\/td>\n<td>Default privileges were not configured for the object-creating role<\/td>\n<td>Ownership and <code>ALTER DEFAULT PRIVILEGES<\/code><\/td>\n<\/tr>\n<tr>\n<td>Public users can see restricted data<\/td>\n<td>Broad table grants or an unsafe view<\/td>\n<td>Base-table access, view definition, and RLS<\/td>\n<\/tr>\n<tr>\n<td>Users can modify schema objects unexpectedly<\/td>\n<td>Excessive ownership or <code>CREATE<\/code> permissions<\/td>\n<td>Role memberships, schema privileges, and object ownership<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2><span class=\"ez-toc-section\" id=\"7_Summary_Checklist_for_GIS_Systems_Administrators\"><\/span>7. Summary Checklist for GIS Systems Administrators<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before making a multi-user spatial database available to an organization, check that you have:<\/p>\n<ul>\n<li><input type=\"text\" \/>\n<div>\n<p>Created separate administrative, ownership, editor, and viewer roles.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Created an individual login account for each person or application.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Avoided sharing a single powerful database login among all GIS users.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Granted <code>CONNECT<\/code> and schema <code>USAGE<\/code> where required.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Granted editors only the necessary data-modification privileges.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Restricted viewers to approved tables or curated views.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Configured default privileges for objects created by the appropriate owner role.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Applied row-level security where different users require access to different records.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Defined geometry type, SRID, validity, and other relevant data constraints.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Tested the permissions using each role&#8217;s actual login credentials.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Protected connection files, passwords, and authentication configurations.<\/p>\n<\/div>\n<\/li>\n<li><input type=\"text\" \/>\n<div>\n<p>Documented the role architecture and established processes for revoking access when users leave or change responsibilities.<\/p>\n<\/div>\n<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"8_Conclusion\"><\/span>8. Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A secure multi-user PostGIS database requires more than creating a username and granting access to a collection of tables. It requires a clear role architecture, deliberate object ownership, appropriate privileges, and a consistent approach to data integrity.<\/p>\n<p>The most maintainable pattern is to separate ownership from ordinary users, grant editing rights through group roles, expose approved data through read-only privileges or views, and apply row-level security when access depends on the individual record.<\/p>\n<p>With these controls in place, QGIS and ArcGIS Pro users can collaborate through a central spatial database while administrators retain control over security, data quality, and the database lifecycle.<\/p>\n<p><strong>The central principle is simple: give each user the access required to perform their work, and no more than that.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Building Multi-User Spatial Databases: Managing User Roles &amp; Permissions in PostgreSQL &nbsp; 1. Introduction: Managing a Shared Spatial Database Building Multi-User Spatial Databases in PostgreSQL is essential for organizations sharing geospatial datasets securely across teams. Imagine a county government maintaining a central spatial database containing land parcels, roads, water pipelines, building footprints, and zoning boundaries. &#8230; <a title=\"Building Multi-User Spatial Databases: Managing User Roles &#038; Permissions in PostgreSQL\" class=\"read-more\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/multi-user-spatial-databases-in-postgresql\/\" aria-label=\"Read more about Building Multi-User Spatial Databases: Managing User Roles &#038; Permissions in PostgreSQL\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":1177,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,4],"tags":[],"class_list":["post-1160","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-civil-engineering","category-gis-articles"],"_links":{"self":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/comments?post=1160"}],"version-history":[{"count":4,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1160\/revisions"}],"predecessor-version":[{"id":1178,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1160\/revisions\/1178"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/media\/1177"}],"wp:attachment":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/media?parent=1160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/categories?post=1160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/tags?post=1160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}