Resource Pool and Rols

VerticaQ

Hi

 

I want to creat emultiple roles for sifferent type of users.

In addition i want to assign each role to a different resource pool.

 

I couldnt find an option to acomplish this. i can assign a user to a deafult resource pool but than i will have to manage each user (very bad for me)

 

How can i assign a role to a specific resource pool?

 

Thank you

knicely87

Hi,

 

From my understanding, you wouldn't "assign each role to a different resource pool", you'd "assign (i.e. grant usage on) resource pools to roles ":

 

dbadmin=> create role role1;
CREATE ROLE

dbadmin=> create resource pool pool1;
CREATE RESOURCE POOL

dbadmin=> grant usage on resource pool pool1 to role1;
GRANT PRIVILEGE

dbadmin=> create user user1;
CREATE USER

dbadmin=> grant role1 to user1;
GRANT ROLE

dbadmin=> alter user user1 default role all;
ALTER USER

But doing that only gives the user the usage privilege to the resource pool. It does not change the his/her's default resource pool. Roles do not do that.

 

You are still either going to have to change the user's default resource pool with the ALTER USER command, or use the SET SESSION RESOURCE POOL command.

 

dbadmin=> \c dbadmin user1;
You are now connected to database "dbadmin" as user "user1".
dbadmin=> show resource_pool;
     name      | setting
---------------+---------
 resource_pool | general
(1 row)

dbadmin=> set session resource pool pool1;
SET
dbadmin=> show resource_pool;
     name      | setting
---------------+---------
 resource_pool | pool1
(1 row)

Thanks!

 

VerticaQ

Hi

 

This is what i already done.

I wanted to avoid dealing with specific users and manage all the permissions via rols.

The world is not perfect...

Adrian_Oprea_1

 You cannot add a Resource Pool to a Role. 

 Roles are used with a different purpouse in DB admin, they offer the flexibility of assigning permissions to groups of users, instead of having to assign permissions individually. 

 I think that having Resource pools attached to a db role would make your life harder when doing maintainance.

VerticaQ

Thnak you for your reply.

If this is by design of the product i guess i have no choice but assigning each user a default resource pool.

Adrian_Oprea_1

 To be honest i don`t know any database engine that will allow you to attach a resource pool to a database role. 

 They are two different parts of a database engine, one is a logical object(role) and the other is fisical resource/memory structure(resource pool)