12. May 2019
The IssueIf 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 SetupI 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 RoleFirst 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 TriggerAs 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.