Tutorial: Manage privileges in a non-production cluster

This tutorial walks you through creating a new user and managing roles in Materialize. By the end of this tutorial you will:

Invite a new user in the Materialize Console
Create two new roles in your Materialize
Apply privileges to the new roles
Assign a role to the new user
Modify and remove privileges on roles

In this scenario, you are an administrator on your Materialize account. You recently hired a new developer who needs privileges in a non-production cluster. You will create specific privileges for the new role that align with your business needs and restrict the developer role from having access to your production cluster.

Before you begin

Make sure you have a Materialize account and already have a password to connect with.

Step 1. Invite a new user

Login to the Materialize console and navigate to Account > Account Settings > Users.
Click Invite User and fill in the user information.

The Organization Admin and Organization Member roles refer to Materialize console privileges. Select Organization Member for this example.

Step 2. Create a new role

In the SQL Shell, or your preferred SQL client connected to Materialize, create a new role:
```
CREATE ROLE dev_role;
```
Each role you create has default role attributes that determine how they can interact with Materialize objects. Let’s look at the role attributes of the role you created:
```
SELECT * FROM mz_roles WHERE name = 'dev_role';
```
Your dev_role returns attributes similar to the following:
```
-[ RECORD 1 ]--+------
id             | u8
oid            | 50991
name           | dev_role
inherit        | t
create_role    | f
create_db      | f
create_cluster | f
```
Your id and oid values will look different.

The inherit is the role attribute assigned to a role when it is created.

INHERIT is set to true by default and allows roles to inherit the privileges of roles it is a member of. It is not possible to set this to false.
CREATEROLE is deprecated and will be removed soon. It has no effect.
CREATEDB is deprecated and will be removed soon. It has no effect.
CREATECLUSTER is deprecated and will be removed soon. It has no effect.

Step 3. Create example objects

Your dev_role has the default system-level permissions and needs object-level privileges. RBAC allows you to apply granular privileges to objects in the SQL hierarchy. Let’s create some example objects in the system and determine what privileges the role needs.

In the SQL client connected to Materialize, create a new example cluster to avoid impacting other environments:
```
CREATE CLUSTER dev_cluster (SIZE = '25cc');
```
Change into the example cluster:
```
SET CLUSTER TO dev_cluster;
```

Create a new database, schema, and table:

CREATE DATABASE dev_db;

CREATE SCHEMA dev_db.schema;

CREATE TABLE dev_table (a int, b text NOT NULL);

You just created a set of objects. Your schema object belongs to the database. You can access the cluster from any database. The next step is to grant privileges to your role based on the role needs.

Step 4. Grant privileges to the role

In this example, let’s say your dev_role needs the following permissions:

Read, write, and append privileges on the table
Usage privileges on the schema
All available privileges on the database
Usage and create privileges on the cluster

In your terminal, grant table-level privileges to the dev_role:
```
GRANT SELECT, UPDATE, INSERT ON dev_table TO dev_role;
```
Table objects have four available privileges - read, write, append, and delete. The dev_role doesn’t need delete permissions, so it is not applied in the GRANT statement above.
Grant schema privileges to the dev_role:
```
GRANT USAGE ON SCHEMA dev_db.schema TO dev_role;
```
Schemas have USAGE and CREATE privileges available to grant.
Grant database privileges to the dev_role. You can use the GRANT ALL statement to grant all available privileges on an object.
```
GRANT ALL ON DATABASE dev_db TO dev_role;
```
Grant cluster privileges to the dev_role:
```
GRANT USAGE, CREATE ON CLUSTER dev_cluster TO dev_role;
```
Materialize cluster privileges are unique to the Materialize RBAC structure. To have access to the objects within a cluster, you must also have the same level of access to the cluster itself.

Step 5. Assign the role to a user

The dev_role now has the acceptable privileges it needs. Let’s apply this role to a user in your Materialize organization.

In your terminal, use the GRANT statement to apply a role to your new user:
```
GRANT dev_role TO <new_user>;
```
To review the permissions a role has, you can view the object data:
```
SELECT name, privileges FROM mz_tables WHERE name='dev_table';
```
The output should return the object ID, the level of permission, and the assigning role ID.
```
name|privileges
dev_table|{u1=arwd/u1,u8=arw/u1}
(1 row)
```
In this example, role ID u1 has append, read, write, and delete privileges on the table. Object ID u8 is the dev_role and has append, read, and write privileges, which were assigned by the u1 user.

Step 6. Create a second role

Next, you will create a new role with different privileges to other objects. Then you will apply those privileges to the dev role and alter or drop privileges as needed.

Create a second role your Materialize account:
```
CREATE ROLE qa_role;
```
Apply CREATEDB privileges to the qa_role
```
GRANT CREATEDB ON SYSTEM TO qa_role;
```
This role has permission to create a new database in the Materialize account.
Create a new qa_db database:
```
CREATE DATABASE qa_db;
```
Apply USAGE and CREATE privileges to the qa_role role for the new database:
```
GRANT USAGE, CREATE ON DATABASE qa_db TO qa_role;
```

Step 7. Add inherited privileges

Your dev_role also needs access to qa_db. You can apply these privileges individually or you can choose to grant the dev_role the same permissions as the qa_role.

Add dev_role as a member of qa_role:
```
GRANT qa_role TO dev_role;
```
Roles also inherit all the privileges of the granted role. Making roles members of other roles allows you to manage sets of permissions, rather than granting privileges to roles on an individual basis.
Review the privileges of qa_role and dev_role:
```
SELECT name, privileges FROM mz_databases WHERE name='qa_db';
```
Your output will be similar to the example below:
```
name|privileges
qa_db|{u1=UC/u1,u9=UC/u1}
(1 row)
```
Both dev_role and qa_role have usage and create access to the qa_db. In the next section, you will edit role attributes for these roles and drop privileges.

Step 8. Revoke privileges

You can revoke certain privileges for each role, even if they are inherited from another role.

Let’s say you decide dev_role no longer needs CREATE privileges on the dev_table object. You can revoke that privilege for the role:
```
REVOKE CREATE ON DATABASE dev_table FROM dev_role;
```
Your output should contain the new privileges for dev_role:
```
name|privileges
qa_db|{u1=UC/u1,u8=U/u1,u9=UC/u1}
(1 row)
```
NOTE:
If you need to revoke specific privileges from a role that have been inheritied from another role, you must revoke the role with those privileges.
```
REVOKE qa_role FROM dev_role;
```
In this example, when dev_role inherits from qa_role, dev_role always has all privileges of qa_role. You cannot revoke specific privileges for an inherited role because inheritance gives effective permissions for the entire role.

Next steps

You just altered privileges and attributes on your Materialize roles! Remember to destroy the objects you created for this guide.

Drop the roles you created:
```
DROP ROLE qa_role;
DROP ROLE dev_role;
```

Drop the other objects you created:

DROP CLUSTER dev_cluster CASCADE;
DROP DATABASE dev_db CASCADE;
DROP TABLE dev_table;
DROP DATABASE qa_db CASCADE;

For more information on RBAC in Materialize, review the reference documentation: