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

Question

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?

AWS-JohnH · 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

vimlech · Answer

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

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

Relevant content