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

I can not reproduce your issue. I have a standard read-only user. I connect
from qgis to postgres with that user and I can not even enable editing in
qgis (the "pencil" button is disabled). I'm on Linux, postgres 9.4, qgis
2.14. It sounds like your pgadmin is working as expected so I would suggest
that you double check your qgis configuration.

Rich

On Fri, Mar 17, 2017 at 9:45 AM, Osahon Oduware <oduwareosahon(at)gmail(dot)com>
wrote:

> Hi Tim,
>
> Ok, thanks for your help.
>
> On Fri, Mar 17, 2017 at 4:32 PM, HEARNE, TIMOTHY S <th1618(at)att(dot)com> wrote:
>
>> I am unfamiliar with the QGIS product. If when you logon using pgAdmin,
>> you can’t change the field / column then it is probably not a configuration
>> issue on the database side.
>>
>> It may be related to how the QGIS tool is configured and the permissions
>> necessary to use the tool. You may want to review how the tool was
>> installed or possibly contact their discussion group.
>>
>> *Tim Hearne*
>>
>> PROPRIETARY INFORMATION
>> The Information contained herein is for use only by authorized employees
>> of AT&T, and authorized
>>
>> Affiliates of AT&T, and is not for general distribution within or outside
>> the respective companies
>>
>>
>>
>> *From:* Osahon Oduware [mailto:oduwareosahon(at)gmail(dot)com]
>> *Sent:* Friday, March 17, 2017 8:22 AM
>> *To:* HEARNE, TIMOTHY S <th1618(at)att(dot)com>; pgadmin-support(at)postgresql(dot)org
>> *Subject:* Fwd: [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS
>> User Privileges/Permissions
>>
>>
>>
>> I would like to know why the privileges/permission is working when tested
>> with pgAdmin/pgsql, but it is not working with the same user/schema/table
>> in QGIS.
>>
>>
>>
>> ---------- Forwarded message ----------
>> From: *Osahon Oduware* <oduwareosahon(at)gmail(dot)com>
>> Date: Fri, Mar 17, 2017 at 4:17 PM
>> Subject: Re: [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS
>> User Privileges/Permissions
>> To: "HEARNE, TIMOTHY S" <th1618(at)att(dot)com>, pgadmin-support(at)postgresql(dot)org
>>
>> I can confirm that the user is not a superuser. This is the script
>> generated for the user:
>>
>> CREATE USER <username> WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB
>> NOCREATEROLE NOREPLICATION;
>>
>>
>>
>> GRANT <role_name> TO <username>;
>>
>> **Where <role_name> is as created in previous mail
>>
>>
>>
>>
>>
>> On Fri, Mar 17, 2017 at 4:08 PM, HEARNE, TIMOTHY S <th1618(at)att(dot)com>
>> wrote:
>>
>> If you created with superuser, it will have access to everything in the
>> instance by default. Your example below does not include the SUPERUSER key
>> word; however, as mine does below:
>>
>> create user operator with password 'xxxxxxx' *superuser* createdb
>> inherit login createrole;
>>
>>
>>
>> From the documentation (https://www.postgresql.org/do
>> cs/9.2/static/sql-createrole.html
>> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_9.2_static_sql-2Dcreaterole.html&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=n2Q7S8Qn9NmqgTstKR1X0A&m=nCEF4OUozaynmdnEsgNOZ5S3DG9ndXxyEmyuL78M90c&s=YYwNdtp-rn7Phou16wd5DvMhPjg6LybPxxMfISg0N18&e=>
>> ):
>>
>> SUPERUSER
>> NOSUPERUSER
>>
>> These clauses determine whether the new role is a "superuser", who can
>> override all access restrictions within the database. Superuser status is
>> dangerous and should be used only when really needed. You must yourself be
>> a superuser to create a new superuser. If not specified, NOSUPERUSER is
>> the default.
>>
>>
>>
>> If you have any questions, please feel free to contact me.
>>
>> *Tim Hearne*
>>
>> PROPRIETARY INFORMATION
>> The Information contained herein is for use only by authorized employees
>> of AT&T, and authorized
>>
>> Affiliates of AT&T, and is not for general distribution within or outside
>> the respective companies
>>
>>
>>
>> *From:* Osahon Oduware [mailto:oduwareosahon(at)gmail(dot)com]
>> *Sent:* Friday, March 17, 2017 8:01 AM
>> *To:* HEARNE, TIMOTHY S <th1618(at)att(dot)com>
>> *Subject:* Re: [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS
>> User Privileges/Permissions
>>
>>
>>
>> Hi,
>>
>>
>>
>> Thanks for your prompt response. The user was created with the postgres
>> user (superuser) as below:
>>
>>
>>
>> CREATE USER <username> WITH PASSWORD '<password>'
>>
>>
>>
>>
>>
>> On Fri, Mar 17, 2017 at 3:56 PM, HEARNE, TIMOTHY S <th1618(at)att(dot)com>
>> wrote:
>>
>> Did you create the user with SUPERUSER or some other elevated privilege?
>> Since you did not include the script for the user, it is hard to determine
>> the exact root cause.
>>
>> If you have any questions, please feel free to contact me.
>>
>> *Tim Hearne*
>>
>> PROPRIETARY INFORMATION
>> The Information contained herein is for use only by authorized employees
>> of AT&T, and authorized
>>
>> Affiliates of AT&T, and is not for general distribution within or outside
>> the respective companies
>>
>>
>>
>> *From:* pgadmin-support-owner(at)postgresql(dot)org [mailto:
>> pgadmin-support-owner(at)postgresql(dot)org] *On Behalf Of *Osahon Oduware
>> *Sent:* Friday, March 17, 2017 7:46 AM
>> *To:* pgadmin-support(at)postgresql(dot)org
>> *Subject:* [pgadmin-support] QGIS Seem To Bypass PostgreSQL/PostGIS User
>> Privileges/Permissions
>>
>>
>>
>> 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]
>>
>>
>>
>> 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.
>>
>>
>>
>>
>>
>>
>>
>
>

--
Richard W. Greenwood, PLS
www.greenwoodmap.com