Test authenticate postgres user without login

Question

I created a role with login permission, but I can't login with it, I get:

FATAL:  password authentication failed for user "myuser"

I have confirmed by select * from pg_roles that the role exists and has login permission.

I suspect an error in my db set up script caused it to be created with a different password to what I believed it was set to.

I realise that you cannot lookup the existing password of a role but I figure there ought to be a function which can be used to tell if a plaintext matches password for a role?

eg in a web app when we store passwords in the db they are salted and hashed... you can't tell what is the password from looking at that, but you can take a new plaintext and run the password hashing on it and say if the hash of your plaintext matches the stored password.

Is there something like that for psql?

I found other SO questions, eg Authenticate PostgreSQL user and password without database, about how to check password but they suggest trying to login with that role from commandline, which I can't do ("password authentication failed").

In my case I realise I should just trust the error message and assume role was created with wrong password... but let's imagine I'd created a role without LOGIN permission - this way of checking wouldn't be possible.

There are other ways to debug my script of course, but I am curious if such a 'password check' function exists.

For example, I found these in the docs:
http://www.postgresql.org/docs/8.3/static/pgcrypto.html
...but they are provided as helpers for building applications on top of Postgres - it is not clear if they are used by Postgres iteself for role passwords.

You say you cannot test on commandline, so I presume you are using some web interface to connect to PostgreSQL to test this? If so, it might be that your access rules for host in your pg_hba.conf are letting you login via peer (Unix user credentials) instead of via md5 (The actual database credentials). Do you have any means to access the pg_hba.conf file? — Timusan
– Timusan, Commented Dec 22, 2014 at 0:05
I can login with superuser on the command-line, but not with the role I created for my web app... I have since confirmed (by dropping and recreating role) it was a problem with password set to wrong value ...but I'd still like to know if there is a suitable function for checking psql passwords — Anentropic
– Anentropic, Commented Dec 22, 2014 at 0:59
to whoever left the 'close' vote... please read the question more carefully - I am not asking for vague help with my login problem, I'm asking for specific solution for checking a plaintext against an existing role password in psql console (or via SQL) — Anentropic
– Anentropic, Commented Dec 22, 2014 at 1:07
Well, PostgreSQL user passwords are stored in md5 and (if I remember correctly) use the username as a salt. I don't think there is a function directly, but knowing the algorithm and the salting, you should be able to check if a given plain text password is the same as what is stored using (as you already found) the md5 hashing function of the pg_crypto package. — Timusan
– Timusan, Commented Dec 22, 2014 at 1:11
I guess what you want is: SELECT rolpassword FROM pg_authid WHERE rolname='myrole';. From the docs. — Timusan
– Timusan, Commented Dec 22, 2014 at 1:21

Timusan · Accepted Answer · 2014-12-22 04:47:18Z

9

As posted in the comments, this answer is to compare a given plain text password with a stored md5 hashed PostgreSQL role password. I consider this somewhat of a hack, but let's do it anyway.

The following can be done with the "plain" md5() function in core PostgreSQL.

PostgreSQL's role passwords are hashed with md5 and salted with the role name. As an added bonus, they are prefixed with 'md5'. So a query to match a password to a role would be

select * from pg_authid WHERE rolpassword = 'md5' || md5('the-plain-text-password' || 'the-role-name');

The important bit (of course) is:

'md5' || md5('the-plain-text-password' || 'the-role-name');

Where we concatenate a string "md5" with an md5 hash of the plain text password and the role name as the salt.

edited Dec 22, 2014 at 4:47

answered Dec 22, 2014 at 1:51

Timusan

3,4551 gold badge24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anentropic Over a year ago

great, this works! I didn't even have to install pgcrypto... the builtin md5 function works fine with exact syntax above postgresql.org/docs/9.1/static/functions-string.html

Timusan Over a year ago

Good to hear. Ah, I may have been to hasty to recommend PGCrypto then, I will test and alter my answer accordingly. Thanks for the heads up!

Anentropic Over a year ago

it's worth noting also that pg_authid docs helpfully describe exactly the method used to hash the passwords

Timusan Over a year ago

Correct. I have adapted my answer, which makes it simpler. You indeed don't need PGCrypto at all.

Eloff Over a year ago

Note that only a superuser can access that table, on RDS you have an RDS_SUPERUSER which is not the same thing. So you're out of luck there without actually attempting the login.

Collectives™ on Stack Overflow

Test authenticate postgres user without login

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related