How to create an editable personal copy of a read-only PostGIS layer in QGIS 3.4

Question

I have a few vector layers in a PostGIS database. I want to give the other users of the database read access to these layers. I don't want them to be able to alter them, though. Instead, if anybody wants to make their personal adjustments, they should create their personal copy, of which they are the owner. In PostgreSQL,

CREATE TABLE personal_table as public_table;

Does what I want. Of course, this can also be done from the DBManager.

Is there any other way to accomplish that within the GUI of QGIS 3.4 for users, who are not comfortable with learning code commands?

Usually, the users drag and drop the layers they need from the PostGIS menu in the browser. Right click==>duplicate only creates a copy of the layer in the project but doesn't create a new table with ownership, so the user cannot edit.

Answer 1

The users can simply drag'n'drop the table into the schema they have rights to, directly within the PostGIS tab in the Browser (no need to import first, just 'move copy'); the role defined in the QGIS to PostGIS connection settings will be the owner of the copy.

If your users are UI bound and want to create a copy within the same schema, they need to import into QGIS, rename and drag'n'drop back into the same schema.

There are alternatives by creating Virtual Layers via the DB Manager, but that requires some minor SQL knowledge.

---

A more advanced option could be to work with a View of the base layer, and have triggers in place to reroute any edits to a personal copy (automatically created by the trigger) of the table, or into (a set of) edit log tables (A & D tables)