Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

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_keystore(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;
      encrypt       
---------------------
 CyftgWky|C2f10
 V8zzguku5qYmwD8
 T8jpjKkwi0
 BuyvqKkwi0
 R0VtqKkviC2
...

Parent topic:Encrypting Sensitive Data