The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

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

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

  • 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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.