Managing Roles

Roles are used for both users and groups, and are global across all databases in the BLUE cluster. For a ROLE to be used as a user, it requires log-in and connect permissions to the relevant databases.

The Managing Roles section describes the following role-related operations:

Creating New Roles (Users)

A user role logging in to the database requires LOGIN permissions.

The following is the syntax for creating a new role:

CREATE ROLE <role_name> ;
GRANT LOGIN TO <role_name>;
GRANT CONNECT ON DATABASE <database_name> TO <role_name> ;

The following is an example of creating a new role:

CREATE  ROLE  new_role_name  ;
GRANT  LOGIN  TO  new_role_name;
GRANT  CONNECT  ON  DATABASE  master TO new_role_name;

A database role may have a number of permissions that define what tasks it can perform, which are assigned using the GRANT command.

Dropping a User

The following is the syntax for dropping a user:

DROP ROLE <role_name> ;

The following is an example of dropping a user:

DROP ROLE  admin_role ;

Altering a User Name

The following is the syntax for altering a user name:

ALTER ROLE <role_name> RENAME TO <new_role_name> ;

The following is an example of altering a user name:

ALTER ROLE admin_role RENAME TO copy_role ;

Altering Public Role Permissions

There is a public role which always exists. Each role is granted to the PUBLIC role (i.e. is a member of the public group), and this cannot be revoked. You can alter the permissions granted to the public role.

The PUBLIC role has USAGE and CREATE permissions on PUBLIC schema by default, therefore, new users can INSERT, DELETE, SELECT, UPDATE and CREATE (databases, schemas, roles, functions, views, and tables) from objects in the PUBLIC schema.

Altering Role Membership (Groups)

Many database administrators find it useful to group user roles together. By grouping users, permissions can be granted to, or revoked from a group with one command. In BLUE, this is done by creating a group role, granting permissions to it, and then assigning users to that group role.

To use a role purely as a group, omit granting it LOGIN permissions.

The CONNECT permission can be given directly to user roles, and/or to the groups they are part of.

CREATE ROLE my_group;

Once the group role exists, you can add user roles (members) using the GRANT command. For example:

-- Add my_user to this group
GRANT my_group TO my_user;

To manage object permissions like databases and tables, you would then grant permissions to the group-level role (see the permissions table below.

All member roles then inherit the permissions from the group. For example:

-- Grant all group users connect permissions
GRANT  CONNECT  ON  DATABASE  a_database  TO  my_group;

-- Grant all permissions on tables in public schema
GRANT  ALL  ON  all  tables  IN  schema  public  TO  my_group;

Removing users and permissions can be done with the REVOKE command:

-- remove my_other_user from this group
REVOKE my_group FROM my_other_user;