Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

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 <jayknowsunix(at)gmail(dot)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 <oduwareosahon(at)gmail(dot)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(dot)broccolo(at)2ndquadrant(dot)it> wrote:
>>
>>> Hi Osahon,
>>>
>>> 2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosahon(at)gmail(dot)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(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it
>>>
>>
>>
>