Sample Header Ad - 728x90

Unable to change password in postgres even though I'm logged in as that user

0 votes
1 answer
1012 views
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 '' 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.
Asked by Jim (103 rep)
Oct 4, 2022, 03:17 PM
Last activity: Oct 4, 2022, 05:42 PM