I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY privilege on all tables in a schema as shown below:
GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [role_name] TO [user_name]
I'd have done this as followed:
REVOKE ALL ON SCHEMA [schema_name] FROM PUBLIC; GRANT USAGE ON SCHEMA [schema_name] TO [role_name]; GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name];
GRANT [role_name] TO [user_name];
Next, I test this by trying to UPDATE a column in a table (same schema as above) with pgAdmin/psql and this works fine by giving a response that the user has no permission - 'ERROR: permission denied for relation <table_name>.'
Next, I connect with the same user in QGIS and add a layer from the same table (same schema as above). I open the attribute table for the layer, turn on editing mode (by clicking on the pencil-like icon), and edit the same field/column above. To my surprise, the edit was saved successfully without any permission error prompt.
Next, I check the value of the field/column (same table/schema as above) in pgAdmin/psql and it is having the new (edited) value from QGIS. This is rather strange as it seems QGIS is bypassing the permissions set for the same user in the PostgreSQL/PostGIS database.
I will be glad if someone can help me unravel this mystery.
Check which user is used the first time you connect to the database through QGIS, and if you switch the user to [user_name] in a second moment. I'm wondering if you are keeping some privileges from a previous session.
