I have a PostGIS database for several projects, most of which we work with in QGIS. Each project has it's own schema. For each schema I have created a group role project-members, where project is replaced by the respective project name. What I want to achieve is that all members of a project have read rights for all objects that are created byother members within their project.
For that I thought of the follwing settings:
GRANT CREATE, USAGE ON SCHEMA *project*;
ALTER DEFAULT PRIVILEGES IN SCHEMA *project*
GRANT SELECT ON TABLES TO *project*_members;
Now each user establishes a connection to PostGIS in the QGIS browser and members of the same project should be able to see what the other members did and load layers. Unfortunately, this does not work with my setup. In this answer I learned why: The default privileges are only set for the group role and not for it's members. The proposed solution - to create tables only as the group role and not as individual users - works when I use the console but does not work for users that are connected to the database through QGIS because cannot use SET ROLE ...
within QGIS. Also, I don't want to bother every QGIS user with PostgreSQL commands.
Does anybody have a hint, how I can solve this? Maybe I can change my setup in a way that works the way I would like it to.
With help of the answer from DavidP and this answer I could come up with the following solution: Whenever a table is created, the event trigger trg_create_table calls the function trg_create_table_func(). This function queries for all users, which have USAGE
and/or CREATE
privileges in the schema in which the table was created and grants them the SELECT
privilege for the new table.
CREATE OR REPLACE FUNCTION trg_create_table_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
obj record;
usr record;
BEGIN
FOR obj IN
SELECT *
FROM pg_event_trigger_ddl_commands()
WHERE command_tag IN ('CREATE TABLE','CREATE TABLE AS')
LOOP
FOR usr IN
SELECT split_part(unnest(nspacl)::text, '=', 1) AS user_name
FROM pg_namespace
WHERE nspname = split_part(obj.object_identity , '.', 1)
LOOP
EXECUTE 'GRANT SELECT ON TABLE ' || obj.object_identity || ' TO ' || usr.user_name;
END LOOP;
END LOOP;
END;
$$;
CREATE EVENT TRIGGER trg_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE','CREATE TABLE AS')
EXECUTE PROCEDURE trg_create_table_func();
Since this is my first time working with functions and event triggers in PostgreSQL, please feel free to improve if you know a better way.