Vertica

How to assign a resource pool to Vertica user

Recently I came across a use case, which to me, was a very easy task, but when I started to work, I found it to be rather complex.

The only reason was confusing documentation done by Vertica team.

So the question was “How to assign a resource pool to any Vertica user”

Requirement –

We have a user who needs to be using a specific resource pool always.

Solution –

Create a user

Here we create a SQL authenticated user and just check the default resource pool assigned to it in system tables. We will see every user gets assigned to ‘general’ resource pool by default

navin=> create user navin identified by 'xxxxxxx';
CREATE USER


navin=> show resource_pool
navin-> ;
     name      | setting
---------------+---------
 resource_pool | general
(1 row)

navin=> select user_name, resource_pool from users where user_name like 'navin';
-[ RECORD 1 ]-+--------
user_name     | navin
resource_pool | general

Grant usage permission to the user for this resource pool.

User needs to have USAGE privileges on a specific resource pool, before assigning the resource pool as a default pool for that user. This can be done by using the below command,

navin=> GRANT USAGE ON RESOURCE POOL recovery to navin;
GRANT PRIVILEGE


Assign a default resource pool to the user

By default, if you don’t assign a resource pool to the user, ‘general’ resource pool is applied by Vertica.

If we need a user to use a specific pool, we should assign this resource pool to the user , using the below command . Here we have assigned resource pool “recovery” to this user.

navin=> ALTER USER navin RESOURCE POOL recovery;
ALTER USER
navin=> select user_name, resource_pool from users where user_name like 'navin';
 user_name | resource_pool
-----------+---------------
 navin     | recovery

--Validating if user can use this pool

navin=> set resource_pool=recovery;
SET
navin=> show resource_pool;
     name      | setting
---------------+----------
 resource_pool | recovery
(1 row)


These are the 3 steps which needs to be followed in order to assign a resource pool to the user.

Note :

  1. we have assigned this test user to recovery resource pool here, just for example purpose. recovery is a inbuilt resource pool, we should ideally avoid any kind of usage of this pool by any user of Vertica.
  2. We can assign USAGE permission on multiple user resource pools to a user, but only 1 resource pool can be set to default for a user.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s