This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Database roles

A role is a collection of privileges that can be granted to one or more users or other roles.

1: Predefined database roles

1.1: DBADMIN
1.2: PSEUDOSUPERUSER
1.3: DBDUSER
1.4: SYSMONITOR
1.5: UDXDEVELOPER
1.6: MLSUPERVISOR
1.7: PUBLIC

2: Role hierarchy
3: Creating and dropping roles
4: Granting privileges to roles
5: Granting database roles
6: Revoking database roles
7: Enabling roles
8: Enabling roles automatically
9: Viewing user roles

A role is a collection of privileges that can be granted to one or more users or other roles. Roles help you grant and manage sets of privileges for various categories of users, rather than grant those privileges to each user individually.

For example, several users might require administrative privileges. You can grant these privileges to them as follows:

Create an administrator role with CREATE ROLE:
```
CREATE ROLE administrator;
```
Grant the role to the appropriate users.
Grant the appropriate privileges to this role with one or more GRANT statements. You can later add and remove privileges as needed. Changes in role privileges are automatically propagated to the users who have that role.

After users are assigned roles, they can either enable those roles themselves, or you can automatically enable their roles for them.

1 - Predefined database roles

Vertica has the following predefined roles:.

Vertica has the following predefined roles:

Automatic role grants

On installation, Vertica automatically grants and enables predefined roles as follows:

The DBADMIN, PSEUDOSUPERUSER, and DBDUSER roles are irrevocably granted to the dbadmin user. These roles are always enabled for dbadmin, and can never be dropped.
PUBLIC is granted to dbadmin, and to all other users as they are created. This role is always enabled and cannot be dropped or revoked.

Granting predefined roles

After installation, the dbadmin user and users with the PSEUDOSUPERUSER role can grant one or more predefined roles to any user or non-predefined role. For example, the following set of statements creates the userdba role and grants it the predefined role DBADMIN:

=> CREATE ROLE userdba;
CREATE ROLE
=> GRANT DBADMIN TO userdba WITH ADMIN OPTION;
GRANT ROLE

Users and roles that are granted a predefined role can extend that role to other users, if the original GRANT (Role) statement includes WITH ADMIN OPTION. One exception applies: if you grant a user the PSEUDOSUPERUSER role and omit WITH ADMIN OPTION, the grantee can grant any role, including all predefined roles, to other users.

For example, the userdba role was previously granted the DBADMIN role. Because the GRANT statement includes WITH ADMIN OPTION, users who are assigned the userdba role can grant the DBADMIN role to other users:

=> GRANT userdba TO fred;
GRANT ROLE
=> \c - fred
You are now connected as user "fred".
=> SET ROLE userdba;
SET
=> GRANT dbadmin TO alice;
GRANT ROLE

Modifying predefined Roles

Excluding SYSMONITOR, you can grant predefined roles privileges on individual database objects, such as tables or schemas. For example:

=> CREATE SCHEMA s1;
CREATE SCHEMA
=> GRANT ALL ON SCHEMA s1 to PUBLIC;
GRANT PRIVILEGE

You can grant PUBLIC any role, including predefined roles. For example:


=> CREATE ROLE r1;
CREATE ROLE
=> GRANT r1 TO PUBLIC;
GRANT ROLE

You cannot modify any other predefined role by granting another role to it. Attempts to do so return a rollback error:

=> CREATE ROLE r2;
CREATE ROLE
=> GRANT r2 TO PSEUDOSUPERUSER;
ROLLBACK 2347:  Cannot alter predefined role "pseudosuperuser"

1.1 - DBADMIN

The DBADMIN role is a predefined role that is assigned to the dbadmin user on database installation.

The DBADMIN role is a predefined role that is assigned to the dbadmin user on database installation. Thereafter, the dbadmin user and users with the PSEUDOSUPERUSER role can grant any role to any user or non-predefined role.

For example, superuser dbadmin creates role fred and grants fred the DBADMIN role:

=> CREATE USER fred;
CREATE USER
=> GRANT DBADMIN TO fred WITH ADMIN OPTION;
GRANT ROLE

After user fred enables its DBADMIN role, he can exercise his DBADMIN privileges by creating user alice. Because the GRANT statement includes WITH ADMIN OPTION, fred can also grant the DBADMIN role to user alice:


=> \c - fred
You are now connected as user "fred".
=> SET ROLE dbadmin;
SET
CREATE USER alice;
CREATE USER
=> GRANT DBADMIN TO alice;
GRANT ROLE

