Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Must be superuser to run disable_duplicate_key_error

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?

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    edited March 2021 Answer ✓

    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)
    

Answers

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

  • Jim_KnicelyJim_Knicely Administrator

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

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

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.