Granting Database Roles

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

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

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