Table of Contents

Building Multi-User Spatial Databases: Managing User Roles & Permissions in PostgreSQL

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. Several GIS analysts use QGIS or ArcGIS Pro to access the same datasets.

One analyst updates parcel boundaries, another edits road attributes, a planning officer reviews zoning maps, and students access selected layers for coursework.

Without a structured permission system, users might accidentally modify restricted datasets, delete important records, or gain access to information beyond their responsibilities.

Multi-user spatial database administration involves designing a PostgreSQL environment in which multiple users can access shared geographic data while maintaining appropriate security, data integrity, and editing controls.

PostgreSQL manages this through roles, privileges, ownership, and row-level security. PostGIS adds spatial data types and functions, while desktop applications such as QGIS and ArcGIS Pro connect to the database using individual database credentials.

The objective is to establish a centralized system in which each user can perform authorized tasks without receiving unnecessary privileges.

Why use a central PostgreSQL/PostGIS database?

Concurrent access: Multiple users can read and edit shared spatial datasets through database transactions.
Access control: Permissions determine which schemas, tables, and operations users can access.
Data integrity: Database constraints can enforce geometry types, coordinate reference systems, and other rules.
Centralized administration: Administrators can manage data, permissions, indexes, backups, and maintenance centrally.
Application flexibility: Different GIS clients can connect to the same database without maintaining separate copies of every dataset.

PostgreSQL’s role system provides the foundation for this architecture. Roles can represent individual logins, groups of users, or ownership identities. See the official PostgreSQL role management documentation.

2. Designing the Role Architecture

Before writing SQL permissions, determine who needs access and what each type of user should be allowed to do.

A practical enterprise GIS system separates administrative responsibilities, data ownership, editing, and viewing.

2.1 Administrator Role

Administrators maintain database security, schemas, database objects, and operational settings.

Typical responsibilities include:

Creating and managing database roles.
Granting and revoking permissions.
Managing schemas, tables, indexes, and constraints.
Monitoring database performance.
Configuring backups, maintenance, and database connectivity.

A critical distinction is that a GIS data administrator does not automatically need PostgreSQL superuser privileges.

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.

A non-login ownership role can own schemas and tables, while designated administrators receive controlled membership in that role.

2.2 Data Editor Role

Editors work with approved spatial datasets in QGIS or ArcGIS Pro.

They commonly require:

SELECT to view records.
INSERT to add records.
UPDATE to modify records.
DELETE to remove records when authorized.

Editors usually should not have CREATE privileges on shared schemas or ownership of production tables. Otherwise, routine editing could become unrestricted database administration.

2.3 Read-Only Viewer Role

Viewers might include planning officers, students, researchers, and members of the public using approved datasets.

They typically require only SELECT privileges on the information they are permitted to access.

A viewer should not be able to:

Insert new features.
Change existing geometry or attributes.
Delete records.
Create or modify database objects.

For public or student access, granting SELECT on curated views may be safer than granting access to every table in the database.

2.4 Recommended Architecture

<box gap={3}> <box border radius=”lg” padding={3} gap={1}> <row align=”center” gap={2}> <icon name=”shield-check” size=”xl” /> **Database administrator** </row> <text color=”secondary” size=”sm”>Manages cluster settings, roles, security, backups, and privileged operations.</text> <text size=”sm”>Uses a dedicated administrative login.</text> </box> <box align=”center”> <icon name=”arrow-down” size=”lg” color=”secondary” /> </box> <box border radius=”lg” padding={3} gap={1}> <row align=”center” gap={2}> <icon name=”database” size=”xl” /> **GIS data owner** </row> <text color=”secondary” size=”sm”>A non-login role owns the GIS schema and production tables.</text> <text size=”sm”>Separates object ownership from individual users.</text> </box> <box align=”center”> <icon name=”arrow-down” size=”lg” color=”secondary” /> </box> <grid columns={2} gap={2}> <grid-item> <box border radius=”lg” padding={3} gap={1} height=”100%”> <icon name=”edit-3″ size=”lg” /> **Editor group** <text size=”sm” color=”secondary”>Read and modify authorized spatial records.</text> </box> </grid-item> <grid-item> <box border radius=”lg” padding={3} gap={1} height=”100%”> <icon name=”eye” size=”lg” /> **Viewer group** <text size=”sm” color=”secondary”>Read approved tables or views only.</text> </box> </grid-item> </grid> </box>

The central principle is least privilege: every account receives only the permissions necessary for its work.

3. Step-by-Step SQL Scripting Guide

The following examples use a database named spatialdb and a schema named gis. Run administrative statements using an appropriately privileged account.

Step 1: Create Custom Database Roles

PostgreSQL has one role system for both users and groups. A role with LOGIN can authenticate to the database; a role with NOLOGIN can act as a group or ownership identity.

Create the ownership and permission groups:

