
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: