Eon Mode Sublcuster authorization

HAkbulut

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

SruthiA

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

HAkbulut

ı 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

SruthiA

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

GoCougs

@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

GoCougs

^^

Jim_Knicely

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!

GoCougs

@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?

SruthiA

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

GoCougs

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

SruthiA

@GoCougs : Sure. I will update here..