0

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)

An 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 sentences:

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.

Update 3

<dbname> => \dn+ topology
                          List of schemas
   Name   |  Owner   | Access privileges |       Description
----------+----------+-------------------+-------------------------
 topology | rdsadmin |                   | PostGIS Topology schema
(1 row)


6
  • Have you tried going in as user postgres?
    – RonJohn
    Commented Mar 21, 2023 at 16:38
  • Yep, same result. Commented Mar 21, 2023 at 16:44
  • You got the GRANT errors when logged in as postgres?
    – RonJohn
    Commented Mar 21, 2023 at 16:55
  • Yep, permission denied. Commented Mar 21, 2023 at 17:03
  • That's because you are using a hosted database. What do you get if you run \dn+ topology using psql? Commented Mar 22, 2023 at 7:44

0

Your Answer

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

Browse other questions tagged or ask your own question.