Securing Account PostgreSQL

using pgcrypto

The pgcrypto module provides cryptographic functions for PostgreSQL.[2]

1. enter postgreSQL via terminal
   

   # psql 

2. enter the database
   

   # \c database_name;

3. create extension[1]
   

   # CREATE EXTENSION pgcrypto; 

4. save the crypted password
   

   # INSERT INTO table_name(password_column, ...) VALUES(crypt('new password', gen_salt('md5')), ...)

   for new entries or for updating

   # UPDATE ... SET password_column = crypt('new password', gen_salt('md5')); 

   * md5 can be substitute by other algorithm as state in reference[2]
5. example to match encrypted password
   

   # SELECT (password_column = crypt('entered password', password_column)) AS pswmatch FROM ... ;

   * return pswmatch as boolean

 

reference:

[1] http://www.postgresql.org/docs/current/static/contrib.html
[2] http://www.postgresql.org/docs/9.5/static/pgcrypto.html