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)
postgres
?GRANT
errors when logged in aspostgres
?\dn+ topology
usingpsql
?