DBADMIN privileges

The following table lists privileges that are supported for the DBADMIN role:

Create users and roles, and grant them roles and privileges
Create and drop schemas
View all system tables
View and terminate user sessions
Access all data created by any user

1.2 - PSEUDOSUPERUSER

The PSEUDOSUPERUSER role is a predefined role that is automatically assigned to the dbadmin user on database installation.

The PSEUDOSUPERUSER role is a predefined role that is automatically assigned to the dbadmin user on database installation. The dbadmin can grant this role to any user or non-predefined role. Thereafter, PSEUDOSUPERUSER users can grant any role, including predefined roles, to other users.

PSEUDOSUPERUSER privileges

Users with the PSEUDOSUPERUSER role are entitled to complete administrative privileges, which cannot be revoked. Role privileges include:

Bypass all GRANT/REVOKE authorization
Create schemas and tables
Create users and roles, and grant privileges to them
Modify user accounts—for example, set user account's passwords, and lock/unlock accounts.
Create or drop a UDF library and function, or any external procedure

1.3 - DBDUSER

The DBDUSER role is a predefined role that is assigned to the dbadmin user on database installation.

The DBDUSER role is a predefined role that is assigned to the dbadmin user on database installation. The dbadmin and any PSEUDOSUPERUSER can grant this role to any user or non-predefined role. Users who have this role and enable it can call Database Designer functions from the command line.

Note

Non-DBADMIN users with the DBDUSER role cannot run Database Designer through Administration Tools. Only DBADMIN users can run Administration Tools.

Associating DBDUSER with resource pools

Be sure to associate a resource pool with the DBDUSER role, to facilitate resource management when you run Database Designer. Multiple users can run Database Designer concurrently without interfering with each other or exhausting all the cluster resources. Whether you run Database Designer programmatically or with Administration Tools, design execution is generally contained by the user's resource pool, but might spill over into system resource pools for less-intensive tasks.

1.4 - SYSMONITOR

An organization's database administrator may have many responsibilities outside of maintaining Vertica as a DBADMIN user.

An organization's database administrator may have many responsibilities outside of maintaining Vertica as a DBADMIN user. In this case, as the DBADMIN you may want to delegate some Vertica administrative tasks to another Vertica user.

The DBADMIN can assign a delegate the SYSMONITOR role to grant access to system tables without granting full DBADMIN access.

The SYSMONITOR role provides the following privileges.

View all system tables that are marked as monitorable. You can see a list of all the monitorable tables by issuing the statement:
```
=> SELECT * FROM system_tables WHERE is_monitorable='t';
```
If WITH ADMIN OPTION was included when granting SYSMONITOR to the user or role, that user or role can then grant SYSMONITOR privileges to other users and roles.

Grant a SYSMONITOR role

To grant a user or role the SYSMONITOR role, you must be one of the following:

a DBADMIN user
a user assigned the SYSMONITOR who has the ADMIN OPTION

Use the GRANT (Role) SQL statement to assign a user the SYSMONITOR role. This example shows how to grant the SYSMONITOR role to user1 and includes administration privileges by using the WITH ADMIN OPTION parameter. The ADMIN OPTION grants the SYSMONITOR role administrative privileges:

=> GRANT SYSMONITOR TO user1 WITH ADMIN OPTION;

This example shows how to revoke the ADMIN OPTION from the SYSMONITOR role for user1:

=> REVOKE ADMIN OPTION for SYSMONITOR FROM user1;

Use CASCADE to revoke ADMIN OPTION privileges for all users assigned the SYSMONITOR role:

=> REVOKE ADMIN OPTION for SYSMONITOR FROM PUBLIC CASCADE;

Example

This example shows how to:

Create a user
Create a role
Grant SYSMONITOR privileges to the new role
Grant the role to the user

=> CREATE USER user1;
=> CREATE ROLE monitor;
=> GRANT SYSMONITOR TO monitor;
=> GRANT monitor TO user1;

Assign SYSMONITOR privileges

This example uses the user and role created in the Grant SYSMONITOR Role example and shows how to:

Create a table called personal_data
Log in as user1
Grant user1 the monitor role. (You already granted the monitor SYSMONITOR privileges in the Grant a SYSMONITOR Role example.)
Run a SELECT statement as user1

The results of the operations are based on the privilege already granted to user1.

=> CREATE TABLE personal_data (SSN varchar (256));
=> \c -user1;
=> SET ROLE monitor;
=> SELECT COUNT(*) FROM TABLES;
 COUNT
-------
 1
(1 row)

Because you assigned the SYSMONITOR role, user1 can see the number of rows in the Tables system table. In this simple example, there is only one table (personal_data) in the database so the SELECT COUNT returns one row. In actual conditions, the SYSMONITOR role would see all the tables in the database.

Check if a table is accessible by SYSMONITOR

To check if a system table can be accessed by a user assigned the SYSMONITOR role:

=> SELECT table_name, is_monitorable FROM system_tables WHERE table_name='table_name'

For example, the following statement shows that the CURRENT_SESSION system table is accessible by the SYSMONITOR:

=> SELECT table_name, is_monitorable FROM system_tables WHERE table_name='current_session';
   table_name    | is_monitorable
-----------------+----------------
 current_session | t
(1 row)

1.5 - UDXDEVELOPER

The UDXDEVELOPER role is a predefined role that enables users to create and replace user-defined libraries.

The UDXDEVELOPER role is a predefined role that enables users to create and replace user-defined libraries. The dbadmin can grant this role to any user or non-predefined role.

UDXDEVELOPER privileges

Users with the UDXDEVELOPER role can perform the following actions:

CREATE LIBRARY
If library owner or with the DROP privilege:
- CREATE OR REPLACE LIBRARY
- DROP LIBRARY
- ALTER LIBRARY

To use the privileges of this role, you must explicitly enable it using SET ROLE.

Security considerations

A user with the UDXDEVELOPER role can create libraries and, therefore, can install any UDx function in the database. UDx functions run as the Linux user that owns the database, and therefore have access to resources that Vertica has access to.

A poorly-written function can degrade database performance. Give this role only to users you trust to use UDxs responsibly. You can limit the memory that a UDx can consume by running UDxs in fenced mode and by setting the FencedUDxMemoryLimitMB configuration parameter.

1.6 - MLSUPERVISOR

The MLSUPERVISOR role is a predefined role to which all the ML-model management privileges of DBADMIN are delegated.

The MLSUPERVISOR role is a predefined role to which all the ML-model management privileges of DBADMIN are delegated. An MLSUPERVISOR can manage all models in the V_CATALOG.MODELS table on behalf of a dbadmin. However, it cannot import/export models.

In the following example, user alice uses her MLSUPERVISOR privileges to reassign ownership of the model my_model from user bob to user nina:


=> \c - alice
You are now connected as user "alice".

=> SELECT model_name, schema_name, owner_name FROM models;
 model_name  | schema_name | owner_name
-------------+-------------+------------
 my_model    | public      | bob
 mylinearreg | myschema2   | alice
 (2 rows)

=> SET ROLE MLSUPERVISOR;

=> ALTER MODEL my_model OWNER to nina;

=> SELECT model_name, schema_name, owner_name FROM models;
 model_name  | schema_name | owner_name
-------------+-------------+------------
 my_model    | public      | nina
 mylinearreg | myschema2   | alice
 (2 rows)

=> DROP MODEL my_model;

MLSUPERVISOR privileges

The following privileges are supported for the MLSUPERVISOR role:

ML-model management privileges of DBADMIN
Management (USAGE, ALTER, DROP) of all models in V_CATALOG.MODELS

To use the privileges of this role, you must explicitly enable it using SET ROLE. Note that an MLSUPERVISOR cannot import/export models.

1.7 - PUBLIC

The PUBLIC role is a predefined role that is automatically assigned to all new users.

The PUBLIC role is a predefined role that is automatically assigned to all new users. It is always enabled and cannot be dropped or revoked. Use this role to grant all database users the same minimum set of privileges.

Like any role, the PUBLIC role can be granted privileges to individual objects and other roles. The following example grants the PUBLIC role INSERT and SELECT privileges on table publicdata. This enables all users to read data in that table and insert new data:

=> CREATE TABLE publicdata (a INT, b VARCHAR);
CREATE TABLE
=> GRANT INSERT, SELECT ON publicdata TO PUBLIC;
GRANT PRIVILEGE
=> CREATE PROJECTION publicdataproj AS (SELECT * FROM publicdata);
CREATE PROJECTION
=> \c - bob
You are now connected as user "bob".
=> INSERT INTO publicdata VALUES (10, 'Hello World');
OUTPUT
--------
      1
(1 row)

The following example grants PUBLIC the employee role, so all database users have employee privileges:

=> GRANT employee TO public;
GRANT ROLE

Important

The clause WITH ADMIN OPTION is invalid for any GRANT statement that specifies PUBLIC as grantee.

2 - Role hierarchy

By granting roles to other roles, you can build a hierarchy of roles, where roles lower in the hierarchy have a narrow range of privileges, while roles higher in the hierarchy are granted combinations of roles and their privileges. When you organize roles hierarchically, any privileges that you add to lower-level roles are automatically propagated to the roles above them.

Creating hierarchical roles

The following example creates two roles, assigns them privileges, then assigns both roles to another role.

Create table applog:

=> CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));

Create the logreader role and grant it read-only privileges on table applog:

=> CREATE ROLE logreader;
CREATE ROLE
=> GRANT SELECT ON applog TO logreader;
GRANT PRIVILEGE

Create the logwriter role and grant it write privileges on table applog:

=> CREATE ROLE logwriter;
CREATE ROLE
=> GRANT INSERT, UPDATE ON applog to logwriter;
GRANT PRIVILEGE

Create the logadmin role and grant it DELETE privilege on table applog:

=> CREATE ROLE logadmin;
CREATE ROLE
=> GRANT DELETE ON applog to logadmin;
GRANT PRIVILEGE

Grant the logreader and logwriter roles to role logadmin:
```
=> GRANT logreader, logwriter TO logadmin;
```

Create user bob and grant him the logadmin role:

=> CREATE USER bob;
CREATE USER
=> GRANT logadmin TO bob;
GRANT PRIVILEGE

Modify user bob's account so his logadmin role is automatically enabled on login:


=> ALTER USER bob DEFAULT ROLE logadmin;
ALTER USER
=> \c - bob
You are now connected as user "bob".
=> SHOW ENABLED_ROLES;
     name      | setting
---------------+----------
 enabled roles | logadmin
(1 row)

Enabling hierarchical roles

Only roles that are explicitly granted to a user can be enabled for that user. In the previous example, roles logreader or logwriter cannot be enabled for bob. They can only be enabled indirectly, by enabling logadmin.

Hierarchical role grants and WITH ADMIN OPTION

If one or more roles are granted to another role using WITH ADMIN OPTION, then users who are granted the 'higher' role inherit administrative access to the subordinate roles.

For example, you might modify the earlier grants of roles logreader and logwriter to logadmin as follows:

=> GRANT logreader, logwriter TO logadmin WITH ADMIN OPTION;
NOTICE 4617:  Role "logreader" was already granted to role "logadmin"
NOTICE 4617:  Role "logwriter" was already granted to role "logadmin"
GRANT ROLE

User bob , through his logadmin role, is now authorized to grant its two subordinate roles to other users—in this case, role logreader to user Alice:


=> \c - bob;
You are now connected as user "bob".
=> GRANT logreader TO Alice;
GRANT ROLE
=> \c - alice;
You are now connected as user "alice".
=> show available_roles;
      name       |  setting
-----------------+-----------
 available roles | logreader
(1 row)

Note

Because the grant of the logadmin role to bob did not include WITH ADMIN OPTION, he cannot grant that role to alice:


=> \c - bob;
You are now connected as user "bob".
=> GRANT logadmin TO alice;
ROLLBACK 4925:  The role "logadmin" cannot be granted to "alice"

3 - Creating and dropping roles

As a superuser with the DBADMIN or PSEUDOSUPERUSER role, you can create and drop roles with CREATE ROLE and DROP ROLE, respectively.

As a superuser with the DBADMIN or PSEUDOSUPERUSER role, you can create and drop roles with CREATE ROLE and DROP ROLE, respectively.

=> CREATE ROLE administrator;
CREATE ROLE

A new role has no privileges or roles granted to it. Only superusers can grant privileges and access to the role.

Dropping database roles with dependencies

If you try to drop a role that is granted to users or other roles Vertica returns a rollback message:

=> DROP ROLE administrator;
NOTICE:  User Bob depends on Role administrator
ROLLBACK:  DROP ROLE failed due to dependencies
DETAIL:  Cannot drop Role administrator because other objects depend on it
HINT:  Use DROP ROLE ... CASCADE to remove granted roles from the dependent users/roles

To force the drop operation, qualify the DROP ROLE statement with CASCADE:

=> DROP ROLE administrator CASCADE;
DROP ROLE

4 - Granting privileges to roles

You can use GRANT statements to assign privileges to a role, just as you assign privileges to users.

You can use GRANT statements to assign privileges to a role, just as you assign privileges to users. See Database privileges for information about which privileges can be granted.

Granting a privilege to a role immediately affects active user sessions. When you grant a privilege to a role, it becomes immediately available to all users with that role enabled.

The following example creates two roles and assigns them different privileges on the same table.

Create table applog:

=> CREATE TABLE applog (id int, sourceID VARCHAR(32), data TIMESTAMP, event VARCHAR(256));

Create roles logreader and logwriter:

=> CREATE ROLE logreader;
CREATE ROLE
=> CREATE ROLE logwriter;
CREATE ROLE

Grant read-only privileges on applog to logreader, and write privileges to logwriter:

=> GRANT SELECT ON applog TO logreader;
GRANT PRIVILEGE
=> GRANT INSERT ON applog TO logwriter;
GRANT PRIVILEGE

Revoking privileges from roles

Use REVOKE statements to revoke a privilege from a role. Revoking a privilege from a role immediately affects active user sessions. When you revoke a privilege from a role, it is no longer available to users who have the privilege through that role.

For example:

=> REVOKE INSERT ON applog FROM logwriter;
REVOKE PRIVILEGE

5 - Granting database roles

You can assign one or more roles to a user or another role with GRANT (Role):.

You can assign one or more roles to a user or another role with GRANT (Role):

GRANT role[,...] TO grantee[,...] [ WITH ADMIN OPTION ]

For example, you might create three roles—appdata, applogs, and appadmin—and grant appadmin to user bob:

=> CREATE ROLE appdata;
CREATE ROLE
=> CREATE ROLE applogs;
CREATE ROLE
=> CREATE ROLE appadmin;
CREATE ROLE
=> GRANT appadmin TO bob;
GRANT ROLE

Granting roles to another role

GRANT can assign one or more roles to another role. For example, the following GRANT statement grants roles appdata and applogs to role appadmin:

=> GRANT appdata, applogs TO appadmin;
 -- grant to other roles
GRANT ROLE

Because user bob was previously assigned the role appadmin, he now has all privileges that are granted to roles appdata and applogs.

When you grant one role to another role, Vertica checks for circular references. In the previous example, role appdata is assigned to the appadmin role. Thus, subsequent attempts to assign appadmin to appdata fail, returning with the following warning:

=> GRANT appadmin TO appdata;
WARNING:  Circular assignation of roles is not allowed
HINT:  Cannot grant appadmin to appdata
GRANT ROLE

Enabling roles

After granting a role to a user, the role must be enabled. You can enable a role for the current session:


=> SET ROLE appdata;
SET ROLE

You can also enable a role as part of the user's login, by modifying the user's profile with ALTER USER...DEFAULT ROLE:


=> ALTER USER bob DEFAULT ROLE appdata;
ALTER USER

For details, see Enabling roles and Enabling roles automatically.

Granting administrative privileges

You can delegate to non-superusers users administrative access to a role by qualifying the GRANT (Role) statement with the option WITH ADMIN OPTION. Users with administrative access can manage access to the role for other users, including granting them administrative access. In the following example, a superuser grants the appadmin role with administrative privileges to users bob and alice.

=> GRANT appadmin TO bob, alice WITH ADMIN OPTION;
GRANT ROLE

Now, both users can exercise their administrative privileges to grant the appadmin role to other users, or revoke it. For example, user bob can now revoke the appadmin role from user alice:


=> \connect - bob
You are now connected as user "bob".
=> REVOKE appadmin FROM alice;
REVOKE ROLE

Caution

As with all user privilege models, database superusers should be cautious when granting any user a role with administrative privileges. For example, if the database superuser grants two users a role with administrative privileges, either user can revoke that role from the other user.

Example

The following example creates a role called commenter and grants that role to user bob:

Create the comments table:

=> CREATE TABLE comments (id INT, comment VARCHAR);

Create the commenter role:
```
=> CREATE ROLE commenter;
```
Grant to commenter INSERT and SELECT privileges on the comments table:
```
=> GRANT INSERT, SELECT ON comments TO commenter;
```
Grant the commenter role to user bob.
```
=> GRANT commenter TO bob;
```
In order to access the role and its associated privileges, bob enables the newly-granted role for himself:
```
=> \c - bob
=> SET ROLE commenter;
```

Because bob has INSERT and SELECT privileges on the comments table, he can perform the following actions:

=> INSERT INTO comments VALUES (1, 'Hello World');
 OUTPUT
--------
      1
(1 row)
=> SELECT * FROM comments;
 id |   comment
----+-------------
  1 | Hello World
(1 row)
=> COMMIT;
COMMIT

Because bob's role lacks DELETE privileges, the following statement returns an error:


=> DELETE FROM comments WHERE id=1;
ERROR 4367:  Permission denied for relation comments

6 - Revoking database roles

REVOKE (Role) can revoke roles from one or more grantees—that is, from users or roles:.

REVOKE (Role) can revoke roles from one or more grantees—that is, from users or roles:

REVOKE [ ADMIN OPTION FOR ] role[,...] FROM grantee[,...] [ CASCADE ]

For example, the following statement revokes the commenter role from user bob:

=> \c
You are now connected as user "dbadmin".
=> REVOKE commenter FROM bob;
REVOKE ROLE

Revoking administrative access from a role

You can qualify REVOKE (Role) with the clause ADMIN OPTION FOR. This clause revokes from the grantees the authority (granted by an earlier GRANT (Role)...WITH ADMIN OPTION statement) to grant the specified roles to other users or roles. Current roles for the grantees are unaffected.

The following example revokes user Alice's authority to grant and revoke the commenter role:

=> \c
You are now connected as user "dbadmin".
=> REVOKE ADMIN OPTION FOR commenter FROM alice;
REVOKE ROLE

7 - Enabling roles

When you enable a role in a session, you obtain all privileges assigned to that role.

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)

Important

You can also enable user roles on login. For more information, see Enabling roles automatically.

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)

8 - Enabling roles automatically

By default, new users are assigned the PUBLIC role, which is automatically enabled when a new session starts.

By default, new users are assigned the PUBLIC, which is automatically enabled when a new session starts. Typically, other roles are created and users are assigned to them, but these are not automatically enabled. Instead, users must explicitly enable their assigned roles with each new session, with SET ROLE.

You can automatically enable roles for users in two ways:

Enable roles for individual users on login
Enable all roles for all users on login

Enable roles for individual users

After assigning roles to users, you can set one or more default roles for each user by modifying their profiles, with ALTER USER...DEFAULT ROLE. User default roles are automatically enabled at the start of the user session. You should consider setting default roles for users if they typically rely on the privileges of those roles to carry out routine tasks.

Important

ALTER USER...DEFAULT ROLE overwrites previous default role settings.

The following example shows how to set regional_manager as the default role for user LilyCP:

=> \c
You are now connected as user "dbadmin".
=> GRANT regional_manager TO LilyCP;
GRANT ROLE
=> ALTER USER LilyCP DEFAULT ROLE regional_manager;
ALTER USER
=> \c - LilyCP
You are now connected as user "LilyCP".
=> SHOW ENABLED_ROLES;
     name      |     setting
---------------+------------------
 enabled roles | regional_manager
(1 row)

Enable all roles for all users

Configuration parameter EnableAllRolesOnLogin specifies whether to enable all roles for all database users on login. By default, this parameter is set to 0. If set to 1, Vertica enables the roles of all users when they log in to the database.

Clearing default roles

You can clear all default role assignments for a user with ALTER USER...DEFAULT ROLE NONE. For example:

=> ALTER USER fred DEFAULT ROLE NONE;
ALTER USER
=> SELECT user_name, default_roles, all_roles FROM users WHERE user_name = 'fred';
 user_name | default_roles | all_roles
-----------+---------------+-----------
 fred      |               | logreader
(1 row)

9 - Viewing user roles

You can obtain information about roles in three ways:.

You can obtain information about roles in three ways:

Verify specific role assignments with the function HAS_ROLE.
View all available (granted) and enabled roles.
Obtain comprehensive information about roles, the users assigned to them, and the privileges granted to those users and roles by querying system tables ROLES, USERS, AND GRANTS, respectively.

Note
System tables do not show whether a role is available to a user indirectly through other roles. Call HAS_ROLE to obtain that information.

Verifying role assignments

The function HAS_ROLE checks whether a Vertica role is granted to the specified user or role. Non-superusers can use this function to check their own role membership. Superusers can use it to determine role assignments for other users and roles. You can also use Management Console to check role assignments.

In the following example, a dbadmin user checks whether user MikeL is assigned the admnistrator role:

