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;
=> ALTER USER LilyCP DEFAULT ROLE regional_manager;
=> \c - LilyCP
You are now connected as user "LilyCP".
     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:

=> SELECT user_name, default_roles, all_roles FROM users WHERE user_name = 'fred';
 user_name | default_roles | all_roles
 fred      |               | logreader
(1 row)