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

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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!

Comments

  • qinchaofengqinchaofeng Registered User

    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;
    
Sign In or Register to comment.