From: | Osahon Oduware <oduwareosahon(at)gmail(dot)com> |
---|---|
To: | Giuseppe Broccolo <giuseppe(dot)broccolo(at)2ndquadrant(dot)it> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions |
Date: | 2017-03-17 15:43:52 |
Message-ID: | CAFmwTohwivm_N0zQ10538pkDTKuagGoC-tDREhwicM5mCHQz4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Giuseppe,
Thanks for the response. I have provided the GRANTS and other PostgreSQL
setup scripts below as it includes what you have suggested:
ROLE
---------
CREATE ROLE <role_name> WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB
NOCREATEROLE NOREPLICATION;
USER
----------
CREATE USER <username> WITH PASSWORD '<password>'
REVOKES
----------------
REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC;
REVOKE ALL PRIVILEGES ON DATABASE postgres FROM PUBLIC;
GRANTS
-------------
GRANT CONNECT ON DATABASE <database_name> TO <role_name>;
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 <username>;
I cannot but wonder why these privileges are working when tested in
pgAdmin/pgsql, but not in QGIS with the same user/schema/table/database.
On Fri, Mar 17, 2017 at 4:16 PM, Giuseppe Broccolo <
giuseppe(dot)broccolo(at)2ndquadrant(dot)it> wrote:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Petr Jelinek | 2017-03-17 15:54:03 | Re: [COMMITTERS] pgsql: Use asynchronous connect API in libpqwalreceiver |
Previous Message | Tom Lane | 2017-03-17 15:42:10 | Re: \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless) |