Description

Many thanks to Luna Geospatial for the video content at the bottom of this GIS article!!!

Also, please refer to:

PostGIS/QGIS and granting permissions – Geographic Information Systems Stack Exchange

*** Thanks to Spatial Database Advisor for some initial feedback ***

Setting up appropriate table permissions in PostgreSQL for QGIS users is
crucial for maintaining data integrity and controlling access levels.
Here’s a guide on best practices:

1. Define User Roles Based on Access Requirements

Read-Only Users: Users who should only view spatial and attribute data without making modifications.

Attribute Editors: Users permitted to edit non-spatial attributes but restricted from altering spatial geometries.

Spatial Editors: Users authorized to modify both spatial features and attribute data.

2. Create PostgreSQL Roles and Assign Permissions

Read-Only Role:

— Grant CONNECT on the database
  GRANT CONNECT ON DATABASE your_database TO readonly_role;
 
  — Grant USAGE on the schema
  GRANT USAGE ON SCHEMA your_schema TO readonly_role;
 
  — Grant SELECT on all tables within the schema
  GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO readonly_role;

This configuration ensures that users assigned to readonly_role can
connect to the database, access the schema, and perform SELECT
operations on all tables.

Attribute Editor Role:

— Grant CONNECT on the database

  GRANT CONNECT ON DATABASE your_database TO attribute_editor_role;
 
  — Grant USAGE on the schema
  GRANT USAGE ON SCHEMA your_schema TO attribute_editor_role;
 
  — Grant SELECT and UPDATE on non-geometry columns
  GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO attribute_editor_role;
  GRANT UPDATE (non_geom_column1, non_geom_column2, …) ON your_table TO attribute_editor_role;

Replace non_geom_column1, non_geom_column2, … with the specific
attribute columns that attribute editors are allowed to modify.

Spatial Editor Role:

  — Grant CONNECT on the database
  GRANT CONNECT ON DATABASE your_database TO spatial_editor_role;
 
  — Grant USAGE on the schema
  GRANT USAGE ON SCHEMA your_schema TO spatial_editor_role;
 
  — Grant SELECT and UPDATE on all columns, including geometry
  GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA your_schema TO spatial_editor_role;
This setup allows spatial editors full read and write access to both spatial and non-spatial data.

3. Assign Roles to Users

After defining roles, assign them to respective users:

— Assign readonly_role to a user
GRANT readonly_role TO readonly_user;

— Assign attribute_editor_role to a user
GRANT attribute_editor_role TO attribute_editor_user;

— Assign spatial_editor_role to a user
GRANT spatial_editor_role TO spatial_editor_user;

4. Implement Row-Level Security (Optional)

For more granular control, such as restricting access to specific rows
based on user roles, consider implementing Row-Level Security (RLS):

— Enable RLS on the table
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

— Create a policy for read-only users
CREATE POLICY read_only_policy ON your_table
  FOR SELECT
  USING (true)
  TO readonly_role;

— Create a policy for attribute editors
CREATE POLICY attribute_edit_policy ON your_table
  FOR UPDATE OF non_geom_column1, non_geom_column2, …
  USING (true)
  TO attribute_editor_role;

— Create a policy for spatial editors
CREATE POLICY spatial_edit_policy ON your_table
  FOR UPDATE
  USING (true)
  TO spatial_editor_role;

5. Considerations for QGIS Integration

Primary Keys: Ensure all tables have primary keys. QGIS requires primary keys to identify and edit features properly.

Multiple Geometry Columns: Be cautious when tables have multiple
geometry columns. Users need UPDATE permissions on all geometry columns
to avoid issues when saving edits in QGIS.

User Connections: QGIS may cache user privileges. If you change a user’s
permissions, advise them to restart QGIS to ensure the new permissions
take effect.

6. Additional Resources

For a comprehensive understanding and visual guidance, consider watching the following tutorial:

Multiuser Editing with QGIS and PostGIS in an Enterprise Environment

This video covers user and group role management in PostgreSQL, schema,
table, and field permissions, and row-level security in a multiuser
environment.

The Author has Field Tested the above and put the results in a PowerPoint that can be downloaded here – PostGISConfigEnterprise.