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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
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/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general