https://issues.qgis.org/https://issues.qgis.org/favicon.ico2012-02-15T03:48:04ZQGIS Issue TrackingQGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=291932012-02-15T03:48:04ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>I'm on master under Ubuntu 64 bit and it works fine here.</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=291962012-02-15T04:44:57ZEtienne Tourigny
<ul><li><strong>File</strong> <a href="/attachments/download/4260/0a17ad5-ok.png">0a17ad5-ok.png</a> added</li><li><strong>File</strong> <a href="/attachments/download/4261/d2775ba-notok.png">d2775ba-notok.png</a> added</li></ul><p>I have figured out the problem - my tables are in the "gis_schema", while the spatial tables are int the "public" schema. This works in 1.7.4 and 0a17ad5 master, but not in recent master. Adding 2 screenshots illustrating the problem.</p>
<p>I added a table to the "public" schema and now it is visible in the browser (but not the others). Both schemas are available in the "Add PostGIS Layers" dialog.</p>
<p>I have created the "gis_schema" because it is recommended, but not sure it it's really needed. Any way, this is unexpected behaviour.</p>
<p>Can you confirm this happens with you also? Thanks</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292302012-02-16T07:53:16ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Operating System</strong> deleted (<del><i>Linux</i></del>)</li><li><strong>OS version</strong> deleted (<del><i>Linux Mint 12</i></del>)</li></ul><p>Etienne Tourigny wrote:</p>
<blockquote>
<p>I have figured out the problem - my tables are in the "gis_schema", while the spatial tables are int the "public" schema. This works in 1.7.4 and 0a17ad5 master, but not in recent master. Adding 2 screenshots illustrating the problem.</p>
<p>I added a table to the "public" schema and now it is visible in the browser (but not the others). Both schemas are available in the "Add PostGIS Layers" dialog.</p>
<p>I have created the "gis_schema" because it is recommended, but not sure it it's really needed. Any way, this is unexpected behaviour.</p>
<p>Can you confirm this happens with you also? Thanks</p>
</blockquote>
<p>can you please change the title to reflect your new finding? thanks</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292312012-02-16T08:01:33ZEtienne Tourigny
<ul></ul><p>I would like to but it seems I don't have the permissions... I can only add notes.</p>
<p>Perhaps it also should fall into another category, but I can't change that either.</p>
<p>The title should read: postgis tables that are not in the "public" schema are not available in browser</p>
<p>Thanks</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292322012-02-16T08:02:11ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Subject</strong> changed from <i>postgis layers are not available in browser (master) - "Failed to retrieve layers"</i> to <i> postgis tables that are not in the "public" schema are not available in browser</i></li></ul> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292472012-02-18T06:54:08ZJürgen Fischerjef@norbit.de
<ul><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li></ul><p>Fixed in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/4c0dd4496d788ea43ca7f765f7ace5fc449074b2" title="fix #5022">4c0dd4496d788ea43ca7f765f7ace5fc449074b2</a>.</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292612012-02-18T10:38:03ZEtienne Tourigny
<ul></ul><p>I cannot access my postgis tables (even in Add PostGIS layers) after applying this fix, and both schemas show "Failed to retrieve layers" in the browser.</p>
<p>Just to make sure I have selected "only look for tables in the public schema" (I have a table there).</p>
<p>Here is debugging output after I click "connect":</p>
<pre>
Debug: src/providers/postgres/qgspostgresconn.cpp: 1344: (connUri) theConnName = test
Debug: src/providers/postgres/qgspgsourceselect.cpp: 431: (on_btnConnect_clicked) Connection info: dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 135: (connectDb) Using cached connection for dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 305: (getTableInfo) Entering.
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geometry_column,upper(type),srid,pg_class.relkind FROM geometry_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') AND f_table_schema='public' ORDER BY f_table_schema,f_table_name,f_geometry_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : public.brazil-main.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') AND f_table_schema='public' ORDER BY f_table_schema,f_table_name,f_geography_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 727: (PQexec) Not logged erroneous query: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') AND f_table_schema='public' ORDER BY f_table_schema,f_table_name,f_geography_column returned 7 [ERROR: permission denied for relation geography_columns
]
WARNING: there is no transaction in progress
Debug: src/core/qgsmessagelog.cpp: 41: (logMessage) 2012-02-18T16:32:41 PostGIS[0] Unable to get list of spatially enabled tables from the database
</pre>
<p>And here is the output without "only look for tables in the public schema":</p>
<pre>
Debug: src/providers/postgres/qgspostgresconn.cpp: 1344: (connUri) theConnName = test
Debug: src/providers/postgres/qgspgsourceselect.cpp: 431: (on_btnConnect_clicked) Connection info: dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 135: (connectDb) Using cached connection for dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 305: (getTableInfo) Entering.
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geometry_column,upper(type),srid,pg_class.relkind FROM geometry_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') ORDER BY f_table_schema,f_table_name,f_geometry_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.bioma_simple.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.boundaries_polygon.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.boundaries_polygon2.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.brazil_cont.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.grid-br-05.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.modis_sinusoidal_grid_world-wgs84.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : public.brazil-main.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') ORDER BY f_table_schema,f_table_name,f_geography_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 727: (PQexec) Not logged erroneous query: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') ORDER BY f_table_schema,f_table_name,f_geography_column returned 7 [ERROR: permission denied for relation geography_columns
]
WARNING: there is no transaction in progress
Debug: src/core/qgsmessagelog.cpp: 41: (logMessage) 2012-02-18T16:36:35 PostGIS[0] Unable to get list of spatially enabled tables from the database
</pre>
<p>Thanks!</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292632012-02-18T11:00:28ZJürgen Fischerjef@norbit.de
<ul></ul><p>Etienne Tourigny wrote:</p>
<blockquote>
<p>Here is debugging output after I click "connect":</p>
<p>[... ERROR: permission denied for relation geography_columns]</p>
</blockquote>
<p>Why don't you have permission to query geography_columns? Should work with <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/f432e83025f52e1562bc32e335a2eb56f75e39bd" title="postgres provider: ignore errors on querying geography_columns">f432e830</a>.</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292642012-02-18T12:05:21ZEtienne Tourigny
<ul></ul><p>some weird permission issues with geography_columns view - resolved that and now it works (before and after f432e830).</p>
<p>Sorry for the trouble, but it worked previously so I had no idea about the permissions problem.</p>
<p>Thanks</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292812012-02-19T10:56:47ZPaolo Cavallinicavallini@faunalia.it
<ul><li><strong>Resolution</strong> set to <i>invalid</i></li></ul> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292922012-02-20T04:39:28ZEtienne Tourigny
<ul></ul><p>Paolo - I am curious why you set this as invalid - there was a problem with tables not in the public schema, which is clearly not an "invalid" bug.</p>
<p>The problem with geography_columns was unrelated (I think).</p> QGIS Application - Bug report #5022: postgis tables that are not in the "public" schema are not available in browserhttps://issues.qgis.org/issues/5022?journal_id=292952012-02-20T07:00:22ZJürgen Fischerjef@norbit.de
<ul><li><strong>Resolution</strong> changed from <i>invalid</i> to <i>fixed</i></li></ul>