Amazon RDS - Postgresql database owner can't access database after DB engine upgrade

Question

After upgrading from postgresql 9.6 to 10.1-R1, the user do not have access to tables.

after investigation, I found the exact same issue on that post

When I look at my access privileges, it is empty:

\dn+
                        List of schemas
  Name  |  Owner   | Access privileges |      Description
--------+----------+-------------------+------------------------
 public | rdsadmin |                   | standard public schema

When it should be postgres=UC/postgres=UC/postgres

It was correctly set before migration

the recommendation was to run the command grant all on schema public to <my user>; but I don't have enough permission with RDS. Any recomendation to handle this with RDS?

Answer 1

I have the solution from the AWS forum.

First, run

ALTER SCHEMA PUBLIC OWNER TO <my rds_superuser>;

Then run the grant statement after: grant all on schema public to <my user>;