1

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?

Loukas Avramidis
  • 519
  • 3
  • 10
  • 24
  • 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 May 04 '16 at 07: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 May 04 '16 at 16:00

1 Answers1

1

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.

korry
  • 826
  • 6
  • 5