Set All User’s Default Roles to All of their Granted Roles

Jim_Knicely

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. This is done with the ALTER USER … DEFAULT ROLE command.

I’d like to assign all of my database user’s 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 DB, I can have Vertica generate and run the 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:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/DefaultRolesForDatabaseUsers.htm

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERUSER.htm

Have fun!

qinchaofeng

I also write some backup SQL ：

---backup resource_pools

SELECT    'CREATE RESOURCE POOL ' || name
        || CASE WHEN memorysize                IS NULL THEN ' ' ELSE ' MEMORYSIZE '                 || '''' || memorysize               || '''' END
        || CASE WHEN maxmemorysize = ''                THEN ' ' ELSE ' MAXMEMORYSIZE '              || '''' || maxmemorysize            || '''' END
        || CASE WHEN executionparallelism     = 'AUTO' THEN ' ' ELSE ' EXECUTIONPARALLELISM '       || '''' || executionparallelism     || '''' END
        || CASE WHEN NULLIFZERO(priority)      IS NULL THEN ' ' ELSE ' PRIORITY '                   || '''' || priority                 || '''' END
        || CASE WHEN runtimepriority           IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITY '            ||         runtimepriority                  END
        || CASE WHEN runtimeprioritythreshold  IS NULL THEN ' ' ELSE ' RUNTIMEPRIORITYTHRESHOLD '   ||         runtimeprioritythreshold         END
        || CASE WHEN queuetimeout              IS NULL THEN ' ' ELSE ' QUEUETIMEOUT '               ||         queuetimeout                     END
        || CASE WHEN maxconcurrency            IS NULL THEN ' ' ELSE ' MAXCONCURRENCY '             ||         maxconcurrency                   END
        || CASE WHEN runtimecap                IS NULL THEN ' ' ELSE ' RUNTIMECAP '                 || '''' || runtimecap               || '''' END
        || ' ; '
FROM v_catalog.resource_pools
WHERE NOT is_internal
ORDER BY name;
---backup roles

-- Roles
------------------------------------------------------------
SELECT '-- Create Roles';
SELECT 'CREATE ROLE ' || name || ' ;' AS TXT_CR
FROM v_catalog.roles
WHERE name NOT IN ('public','dbadmin','pseudosuperuser','dbduser')
ORDER BY 1;

------------------------------------------------------------
-- Add users to Roles
------------------------------------------------------------
SELECT '-- Add users to roles';
SELECT 'GRANT ' || all_roles || ' TO ' || user_name || ';'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

--backup schema
SELECT '-- Create Schema';
SELECT 'CREATE SCHEMA ' || schema_name  ||  ';'
FROM schemata
WHERE schema_name NOT IN ('v_internal','v_catalog','v_monitor','TxtIndex')
ORDER BY 1;

--backup users

SELECT '-- Create Users';
SELECT 'CREATE USER ' || user_name  || ' RESOURCE POOL ' || resource_pool ||  ' ;'
FROM v_catalog.users
WHERE user_name NOT IN ('dbadmin')
ORDER BY 1;

--backup grants
 select 'grant '|| privileges_description || ' on '|| object_name || ' to '|| grantee||';' 
 from grants where grantor<>grantee 
 order by object_name;