AWS RDS (PostgreSQL) - Master user lost all privileges after upgrading from v9.6 to v10.6-R1

Question

I just upgraded my AWS RDS DB instance from Postgresql v9.6 to v10.6-R1. I did this manually using the "Modify" tab in the Databases section under the Dashboard. Immediately afterwards, as the master user, I lost the ability to perform any operations in the public schema.

I can connect to the DB without issue, but am seeing this error when I try running a select statement on tables in the public schema: ERROR: permission denied for schema public

It appears that my rds_superuser user can't log in anymore (freetiersuperuser is the name of my master user, although its name is misleading):

master_prod=> \du+
                                                                       List of roles
     Role name     |                         Attributes                         |                          Member of                          | Description
-------------------+------------------------------------------------------------+-------------------------------------------------------------+-------------
 freetiersuperuser | Create role, Create DB                                    +| {rds_superuser}                                             |
                   | Password valid until infinity                              |                                                             |
 rds_iam           | Cannot login                                               | {}                                                          |
 rds_password      | Cannot login                                               | {}                                                          |
 rds_replication   | Cannot login                                               | {}                                                          |
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password} |
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}                                                          |
                   | Password valid until infinity                              |                                                             |
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}                                                          |

Additionally, running ANALYZE VERBOSE; works, and I can statistic information for my public tables.

If it helps, here are my default access privileges

master_prod=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

Any ideas as to what went wrong or what I can do to fix this? I appreciate all suggestions.

Thank you

Answer 1

So running these two statements resolved my issue (being unable to access anything in the public schema):

master_prod=> ALTER SCHEMA public OWNER to freetiersuperuser;
ALTER SCHEMA
master_prod=> GRANT ALL ON SCHEMA public TO freetiersuperuser;
GRANT

However my rds_superuser role's attributes haven't changed, and I would definitely like to understand if this is normal (I don't think it is):

     Role name     |                         Attributes                         |                          Member of
-------------------+------------------------------------------------------------+-------------------------------------------------------------
 freetiersuperuser | Create role, Create DB                                    +| {rds_superuser}
                   | Password valid until infinity                              |
 rds_iam           | Cannot login                                               | {}
 rds_password      | Cannot login                                               | {}
 rds_replication   | Cannot login                                               | {}
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until infinity                              |
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}