Postgres Migration 10.6 -> 11.1: "permission denied for schema public"

0

We have increased our storage from 600GB to 1 TB and migrated from Postgres 10.6 to 11.1.
After the migration all the Postgres roles including the master role cannot access objects in the public schema any more.
E.g.:

select * from public.spatial_ref_sys;

results in 42501 ERROR: permission denied for schema public

This query executed as master shows false for create and usage permissions:

WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname = 'public'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
    FROM "names";

I have tried

GRANT ALL ON DATABASE dwh TO master;

which is executing without errors. But

GRANT ALL ON SCHEMA public TO master;

would result in permission denied error.

Does anyone have an idea, how this can be resolved?

asked 6 years ago3.7K views
2 Answers
0
Accepted Answer

Hello,

Can you try running the following first as a role that has rds_superuser:

ALTER SCHEMA public OWNER to master;

And if that resolves your issue?

Thanks!
John H

Edited by: awsatjohnh on Apr 12, 2019 6:15 PM

AWS
answered 6 years ago
profile picture
EXPERT
reviewed 7 months ago
0

I could resolve it.

I had to run as master:

ALTER SCHEMA public OWNER TO master;
GRANT ALL ON SCHEMA public TO master;

Now I can access the objects in public again. However, it is strange why it happened that no role could access the public object anymore. Did the migration procedure change the owner and revoke the grants?

Edited by: vimlech on Apr 15, 2019 1:57 AM

answered 6 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions