Decrypting Columns in a Restored Database

If you have tables with encrypted columns in a database that you restore to a database on another system, you need to create the encryption key on the target system in order to decrypt the encrypted data in those columns. This section walks through an example of this procedure. Note that if you are restoring a database on the same system where the source database was backed up, you can reference the existing encryption key, and this procedure is not necessary.

Assume that you have followed the steps under Encrypting Sensitive Data. Your source system has an encryption key called yb100key with the secret a1b2c3d4e5f0. ALL privileges on the key are granted to role yb100. You have created some tables in the premdb database (public schema) and some of the table columns are encrypted. Now you load the database and take a full backup, in anticipation of restoring the database on another system.

To restore the database and enable decryption of the encrypted data in the restored tables, follow these steps:

Log into the restored version of the database and set the schema.

Create an identical encryption key in the restored database, using the same schema, the same key name, and the same secret:

premdb=# create key yb100key secret 'a1b2c3d4e5f0';
CREATE KEY
premdb=# select * from sys.key;
 key_id  |   name   | schema_id | owner_id |         creation_time         
---------+----------+-----------+----------+-------------------------------
 1712473 | yb100key |      2200 |    16007 | 2021-11-03 16:30:23.206582-07
(1 row)

Grant ALL privileges (or fewer privileges as appropriate) to the users and roles on the target system who need access to encrypted data.
```
premdb=# grant all on key yb100key to yb100;
GRANT
```
Also make sure that the same users and roles have SELECT privilege on the target tables that contain encrypted data. For example:
```
premdb=# grant select on encrypted_names to yb100;
GRANT
```

Log into the restored database using one of the accounts with those grants.

premdb=# \c premdb yb100
Password for user yb100: 
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "yb100".

Test the user's ability to decrypt columns by running some queries with the DECRYPT_KS function.

premdb=> select decrypt_ks(enc,yb100key) from encrypted_names;
 decrypt_ks 
------------
 Black Cats
 Swans
 Robins
...