Tutorial: Manage privileges with Terraform

This tutorial walks you through managing roles in Materialize with Terraform. By the end of this tutorial you will:

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

In this scenario, you are a DevOps engineer responsible for managing your Materialize account with code. 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.
You should be familiar with setting up a Terraform project in Materialize.
Have an understanding of permissions in Materialize. These grant resources in this tutorial are modeled after the access-control tutorial.

Step 1. Create Role

A role is a collection of privileges you can apply to users. First, we will create a role resource in Terraform.
```
resource "materialize_role" "dev_role" {
  name = "dev_role"
}
```
We will run Terraform to create this role.
```
terraform apply
```
NOTE: All of the resources in this tutorial can be run with a single terraform apply but we will add and apply resources incrementally to better illustrate grants.

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

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

Step 2. 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 Terraform project we will add a cluster, cluster replica, database, schema and table.

resource "materialize_cluster" "cluster" {
  name = "dev_cluster"
}

resource "materialize_cluster_replica" "cluster_replica" {
  name         = "devr1"
  cluster_name = materialize_cluster.cluster.name
  size         = "25cc"
}

resource "materialize_database" "database" {
  name = "dev_db"
}

resource "materialize_schema" "schema" {
  name          = "schema"
  database_name = materialize_database.database.name
}

resource "materialize_table" "table" {
  name          = "dev_table"
  schema_name   = materialize_schema.schema.name
  database_name = materialize_database.database.name

  column {
    name = "a"
    type = "int"
  }
  column {
    name     = "b"
    type     = "text"
    nullable = true
  }
}

We will apply our Terraform project again to create the object resources.
```
terraform apply
```

Now that our resources exist, we can query their privileges before they have been associated with our role created in step 1.

SELECT name, privileges FROM mz_tables WHERE name = 'dev_table';

name|privileges
dev_table|{u1=arwd/u1,u8=arw/u1}
(1 row)

Currently, the dev_role has no permissions on the table dev_table.

Step 3. Grant privileges on example objects

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

We will add the grant resources to our Terraform project.

resource "materialize_table_grant" "dev_role_table_grant" {
  for_each = toset(["SELECT", "INSERT", "UPDATE"])

  role_name     = materialize_role.dev_role.name
  privilege     = each.value
  database_name = materialize_table.table.database_name
  schema_name   = materialize_table.table.schema_name
  table_name    = materialize_table.table.name
}

NOTE: All of the grant resources are a 1:1 between a specific role, object and privilege. So adding three privileges to the dev_role will require three Terraform resources which can can be accomplished with the for_each meta-argument.

We will run Terraform to grant these privileges on the dev_table table.
```
terraform apply
```

We can now check the privileges on our table again

SELECT name, privileges FROM mz_tables WHERE name = 'dev_table';

name|privileges
dev_table|{u1=arwd/u1,u8=arw/u1}
(1 row)

Now we will include the additional grants for the schema, database and cluster.

resource "materialize_schema_grant" "dev_role_schema_grant_usage" {
  role_name     = materialize_role.dev_role.name
  privilege     = "USAGE"
  database_name = materialize_schema.schema.database_name
  schema_name   = materialize_schema.schema.name
}

resource "materialize_database_grant" "dev_role_database_grant" {
  for_each = toset(["USAGE", "CREATE"])

  role_name     = materialize_role.dev_role.name
  privilege     = each.value
  database_name = materialize_database.database.name
}

resource "materialize_cluster_grant" "dev_role_cluster_grant" {
  for_each = toset(["USAGE", "CREATE"])

  role_name    = materialize_role.dev_role.name
  privilege    = each.value
  cluster_name = materialize_cluster.cluster.name
}

Run Terraform again to grant these additional privileges on the database, schema and cluster.
```
terraform apply
```

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

Include a Terraform resource that grants the role we have created in our Terraform project to a Materialize user.

resource "materialize_role_grant" "dev_role_grant_user" {
  role_name   = materialize_role.dev_role.name
  member_name = "<user>"
}

Apply our Terraform change.
```
terraform apply
```
To review the permissions a roles, 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 5. 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:

resource "materialize_role" "qa_role" {
  name = "qa_role"
}

Apply CREATEDB privileges to the qa_role:

resource "materialize_grant_system_privilege" "qa_role_system_createdb" {
  role_name = materialize_role.qa_role.name
  privilege = "CREATEDB"
}

Create a new qa_db database:

resource "materialize_database" "database" {
  name = "dev_db"
}

Apply USAGE and CREATE privileges to the qa_role role for the new database:

resource "materialize_database_grant" "qa_role_database_grant" {
  for_each = toset(["USAGE", "CREATE"])

  role_name     = materialize_role.qa_role.name
  privilege     = each.value
  database_name = materialize_database.database.name
}

Step 6. 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:

resource "materialize_role_grant" "qa_role_grant_dev_role" {
  role_name   = materialize_role.qa_role.name
  member_name = materialize_role.dev_role.name
}

We will run Terraform to grant these the inherited privileges.
```
terraform apply
```
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 7. Revoke privileges

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

Remove the resource materialize_database_grant.qa_role_database_grant_create from the Terraform project.
We will run Terraform to revoke privileges.
```
terraform apply
```
Because Terraform is responsible for maintaining the state of our project, removing this grant resource and running an apply is the equivalent of running a revoke statement:
```
REVOKE CREATE ON DATABASE dev_table FROM dev_role;
```

Next steps

To destroy the roles and objects you created, you can remove all resources from your Terraform project. Running a terraform apply will DROP all objects.

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