“Role does not exist” error when granting usage on public schema, but role does exit

Posted on

Question :

In Postgres, I’m trying to grant USAGE on the public schema to a specific role –

GRANT ALL ON SCHEMA public TO MyRole;

The problem is that this command generates an error –

ERROR: role “myrole” does not exist

However, the role does exist. Listing roles outputs the following table –

   Role name   |                         Attributes                         |    Member of
---------------+------------------------------------------------------------+-----------------
 rds_user      | Create role, Create DB                                    +| {rds_superuser}
               | Password valid until infinity                              |
 MyRole        |                                                            | {}
 rds_superuser | Cannot login                                               | {}
 rdsadmin      | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
               | Password valid until infinity                              |
 rdsrepladmin  | No inheritance, Cannot login, Replication                  | {}

I’m new to Postgres, so I may be missing some subtle command, but as far as I can tell I’ve done everything I need to. Can anyone suggest what the problem may be and how I should go about resolving it?

Answer :

Your problem is that the role you are after has capital letters in its name. Mixing cases is usually a bad idea in PostgreSQL, because you need to double-quote such names.

So your grant should look like

GRANT ALL ON SCHEMA public TO "MyRole";

I’d suggest renaming that role to “myrole”, to avoid such problems later.

Leave a Reply

Your email address will not be published. Required fields are marked *