QGIS and PostgreSQL: Setting Up Editor and View Access for Multi-User Data Management

In modern GIS workflows, multiple users often need to access the same spatial data without compromising its integrity.
Combining QGIS with a PostgreSQL/PostGIS database provides a secure and efficient solution. This article demonstrates how editor and viewer roles are created, privileges are assigned, and QGIS is connected for multi-user data editing and viewing.

Assigning Roles and Privileges in a PostgreSQL Database

After creating the database, two primary roles were defined: editor and viewer. These roles ensure controlled access by clearly separating users who can modify data from those who can only view it.

CREATE ROLE editor_role NOLOGIN;
CREATE ROLE viewer_role NOLOGIN;

The keyword NOLOGIN indicates that this role cannot log in directly to the database. They serve as group roles (or templates) to which privileges are assigned, and these privileges are later inherited by individual user accounts. Without the NOLOGIN attribute, each of these roles could function as a standalone user account.

Granting Privileges to Roles

Editor Role
The editor_role has full editing permissions.
It can connect to the database, use the public schema, and perform read, insert, update, and delete operations.

GRANT CONNECT ON DATABASE gis_database TO editor_role;
GRANT USAGE, CREATE ON SCHEMA public TO editor_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO editor_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO editor_role;

Viewer Role
The viewer_role is restricted to read-only access.
It can view existing data but cannot insert, modify, or delete records.

GRANT CONNECT ON DATABASE gis_database TO viewer_role;
GRANT USAGE ON SCHEMA public TO viewer_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO viewer_role;

Applying Privileges to Future Objects

The privileges above apply to all existing objects in the database (tables, sequences, and functions).
To ensure that the same permissions are automatically applied to newly created objects, it is necessary to configure default privileges.

Editor

GRANT CONNECT ON DATABASE gis_database TO editor_role;
GRANT USAGE, CREATE ON SCHEMA public TO editor_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO editor_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO editor_role;

Viewer
The viewer role only needs read access to tables and does not require permissions for sequences or functions.
In QGIS, SQL functions are accessed via SELECT statements rather than direct EXECUTE commands.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO viewer_role;

Accessing the Database through QGIS

After creating the user accounts and assigning roles, the next step is to access the database using QGIS.

Using a standard PostgreSQL connection, a connection to the database is first established. Detailed instructions on how to connect and the required steps can be found here:
How to connect PostGIS to QGIS – Guide

Once the connection is established, users log in with their individual accounts, depending on whether they have been assigned the editor or viewer role.

Login to database using QGIS

Example Use Case – Street Lighting

As an example, we use data for street lighting. The data is of type Point and visualized according to warranty expiration.
More details about this example can be found here: Monitoring Street Lighting Warranties in QGIS.

Logging in as an editor:
A user with the editor_role can add new data thanks to privileges granted:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor_role;

In QGIS, Editor mode is activated, and new objects are added via the standard form for creating new spatial features.

Logging in as a viewer:
Users with the viewer_role will notice that editing tools are disabled (greyed out) and cannot be activated.
Data can be viewed only, without any modification capabilities.

Disabled tools in viewer mode

Tracking Changes through Attributes

In the attribute tables, columns such as edited_by and updated_by are visible.
Using triggers, these columns are automatically populated with the username of the person making the change and the timestamp of the modification.

This functionality is essential for tracking the work of staff managing the data and ensures transparency and control over the entered information.

Optionally, a maximum number of simultaneous connections per user can be defined.
This prevents a single user from overloading the server with too many connections and ensures that accidentally left open connections do not consume unnecessary resources.

Tracking user and date in attribute table

Summary

Defining editor and viewer roles in PostgreSQL enables structured and secure access to spatial data. Editors are able to modify data, while viewers are restricted to read-only access.
This setup integrates seamlessly with QGIS, allowing multiple users to work on the same database while maintaining transparency and control over changes.

📌 Explore more articles