-- Run once using a privileged administrative account.

CREATE ROLE gis_owner NOLOGIN;

CREATE ROLE gis_admin_group NOLOGIN;

CREATE ROLE gis_editor NOLOGIN;

CREATE ROLE gis_viewer NOLOGIN;

These roles establish the ownership, administration, editing, and viewing layers.

Next, create individual login accounts:

-- Individual administrator and GIS user accounts.
-- These roles do not receive passwords in this script.

CREATE ROLE gis_admin_login LOGIN;

CREATE ROLE alice_editor LOGIN;

CREATE ROLE student_viewer LOGIN;

Set each account’s password securely. In the interactive psql terminal, for example, use:

\password alice_editor

Repeat for the other login roles. The command prompts for the password rather than requiring a password literal in a SQL script.

In production, consider your organization’s password policy, secret-management tools, and approved authentication methods.

Step 2: Assign Users to Group Roles

Grant membership in the appropriate permission group:

-- Assign the editor's permissions to Alice.
GRANT gis_editor TO alice_editor;

-- Give the student read-only group membership.
GRANT gis_viewer TO student_viewer;

-- Assign controlled administrative privileges.
GRANT gis_admin_group TO gis_admin_login;
GRANT gis_owner TO gis_admin_login;

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.

In routine workflows, use an owner membership only for trusted accounts and deliberately assume the owner role when creating or changing owned objects:

-- Run in the intended database session.
SET ROLE gis_owner;

-- Create or administer objects that should be owned by gis_owner.

RESET ROLE;

The SET ROLE statement requires appropriate role membership and role-switching permission. Membership and inheritance options can vary with PostgreSQL version and configuration.

Security recommendation: Do not give ordinary editors membership in gis_owner, and do not use a PostgreSQL superuser account for routine editing from QGIS or ArcGIS Pro.

Step 3: Grant Database and Schema Access

A user generally needs permission to connect to the database and USAGE on a schema before accessing objects within it.

Assuming spatialdb already exists:

-- Allow the user groups to connect.
GRANT CONNECT ON DATABASE spatialdb
TO gis_admin_group, gis_editor, gis_viewer;

Connect to spatialdb before executing the following statements. Create the schema if it does not already exist:

-- Run in the spatialdb database.
CREATE SCHEMA gis AUTHORIZATION gis_owner;

If the schema already exists, inspect its owner rather than trying to create it again.

Grant schema access:

-- Allow the groups to resolve objects in the GIS schema.
GRANT USAGE ON SCHEMA gis
TO gis_admin_group, gis_editor, gis_viewer;

USAGE does not grant permission to read or modify tables. Those permissions are granted separately.

Also note that PostgreSQL requires appropriate network access, authentication configuration, and database privileges. A SQL GRANT CONNECT does not by itself configure the server’s pg_hba.conf rules or open a firewall port.

Step 4: Create a Spatial Table with Data Integrity Constraints

A geometry column can enforce a specific geometry type and spatial reference system.

For example, create a parcels table:

-- Execute as the designated owner role.
SET ROLE gis_owner;

CREATE TABLE gis.parcels (
    parcel_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_code TEXT NOT NULL UNIQUE,
    land_use TEXT NOT NULL,
    is_public BOOLEAN NOT NULL DEFAULT FALSE,

    -- Store only multipolygons in EPSG:4326.
    geom geometry(MultiPolygon, 4326) NOT NULL,

    -- Reject invalid polygon geometry.
    CONSTRAINT parcels_geom_valid
        CHECK (ST_IsValid(geom))
);

RESET ROLE;

This table illustrates several useful database controls:

The primary key uniquely identifies each parcel.
NOT NULL prevents required attributes from being omitted.
UNIQUE prevents duplicate parcel codes.
The geometry type and SRID restrict the spatial data structure.
The validity check rejects geometries that fail the selected validity test.

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.

PostGIS geometry types and spatial metadata are described in the PostGIS data management documentation.

Step 5: Grant Editing Privileges

Editors need permissions on the tables they are authorized to change.

For an existing GIS dataset, the basic grants are:

-- Allow existing editors to read and change approved tables.
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA gis
TO gis_editor;

If your tables use traditional SERIAL columns or sequences that the application accesses directly, grant the required sequence privileges as well:

-- Permit normal sequence usage for editor workflows.
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA gis
TO gis_editor;

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.

Notice what is not included: CREATE on the schema, table ownership, and superuser privileges. This lets editors manage approved records without automatically granting authority to redesign the database.

These grants apply to existing objects. They do not automatically grant equivalent permissions on every table created in the future.

Step 6: Make Permissions Work for Future Tables

In enterprise databases, new datasets are regularly added. PostgreSQL supports default privileges to control permissions on objects created in the future by a particular role.

For example:

-- Run as a role permitted to manage gis_owner's defaults.
-- These defaults apply to future tables created by gis_owner.

ALTER DEFAULT PRIVILEGES
FOR ROLE gis_owner
IN SCHEMA gis
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO gis_editor;

ALTER DEFAULT PRIVILEGES
FOR ROLE gis_owner
IN SCHEMA gis
GRANT USAGE, SELECT
ON SEQUENCES TO gis_editor;

These default privileges apply when gis_owner creates future objects in the gis schema. If another role creates a table, it needs its own applicable default privilege settings or explicit grants.

Do not apply broad default SELECT 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.

Step 7: Configure Read-Only Viewer Access

There are two common designs for viewer permissions.

Option A: The viewer can read every table in a designated read-only schema.

If all existing tables in a schema are approved for viewing, grant:

-- Allow read-only access to existing approved tables.
GRANT SELECT
ON ALL TABLES IN SCHEMA gis
TO gis_viewer;

For future tables, a default privilege can be used:

-- Use only if all new tables in this schema are approved
-- for viewer access.

ALTER DEFAULT PRIVILEGES
FOR ROLE gis_owner
IN SCHEMA gis
GRANT SELECT ON TABLES TO gis_viewer;

This pattern is convenient, but it can unintentionally expose future tables if the schema contains mixed public and confidential data.

Option B: The viewer can read only approved layers or columns.

For public or student access, a curated view is often safer.

-- Create a view containing only approved public parcels.
-- Run as the trusted object owner.

SET ROLE gis_owner;

CREATE VIEW gis.public_parcels AS
SELECT
    parcel_id,
    parcel_code,
    land_use,
    geom
FROM gis.parcels
WHERE is_public = TRUE;

RESET ROLE;

Remove direct access to the underlying table and grant access to the view:

-- Ensure viewers cannot query the underlying parcel table.
REVOKE ALL PRIVILEGES
ON gis.parcels
FROM gis_viewer;

-- Expose only the curated public layer.
GRANT SELECT
ON gis.public_parcels
TO gis_viewer;

A normal PostgreSQL view uses its owner’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.

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.

4. Enforcing Row-Level Security and Spatial Constraints

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.

For example, a road-maintenance organization might want each employee to update only the work orders assigned to them.

PostgreSQL Row-Level Security (RLS) supports these restrictions through policies.

4.1 Create a Table with User-Specific Records

Consider a table of maintenance work orders:

-- Run as gis_owner.
SET ROLE gis_owner;

CREATE TABLE gis.work_orders (
    work_order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    assigned_to NAME NOT NULL,

    status TEXT NOT NULL
        CHECK (status IN ('open', 'in_progress', 'closed')),

    -- Point location stored in WGS 84.
    geom geometry(Point, 4326) NOT NULL,

    CONSTRAINT work_orders_geom_valid
        CHECK (ST_IsValid(geom))
);

RESET ROLE;

The assigned_to column stores the database login name responsible for each work order.

The geometry declaration requires point geometry in EPSG:4326. Additional constraints could enforce the permitted geographic extent or other business-specific rules.

4.2 Enable Row-Level Security

First, grant the editor group the table-level permissions it requires:

GRANT SELECT, INSERT, UPDATE, DELETE
ON gis.work_orders
TO gis_editor;

GRANT SELECT, INSERT, UPDATE, DELETE
ON gis.work_orders
TO gis_admin_group;

Now enable row-level security:

-- Require applicable policies for normal table access.
ALTER TABLE gis.work_orders
ENABLE ROW LEVEL SECURITY;

When RLS is enabled, users must have both the necessary table privileges and an applicable row policy to perform an operation.

Without an applicable policy, access is denied by default.

4.3 Restrict Editors to Their Assigned Records

Create a policy for the editor group:

-- Editors may view, modify, or delete only their own
-- assigned records. New or updated records must remain
-- assigned to their own database login.

CREATE POLICY work_orders_editor_scope
ON gis.work_orders
FOR ALL
TO gis_editor
USING (
    assigned_to = current_user
)
WITH CHECK (
    assigned_to = current_user
);

The two clauses serve different purposes:

USING determines which existing rows can be accessed by applicable operations.
WITH CHECK determines whether new or updated rows satisfy the policy.

For example, if Alice connects as alice_editor, she can access rows whose assigned_to value equals alice_editor. She cannot change a record’s assignment to another login if that would violate the policy.

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.

4.4 Allow Designated Administrators to Access All Work Orders

An administrative policy can permit the administrator group to access every work order:

-- Only members of the trusted administration group
-- should receive this privilege.

CREATE POLICY work_orders_admin_all
ON gis.work_orders
FOR ALL
TO gis_admin_group, gis_owner
USING (TRUE)
WITH CHECK (TRUE);

To apply policies to the table owner as well, force row-level security:

ALTER TABLE gis.work_orders
FORCE ROW LEVEL SECURITY;

This is useful where the owner should also be subject to policies. The explicit owner policy allows that role access to the rows.

Important: PostgreSQL superusers and roles with the BYPASSRLS attribute bypass row-level security. Table owners normally bypass RLS unless FORCE ROW LEVEL SECURITY is enabled. Carefully control these privileged accounts.

For more details, consult the official PostgreSQL row-level security documentation.

4.5 Enforcing Spatial Layer Constraints

RLS controls which rows a user may access; it does not inherently restrict the geographic area covered by a geometry.

Use spatial column definitions and constraints to maintain data integrity. For example:

-- Example: a layer for points in a known projected CRS.
CREATE TABLE gis.inspection_sites (
    site_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    site_name TEXT NOT NULL,

    geom geometry(Point, 32737) NOT NULL,

    CONSTRAINT inspection_sites_geom_valid
        CHECK (ST_IsValid(geom))
);

This definition requires valid point geometry with the specified SRID. Replace EPSG:32737 with the correct projected coordinate system for your study area.

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.

The best design combines separate controls:

Table privileges determine whether the user can read or modify records.
Row-level security determines which records the user can access.
Geometry constraints enforce spatial type, SRID, validity, and other selected data rules.
Views expose curated layers or attributes to less-privileged users.

5. Connecting User Roles from QGIS and ArcGIS Pro

Once the PostgreSQL roles and privileges are configured, desktop GIS clients can connect using the corresponding login accounts.

5.1 Connect from QGIS

In QGIS:

Open the Browser panel.
Expand PostgreSQL and select New Connection.
Enter the database host, port, database name, and username.
Authenticate using the assigned login credentials.
Connect and expand the schemas.
Add the authorized spatial tables or views to the map.

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.

For production, use QGIS’s Authentication system to store and reference credentials securely. Avoid storing unprotected passwords in shared project files.

The QGIS documentation on authentication configurations explains credential management and connection authentication.

5.2 Connect from ArcGIS Pro

In ArcGIS Pro:

Open the Catalog pane.
Right-click Databases and select New Database Connection.
Choose PostgreSQL as the database platform.
Enter the database server, database name, and login information.
Test the connection.
Expand the database connection to browse available schemas and datasets.

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.

ArcGIS Pro creates a database connection file (.sde). Such files can contain credentials when the save-password option is enabled. Protect these files and use appropriately restricted accounts.

Esri’s guidance on connecting to PostgreSQL explains database connections, authentication, and the implications of saving login information.

5.3 Test Each Role Using Its Own Credentials

After connecting, perform simple verification tests.

For the viewer account:

-- This should succeed if SELECT access was granted.
SELECT COUNT(*)
FROM gis.public_parcels;

Attempting to modify a restricted table should fail if the account has no relevant write privilege:

-- This should fail for a genuinely read-only account.
UPDATE gis.parcels
SET land_use = 'Residential'
WHERE parcel_id = 100;

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’s restricted records.

Testing with actual login accounts is one of the most important steps in verifying a multi-user permission architecture.

6. Troubleshooting Common Permission Problems

Symptom	Likely cause	What to check
Connection is rejected	Authentication, network, or database access problem	Credentials, server access rules, `CONNECT` privilege, and firewall
Schema appears empty	Missing schema or table privileges	`USAGE` on the schema and required table privileges
Layer is visible but cannot be edited	Missing write privilege, unsuitable table structure, or an applicable RLS policy	`INSERT`, `UPDATE`, `DELETE`, primary key, and row policy
Editor can see too many records	Missing or incorrect row-level security	RLS enablement and `USING` policies
New tables are not accessible	Default privileges were not configured for the object-creating role	Ownership and `ALTER DEFAULT PRIVILEGES`
Public users can see restricted data	Broad table grants or an unsafe view	Base-table access, view definition, and RLS
Users can modify schema objects unexpectedly	Excessive ownership or `CREATE` permissions	Role memberships, schema privileges, and object ownership

7. Summary Checklist for GIS Systems Administrators

Before making a multi-user spatial database available to an organization, check that you have:

Created separate administrative, ownership, editor, and viewer roles.
Created an individual login account for each person or application.
Avoided sharing a single powerful database login among all GIS users.
Granted CONNECT and schema USAGE where required.
Granted editors only the necessary data-modification privileges.
Restricted viewers to approved tables or curated views.
Configured default privileges for objects created by the appropriate owner role.
Applied row-level security where different users require access to different records.
Defined geometry type, SRID, validity, and other relevant data constraints.
Tested the permissions using each role’s actual login credentials.
Protected connection files, passwords, and authentication configurations.
Documented the role architecture and established processes for revoking access when users leave or change responsibilities.

8. Conclusion

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.

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.

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.

The central principle is simple: give each user the access required to perform their work, and no more than that.