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

Hi Osahon,

2017-03-17 15:54 GMT+01:00 Osahon Oduware <oduwareosahon(at)gmail(dot)com>:

> 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]
>

I'd have done this as followed:

REVOKE ALL ON SCHEMA [schema_name] FROM PUBLIC;
GRANT USAGE ON SCHEMA [schema_name] TO [role_name];
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.
>

Check which user is used the first time you connect to the database through
QGIS, and if you switch the user to [user_name] in a second moment. I'm
wondering if you are keeping some privileges from a previous session.

All the best,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it