Automating encryption and decryption with access policies

You can automate encryption and decryption by creating access policies.

You can automate encryption and decryption by creating access policies. These policies let you show users with specific roles unencrypted values while users without those roles see encrypted ones. Alternatively, you can create an access policy that masks unencrypted data stored in the database when queried by users who lack a specific role.

Users do not explicitly call the SecureData integration functions when you create access policies to automatically encrypt and decrypt data. However, they still must have access to the SecureData functions and have any necessary session variables set. See Configuring access to SecureData and Granting users access to the Voltage SecureData integration functions for the necessary configuration for the access policies to work. If a user who does not have access to the SecureData integration functions queries a table with an access policy that calls these functions, the query generates an error.

Automatically decrypting table columns for privileged users

To decrypt an encrypted column automatically, create an access policy for the encrypted column that calls VoltageSecureAccess if the user has a role enabled. If the user does not have the role enabled, just return the encrypted value.

The following example:

Creates a role named see_ssn and grants it to the user named Alice, as well as granting Alice access to the customers table.
Creates an access policy on the customers table's ssn column that decrypts the column’s value if the user has the see_ssn role enabled.
Switches to the user named Alice.
Queries the customers table, without the see_ssn role enabled.
Enables the see_ssn role and queries the table again.

=> CREATE ROLE see_ssn;
CREATE ROLE

=> GRANT see_ssn TO alice;
GRANT ROLE

=> GRANT ALL ON TABLE customers TO alice;
GRANT PRIVILEGE

=> CREATE ACCESS POLICY ON customers FOR COLUMN ssn
     CASE
         WHEN enabled_role('see_ssn') THEN VoltageSecureAccess(ssn USING PARAMETERS format='ssn',
                                                               config_dfs_path='/voltagesecure/conf')
         ELSE ssn
     END ENABLE;
CREATE ACCESS POLICY

=> \c vmart alice;
Password:
You are now connected to database "vmart" as user "alice".

=> SELECT first_name, last_name, ssn FROM customers WHERE id < 5355 ORDER BY id ASC;
 first_name | last_name |     ssn
------------+-----------+-------------
 Gil        | Reeves    | 997-92-0657
 Robert     | Moran     | 715-02-0455
 Hall       | Rice      | 938-83-0659
 Micah      | Trevino   | 495-57-0860
(4 rows)

=> SET ROLE see_ssn;
SET
=> SELECT first_name, last_name, ssn FROM customers WHERE id < 5355 ORDER BY id ASC;
 first_name | last_name |     ssn
------------+-----------+-------------
 Gil        | Reeves    | 232-28-0657
 Robert     | Moran     | 725-79-0455
 Hall       | Rice      | 285-90-0659
 Micah      | Trevino   | 853-60-0860
(4 rows)

In the above example, you could combine the see_ssn role with a role that grants users access to the SecureData integration functions. Users who do not have the see_ssn role do not need to have access to the SecureData functions in order to see the encrypted values.

Note

The above example assumes that the shared configuration file (/voltagesecure/conf) contains all of the credentials necessary to authenticate with the SecureData Appliance, including the password or shared secret. This configuration may not be secure enough to meet your requirements. See the caution in VoltageSecureConfigure for more information. In production use, consider having each privileged user set their identity, username, and password or shared secret in session variables. Unprivileged users do not need to set these values.

Automatically encrypting columns for unprivileged users

You can also create access policies that encrypt values. Use this technique to prevent some users from seeing values in columns that aren't sensitive enough to require encryption in the database, but should not be seen by all users. This technique is the opposite of the encryption-on-the-fly: users that have a specific role enabled get the value from the table; if they do not have the role, the access policy encrypts the value.

The following example:

Creates a role named see_dob and assigns it to the user Alice.
Creates an access policy on the dob column of the customers table. It returns the value in the column if the user has the see_dob role active and encrypts the value if not.
Switches to the user named Alice.
Queries the customers table including the dob column.
Sets the see_dob role and queries customers again.

=> CREATE ROLE see_dob;
CREATE ROLE

=> GRANT see_dob TO alice;
GRANT ROLE

=> CREATE ACCESS POLICY ON customers FOR COLUMN dob
     CASE
         WHEN enabled_role('see_dob') THEN dob
         ELSE VoltageSecureProtect(dob::varchar USING PARAMETERS format='birthday',
                                   config_dfs_path='/voltagesecure/conf')::date
     END ENABLE;
CREATE ACCESS POLICY

=> \c vmart alice
Password:
You are now connected to database "vmart" as user "alice".

=> SELECT first_name, last_name, dob FROM customers ORDER BY id ASC LIMIT 10;
 first_name | last_name  |    dob
------------+------------+------------
 Gil        | Reeves     | 2048-08-09
 Robert     | Moran      | 1917-03-05
 Hall       | Rice       | 2022-01-07
 Micah      | Trevino    | 2018-06-01
 Kuame      | Stephenson | 2053-02-13
 Hedda      | Cooper     | 2002-03-12
 MacKenzie  | Burks      | 2061-10-30
 Anne       | Marquez    | 2078-08-02
 Dominic    | Avery      | 1940-08-10
 Alfreda    | Mcdaniel   | 1904-04-27
(10 rows)

=> SET ROLE see_dob;
SET

=> SELECT first_name, last_name, dob FROM customers ORDER BY id ASC LIMIT 10;
 first_name | last_name  |    dob
------------+------------+------------
 Gil        | Reeves     | 1955-11-04
 Robert     | Moran      | 1991-12-01
 Hall       | Rice       | 1977-07-07
 Micah      | Trevino    | 1980-12-05
 Kuame      | Stephenson | 1979-09-12
 Hedda      | Cooper     | 1987-05-02
 MacKenzie  | Burks      | 1982-11-07
 Anne       | Marquez    | 1949-07-09
 Dominic    | Avery      | 1976-12-02
 Alfreda    | Mcdaniel   | 1975-02-08
(10 rows)

Note

The values you pass to VoltageSecureProtect and VoltageSecureAccess must be VARCHARs. In the previous example, the dob column's data type is DATE, so its value has to be cast to VARCHAR when passed to VoltageSecureProtect. The encrypted value has to be cast back to a DATE value because its output needs to match the table schema.

In the previous example, users who do not have the see_dob role enabled must have access to the SecureData functions in order to see the masked values. If they do not have access to the SecureData functions, querying the customers table results in an error message. The following example creates a new user named Bob and grants him access to the customers table, without any access to the SecureData functions.

=> CREATE USER bob;
CREATE USER
=> GRANT ALL ON TABLE customers TO bob;
GRANT PRIVILEGE
=> \c vmart bob
You are now connected to database "vmart" as user "bob".
=> SELECT * FROM customers LIMIT 10;
ERROR 6482:  Failed to parse Access Policies for table "customers"
[Function public.VoltageSecureProtect(varchar) does not exist, or permission
is denied for public.VoltageSecureProtect(varchar)]