Eon Mode Sublcuster authorization

HAkbulutHAkbulut Vertica Employee

Is it possible to give authorization vertica database user without creating routing rule for each subcluster.

Answers

  • SruthiASruthiA Vertica Employee Administrator

    @HAkbulut Could you please share more details pertaining to the usecase?

  • HAkbulutHAkbulut Vertica Employee

    ı am trying to give acces to subcluster on user bases at EON MODE Database installation, however all documentation on our web site telling that ı have to create routing protocols for user group bases. So I thought we should solve this issue with resource pool. However it does not work either.

    You will find my code below.

    TEST 1

    User=dbadmin, Subcluster=default_subcluster

    select current_subcluster_name()
    --default_subcluster

    CREATE RESOURCE POOL datascience_pool FOR SUBCLUSTER datascience_cluster
    MEMORYSIZE ‘50%’
    PLANNEDCONCURRENCY 16
    EXECUTIONPARALLELISM 8;

    select name from resource_pools
    --general
    --sysquery
    --tm
    --refresh
    --recovery
    --dbd
    --jvm
    --blobdata
    --metadata
    --datascience_pool

    create table test.t1(id int, values varchar(100));
    create user hamza;
    grant usage on schema test to hamza;
    grant select on test.t1 to hamza;
    grant usage on resource pool datascience_pool for subcluster datascience_cluster to hamza;

    User=hamza, Subcluster=datascience_cluster
    select current_subcluster_name()
    --datascience_cluster

    select * from test.t1;
    --SQL Error [9099] [55000]: [Vertica] [VJDBC] (9099) ERROR 9099: Cannot find participating nodes to run the query

    User=hamza, Subcluster=default_subcluster
    select current_subcluster_name()
    --default_subcluster

    select * from test.t1;
    --no errors

    TEST 2

    User=dbadmin, Subcluster=default_subcluster

    select current_subcluster_name()
    --default_subcluster

    CREATE RESOURCE POOL datascience_pool;
    CREATE RESOURCE POOL datascience_pool FOR SUBCLUSTER datascience_cluster
    MEMORYSIZE ‘50%’
    PLANNEDCONCURRENCY 16
    EXECUTIONPARALLELISM 8;

    select name, subcluster_name from resource_pools
    --general NULL
    --sysquery NULL
    --tm NULL
    --refresh NULL
    --recovery NULL
    --dbd NULL
    --jvm NULL
    --blobdata NULL
    --metadata NULL
    --datascience_pool NULL
    --datascience_pool datascience_cluster

    create table test.t1(id int, values varchar(100));
    create user hamza resource pool datascience_pool;
    grant usage on schema test to hamza;
    grant select on test.t1 to hamza;
    grant usage on resource pool datascience_pool for subcluster datascience_cluster to hamza;

    User=hamza, Subcluster=datascience_cluster
    select current_subcluster_name()
    --datascience_cluster

    select * from test.t1;
    --SQL Error [9099] [55000]: [Vertica] [VJDBC] (9099) ERROR 9099: Cannot find participating nodes to run the query

    User=hamza, Subcluster=default_subcluster
    select current_subcluster_name()
    --default_subcluster

    select * from test.t1;
    --no errors

  • SruthiASruthiA Vertica Employee Administrator

    I dont think you can force connections to a subcluster using resource pools. Routing rules need to be configured.

  • GoCougsGoCougs Vertica Customer ✭✭

    @SruthiA can you confirm this? What we are looking for is the ability to assign users to a resource pool which belongs to a subcluster so when that user connects to the database the user is connected to the subcluster automatically using the default connection string.

    For example,
    You have Primary subclusterA and a secondary subcluster called subclusterB.
    I create a resource pool for subclusterB called query_pool
    see documentation

    I then assign a db user called CustomerQuery to query_pool

    I would then expect whenever the CustomerQuery user logs on to the db using default connection settings like connecting to node001 in the subclusterA it would log them into the resource pool query_pool which is in subclusterB. Thus everything that user runs would run in subclusterB because the resource pool they are assigned lives in subclusterB.

    Can you clarify if this is possible? cc @HAkbulut

  • GoCougsGoCougs Vertica Customer ✭✭
    edited December 2021

    ^^

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2021

    The Vertica User Profiles documentation page is pretty clear on this:

    In an Eon Mode database, you can set the user's default resource pool to a subcluster-specific resource pool. If that is the case, Vertica uses one of the following methods to determine which resource pool is used for queries when a user connects to a subcluster:

    • If the subcluster uses their assigned default resource pool, then the user's queries use their assigned resource pool.
    • If the subcluster does not use their assigned default resource pool, but the user has access to the GENERAL pool, the user's queries use the GENERAL pool.
    • If the subcluster does not use the resource pool assigned to the user, and the user does not have privileges to the GENERAL pool, then the user cannot query from any node of this subcluster.

    For now you have to use Connection Load Balancing Policies to redirect a connection to a specific subcluster based on the connection's origin.

    But good news, there is a feature in the works that will allow you to direct new connections to partitcular subclusters based on a specified workload or the user name!

  • GoCougsGoCougs Vertica Customer ✭✭

    @Jim_Knicely thanks for the quick response and clarification.

    Unfortunately, we have one bank of application servers that all hit the vertica dbs and handle all work loads (loading, querying, ETL) so having the ability to direct connections to subclusters by user is going to be a life saver for us to get eon mode working with our system.

    Will this be in vertica 12 or are you hoping for a minor release in vertica 11?

  • SruthiASruthiA Vertica Employee Administrator

    @GoCougs : It is still being worked on by Engineering. so we cannot confirm the version exactly as in when it will be released.

  • GoCougsGoCougs Vertica Customer ✭✭
    edited December 2021

    Thanks @SruthiA. When you have an update on when this will be released, please post it here.

  • SruthiASruthiA Vertica Employee Administrator

    @GoCougs : Sure. I will update here..

Leave a Comment

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