Managing PostGIS default privileges for users in QGIS?

Question

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.

Answer 1

You can use Event triggers to execute code after DDL operations. So, whenever your user creates an object in the desired schema, you can trigger function that executes GRANT on that object (or do whatever you want).

My proposed solution is this...

1. Create role read_all and grant that role to every user (or as desired)
2. Create Event trigger that changes ownership of newly created objects to desired owner (presumably to the project-members role). Then grant selects on that object to read_all role.

Now, each member of role project-members can edit objects owned by that role and every member of read_all role has read access to objects in diferent projects (schemas).

Maybe some tweaking will be necessary. It's from tom top of my head, not tested solution...