bcrypt in the auth query

infra wayback

2 min A pam_pgsql path could not verify Django’s bc$ bcrypt value directly, so SQL performs the check and returns the submitted password as its own answer.

Published

Sep 6, 2012

I wanted Django’s web-app users to be SFTP users too, with no synchronization job copying accounts between two systems—one password store, checked live. The pam_pgsql path I was using could not verify Django’s bc$-prefixed bcrypt representation directly.

The inversion: tell the module the passwords are cleartext, and move the entire bcrypt comparison into the SQL query it runs. Set pw_type = clear, then write an auth_query that does the crypto itself and hands the module a comparison it cannot fail:

SELECT CAST(%p AS varchar) FROM auth_user au ...
WHERE ...
  AND substring(au.password FROM 4) = crypt(%p, substring(au.password FROM 4))
  ...
LIMIT 1

%p is the submitted password. substring(au.password from 4) strips Django’s bc$ prefix to get the raw bcrypt hash. crypt(%p, <hash>)—pgcrypto—re-hashes the submission using the stored hash as its own salt, and if they match, the row is real. So the WHERE clause performs the actual bcrypt verification, in the database.

The returned column is the trick’s other half: CAST(%p AS varchar) selects the user’s own submitted password straight back out. pam_pgsql then performs the clear-mode equality it was configured to do: it compares the submission against itself. It passes by construction. The module has been reduced to an equality oracle; the database did all the cryptography and already decided the answer.

Two more things ride along the same path. The permission check—the account must hold use_api through a group join—is just more AND clauses in that WHERE, so authorization and authentication resolve in one query. And password changes re-enter Django’s exact format so the web app never notices:

concat('bc$', crypt(%p, gen_salt('bf', 12)))

gen_salt('bf', 12) mints a fresh bcrypt salt at cost factor 12; the result is prefixed back to bc$… and written to auth_user.password.

The connection ran sslmode=disable, justified in the config comment as a localhost-only NSS-deadlock workaround, not a general stance. It is tied to pgcrypto and Django’s specific hash format, and it is firmly of its era. The gist has the full ProFTPd and PAM configuration.