Deprecated Encryption Functions

The ENCRYPT and DECRYPT functions are deprecated. Yellowbrick recommends the use of ENCRYPT_KS and DECRYPT_KS; see Encrypting Sensitive Data.

Note: The deprecated encryption functions are not backed by a keystore on the manager node. You must take steps to secure the raw values that are used for keys and vectors. When the ENCRYPT and DECRYPT functions are executed, the syntax you use is logged to ~/ybd/postgresql/build/db/log/pg.log. Key values may also be compromised when ODBC or JDBC applications are used to pass these functions to the database or when log files are sent back to Yellowbrick for investigation by Customer Support.
Here is a simple example of the ENCRYPT function. The nickname column in the team table is encrypted in the results of this query. The MD5 function is used to hash the key value as a hexadecimal string:
premdb=# select teamid, name, 
encrypt(nickname,md5('We are such stuff as dreams are made on')), 
city, stadium, capacity 
from team 
where city in('Birmingham','London') 
order by city;
 teamid |         name         |    encrypt     |    city    |     stadium      | capacity 
      2 | Aston Villa          | RuCsrql{7K2    | Birmingham | Villa Park       |    42785
      4 | Birmingham City      | FiSmyyE        | Birmingham | St. Andrew's     |    30016
     43 | West Bromwich Albion | FOyq{KlzQ0     | Birmingham | The Hawthorns    |    27000
      1 | Arsenal              | A8jstaVuQ0     | London     | Emirates Stadium |    60260
     12 | Charlton Athletic    | CCCqmyF{Q0     | London     | The Valley       |    27111
     13 | Chelsea              | T8isgKF|7CpX21 | London     | Stamford Bridge  |    41663
     15 | Crystal Palace       | 8OyqraFu       | London     | Selhurst Park    |    26255
     18 | Fulham               | EWCmjqFzCGYX   | London     | Craven Cottage   |    25700
     32 | Queens Park Rangers  | 5WysfyE        | London     | Loftus Road      |    18439
     41 | Tottenham Hotspur    | USTmhyE        | London     | White Hart Lane  |    36284
     44 | West Ham United      | 5OSsqaVuQ0     | London     | Upton Park       |    35016
     46 | Wimbledon            | 9Wisg0         | London     | Selhurst Park    |    26255
(12 rows)
Only two parameters are supplied for the function in this example: the input column name (nickname) and the key, which must be a hexadecimal string (in this case, a user-defined character string with MD5 applied to it). The default algorithm is used, and no initialization vector is specified. Every time this query is run with these exact parameters, the nickname values are encrypted with the same results. If these query results were stored in a table, the encrypted nickname values could be returned by using the DECRYPT function with the same parameters. For example:
select decrypt(encrypted_nickname_column,md5('We are such stuff as dreams are made on')) as nickname
from encrypted_team_table
Note: This example presents the actual key value in clear text for demonstration purposes. In a production system, you would need to "hide" the key itself. For example, you could select a key value that is stored in another table, where the stored value is already a hash of the actual key, not the original character string. For example, you might have a table called userkeys, which stores database usernames and keys (hexadecimal strings):
premdb=> select * from userkeys;
 whoami |               key                
 bobr   | 4765123b652644f84007485d110fa29f
(1 row)
premdb=> select encrypt(nickname,(select key from userkeys where whoami=current_user)) from team order by teamid;