Enabling roles

When you enable a role in a session, you obtain all privileges assigned to that role. You can enable multiple roles simultaneously, thereby gaining all privileges of those roles, plus any privileges that are already granted to you directly.

By default, only predefined roles are enabled automatically for users. Otherwise, on starting a session, you must explicitly enable assigned roles with the Vertica function SET ROLE.

For example, the dbadmin creates the logreader role and assigns it to user alice:

=> \c
You are now connected as user "dbadmin".
=> CREATE ROLE logreader;
CREATE ROLE
=> GRANT SELECT ON TABLE applog to logreader;
GRANT PRIVILEGE
=> GRANT logreader TO alice;
GRANT ROLE

User alice must enable the new role before she can view the applog table:

=> \c - alice
You are now connected as user "alice".
=> SELECT * FROM applog;
ERROR:  permission denied for relation applog
=> SET ROLE logreader;
SET
=> SELECT * FROM applog;
 id | sourceID |            data            |                    event
----+----------+----------------------------+----------------------------------------------
  1 | Loader   | 2011-03-31 11:00:38.494226 | Error: Failed to open source file
  2 | Reporter | 2011-03-31 11:00:38.494226 | Warning: Low disk space on volume /scratch-a
(2 rows)

Enabling all user roles

You can enable all roles available to your user account with SET ROLE ALL:

=> SET ROLE ALL;
SET
=> SHOW ENABLED_ROLES;
     name      |           setting
---------------+------------------------------
 enabled roles | logreader, logwriter
(1 row)

Disabling roles

A user can disable all roles with SET ROLE NONE. This statement disables all roles for the current session, excluding predefined roles:

=> SET ROLE NONE;
=> SHOW ENABLED_ROLES;
     name      | setting
---------------+---------
 enabled roles |
(1 row)