Predefined database roles

• 1: DBADMIN
• 2: PSEUDOSUPERUSER
• 3: DBDUSER
• 4: SYSMONITOR
• 5: UDXDEVELOPER
• 6: MLSUPERVISOR
• 7: PUBLIC

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

=> 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

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

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

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

1 - DBADMIN

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

2 - PSEUDOSUPERUSER

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

3 - DBDUSER

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.

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.

4 - SYSMONITOR

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)

5 - UDXDEVELOPER

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.

6 - MLSUPERVISOR

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

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.

See also

• Model versioning
• REGISTER_MODEL
• CHANGE_MODEL_STATUS

7 - PUBLIC

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