Re: [GENERAL] permission denied for schema topology
Thanks Tom and everyone that replied. Since my last email my service provider managed to solve the problem on my main database. I looked at the schemas listed in phpPgAdmin on this database before it was fixed and there were two main schemas listed, public and topology, both owned by postgres. Now when I look, topology is no longer there. The problem was fixed on that database however not on others and when I create a new datbase via cpanel (the only way the system allows) - the problem reoccurs and I can't do a dump of the new db. Thanks again for your input - I think it will help me communicate with them to resolve the problem now fully. Cheers, Iain Em Qui, 2014-09-11 às 10:05 -0400, Tom Lane escreveu: Iain Mott m...@reverberant.com writes: Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump If it works through phpPgAdmin, then phpPgAdmin must be using some other (more privileged) user ID than what pg_dump is using by default. A simple workaround therefore ought to be to use pg_dump's -U switch to use that other user ID. From the rest of the thread I gather that you ought to complain to your service provider that they installed postgis when you didn't ask for it. But selecting the right user ID to dump as is important anyway; it generally doesn't work to use an underprivileged ID for pg_dump. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] permission denied for schema topology
On 09/12/2014 02:27 AM, Iain Mott wrote: Thanks Tom and everyone that replied. Since my last email my service provider managed to solve the problem on my main database. I looked at the schemas listed in phpPgAdmin on this database before it was fixed and there were two main schemas listed, public and topology, both owned by postgres. Now when I look, topology is no longer there. The problem was fixed on that database however not on others and when I create a new datbase via cpanel (the only way the system allows) - the problem reoccurs and I can't do a dump of the new db. Two things: 1) Looks like the template database they are using with CREATE DATABASE has the topology schema already in it, which is why it is showing up again. 2) What user are you running the pg_dump as? Thanks again for your input - I think it will help me communicate with them to resolve the problem now fully. Cheers, Iain -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] permission denied for schema topology
Hello, The server for my websites was recently changed and upgraded. I have ssh access to the server and since the upgrade I am no longer able to use pg_dump to perform scripted backups. I've written to the site's support services, but until now, they've not been able to help (they've responded saying they will likely need to refer the case on to a developer - I don't have any guarantees that it will be resolved). I've googled for this - but didn't come up with any relevant solutions. Can someone on this list please make suggestions that I can pass on to the technical support? Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql Senha: pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE --- I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump Thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] permission denied for schema topology
Hey, if you are using postgis and postgis_topology, there are specific backup/restore process. Cheers, Rémi-C 2014-09-11 12:49 GMT+02:00 Iain Mott m...@reverberant.com: Hello, The server for my websites was recently changed and upgraded. I have ssh access to the server and since the upgrade I am no longer able to use pg_dump to perform scripted backups. I've written to the site's support services, but until now, they've not been able to help (they've responded saying they will likely need to refer the case on to a developer - I don't have any guarantees that it will be resolved). I've googled for this - but didn't come up with any relevant solutions. Can someone on this list please make suggestions that I can pass on to the technical support? Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql Senha: pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE --- I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump Thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] permission denied for schema topology
Thanks Rémi-C for the quick reply. By coincidence my site does involve mapping, however I wasn't aware that the server might be using postgis and postgis_topology. In psql when I type \d i get the following: public | comentarios | tabela | myusername public | featuredata | tabela | myusername public | geography_columns | visão | myusername public | geometry_columns | visão | myusername public | raster_columns| visão | myusername public | raster_overviews | visão | myusername public | rss | tabela | myusername public | spatial_ref_sys | tabela | myusername geography_columns, geometry_columns, raster_columns, raster_overviews and spatial_ref_sys are all recent changes to my database (I didn't create them and I wasn't aware of their existence until the switch to the new server). Do these and the errors I'm receiving indicate the use of postgis and postgis_topology by the server? Just googled for doing a dump of a postgis database and encountered this command: pg_dump --no-acl --no-owner $DATABASE dump.sql I tried it, but it results in the same error messages Cheers, Em Qui, 2014-09-11 às 12:53 +0200, Rémi Cura escreveu: Hey, if you are using postgis and postgis_topology, there are specific backup/restore process. Cheers, Rémi-C 2014-09-11 12:49 GMT+02:00 Iain Mott m...@reverberant.com: Hello, The server for my websites was recently changed and upgraded. I have ssh access to the server and since the upgrade I am no longer able to use pg_dump to perform scripted backups. I've written to the site's support services, but until now, they've not been able to help (they've responded saying they will likely need to refer the case on to a developer - I don't have any guarantees that it will be resolved). I've googled for this - but didn't come up with any relevant solutions. Can someone on this list please make suggestions that I can pass on to the technical support? Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql Senha: pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE --- I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump Thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] permission denied for schema topology
Yep, this means you are using postgis. I don't know if you use postgis topology. If you have a schema topology containing a topology table, then you also have postgis_topology installed. You can check this with this query : SELECT postgis_full_version(); Maybe the extensions have been created with another user id than your's, hence the trouble. Another trouble could come from the fact that postgis and postgis_topology may have been installed without using postgres CREAtE EXTENSION (old version of postgis). Cheers, Rémi-C 2014-09-11 13:12 GMT+02:00 Iain Mott m...@reverberant.com: Thanks Rémi-C for the quick reply. By coincidence my site does involve mapping, however I wasn't aware that the server might be using postgis and postgis_topology. In psql when I type \d i get the following: public | comentarios | tabela | myusername public | featuredata | tabela | myusername public | geography_columns | visão | myusername public | geometry_columns | visão | myusername public | raster_columns| visão | myusername public | raster_overviews | visão | myusername public | rss | tabela | myusername public | spatial_ref_sys | tabela | myusername geography_columns, geometry_columns, raster_columns, raster_overviews and spatial_ref_sys are all recent changes to my database (I didn't create them and I wasn't aware of their existence until the switch to the new server). Do these and the errors I'm receiving indicate the use of postgis and postgis_topology by the server? Just googled for doing a dump of a postgis database and encountered this command: pg_dump --no-acl --no-owner $DATABASE dump.sql I tried it, but it results in the same error messages Cheers, Em Qui, 2014-09-11 às 12:53 +0200, Rémi Cura escreveu: Hey, if you are using postgis and postgis_topology, there are specific backup/restore process. Cheers, Rémi-C 2014-09-11 12:49 GMT+02:00 Iain Mott m...@reverberant.com: Hello, The server for my websites was recently changed and upgraded. I have ssh access to the server and since the upgrade I am no longer able to use pg_dump to perform scripted backups. I've written to the site's support services, but until now, they've not been able to help (they've responded saying they will likely need to refer the case on to a developer - I don't have any guarantees that it will be resolved). I've googled for this - but didn't come up with any relevant solutions. Can someone on this list please make suggestions that I can pass on to the technical support? Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql Senha: pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE --- I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump Thanks, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] permission denied for schema topology
Thanks a lot. Here is the response to = SELECT postgis_full_version(); postgi s_full_version - POSTGIS=2.0.1 r9979 GEOS=3.3.5-CAPI-1.7.5 PROJ=Rel. 4.8.0, 6 March 2012 G DAL=GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found LIBXML=2.7.6 TOPOLOG Y RASTER (1 registro) Em Qui, 2014-09-11 às 13:21 +0200, Rémi Cura escreveu: Yep, this means you are using postgis. I don't know if you use postgis topology. If you have a schema topology containing a topology table, then you also have postgis_topology installed. You can check this with this query : SELECT postgis_full_version(); Maybe the extensions have been created with another user id than your's, hence the trouble. Another trouble could come from the fact that postgis and postgis_topology may have been installed without using postgres CREAtE EXTENSION (old version of postgis). Cheers, Rémi-C 2014-09-11 13:12 GMT+02:00 Iain Mott m...@reverberant.com: Thanks Rémi-C for the quick reply. By coincidence my site does involve mapping, however I wasn't aware that the server might be using postgis and postgis_topology. In psql when I type \d i get the following: public | comentarios | tabela | myusername public | featuredata | tabela | myusername public | geography_columns | visão | myusername public | geometry_columns | visão | myusername public | raster_columns| visão | myusername public | raster_overviews | visão | myusername public | rss | tabela | myusername public | spatial_ref_sys | tabela | myusername geography_columns, geometry_columns, raster_columns, raster_overviews and spatial_ref_sys are all recent changes to my database (I didn't create them and I wasn't aware of their existence until the switch to the new server). Do these and the errors I'm receiving indicate the use of postgis and postgis_topology by the server? Just googled for doing a dump of a postgis database and encountered this command: pg_dump --no-acl --no-owner $DATABASE dump.sql I tried it, but it results in the same error messages Cheers, Em Qui, 2014-09-11 às 12:53 +0200, Rémi Cura escreveu: Hey, if you are using postgis and postgis_topology, there are specific backup/restore process. Cheers, Rémi-C 2014-09-11 12:49 GMT+02:00 Iain Mott m...@reverberant.com: Hello, The server for my websites was recently changed and upgraded. I have ssh access to the server and since the upgrade I am no longer able to use pg_dump to perform scripted backups. I've written to the site's support services, but until now, they've not been able to help (they've responded saying they will likely need to refer the case on to a developer - I don't have any guarantees that it will be resolved). I've googled for this - but didn't come up with any relevant solutions. Can someone on this list please make suggestions that I can pass on to the technical support? Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql Senha: pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE --- I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump Thanks,
Re: [GENERAL] permission denied for schema topology
On 09/11/2014 04:37 AM, Iain Mott wrote: Thanks a lot. Here is the response to = SELECT postgis_full_version(); postgi So what does pgAdmin show for ownership of the schemas? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] permission denied for schema topology
Iain Mott m...@reverberant.com writes: Here's what happens (the important error messages are in English): [~]# pg_dump mydatabase dump.sql pg_dump: comando SQL falhou pg_dump: Mensagem de erro do servidor: ERROR: permission denied for schema topology pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE I am able to perform dumps of the databases via phpPdAdmin in the cpanel of the server, but this is going very inconvenient - hoping to use pg_dump If it works through phpPgAdmin, then phpPgAdmin must be using some other (more privileged) user ID than what pg_dump is using by default. A simple workaround therefore ought to be to use pg_dump's -U switch to use that other user ID. From the rest of the thread I gather that you ought to complain to your service provider that they installed postgis when you didn't ask for it. But selecting the right user ID to dump as is important anyway; it generally doesn't work to use an underprivileged ID for pg_dump. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
