Hi John, Thanks for your response. From my experience as a Software Developer, I don't think QGIS can logon to my database/schema/table with a dedicated user as they need authentication to do so. What you described about using a dedicated user in applications is only possible because that user was created in the database server and granted necessary privileges/permissions.
On Fri, Mar 17, 2017 at 5:09 PM, John Scalia <jayknowsu...@gmail.com> wrote: > 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.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 >>> >> >> >
