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/docs/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. > > > > > > >
