User Profiles
User profiles are attributes associated with a user that control that user's access to several system resources. These resources include:
- Resource pool to which a user is assigned (RESOURCE POOL)
- Maximum amount of memory a user's session can use (MEMORYCAP)
- Maximum amount of temporary file storage a user's session can use (TEMPSPACECAP)
- Maximum amount of time a user's query can run (RUNTIMECAP)
You can set these attributes with the CREATE USER statement and modify the attributes later with ALTER USER.
Two strategies limit a user's access to resources: setting attributes on the user directly to control resource use, or assigning the user to a resource pool. The first method lets you fine tune individual users, while the second makes it easier to group many users together and set their collective resource usage.
If you limit user resources collectively with resource pool assignments, consider the following:
- A user cannot log in to Vertica unless they either have privileges to the GENERAL pool, or are assigned to a default resource pool.
- If a user's default resource pool is dropped, the user's queries use the GENERAL pool.
- If a user does not have privileges to the GENERAL pool and you attempt to drop their assigned resource pool, the DROP operation fails.
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.
The following examples illustrate how to set a user's resource pool attributes. For additional examples, see the scenarios described in Using User-Defined Pools and User-Profiles for Workload Management.
Example
Set the user's RESOURCE POOL attribute to assign the user to a resource pool. To create a user named user1
who has access to the resource pool my_pool
, use the command:
=> CREATE USER user1 RESOURCE POOL my_pool;
To limit the amount of memory for a user without designating a pool, set the user's MEMORYCAP
to either a particular unit or a percentage of the total memory available. For example, to create a user named user2
whose sessions are limited to using 200 MBs memory each, use the command:
=> CREATE USER user2 MEMORYCAP '200M';
To limit the time a user's queries are allowed to run, set the RUNTIMECAP
attribute. To prevent queries for user2
from running more than five minutes, use this command:
=> ALTER USER user2 RUNTIMECAP '5 minutes';
To limit the amount of temporary disk space that the user's sessions can use, set the TEMPSPACECAP
to either a particular size or a percentage of temporary disk space available. For example, the next statement creates user3
, and limits her to using 1 GB of temporary space:
=> CREATE USER user3 TEMPSPACECAP '1G';
You can combine different attributes into a single command. For example, to limit the MEMORYCAP
and RUNTIMECAP
for user3
, include both attributes in an ALTER USER statement:
=> ALTER USER user3 MEMORYCAP '750M' RUNTIMECAP '10 minutes'; ALTER USER => \x Expanded display is on. => SELECT * FROM USERS; -[ RECORD 1 ]-----+------------------ user_id | 45035996273704962 user_name | release is_super_user | t resource_pool | general memory_cap_kb | unlimited temp_space_cap_kb | unlimited run_time_cap | unlimited -[ RECORD 2 ]-----+------------------ user_id | 45035996273964824 user_name | user1 is_super_user | f resource_pool | my_pool memory_cap_kb | unlimited temp_space_cap_kb | unlimited run_time_cap | unlimited -[ RECORD 3 ]-----+------------------ user_id | 45035996273964832 user_name | user2 is_super_user | f resource_pool | general memory_cap_kb | 204800 temp_space_cap_kb | unlimited run_time_cap | 00:05 -[ RECORD 4 ]-----+------------------ user_id | 45035996273970230 user_name | user3 is_super_user | f resource_pool | general memory_cap_kb | 768000 temp_space_cap_kb | 1048576 run_time_cap | 00:10