Enable All Granted Roles on Login

Posted June 20, 2019 by Jim Knicely, Vertica Principal Solution Architect

Helpful Tips message on post-it note
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/latest/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/EnablingRolesAutomatically.htm

Have fun!