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

Eon Mode Sublcuster authorization

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

Answers

  • SruthiASruthiA Employee

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

  • ı 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 Employee

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

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.