User profiles

User profiles are attributes associated with a user that control that user's access to several system resources.

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

See also