Enable All Granted Roles on Login

Jim_KnicelyJim_Knicely - Select Field - Administrator

You can automatically enable roles for users in two ways:

  1. Enable roles for individual users on login (either with the SET ROLE command or default roles)
  2. Enable all roles for all users on login (At the database level via the EnableAllRolesOnLogin parameter)

The second option is great (and easy) if you want all users to automatically have their granted roles become their enabled roles after logging in!

Example:

dbadmin=> SELECT all_roles, default_roles
dbadmin->   FROM users
dbadmin->  WHERE user_name = 'lucy';
            all_roles             | default_roles
----------------------------------+---------------
read_all_reports, update_allowed |
(1 row)

dbadmin=> \c - lucy
You are now connected as user "lucy".

dbadmin=> SHOW enabled roles;
     name      | setting
---------------+---------
enabled roles |
(1 row)

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> SELECT parameter_name, current_value, default_value, allowed_levels, description
dbadmin->   FROM configuration_parameters
dbadmin->  WHERE parameter_name = 'EnableAllRolesOnLogin';
    parameter_name     | current_value | default_value | allowed_levels |            description
-----------------------+---------------+---------------+----------------+-----------------------------------
EnableAllRolesOnLogin | 0             | 0             | DATABASE       | Enable all granted roles on login
(1 row)

dbadmin=> ALTER DATABASE test_db SET EnableAllRolesOnLogin = 1;
ALTER DATABASE

dbadmin=> \c - lucy
You are now connected as user "lucy".

dbadmin=> SHOW enabled roles;
     name      |             setting
---------------+----------------------------------
enabled roles | read_all_reports, update_allowed
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/EnablingRolesAutomatically.htm

Have fun!

Sign In or Register to comment.