Hi Giuseppe, Thanks for the response. I have provided the GRANTS and other PostgreSQL setup scripts below as it includes what you have suggested:
ROLE --------- CREATE ROLE <role_name> WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; USER ---------- CREATE USER <username> WITH PASSWORD '<password>' REVOKES ---------------- REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC; REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC; GRANTS ------------- GRANT CONNECT ON DATABASE <database_name> TO <role_name>; 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 <username>; I cannot but wonder why these privileges are working when tested in pgAdmin/pgsql, but not in QGIS with the same user/schema/table/database. On Fri, Mar 17, 2017 at 4:16 PM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > 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 >
