Re: Suboptimal query plan when using expensive BCRYPT functions

On Sat, Mar 22, 2014 at 11:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Maybe I'm missing something ... but isn't the OP's query completely bogus?
>
> SELECT DISTINCT u.*
> FROM auth_user u
> JOIN bb_userprofile p ON p.user_id = u.id
> JOIN bb_identity i ON i.profile_id = p.id
> WHERE
> (
> (
> u.username ILIKE 'detkin'
> OR
> i.email ILIKE 'foo(at)example(dot)com'
> )
> AND
> (
> SUBSTRING(password FROM 8) = CRYPT(
> 'detkin', SUBSTRING(password FROM 8))
> )
> )
>
> Granting that there are not chance collisions of password hashes (which
> would surely be a bad thing if there were),

Would it?

Any hashing system is inherently open to collision (although you're
more likely to find 2 identical snowflakes), but how does that affect
our situation? It means you simply would have found another password
for that user that is just as valid. The system will accept it.

> success of the second AND arm
> means that we are on user detkin's row of auth_user.

My password could be 'detkin' too, but my username is 'erik'.

> Therefore the OR
> business is entirely nonfunctional: if the password test passes, then
> the u.username ILIKE 'detkin' clause succeeds a fortiori, while if the
> password test fails, it hardly matters what i.email is, because the WHERE
> clause as a whole fails.

My email could be 'foo(at)example(dot)com', my username 'erik' and my
password 'detkin'.

Users are identified through their unique username or email address.
Passwords are not unique.

> I suppose we've been shown a lobotomized version of the real logic,
> but it's hard to give advice in such situations.

This is an actual query taken from the system.

Cheers,
Erik