Enabling Roles Automatically
By default, new users are assigned the PUBLIC, which is automatically enabled when a new session starts. Typically, other roles are created and users are assigned to them, but these are not automatically enabled. Instead, users must explicitly enable their assigned roles with each new session, with SET ROLE
.
You can automatically enable roles for users in two ways:
- Enable roles for individual users on login
- Enable all roles for all users on login
Enable Roles for Individual Users
After assigning roles to users, you can set one or more default roles for each user by modifying their profiles, with ALTER USER...DEFAULT ROLE
. User default roles are automatically enabled at the start of the user session. You should consider setting default roles for users if they typically rely on the privileges of those roles to carry out routine tasks.
ALTER USER...DEFAULT ROLE
overwrites previous default role settings.
The following example shows how to set regional_manager
as the default role for user LilyCP
:
=> \c You are now connected as user "dbadmin". => GRANT regional_manager TO LilyCP; GRANT ROLE => ALTER USER LilyCP DEFAULT ROLE regional_manager; ALTER USER => \c - LilyCP You are now connected as user "LilyCP". => SHOW ENABLED_ROLES; name | setting ---------------+------------------ enabled roles | regional_manager (1 row)
Enable All Roles for All Users
Configuration parameter EnableAllRolesOnLogin
specifies whether to enable all roles for all database users on login. By default, this parameter is set to 0. If set to 1, Vertica enables the roles of all users when they log in to the database.
Clearing Default Roles
You can clear all default role assignments for a user with ALTER USER...DEFAULT ROLE NONE
. For example:
=> ALTER USER fred DEFAULT ROLE NONE; ALTER USER => SELECT user_name, default_roles, all_roles FROM users WHERE user_name = 'fred'; user_name | default_roles | all_roles -----------+---------------+----------- fred | | logreader (1 row)