Must be superuser to run disable_duplicate_key_error

rajatpaliwal86

select DISABLE_DUPLICATE_KEY_ERROR();

This method works well we run it from the dbadmin. However, when we try to run it from another user we get an error.

We also tried to grant permission but then we get another error that the function doesn't exist.
dbadmin=> GRANT EXECUTE ON FUNCTION DISABLE_DUPLICATE_KEY_ERROR() to athena_ui;
ROLLBACK 2059: Function with specified name and parameters does not exist: DISABLE_DUPLICATE_KEY_ERROR

We want to execute this function from another user (athena_ui), is it possible?

Jim_Knicely

Any user that has the PSEUDOSUPERUSER role active can call the DISABLE_DUPLICATE_KEY_ERROR() function.

But be careful, the PSEUDOSUPERUSER role gives a lot of power to the user!

See:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/PSEUDOSUPERUSERRole.htm

Example:

dbadmin=> CREATE USER athena_ui;
CREATE USER

dbadmin=> GRANT pseudosuperuser TO athena_ui;
GRANT ROLE

dbadmin=> \c - athena_ui;
You are now connected as user "athena_ui".

dbadmin=> SELECT disable_duplicate_key_error();
ERROR 4019:  Must be superuser to run disable_duplicate_key_error

dbadmin=> SET ROLE pseudosuperuser;
SET

dbadmin=> SELECT disable_duplicate_key_error();
WARNING 3152:  Duplicate values in columns marked as UNIQUE will now be ignored for the remainder of your session or until reenable_duplicate_key_error() is called
WARNING 3539:  Incorrect results are possible. Please contact Vertica Support if unsure
 disable_duplicate_key_error
------------------------------
 Duplicate key error disabled
(1 row)

rajatpaliwal86

@Jim_Knicely Thank you for the quick reply, so there is no way to grant execute access to just this function?

Jim_Knicely

@rajatpaliwal86 - Nope... Why not just fix the constraint violations?

rajatpaliwal86

@Jim_Knicely said:
@rajatpaliwal86 - Nope... Why not just fix the constraint violations?

We have a cron that deduplicates those events but it is scheduled every 1 hour, but meanwhile, if there are duplicates then UI becomes useless because of the JOIN query errors for the duplicates. We also don't want to give a lot of power to the UI user.