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:- have a shared db
- have shared and non shared schemas
- every table/layer created in a shared schema can be altered by different users
- 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: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.