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”
We have a user who needs to be using a specific resource pool always.
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.
- 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.
- 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.