Automatic password hashing in PostgreSQL

Question

I have been using PostgreSQL for the past few weeks and I have been loving it!

I use crypt() and gen_salt() to generate the password hashes, by adding it to the insert query like so:

crypt(:password, gen_salt('bf', 8))

Likewise for the select I use something like:

crypt(:password, u.password)

I want to simplify my SQL code by automating the hash on the table's password column, instead of the SQL queries or additional functions.

To be more clear, when I insert a row in the table, I want it to convert hash/compare immediately.

Is there a way? And if yes, would that be wise?

So you have a table, and you store a password hash in it, but your question is very "cryptic" haha. Seriously, what do you mean by automatizing (automating)? Which SQL queries or additional functions? What do you want to achieve? — Ezequiel Tolnay
– Ezequiel Tolnay, Commented May 4, 2016 at 7:48
Yeah sorry, you might be right. So... I have a query which works fine. But I want to remove the crypt(:password, gen_salt('bf', 8)) from the query, ad somehow implement it on the table's column itself, so every time a value is added, it is immediately hashed and/or compared to the hash. — Loukas Avramidis
– Loukas Avramidis, Commented May 4, 2016 at 16:00

korry · Accepted Answer · 2016-05-04 16:54:16Z

I won't comment on the "would that be wise?" part of the question (not because I think it's unwise, but because I don't know enough about your needs).

If you want to automatically compute a column value during an INSERT or UPDATE, you need a trigger (see CREATE TRIGGER).

If you want to automatically compute a column value during a SELECT, you need a view (see CREATE VIEW).

There are other ways to achieve what you ask, but triggers and views are probably the most straightforward mechanisms.

Thanks for the tips man, I will check it out tomorrow and get back to you!

Collectives™ on Stack Overflow

Automatic password hashing in PostgreSQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related