I am using the pgcrypto extension in Postgresql to encrypt and salt the storage of user passwords in a database and I am having trouble retrieving them.
Specifically, the insert statement for a user looks like this:
INSERT INTO Users (username, password) VALUES ('test1', crypt('Password1234', gen_salt('bf', 8)));
This works fine however when I try to retrieve from the database, this query:
SELECT username, password FROM Users WHERE username = 'test1' AND password = crypt('Password1234', gen_salt('bf', 8));
returns no results, the query does not fail, just no results.
Currently, I am not using python to interact with the database (I will be in the future) I am just using psql in the terminal. I know something is wrong with this query but I am not sure what and how to fix it. Is there some other way that this should be structured or what am I doing wrong? Thanks for any help!
You are re-salting the password with a new salt, which of course leads to a different answer than before. That is the whole point of salting.
You need to reuse the first salt in the new hashing, which you do by feeding the password hash in place of gen_salt(...). (The stored hashed password has the salt embedded within it, and crypt knows how to extract and reuse it).
SELECT username, password FROM Users WHERE username = 'test1' AND password = crypt('Password1234', password);
But, why are you selecting "password"? Once you have verified that is hashes correctly, what further use is there in seeing it?