Creating column access policies

CREATE ACCESS POLICY can create access policies on individual table columns, one policy per column. Each column access policy lets you specify, for different users and roles, various levels of access to the data of that column. The column access expression can also specify how to render column data for users and roles.

The following example creates an access policy on the customer_address column in the client_dimension table. This access policy gives non-superusers with the administrator role full access to all data in that column, but masks customer address data from all other users:

=> CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address
-> CASE
-> WHEN ENABLED_ROLE('administrator') THEN customer_address
-> ELSE '**************'
-> END ENABLE;
CREATE ACCESS POLICY

Vertica uses this policy to determine the access it gives to users MaxineT and MikeL, who are assigned employee and administrator roles, respectively. When these users query the customer_dimension table, Vertica applies the column access policy expression as follows:

=> \c - MaxineT;
You are now connected as user "MaxineT".
=> SET ROLE employee;
SET
=> SELECT customer_type, customer_name, customer_gender, customer_address, customer_city FROM customer_dimension;
 customer_type |      customer_name      | customer_gender | customer_address |  customer_city
---------------+-------------------------+-----------------+------------------+------------------
 Individual    | Craig S. Robinson       | Male            | **************   | Fayetteville
 Individual    | Mark M. Kramer          | Male            | **************   | Joliet
 Individual    | Barbara S. Farmer       | Female          | **************   | Alexandria
 Individual    | Julie S. McNulty        | Female          | **************   | Grand Prairie
 ...

=> \c - MikeL
You are now connected as user "MikeL".
=> SET ROLE administrator;
SET
=> SELECT customer_type, customer_name, customer_gender, customer_address, customer_city FROM customer_dimension;
 customer_type |      customer_name      | customer_gender | customer_address |  customer_city
---------------+-------------------------+-----------------+------------------+------------------
 Individual    | Craig S. Robinson       | Male            | 138 Alden Ave    | Fayetteville
 Individual    | Mark M. Kramer          | Male            | 311 Green St     | Joliet
 Individual    | Barbara S. Farmer       | Female          | 256 Cherry St    | Alexandria
 Individual    | Julie S. McNulty        | Female          | 459 Essex St     | Grand Prairie
 ...

Restrictions

The following limitations apply to access policies:

• A column can have only one access policy.

• Column access policies cannot be set on columns of complex types other than native arrays.

• Column access policies cannot be set for materialized columns on flex tables. While it is possible to set an access policy for the __raw__ column, doing so restricts access to the whole table.

• Row access policies are invalid on temporary tables and tables with aggregate projections.

• Access policy expressions cannot contain:

  • Subqueries

  • Aggregate functions

  • Analytic functions

  • User-defined transform functions (UDTF)

• If the query optimizer cannot replace a deterministic expression that involves only constants with their computed values, it blocks all DML operations such as INSERT.