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

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