Multiuser PostGIS: permissions, schemas and concurrent editing

Sharing one password across analysts prevents individual revocation and makes change attribution difficult. Design permission groups and separate personal or service accounts. A table owner should not be the account used routinely by a public viewer.

A role laboratory

In a test database you administer, this example creates a private schema, synthetic table and two roles unable to log in directly. Do not run it with existing names or in production.

CREATE ROLE gis_demo_reader NOLOGIN;
CREATE ROLE gis_demo_editor NOLOGIN;
CREATE SCHEMA access_lab;
CREATE TABLE access_lab.assets (
  id bigint PRIMARY KEY, name text NOT NULL,
  revision integer NOT NULL DEFAULT 1
);
INSERT INTO access_lab.assets VALUES (1, 'Synthetic asset', 1);
GRANT USAGE ON SCHEMA access_lab TO gis_demo_reader, gis_demo_editor;
GRANT SELECT ON access_lab.assets TO gis_demo_reader, gis_demo_editor;
GRANT UPDATE (name, revision) ON access_lab.assets TO gis_demo_editor;

Have an administrator assign these roles to test accounts. The reader should query successfully and be rejected when editing; the editor may change permitted fields but cannot drop the table. Test using the effective account, not the superuser that created the example.

Avoid silent overwrites

Concurrency needs a product rule: locking, conflict detection or merging. An optimistic-control pattern updates only when the previously read revision remains current:

UPDATE access_lab.assets
SET name = 'Reviewed synthetic asset', revision = revision + 1
WHERE id = 1 AND revision = 1
RETURNING id, revision;

The first execution returns revision 2. Repeating with revision 1 returns zero rows; the client must show a conflict and reload rather than report success. This pattern works only when every writer follows it or the database enforces an equivalent policy. Connecting QGIS does not activate it automatically.

Future-object permissions

Permissions on existing tables do not necessarily cover new tables. Configure default privileges for the role that actually creates objects, checking sequences, views and functions. Restrict search_path and connections to intended schemas. Public views should exclude private attributes before data reaches the map server.

Tests before deployment

Open two sessions, read the same revision and save incompatible changes. Check the second save's response, rollback after a failed transaction and account revocation. Record isolation level and expected locks. PostgreSQL provides transactions and access control; it does not automatically reproduce Esri enterprise-geodatabase versioning. If that workflow is critical, preserve it or rebuild it against explicit criteria.

Map privileges to operations, not job titles alone

Write a small permission matrix before granting access. An analyst who edits asset names may not need to delete assets, alter schemas or modify review decisions. A publication service may need only a restricted view. A migration loader may need temporary staging permissions that should not become permanent operational access.

Use separate login identities for revocation and attribution, then grant group roles. Verify effective privileges using those identities. Testing as the owner can hide missing grants and overbroad assumptions.

Understand future-object grants

Default privileges belong to the role creating the future objects. Configuring them for an administrator does not automatically affect tables created by a separate deployment role. In a lab with an existing gis_owner role, an administrator acting with the necessary rights could configure:

ALTER DEFAULT PRIVILEGES FOR ROLE gis_owner IN SCHEMA access_lab
GRANT SELECT ON TABLES TO gis_demo_reader;

This does not grant access to existing tables, and schema usage is still required. Create a new disposable table as gis_owner, query it as the reader, and confirm an update fails. Repeat for relevant sequences or functions only if the workflow requires them. Avoid broad grants simply to silence an error whose source has not been understood.

Rehearse two writers with explicit expectations

1. Two sessions read asset 1 at revision 1.
2. Session A updates the name using revision 1 and commits, receiving revision 2.
3. Session B submits a different name with revision 1 and receives zero updated rows.
4. The client displays the current record and the user's proposed change, then asks for a deliberate resolution.
5. A revised submission uses the newly read revision; the application does not silently retry over the other person's change.

The SQL pattern in this guide is useful only when the actual write path follows it. Generic desktop editing does not automatically add your revision predicate. If QGIS writes directly, verify its behavior and decide whether editing must pass through a controlled service, trigger design or another tested mechanism. Treat that integration as implementation work, not as a checkbox labeled “PostGIS supports concurrency.”

Separate auditability from conflict prevention

An audit record can explain a lost update without preventing it. Conversely, a conflict check can reject a stale edit without recording who attempted it. Define both requirements. Keep change history proportionate to the task and control access to any personal information it contains. Never infer a person's identity from a shared database account.

For approval workflows, distinguish the editor's proposed geometry from the reviewed result. Do not let a simple status field imply separation of duties unless permissions actually enforce it. Test account revocation and reconnect behavior; already-open sessions may require an operational response.

Acceptance failures worth catching early

A reader can update through a function; a new table is unexpectedly public; an editor can change the identifier used by relationships; a failed transaction leaves the client unusable; or a stale edit is shown as successful. Include these negative cases in the pilot. The backup and recovery guide covers the separate question of restoring data after a valid but unwanted change.

References: PostgreSQL privileges, default privileges, and transaction isolation.

Sources and documentation

• PostGIS reference
• PostgreSQL documentation

Next step

Continue in the Open GIS collection. For a specific project, use the total-cost calculator and request an assessment.

Related reading

• PostGIS spatial indexes: measure before optimizing
• Back up PostGIS and prove it can be restored