1

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.

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

Thank you

Edit: Here are my default access privileges

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

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                  | {}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.