Vertica Access Policy Limitations with Merge Query

Valaravaus

I'm trying to run a merge Query on Table with access Policy on Certain columns.


Def-

ALTER ACCESS POLICY ON T1 FOR column SSN
CASE  WHEN ENABLED_ROLE('dbadmin') then SSN
WHEN ENABLED_ROLE('ADMIN') then SSN
WHEN ENABLED_ROLE('ADMIN2') then SSN
WHEN ENABLED_ROLE('ADMIN3') then SSN
ELSE NULL END ENABLE;


If I run a update statement- It runs successfully .


Merge Operations on this table Fails with Error -

ERROR 6538:  Unable to MERGE: "Access denied due to active access policy on table "T1" for column "SSN""

Vertica Documentation-

https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/AdministratorsGuide/Tables/USINGACCESSPOLICY.htm

has very vague statement -
-If the query cannot be folded (or compressed) by the Vertica optimizer, all functions other than SELECT are blocked. The following error message appears:

=> ERROR 0: Unable to INSERT: "Access denied due to active access policy on table <tablename> for column (columnname>



Is there any fix for it ????


I saw an old ticket for same issue -https://community.dev.hpe.com/t5/Vertica-Forum/ACCESS-POLICY-Load-Issue/td-p/232372

Is it a known Bug??

Valaravaus

Got Response from Vertica Support.

 

Issue resolved in 7.2.x

 

It is logged under VER-41081 and is actually documented in 7.2.x release notes under fixes for 7.2.1 :

http://my.vertica.com/docs/ReleaseNotes/7.2.x/Vertica_7.2.x_Release_Notes.htm

"..Previously, MERGE or COPY could not be performed on a table column that contained an Access Policy. You can now perform MERGE or COPY on a column if the enabled access policy allows you to access all the data in the column. "