sqlpage.hash_password

[Aesth]

I'm confused about how the sqlpage.hash_password function works. Each time it's called with the same password, it returns visually different strings, looking more like random strings. How then can the newly calculated value be compared with the previously obtained value and stored in the database if they are different?

[lovasoa]

short answer

sqlpage.hash_password is meant to be called only once at the creation of an user account. Passwords are salted with a random string. This makes weak passwords harder to crack even if an attacker gets access to the hash.

The authentication component is meant to check the password when the user logs in.

details

official documentation:

• authentication component to check passwords: https://sql-page.com/component?component=authentication
• cookie to store user sessions: https://sql-page.com/component.sql?component=cookie

and related examples:

• https://github.com/sqlpage/SQLPage/tree/main/examples/CRUD%20-%20Authentication
• https://github.com/sqlpage/SQLPage/tree/main/examples/user-authentication

on password hashing :

• explains why the password hashing algorithm used in sqlpage is recommended: https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html
• https://en.wikipedia.org/wiki/Argon2

[Aesth]

I assumed it was a regular hash function that returns the same value for the same string and started building checks based on that assumption, but upon testing, I discovered the hash was changing every time. I hadn't encountered salted hashes before, which are used to store passwords, but I didn't understand where the salt comes from and how I could now compare the stored hashes and the ones retrieved with a new login. From the examples, I understood that this somehow works with the authentication component, but I didn't understand how it does the comparison. I'll keep digging into this. Thank you so much for answering my amateurish questions.

[lovasoa]

The random salt is embedded in the string returned by hash_password. It's in the PHC format: https://github.com/P-H-C/phc-string-format/blob/master/phc-sf-spec.md

[lovasoa]

i updated the docs: 34f5d7f

[Aesth]

Thanks again. While I was digging around, I noticed that the returned string, in addition to the header, had two parts separated by a $ sign. I assumed that one part depended on the other, which would make everything fall into place. But I couldn't figure out how to use this, since there was no explicit comparison function. From the examples, I began to understand that this was related to the authentication component, meaning that hash comparison is part of its functionality.

Previously, I wrote a procedure in pgplsql that was passed a session key and a page path, and it calculated a value that, via the redirect component on the page, redirected to the appropriate page, if necessary. In my case, access to the same page depended on the user's group membership, and I was more accustomed to consolidating the entire analysis into a single server procedure.

With the authentication component, I'd have to split the process into two parts: first, check for permissions via the page. Then, using the server procedure, analyze whether the user has access rights to this page based on their group membership.

In general, I am now rethinking my approach in light of the new requirements.

[lovasoa]

Previously, I wrote a procedure in pgplsql that was passed a session key and a page path, and it calculated a value that, via the redirect component on the page, redirected to the appropriate page, if necessary.

That sounds good ! You should keep that. The session key should be an arbitrary random string, not a password hash. Password checking is expensive, you should do it once on login, then store the session key both in the database and in a cookie. You can see an example here: https://github.com/sqlpage/SQLPage/tree/main/examples/user-authentication

[lovasoa]

You could have a function that looks like

CREATE OR REPLACE FUNCTION redirect_if_unauthorized(
    p_page_path TEXT,
    p_session_key TEXT
)
RETURNS TABLE (component TEXT, link TEXT)
LANGUAGE sql
AS $$
    -- Return a row ONLY when unauthorized
    SELECT 'redirect' AS component,
           '/unauthorized' AS link
    WHERE NOT EXISTS (
        SELECT 1
        FROM user_sessions us
        JOIN page_access pa
          ON pa.user_id = us.user_id
        WHERE us.session_key = p_session_key
          AND pa.page_path = p_page_path
    );
$$;

and then just start your pages with

select * from redirect_if_unauthorized(sqlpage.path(), sqlpage.cookie('session'));

[Aesth]

That's exactly what I intended. The session key was, of course, the value from the sqlpage.random_string(length) function. The password hash from sqlpage.hash_password(password) was only used for storage in the database and comparison with the hash obtained from the password specified during subsequent logins. However, I didn't expect the hash string to be different for the same password and assumed a simple comparison, which I would perform in my procedure, would be sufficient.

In fact, to avoid changing the procedure, I could use a standard hash function, such as SHA-xxx, but that would naturally reduce the level of protection against password guessing. I'm not ready to write my own hash function with a random salt; I have more pressing tasks. Therefore, I'll try to adapt the authentication and access rights verification process to the existing mechanism in SQLPage.

Basically, I want to store as much as possible in the database, including pages and settings, because in the future I'd like to reduce the entire application to a translator that could build an interface on the fly based on metadata from the database. SQLPage is currently the closest analog of such an application I know of.

[Aesth]

Wow! The last example looks interesting, SQLPage is an amazing application.