[Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Oduware Godwin Osahon
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.



_______________________________________________
Qgis-community-team mailing list for organizing community resources such as documentation, translation etc..
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-community-team
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Yves Jacolin
Hello,

On vendredi 17 mars 2017 14:18:36 CET Oduware Godwin Osahon wrote:
> QGIS is bypassing the permissions set for the same user in the
> PostgreSQL/PostGIS database

This is not possible, imho. This is not QGIS that protects the edition process
into your table but PostGreSQL itself.

Did you check that the user is really correct?
Did you check that your credential are still valid when working in QGIS
(something like transaction or so)?

As far as I know this issue never occurs in my PostGIS training (I am using
QGIS as viewer for SQL results).

Y.
_______________________________________________
Qgis-community-team mailing list for organizing community resources such as documentation, translation etc..
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-community-team

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Oduware Godwin Osahon
Hi Yves,

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.



From: Yves Jacolin <[hidden email]>
To: [hidden email]; Oduware Godwin Osahon <[hidden email]>
Sent: Friday, March 17, 2017 4:59 PM
Subject: Re: [Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hello,

On vendredi 17 mars 2017 14:18:36 CET Oduware Godwin Osahon wrote:
> QGIS is bypassing the permissions set for the same user in the
> PostgreSQL/PostGIS database

This is not possible, imho. This is not QGIS that protects the edition process
into your table but PostGreSQL itself.

Did you check that the user is really correct?
Did you check that your credential are still valid when working in QGIS
(something like transaction or so)?

As far as I know this issue never occurs in my PostGIS training (I am using
QGIS as viewer for SQL results).


Y.



_______________________________________________
Qgis-community-team mailing list for organizing community resources such as documentation, translation etc..
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-community-team
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Qgis-community-team] [SOLVED] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Oduware Godwin Osahon
In reply to this post by Yves Jacolin
Hi Yves,

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.


From: Yves Jacolin <[hidden email]>
To: [hidden email]; Oduware Godwin Osahon <[hidden email]>
Sent: Friday, March 17, 2017 4:59 PM
Subject: Re: [Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hello,

On vendredi 17 mars 2017 14:18:36 CET Oduware Godwin Osahon wrote:
> QGIS is bypassing the permissions set for the same user in the
> PostgreSQL/PostGIS database

This is not possible, imho. This is not QGIS that protects the edition process
into your table but PostGreSQL itself.

Did you check that the user is really correct?
Did you check that your credential are still valid when working in QGIS
(something like transaction or so)?

As far as I know this issue never occurs in my PostGIS training (I am using
QGIS as viewer for SQL results).


Y.



_______________________________________________
Qgis-community-team mailing list for organizing community resources such as documentation, translation etc..
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-community-team
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Qgis-community-team] [SOLVED] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Oduware Godwin Osahon
In reply to this post by Oduware Godwin Osahon
Hi All,

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.


From: Oduware Godwin Osahon <[hidden email]>
To: "[hidden email]" <[hidden email]>
Sent: Friday, March 17, 2017 3:18 PM
Subject: [URGENT!] 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.





_______________________________________________
Qgis-community-team mailing list for organizing community resources such as documentation, translation etc..
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-community-team
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Qgis-community-team] Fw: [SOLVED] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Oduware Godwin Osahon
In reply to this post by Oduware Godwin Osahon

FYI



----- Forwarded Message -----
From: Oduware Godwin Osahon <[hidden email]>
To: Yves Jacolin <[hidden email]>; "[hidden email]" <[hidden email]>
Sent: Saturday, March 18, 2017 3:10 AM
Subject: Re: [SOLVED] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hi Yves,

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.


From: Yves Jacolin <[hidden email]>
To: [hidden email]; Oduware Godwin Osahon <[hidden email]>
Sent: Friday, March 17, 2017 4:59 PM
Subject: Re: [Qgis-community-team] [URGENT!] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions

Hello,

On vendredi 17 mars 2017 14:18:36 CET Oduware Godwin Osahon wrote:
> QGIS is bypassing the permissions set for the same user in the
> PostgreSQL/PostGIS database

This is not possible, imho. This is not QGIS that protects the edition process
into your table but PostGreSQL itself.

Did you check that the user is really correct?
Did you check that your credential are still valid when working in QGIS
(something like transaction or so)?

As far as I know this issue never occurs in my PostGIS training (I am using
QGIS as viewer for SQL results).


Y.





_______________________________________________
Qgis-community-team mailing list for organizing community resources such as documentation, translation etc..
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-community-team
Loading...