0

Postgres running in RDS accessible only through an EKS cluster... thus I have a (generic) pod in our cluster which allows me to access our postgres instance. I need to alter the password of the role I'm currently using/logged in as (a root user). The problem is that when I try to alert the user or role postgres says the user/role doesn't exist even though I can clearly see it AND I'm logged in as the user! I'm logged into postgres as root (OS level) and I'm as the postgres root user (not postgres, but a super user which I'll call ABcDeFGh in this question)

postgres=> select user;
   user   
----------
 ABcDeFGh
(1 row)
postgres=> select rolname, rolsuper from pg_roles;
          rolname          | rolsuper 
---------------------------+----------
 pg_monitor                | f
 pg_read_all_settings      | f
 pg_read_all_stats         | f
 pg_stat_scan_tables       | f
 pg_read_server_files      | f
 pg_write_server_files     | f
 pg_execute_server_program | f
 pg_signal_backend         | f
 rds_superuser             | f
 rds_replication           | f
 rds_iam                   | f
 rds_password              | f
 rds_ad                    | f
 rdsadmin                  | t
 ABcDeFGh                  | f
 rdsrepladmin              | f
 rdstopmgr                 | f
(20 rows)

The user I'm logged in as is ABcDeFGh. Even specific queries (btw, I'm not a postgres guy so)

postgres=> \dg ABcDeFGh
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

postgres=> \dg abcdefgh
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

postgres=> \du abcdefgh
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

postgres=> \du ABcDeFGh
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

but this (list truncated):

postgres=> \du
                                                                 List of roles
      Role name      |                         Attributes                         |                          Member of                          
---------------------+------------------------------------------------------------+-------------------------------------------------------------
 ABcDeFGh            | Create role, Create DB                                    +| {rds_superuser}
                     | Password valid until infinity                              | 

and this (list truncated):

postgres=> \dg
                                                                 List of roles
      Role name      |                         Attributes                         |                          Member of                          
---------------------+------------------------------------------------------------+-------------------------------------------------------------
 ABcDeFGh            | Create role, Create DB                                    +| {rds_superuser}
                     | Password valid until infinity                              | 

(and now I just noticed that \du and \dg are the same thing or seem to be aliases of one another)

So this is very confusing and frustrating. As one can see, I'm using both all lower case and the case sensitive version of the name for alter and \dg. The error I get when trying to change the password is either role or user does not exist. It clearly seems to exist.

 alter role 'ABcDeFGh' with password '<REDACTED>' valid until '2023-01-02T00:13:34+00:00';                                                                                                                                                                                                    
ERROR:  syntax error at or near "'ABcDeFGh'"

All documentation I've been able to find fails to explain what might be going on here. The user clearly exists because I'm logged in as the user. I really hate wasting my time on this kind ambiguous garbage from software.

1
  • THIS IS NOT A DUPLICATE AND NONE OF THE SUGGESTIONS COME EVEN CLOSE TO WHAT THIS QUESTION WAS ABOUT!!! If you're going to moderate, please do so thoroughly. Please reopen this question or provide actual proof of duplicity!
    – Jim
    Commented Oct 4, 2022 at 17:45

1 Answer 1

2

Since you (or someone else) have managed to create a role with a case-sensitive name, you now have to quote it every time to prevent Postgres from converting it to lowercase:

\du "ABcDeFGh"

or

alter role "ABcDeFGh" ...
2
  • 1
    there is a name for this: S&M ;) Commented Oct 4, 2022 at 17:27
  • grrr -- this is angering but yes, now I see what I missed was the error with the alter role using single-tics. If I may vent for a second; that is asinine that I'm forced to use a specific kind of quote! In all the documentation I could find, there was no mention that the type of quote I used was mandatory. smh
    – Jim
    Commented Oct 4, 2022 at 17:37

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