<html><head></head><body><div style="color:#000; background-color:#fff; font-family:Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px"><div id="yui_3_16_0_ym19_1_1491203919105_24843"><span>FYI</span></div><div class="qtdSeparateBR"><br><br></div><div class="yahoo_quoted" id="yui_3_16_0_ym19_1_1491203919105_24853" style="display: block;"> <div style="font-family: Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 13px;" id="yui_3_16_0_ym19_1_1491203919105_24852"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;" id="yui_3_16_0_ym19_1_1491203919105_24851"> <div dir="ltr" id="yui_3_16_0_ym19_1_1491203919105_24850"> <font size="2" face="Arial" id="yui_3_16_0_ym19_1_1491203919105_24854"> <br>----- Forwarded Message -----<br> <b><span style="font-weight:bold;">From:</span></b> Oduware Godwin Osahon <victoryaceta@yahoo.com><br> <b><span style="font-weight: bold;">To:</span></b> "pgsql-bugs@postgresql.org" <pgsql-bugs@postgresql.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Saturday, March 18, 2017 3:15 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> [SOLVED] QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions<br> </font> </div> <div class="y_msg_container" id="yui_3_16_0_ym19_1_1491203919105_24889"><br><div id="yiv8504628544"><div id="yui_3_16_0_ym19_1_1491203919105_24891"><div style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;" id="yui_3_16_0_ym19_1_1491203919105_24890"><div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29352">Hi All,</div><div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29350"><br clear="none"></div><div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29348">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 <b id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29334">bug</b> in the QGIS software as <b id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29335">refreshing the edited connection or deleting the connection and creating a new one makes no difference</b>. </div><br clear="none" id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29337">The only way I have found around this "bug" is to <b id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29338">restart the QGIS application before creating a new user connection or editing a connection to a different user</b>.<div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29347"><span></span><br clear="none"></div><div class="yiv8504628544qtdSeparateBR" id="yui_3_16_0_ym19_1_1491203919105_24917"><br clear="none"><br clear="none"></div><div class="yiv8504628544yqt8750642659" id="yiv8504628544yqt76756"><div class="yiv8504628544yahoo_quoted" id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29322" style="display:block;"> <div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29321" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;"> <div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29320" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div dir="ltr" id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29319"> <font id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29373" face="Arial" size="2"> </font><hr size="1"> <b><span style="font-weight:bold;">From:</span></b> Oduware Godwin Osahon <victoryaceta@yahoo.com><br clear="none"> <b><span style="font-weight:bold;">To:</span></b> "pgsql-bugs@postgresql.org" <pgsql-bugs@postgresql.org> <br clear="none"> <b><span style="font-weight:bold;">Sent:</span></b> Friday, March 17, 2017 3:34 PM<br clear="none"> <b><span style="font-weight:bold;">Subject:</span></b> QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions<br clear="none"> </div> <div class="yiv8504628544y_msg_container" id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29377"><br clear="none"><div id="yiv8504628544"><div class="yiv8504628544yqt0363709838" id="yiv8504628544yqtfd67289"><div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29376"><div id="yiv8504628544yui_3_16_0_ym19_1_1489802750574_29375" style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53610">Hi All,<br clear="none"></div><div class="yiv8504628544yahoo_quoted" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53601" style="display:block;"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53600" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53599" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"><div class="yiv8504628544y_msg_container" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53625"><div id="yiv8504628544"><div class="yiv8504628544yqt9334696176" id="yiv8504628544yqtfd31802"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53624"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53623" style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;"><div class="yiv8504628544qtdSeparateBR" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_53626"><br clear="none">I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY privilege on all tables in a schema as shown below:<br clear="none"></div><div class="yiv8504628544yahoo_quoted" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_6242" style="display:block;"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_6241" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_6240" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"><div class="yiv8504628544y_msg_container" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_6247"><div id="yiv8504628544"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_6246"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_6245" style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px;"><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3960"><br clear="none" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3961"></div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3962">GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]</div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3963">GRANT [role_name] TO [user_name]</div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3964"><br clear="none" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3965"></div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3966">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>.'</div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3967"><br clear="none" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3968"></div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3969">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.</div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3970"><br clear="none" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3971"></div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3972">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.</div><div id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3973"><br clear="none" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3974"></div><div dir="ltr" id="yiv8504628544yui_3_16_0_ym19_1_1489759182156_3975">I will be glad if someone can help me unravel this mystery.</div></div></div></div><br clear="none"></div></div></div></div></div></div></div></div></div> </div> </div> </div></div></div></div></div><br clear="none"><br clear="none"></div> </div> </div> </div></div></div></div></div><br><br></div> </div> </div> </div></div></body></html>