=> \c
You are now connected as user "dbadmin".
=> SELECT HAS_ROLE('MikeL', 'administrator');
 HAS_ROLE
----------
 t
(1 row)

User MikeL checks whether he has the regional_manager role:

=> \c - MikeL
You are now connected as user "MikeL".
=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 f
(1 row)

The dbadmin grants the regional_manager role to the administrator role. On checking again, MikeL verifies that he now has the regional_manager role:

dbadmin=> \c
You are now connected as user "dbadmin".
dbadmin=> GRANT regional_manager to administrator;
GRANT ROLE
dbadmin=> \c - MikeL
You are now connected as user "MikeL".
dbadmin=> SELECT HAS_ROLE('regional_manager');
 HAS_ROLE
----------
 t
(1 row)

Viewing available and enabled roles

SHOW AVAILABLE ROLES lists all roles granted to you:

=> SHOW AVAILABLE ROLES;
      name       |           setting
-----------------+-----------------------------
 available roles | logreader, logwriter
(1 row)

SHOW ENABLED ROLES lists the roles enabled in your session:

=> SHOW ENABLED ROLES;
     name      | setting
---------------+----------
 enabled roles | logreader
(1 row)

Querying system tables

You can query tables ROLES, USERS, AND GRANTS, either separately or joined, to obtain detailed information about user roles, users assigned to those roles, and the privileges granted explicitly to users and implicitly through roles.

The following query on ROLES returns the names of all roles users can access, and the roles granted (assigned) to those roles. An asterisk (*) appended to a role indicates that the user can grant the role to other users:

=> SELECT * FROM roles;
      name       | assigned_roles
-----------------+----------------
 public          |
 dbduser         |
 dbadmin         | dbduser*
 pseudosuperuser | dbadmin*
 logreader       |
 logwriter       |
 logadmin        | logreader, logwriter
(7 rows)

The following query on system table USERS returns all users with the DBADMIN role. An asterisk (*) appended to a role indicates that the user can grant the role to other users:

=> SELECT user_name, is_super_user, default_roles, all_roles FROM v_catalog.users WHERE all_roles ILIKE '%dbadmin%';
 user_name | is_super_user |            default_roles             |              all_roles
-----------+---------------+--------------------------------------+--------------------------------------
 dbadmin   | t             | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 u1        | f             |                                      | dbadmin*
 u2        | f             |                                      | dbadmin
(3 rows)

The following query on system table GRANTS returns the privileges granted to user Jane or role R1. An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users:

=> SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='Jane' OR grantee='R1';
grantor | privileges_description | object_name | object_type  |  grantee
--------+------------------------+-------------+--------------+-----------
dbadmin | USAGE                  | general     | RESOURCEPOOL | Jane
dbadmin |                        | R1          | ROLE         | Jane
dbadmin | USAGE*                 | s1          | SCHEMA       | Jane
dbadmin | USAGE, CREATE*         | s1          | SCHEMA       | R1
(4 rows)

Database roles

1 - Predefined database roles

Automatic role grants

Granting predefined roles

Modifying predefined Roles

1.1 - DBADMIN

DBADMIN privileges

1.2 - PSEUDOSUPERUSER

PSEUDOSUPERUSER privileges

1.3 - DBDUSER

Note

Associating DBDUSER with resource pools

1.4 - SYSMONITOR

Grant a SYSMONITOR role

Example

Assign SYSMONITOR privileges

Check if a table is accessible by SYSMONITOR

1.5 - UDXDEVELOPER

UDXDEVELOPER privileges

Security considerations

1.6 - MLSUPERVISOR

MLSUPERVISOR privileges

See also

1.7 - PUBLIC

Important

2 - Role hierarchy

Creating hierarchical roles

Enabling hierarchical roles

Hierarchical role grants and WITH ADMIN OPTION

Note

3 - Creating and dropping roles

Dropping database roles with dependencies

4 - Granting privileges to roles

Revoking privileges from roles

5 - Granting database roles

Granting roles to another role

Enabling roles

Granting administrative privileges

Caution

Example

See also

6 - Revoking database roles

Revoking administrative access from a role

7 - Enabling roles

Enabling all user roles

Important

Disabling roles

8 - Enabling roles automatically

Enable roles for individual users

Important

Enable all roles for all users

Clearing default roles

9 - Viewing user roles

Note

Verifying role assignments

Viewing available and enabled roles

Querying system tables