Обсуждение: 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,

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 <mott@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
>
>

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 <mott@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 <mott@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
>         >
>         >
>
>
>
>
>

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.klaver@aklaver.com

Iain Mott <mott@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

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 <mott@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
>
>

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.klaver@aklaver.com