Restrict access to my PostgresSQL/ Post GIS database used through QGIS

Question

We have a small network of users accessing our QGIS data stored on a PostGIS database (in 3 schemas) which sits on my local drive in my computer. This drive is then shared with the others. Due to the other users being beginners in the GIS world I created a number of layer files (.qlr) for them which point back to the data in my PostGIS database. They can only see these layer files.

But I've just realised when they use these layer files they can still edit, add and remove the data.

How do I restrict this?

Is there a simple way in QGIS or even Windows? (setting permissions on the sharing folders does not seem to work)

The database was created through something called 'pgAdmin 4' and I've tried adding another user/role with different privileges but I can't seem to get that to work its seems to deny access completely or its all still wide open.

The privileges I can alter seem to be too high up the hierarchy all I need to restrict is the ability to write and delete to the schema's and tables, leaving myself with editing rights.

I have no idea how to write in SQL, I'm a geographer not IT. There is no Database Administrator here only me and I made this database just by following the steps on the pgAdmin help pages and some luck. So Im seeking idiot proof help.

Answer 1

That is best done through the PostgreSQL permission system.

Create database users for the people to access the database. Then grant them SELECT on the tables that contain the layer data and USAGE on the schemas containing these tables. For example:

GRANT USAGE ON SCHEMA myschema TO auser;
GRANT SELECT ON myschema.mytable TO auser;