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 <oduwareosa...@gmail.com> wrote: > Hi Tim, > > Ok, thanks for your help. > > On Fri, Mar 17, 2017 at 4:32 PM, HEARNE, TIMOTHY S <th1...@att.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:oduwareosa...@gmail.com] >> *Sent:* Friday, March 17, 2017 8:22 AM >> *To:* HEARNE, TIMOTHY S <th1...@att.com>; pgadmin-support@postgresql.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* <oduwareosa...@gmail.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" <th1...@att.com>, pgadmin-support@postgresql.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 <th1...@att.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:oduwareosa...@gmail.com] >> *Sent:* Friday, March 17, 2017 8:01 AM >> *To:* HEARNE, TIMOTHY S <th1...@att.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 <th1...@att.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-ow...@postgresql.org [mailto: >> pgadmin-support-ow...@postgresql.org] *On Behalf Of *Osahon Oduware >> *Sent:* Friday, March 17, 2017 7:46 AM >> *To:* pgadmin-support@postgresql.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
