Resource Pool and Rols

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!

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...

 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.

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.

 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)