Assign All Users’ Granted Roles as Default Roles

Posted February 26, 2019 by Jim Knicely, Vertica Principal Solution Architect

By default, no roles (other than the default PUBLIC Role) are enabled at the start of a user session. You have to assign one or more of a user’s granted roles as default roles so that they are enabled when a user logs on. You do this with the ALTER USER … DEFAULT ROLE command.

But suppose you want to assign all of your database users’ granted roles to also be their default roles. Instead of manually running an ALTER USER … DEFAULT ROLE command for each of the 357 users in my Vertica database, you can have Vertica generate and run all those commands for me!

Example: dbadmin=> SELECT user_name, all_roles, default_roles dbadmin-> FROM users dbadmin-> WHERE user_name ILIKE 'role_user%' dbadmin-> ORDER BY 1; user_name | all_roles | default_roles ------------+-----------+--------------- role_user1 | r1 | role_user2 | r1, r2 | role_user3 | r1, r3 | role_user4 | r2 | role_user5 | r2, r3 | (5 rows) dbadmin=> \! /opt/vertica/bin/vsql -Atc "SELECT 'ALTER USER ' || user_name || ' DEFAULT ROLE ' || REPLACE(all_roles, '*', '') || ';' FROM users WHERE NOT is_super_user AND all_roles <> '';" | /opt/vertica/bin/vsql –q dbadmin=> SELECT user_name, all_roles, default_roles dbadmin-> FROM users dbadmin-> WHERE user_name ILIKE 'role_user%' dbadmin-> ORDER BY 1; user_name | all_roles | default_roles ------------+-----------+--------------- role_user1 | r1 | r1 role_user2 | r1, r2 | r1, r2 role_user3 | r1, r3 | r1, r3 role_user4 | r2 | r2 role_user5 | r2, r3 | r2, r3 (5 rows) Helpful Links: