CREATE USER produces 'NOTICE: empty string is not valid...'

Question

I'm following the PostgreSQL docs at https://www.postgresql.org/docs/11/sql-createuser.html to create a user. psql hates me.

# CREATE USER blah WITH ENCRYPTED PASSWORD 'md56f1ed002ab5595859014ebf0951522d9';
NOTICE:  empty string is not a valid password, clearing password
CREATE ROLE

I generated the MD5 like...

$ md5 -s 'blah'
MD5 ("blah") = 6f1ed002ab5595859014ebf0951522d9

How do I get the NOTICE: empty string is not a valid password, clearing password to go away and have postgres respect my desire to present it with an encrypted password?

If you are sure that is your password, you must have some extension or modification in your PostgreSQL that replaces it with an empty string. — Laurenz Albe, May 10 '19 at 19:08
`CREATE USER blah PASSWORD 'md56f1ed002ab5595859014ebf0951522d9'` — Ancoron, May 10 '19 at 19:19
@Ancoron. No joy with the `CREATE USER blah PASSWORD 'md56f1ed002ab5595859014ebf0951522d9'` variant. exact same error. THis is a stock brew installed postgres. psql says `psql (11.2)` — Bob Kuhar, May 10 '19 at 20:18
Wow. It might be objecting to my user name and password being the same?!?! `CREATE USER blah PASSWORD 'md54adde5216371c71cc717e56c347e130d';` succeeds. — Bob Kuhar, May 10 '19 at 20:22
Ah yes, they did that for MD5 because otherwise it would be way too easy to break in... — Ancoron, May 10 '19 at 20:29
Just found how to do the MD5 hashing properly (which then also works): https://www.postgresql.org/docs/current/catalog-pg-authid.html So that's the actual reason the simple MD5 hash of the clear text username value will never work (and why it correctly states that the password is "empty"). — Ancoron, May 10 '19 at 20:38
@Ancoron I had completely forgotten about the "combine the password and role name into a single string" part. That is probably what was killing me all along. My MD5 was on just the password, it needs to also include the rolename. The postgres "optimization" that CREATE USER is actually CREATE ROLE further clouded the issue. — Bob Kuhar, May 10 '19 at 21:02

CREATE USER produces 'NOTICE: empty string is not valid...'

0 Answers0