Vertica Access Policy Limitations with Merge Query
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??
Comments
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. "