2

We have a PostgreSQL database with PostGIS, with the following versions:

 PostgreSQL 10.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

                                                                                                                                         postgis_full_version

 POSTGIS="2.4.5 r16765" PGSQL="100" GEOS="3.7.3-CAPI-1.11.3 b50468f" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.4, released 2017/06/23" LIBXML="2.9.1" LIBJSON="0.12.1" LIBPROTOBUF="1.3.0" (core procs from "2.4.4 r16526" need upgrade) RASTER (raster procs from "2.4.4
 r16526" need upgrade)

We are getting this error when trying to do a pg_dump:

pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema topology
pg_dump: [archiver (db)] query was: LOCK TABLE "topology"."topology" IN ACCESS SHARE MODE

We strongly believe that we have not upgraded PostGIS (we are going to though, to 2.5 when we upgrade to PostgreSQL 12), so we may have run one of the testing commands for the upgrade in the production database without realising it.

The things is, we need that access back. I have tried the following commands:

GRANT USAGE ON SCHEMA topology to <user>;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA topology TO <user>;
GRANT SELECT ON ALL TABLES IN SCHEMA topology TO <user>;

But our users do not have permission either! I have tried with the superuser provided by RDS and our own user, so not sure at all what is going on. Any idea?

Update 1

I have run the following query:

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
  pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;

And I have got:

    Name    |     Owner      |         Access privileges         |       Description
 topology   | rdsadmin       |                                   | PostGIS Topology schema

Update 2

I already tried to log in as postgres user, but it does not work either, same error.

Improve this question
3
  • According to PostgreSQL documentation, "if the 'Access privileges' column is empty for a given object, it means the object has default privileges ... Default privileges always include all privileges for the owner ..." What user are you running pg_dump with? From above, rdsadmin should have access. From update 2, is pg_dump failing when postgres user runs it? Do you need to upgrade PostGIS extension?
    – hgb
    Mar 22 at 21:54
  • I'm running the pg_dump with the same user we use on our app. I have tried with postgres and it does not work either. The problem with rdsadmin is that we cannot access that user (controlled by AWS). Not sure what we have run to get to this point. One week ago everything was fine. I think we don't need to run a postgis upgrade (I know we are using and old version, but that should not be the root of this problem)
    – Antonio Gamiz Delgado
    Mar 23 at 14:38
  • Your full version string says you have PostGIS 2.4.5 installed and shows the notes that core procs and raster procs 'from "2.4.4 r16526" need upgrade'. Doesn't that mean that your installed extension in the database and the software libraries installed don't match. That suggests the extensions need to be upgraded to match software updates that were done.
    – hgb
    Mar 24 at 5:44

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.