Granting Access to Database Roles

A pseudosuperuser or dbadmin user can assign one or more roles to a user or to another role, with GRANT (Role):

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

If you try to grant a role to a user who already has that role, Vertica returns a notice. For example:

=> GRANT commenter to Bob;
NOTICE 4622:  Role "commenter" was already granted to user "Bob"

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

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

GRANT can also assign one or more roles to another role. The following statement grants roles appdata and applogs to role appadmin:

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

Now, any privileges assigned to appdata or applogs are also assigned automatically to users with the appadmin role.

Activating a Role

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

=> SET ROLE appdata; 
SET ROLE

You can also activate a role as part of the user's login: 

=> ALTER USER bob DEFAULT ROLE appdata;
ALTER USER

Checking for Circular References

When you grant one role to another role, Vertica combines the newly granted role's permissions with the existing role's permissions. Vertica also checks for circular references when you grant one role to another. The GRANT ROLE function fails with an error if a circular reference is found. 

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

Granting Administrative Privileges

A superuser 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:

  1. Create the comments table:

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

  2. Create the commenter role:

    => CREATE ROLE commenter;

  3. Grant to commenter INSERT and SELECT privileges on the comments table:

    => GRANT INSERT, SELECT ON comments TO commenter;

  4. Grant the commenter role to user Bob.

    => GRANT commenter TO Bob;
  5. In order to access the role and its associated privileges, Bob enables the newly-granted role for himself:
    => \c - Bob
=> SET ROLE commenter;

  6. Because he has INSERT and SELECT privileges on the comments table, Bob 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
  7. Because Bob's role lacks DELETE privileges, the following statement returns with an error: 
    => DELETE FROM comments WHERE id=1;
ERROR 4367:  Permission denied for relation comments

See Also