Database password verification with stored procedures

[Varbin]

To not hand out the whole database passwords, it might be possible to verify the password completely in the database.

Some notes:

• This is specific to the database.
• If query logging is enabled, the plain text password might be written to the logs.
• Encrypted connections to the databases must be implemented, as the plain passwords are sent over from the applications.

Example (adapted from the Dovecot example):

SELECT email as user, NULL AS password, 'Y' as nopassword,
FROM users 
WHERE email = '%n' AND valid_password_by_user('%u', '%w')

This may limit password hashing algorithms or make it entirely impossible.

Documentation:

• Postgres:
  • CREATE PROCEDURE: https://www.postgresql.org/docs/current/sql-createprocedure.html
  • Supported cryptographic functions by pgcrypto: MD5, SHA-1, SHA-2, HMAC, md5crypt, bcrypt
  • Random functions: bytes, uuid
• MariaDB:
  • Stored procedures: https://mariadb.com/kb/en/stored-procedures/
  • Supported cryptographic functions: MD5, SHA-1 and SHA-2 and PBKDF2. There is also Unix' crypt which shall be tested if it supports the whole range of password hashes (e.g. SHA512-crypt)
  • Random function for masking: RANDOM_BYTES
• (Oracle) MySQL:
  • Supported cryptographic functions: PASSWORD (= SHA-1) and MD5, SHA-1, SHA-2. Alternatively, a key for AES can be derived with PBKDF2 since 8.0.30 to use in a single encryption algorithm.
  • Random function for masking: RANDOM_BYTES()
• (Microsoft) SQL Server:
  • Ignoring the internal pwdencrypt/pwdcompare functions, it supports regular hashes with HASHBYTES
  • Random function: NEWID

Implementation:

• Password hash comparison should be timing safe to not leak the hash value to attackers (“timing attack”). Alternatively, the values should be blinded beforehand, e.g. hash(store_hash || blind) == hash(computed_hash || blind)

---

Specification:

The "public" functions should have the following signatures:

valid_password(login TEXT=NULL, username TEXT=NULL, email TEXT=NULL, password TEXT) => (
  username TEXT,
  email TEXT,
)

The return type is a table (e.g. SELECT username as user FROM valid_password(?, NULL, NULL, ?) will work).

The functions should be executed as a dedicated principal that is allowed to read from the users and tokens table, and write to the audit table. Applications are then granted execution permission (or similar, depending on the database).
For each database,