While I do not know QGIS, I'm wondering if it's similar to some of our applications where they always use the same system login for the database while each user provides a unique login to the application. Have you ever set log_connections in your postgresql.conf file? That would show you which user is connecting during your attempts, and they might very well be something you're not expecting. As far as I know, there is no way for any application to bypass PostgreSQL's internal security model.
On Fri, Mar 17, 2017 at 11:43 AM, Osahon Oduware <oduwareosa...@gmail.com> wrote: > 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.broccolo@ > 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 >> > >
