Hi Osahon, 2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosa...@gmail.com>:
> Hi All, > > 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. All the best, Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL & PostGIS Training, Services and Support giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
