Hi Richard, Thanks for your response. I have been able to solve the mystery. The problem was from QGIS as suspected. When you create the first user connection to the database (PostgreSQL/PostGIS in this case) and you add a new connection with a different user or edit the first connection to a different user without restarting the application, QGIS uses the privileges of the first user connection for the new user connection (the privileges can be viewed from the Database Manager menu). This is likely a *bug* in the QGIS software as *refreshing the edited connection or deleting the connection and creating a new one makes no difference*.
The only way I have found around this "bug" is to *restart the QGIS application before creating a new user connection or editing a connection to a different user*. On Sat, Mar 18, 2017 at 12:01 AM, Richard Greenwood < richard.greenw...@gmail.com> wrote: > 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 >
