CODEWITHSUNDEEP
postgresqlpassword-hashpgcrypto
Loukas Avramidis
Loukas Avramidis

Reputation: 537

Automatic password hashing in PostgreSQL

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?

Upvotes: 1

Views: 1726

Answers (1)

korry
korry

Reputation: 866

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.

Upvotes: 1

Related Questions