Question 383485 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres password reset issue

I was trying to reset the password.

r9mzsy20=# ALTER USER 9mz_dba WITH PASSWORD '92837474';
ERROR:  syntax error at or near "9"
LINE 1: ALTER USER 9mz_dba WITH PASSWORD '92837474..

For user u_9mz_rw and u_9mz_ro I was able to reset the password.

ALTER USER u_9mz_ro PASSWORD '92837474' VALID UNTIL 'infinity';
ALTER USER u_9mz_rw PASSWORD '92837474' VALID UNTIL 'infinity';

But for this USER 9mz_dba I am getting an error.

Can anyone please help with that.

Here is a list of my users:

image of the result of du

Tags:

2

Answers


  1. 0

    Usernames which start with digits need to be quoted.

    ALTER USER "9mz_dba" WITH PASSWORD '1234687';
    Login or Signup to reply.
  2. 0

    Try to add double quotes " around the user name:

    ALTER USER "9mz_dba" WITH PASSWORD '92837474';

    From the 4.1.1. Identifiers and Key Words:

    SQL identifiers and key words must begin with a letter (a–z, but also letters with diacritical marks and non-Latin letters) or an underscore (_)
    …
    Quoted identifiers can contain any character, except the character with code zero.

    Which means it’s perfectly fine to have a user/role identifier that doesn’t start with a–z or underscore _, but to reference it, you need to wrap it in double-quotes.

    Also, you lost the 3rd line of that error message with the caret ^ character, which should clarify it’s pointing at the first 9 character, not the second one: demo

    ERROR:  syntax error at or near "9"
LINE 2: ALTER USER 9mz_dba WITH PASSWORD '92837474';
                   ^

    In PostgreSQL 15 and 16, the numeric literal processing was changed, which means the error message will change as well, trying and failing to interpret that as a number: demo

    ERROR:  trailing junk after numeric literal at or near "9m"
LINE 2: ALTER USER 9mz_dba WITH PASSWORD '92837474';
                   ^
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search