Digital Geography

12. May 2019

Colaborative Working in PostGIS

The last days I needed to work with other geoenthusiasts on a PostGIS database. Unfortunately, as you upload a layer from QGIS you will be the owner of the new created table and no one is able to alter it by default. Here I show you, how to change this using some “trigger functions” and some shared roles.

The Issue

If you install PostgreSQL and its great PostGIS extension, you want to edit layers/tables in QGIS which are somehow stored on the db. This works great if you are the only user of the data. But sometimes you want to share the data with a colleague or you have a schema were you and your colleagues would like to work on together. If you, as a worker upload a table/layer to PostGIS, you become the owner of the table. To grant others access to it, can be done using pgsql of course, but it’s still on a very manual basis. I like to have some automatism. So my goal was:
  1. have a shared db
  2. have shared and non shared schemas
  3. every table/layer created in a shared schema can be altered by different users
  4. every “private” table should still be private

The Setup

I will not cover the PostGIS installation as it can be read for example here. Once I have this, I also assume to have two different users called anna and daiyu and a database called gisdb and two schemas roads and places.

The Shared Role

First of all I create a role called pgpublish and add the users to this role:
CREATE ROLE pgpublish WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  CREATEDB
  NOCREATEROLE
  NOREPLICATION;
GRANT pgpublish TO daiyu, anna;
This user should be able to do a lot of stuff with the db and the tables in the shared schemas:
GRANT ALL ON SCHEMA places,roads TO pgpublish;
The user should still be able to create things:
GRANT CREATE ON DATABASE gisdb TO pgpublish;
And of course, any user with this role assigned should be able to edit data:
GRANT UPDATE, INSERT, SELECT, DELETE ON ALL TABLES IN SCHEMA places,roads TO pgpublish;

The Trigger

As a user anna can connect to the DB and has access to the shared schemas, she can upload some tables as well. But she will still be owner of the table and other users can’t see, use nor edit them: For the purpose of real sharing we need to give pgpublish the ownership of the table or directly grant rights to the different user daiyu. I will not do this here. Instead I am adding a “watcher” on system events of the database which listens to the CREATE TABLE event:
CREATE EVENT TRIGGER trgCreateTable
	ON ddl_command_end
	WHEN tag IN ('CREATE TABLE')
	EXECUTE PROCEDURE trgCreateSetOwner();
This event trigger is fired once a table is created. And it starts a function called trgCreateSetOwner:
CREATE FUNCTION public.trgCreateSetOwner()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    if obj.schema_name In (places, roads) THEN
		EXECUTE format('ALTER TABLE %s OWNER TO pgpublish', obj.object_identity);
  	END IF;
  END LOOP;
END;
$BODY$;

ALTER FUNCTION public.trgCreateSetOwner()
    OWNER TO pgpublish;
So, if any user of the role pgpublish now adds a layer to the shared schemas, the layer’s owner is changed and every user with role pgpublish can alter, edit, delete the table if needed/wanted. Any other schema is unaffected so users can still create schemas for their private use.
This example is heavily borrowed from Magnus Hagander‘s blog.