SQL Encryption Examples

This section presents some examples that show how to use the ENCRYPT_KS and DECRYPT_KS functions, how to assign encryption privileges, and how to rotate keys.

Simple example of encryption and decryption of a single column within one query

The following subquery encrypts the city column, then the outer query decrypts it. The query verifies that the decrypted value matches the original value. Because multiple city values are the same, the encrypted column in the result proves that the same string is encrypted the same way. Note that both functions use all four arguments in this example.

premdb=# select city, encrypted, 
decrypt_ks(encrypted,yb100key,1,'abcdef0') as decrypted
 from(select city, encrypt_ks(city,yb100key,1,'abcdef0') as encrypted from team) t1 
order by 1;
     city      |     encrypted      |   decrypted   
---------------+--------------------+---------------
 Barnsley      | mp7{1N7Jx6D        | Barnsley
 Birmingham    | mJ7{2|cJv2Clz2     | Birmingham
 Birmingham    | mJ7{2|cJv2Clz2     | Birmingham
 Birmingham    | mJ7{2|cJv2Clz2     | Birmingham
 Blackburn     | m7twCtcGhgzi       | Blackburn
 Blackpool     | m7twCt6KnUSi       | Blackpool
 Bolton        | mBdvRdcJ           | Bolton
 Bournemouth   | mBt|TZMHpUCgacB    | Bournemouth
 Bradford      | m|swB3sJioC        | Bradford
 Burnley       | mZ6{1hMHd2         | Burnley
 Cardiff       | np7{B|cHu2         | Cardiff
 Coventry      | nB7|AZ6Li6D        | Coventry
 Derby         | sZ7{D|7            | Derby
 Hull          | wZcv33             | Hull
 Ipswich       | xtM{O|sGs2         | Ipswich
 Leeds         | {ZtxBN7            | Leeds
 Leicester     | {ZtuCFsKgsyh       | Leicester
 Liverpool     | {J7|AJ7KnUSi       | Liverpool
 Liverpool     | {J7|AJ7KnUSi       | Liverpool
 London        | {B7vBdcJ           | London
 London        | {B7vBdcJ           | London
 London        | {B7vBdcJ           | London
 ...

CREATE VIEW example with an encrypted expression

As a user with ENCRYPT and DECRYPT privilege, create a view that contains an encrypted expression based on the concatenation of three columns in a table.

premdb=# create view encrypted_team as select 
encrypt_ks(concat_ws(':',teamid,htid,atid),yb100key) as ids, 
name, nickname, city, stadium, capacity, avg_att 
from team;

As the same user, check the decrypted values:

premdb=# select decrypt_ks(ids,yb100key) from encrypted_team limit 5;                                                                         decrypt_keystore 
------------------
 1:2:51
 2:3:52
 3:4:53
 4:5:54
 5:6:55
(5 rows)

Create a new user with SELECT privilege on the table, but not do not grant DECRYPT privilege to this user. Query the view as that user:

premdb=# create user yb007 password '********';
CREATE ROLE
premdb=# grant select on encrypted_team to yb007;
GRANT
premdb=# \c premdb yb007
Password for user yb007: 
You are now connected to database "premdb" as user "yb007".
premdb=> select * from encrypted_team order by 1 limit 5;
     ids     |         name         | nickname  |    city     |     stadium      | capacity | avg_att 
-------------+----------------------+-----------+-------------+------------------+----------+---------
 cdofaC71    | Bournemouth          | Cherries  | Bournemouth | Vitality Stadium |    11464 |  11.189
 ddohkmN2c3  | Bradford City        | Bantams   | Bradford    | Valley Parade    |    25136 |   0.000
 edIgaCd2    | Blackpool            | Seasiders | Blackpool   | Bloomfield Road  |    17338 |   0.000
 fdYfaCt2    | Bolton Wanderers     | Trotters  | Bolton      | Macron Stadium   |    28723 |   0.000
 g32fg8d1cp3 | West Bromwich Albion | Baggies   | Birmingham  | The Hawthorns    |    27000 |  24.631
(5 rows)

Verify that this user cannot decrypt the ids column:

premdb=# \c premdb yb007
Password for user yb007: 
You are now connected to database "premdb" as user "yb007".
premdb=> select decrypt_ks(ids,yb100key) from encrypted_team limit 5;
ERROR:  decrypt_ks: insufficient privileges on key yb100key

Unloading encrypted data

Using ybunload, you can unload data with a query that contains the ENCRYPT_KS function. (This example uses the same concatenated expression in the function as the previous example.)

$ ybunload -d premdb --username yb100 -W --format csv -o /home/yb100/premdb_unloads --truncate-existing --select 
"select encrypt_ks(concat_ws(':',teamid,htid,atid),yb100key) as ids, name, nickname, city, stadium, capacity, avg_att from team;"
Password for user yb100: 
13:01:14.577 [ INFO] ABOUT CLIENT:
    app.cli_args         = "-d" "premdb" "--username" "yb100" "-W" "--format" "csv" "-o" "/home/yb100/premdb_unloads" "--truncate-existing" "--select" "select encrypt_ks(concat_ws(':',teamid,htid,atid),yb100key) as ids, name, nickname, city, stadium, capacity, avg_att from team;"
    app.name_and_version = "ybunload version 3.2.0-20064"
    java.home            = "/usr/lib/jvm/java-8-oracle/jre"
    java.version         = "1.8.0_101"
    jvm.memory           = "512.00 MB (max=6.00 GB)"
    jvm.name_and_version = "Java HotSpot(TM) 64-Bit Server VM (build 25.101-b13, mixed mode)"
    jvm.options          = "-Xms512m, -Xmx6g, -XX:+UseG1GC, -Dapp.name=ybunload, -Dapp.pid=12850, -Dapp.repo=/opt/ybtools/lib, -Dapp.home=/opt/ybtools, -Dbasedir=/opt/ybtools"
    jvm.vendor           = "Oracle Corporation"
    os.name_and_version  = "Linux 4.4.0-31-generic (amd64)"

13:01:14.584 [ INFO] Truncating existing files that start with: unload
13:01:14.586 [ INFO] Verifying unload statement...
13:01:14.681 [ INFO] Unload statement verified
13:01:14.681 [ INFO] Beginning unload to /home/yb100/premdb_unloads
13:01:16.470 [ INFO] Network I/O Complete. Waiting on file I/O
13:01:16.473 [ INFO] Finalizing...
13:01:16.474 [ INFO] Transfer complete
13:01:16.475 [ INFO] Transferred: 3.29 KB Avg Network BW: 22.59 KB/s Avg Disk write rate: 21.35 KB/s

Now check the contents of the unloaded file. The first column is encrypted:

$ cd premdb_unloads
yb100@yb100:~/premdb_unloads$ more unload_1_1_.csv
ld2haCN3,Arsenal,Gunners,London,Emirates Stadium,60260,59.944
idIhaCd3,Aston Villa,Villains,Birmingham,Villa Park,42785,33.690
jdYgaCt3,Barnsley,Tykes,Barnsley,Oakwell Stadium,23009,0.000
gdogaC72,Birmingham City,Blues,Birmingham,St. Andrew's,30016,0.000
hd2gaCN2,Blackburn Rovers,Rovers,Blackburn,Ewood Park,31367,0.000
edIgaCd2,Blackpool,Seasiders,Blackpool,Bloomfield Road,17338,0.000
fdYfaCt2,Bolton Wanderers,Trotters,Bolton,Macron Stadium,28723,0.000
cdofaC71,Bournemouth,Cherries,Bournemouth,Vitality Stadium,11464,11.189
...

You can reload the data as is with ybload, then decrypt it with a query or a CTAS statement that uses the DECRYPT_KS function. (You cannot load and decrypt data in one step with ybload.)

Setting up DECRYPT privileges for members of a role

This example shows how to create a role with DECRYPT privileges, then create users that belong to the role. These users can decrypt data using a specific key, but they cannot encrypt data.

First create a role, then grant select privileges on tables to the role, then grant DECRYPT on a specific key to the role:

premdb=# create role decrypters;
CREATE ROLE
premdb=# grant select on all tables in schema public to decrypters;
GRANT
premdb=# grant decrypt on key yb100key to decrypters;
GRANT

Now create users who belong to the role:

premdb=# create user scofield login in role decrypters password 'I am a decrypter';
CREATE ROLE
premdb=# create user olivier login in role decrypters password 'I am also a decrypter';
CREATE ROLE

Now log in as one of those users and run a query that decrypts an encrypted expression:

premdb=# \c premdb scofield
Password for user scofield: 
You are now connected to database "premdb" as user "scofield".
premdb=> select decrypt_ks(ids,yb100key) from encrypted_team limit 5;
 decrypt_ks 
------------
 1:2:51
 2:3:52
 3:4:53
 4:5:54
 5:6:55
(5 rows)

Finally, attempt to encrypt data:

premdb=> select encrypt_ks(name,yb100key) from encrypted_team limit 5;
ERROR:  encrypt_ks: insufficient privileges on key yb100key