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.

Answer 1

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.