Manage database roles

In Materialize, role-based access control (RBAC) governs access to database objects through privileges granted to database roles.

Disambiguation

Materialize uses roles to manage access control at two levels:

Organization roles, which determines the access to the Console’s administrative features and sets the initial database roles for the user/service account.
Database roles, which controls access to database objects and operations within Materialize.

The focus of this page is on managing database roles. For information on organization roles, see Users and service accounts.

Required privileges for managing roles

NOTE: With their superuser privileges, Organization admins can manage roles (including overriding ownership requirements when granting privileges on various objects).

Role management operations	Required privileges
To create/revoke/grant roles	`CREATEROLE` privileges on the system. WARNING! Roles with the `CREATEROLE` privilege can obtain the privileges of any other role in the system by granting themselves that role. Avoid granting `CREATEROLE` unnecessarily.
To view privileges for a role	None
To grant/revoke role privileges	Ownership of affected objects. `USAGE` privileges on the containing database if the affected object is a schema. `USAGE` privileges on the containing schema if the affected object is namespaced by a schema. superuser status if the privilege is a system privilege.
To alter default privileges	Role membership in `role_name`. `USAGE` privileges on the containing database if `database_name` is specified. `USAGE` privileges on the containing schema if `schema_name` is specified. superuser status if the target_role is `PUBLIC` or ALL ROLES is specified.

Create a role

In Materialize, a database role is created:

Automatically when a user/service account is created:
- When a user account is created, an associated database role with the user email as its name is created.
- When a service account is created, an associated database role with the service account user as its name is created.
Manually to create a role independent of any specific account, usually to define a set of shared privileges that can be granted to other user/service/standalone roles.

To create a new role manually, use the CREATE ROLE statement.

Privilege(s) required to run the command

CREATEROLE privileges on the system.

CREATE ROLE <role_name> [WITH INHERIT];
-- WITH INHERIT behavior is implied and does not need to be specified.

💡 Tip: Role names cannot start with mz_ and pg_ as they are reserved for system roles.

For example, the following creates:

A role for users who need to perform compute/transform operations in the compute/transform.
A role for users who need to manage indexes on the serving cluster(s).
A role for users who need to read results from the serving cluster.

Create a role for users who need to perform compute/transform operations in the compute/transform cluster(s). This role will handle creating views, materialized views, and other transformation objects.

CREATE ROLE view_manager;

Create a role for users who need to manage indexes on the serving cluster(s). This role will handle creating indexes to serve results.

CREATE ROLE serving_index_manager;

Create a role for users who need to read results from the serving cluster.

CREATE ROLE data_reader;

In Materialize, a role is created with inheritance support. With inheritance, when a role is granted to another role (i.e., the target role), the target role inherits privileges (not role attributes and parameters) through the other role. All roles in Materialize are automatically members of PUBLIC. As such, every role includes inherited privileges from PUBLIC.

Once a role is created, you can:

Manage its current privileges (i.e., privileges on existing objects):
- By granting privileges for a role or revoking privileges from a role.
- By granting other roles to the role or revoking roles from the role. Recommended for user account/service account roles.
Manage its future privileges (i.e., privileges on objects created in the future):
- By defining default privileges for objects. With default privileges in place, a role is automatically granted/revoked privileges as new objects are created by others (When an object is created, the creator is granted all applicable privileges for that object automatically).

Disambiguation

Use GRANT|REVOKE ... to modify privileges on existing objects.
Use ALTER DEFAULT PRIVILEGES to ensure that privileges are automatically granted or revoked when new objects of a certain type are created by others. Then, as needed, you can use GRANT|REVOKE <privilege> to adjust those privileges.

Manage current privileges for a role

Example prerequisites

The examples below assume:

The existence of a source_cluster, a compute_cluster, and a serving_cluster. For example:

CREATE CLUSTER source_cluster (SIZE = '25cc');
CREATE CLUSTER compute_cluster (SIZE = '25cc');
CREATE CLUSTER serving_cluster (SIZE = '25cc');

The existence of a mydb database and a sales schema within the mydb database. For example:

CREATE DATABASE IF NOT EXISTS mydb;
CREATE SCHEMA IF NOT EXISTS mydb.sales;

The existence of items, orders, and sales_items tables within the mydb.sales schema. For example:

SET CLUSTER = source_cluster;

SET DATABASE = mydb;
SET SCHEMA  = sales;

CREATE TABLE items(
  item text NOT NULL,
  price numeric(8,4) NOT NULL,
  currency text NOT NULL DEFAULT 'USD'
);

CREATE TABLE orders (
    order_id int NOT NULL,
    order_date timestamp NOT NULL,
    item text NOT NULL,
    quantity int NOT NULL,
    status text NOT NULL
);

CREATE TABLE sales_items (
  week_of date NOT NULL,
  items text[]
);

INSERT INTO items VALUES
('brownie',2.25,'USD'),
('cheesecake',40,'USD'),
('chiffon cake',30,'USD');

INSERT INTO orders VALUES
(1,current_timestamp - (1 * interval '3 day') - (35 * interval '1 minute'),'brownies',12, 'Complete'),
(1,current_timestamp - (1 * interval '3 day') - (35 * interval '1 minute'),'cupcake',12, 'Complete'),
(2,current_timestamp - (1 * interval '3 day') - (15 * interval '1 minute'),'cheesecake',1, 'Complete'),
(3,current_timestamp - (1 * interval '3 day'),'chiffon cake',1, 'Complete'),
(3,current_timestamp - (1 * interval '3 day'),'egg tart',6, 'Complete'),
(3,current_timestamp - (1 * interval '3 day'),'fruit tart',6, 'Complete'),
(4,current_timestamp - (1 * interval '2 day')- (30 * interval '1 minute'),'cupcake',6, 'Shipped'),
(4,current_timestamp - (1 * interval '2 day')- (30 * interval '1 minute'),'cupcake',6, 'Shipped'),
(5,current_timestamp - (1 * interval '2 day'),'chocolate cake',1, 'Processing'),
(6,current_timestamp,'brownie',10, 'Pending'),
(6,current_timestamp,'chocolate cake',1, 'Pending');

INSERT INTO sales_items VALUES
(date_trunc('week', current_timestamp),ARRAY['brownie','chocolate chip cookie','chocolate cake']),
(date_trunc('week', current_timestamp + (1* interval '7 day')), ARRAY['chocolate chip cookie','donut','cupcake']);

View privileges for a role

Privilege(s) required to run the command

No specific privilege is required to run the SHOW PRIVILEGES

To view privileges granted to a role, you can use the SHOW PRIVILEGES command, substituting <role> with the role name (see SHOW PRIVILEGES for the full syntax):

SHOW PRIVILEGES FOR <role>;

NOTE: All roles in Materialize are automatically members of PUBLIC. As such, every role includes inherited privileges from PUBLIC.

For example:

To view privileges for a user, run SHOW PRIVILEGES on the role named after the user’s email (automatically created when the account is activated; i.e., first time the user logs in):

SHOW PRIVILEGES FOR "blue.berry@example.com";

The results show that the role currently has only the privileges inherited through the PUBLIC role.

| grantor           | grantee | database    | schema | name        | object_type | privilege_type |
| ----------------- | ------- | ----------- | ------ | ----------- | ----------- | -------------- |
| admin@example.com | PUBLIC  | mydb        | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | materialize | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | null        | null   | materialize | database    | USAGE          |
| mz_system         | PUBLIC  | null        | null   | quickstart  | cluster     | USAGE          |

To view privileges for a service account, run SHOW PRIVILEGES on the role named after the service account user (automatically created when the account is activated; i.e., first time the service account connects):

SHOW PRIVILEGES FOR sales_report_app;

The results show that the role currently has only the privileges inherited through the PUBLIC role.

| grantor           | grantee | database    | schema | name        | object_type | privilege_type |
| ----------------- | ------- | ----------- | ------ | ----------- | ----------- | -------------- |
| admin@example.com | PUBLIC  | mydb        | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | materialize | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | null        | null   | materialize | database    | USAGE          |
| mz_system         | PUBLIC  | null        | null   | quickstart  | cluster     | USAGE          |

Show the privileges for the view_manager role created in the Create a role section.

SHOW PRIVILEGES FOR view_manager;

The results show that the role currently has only the privileges inherited through the PUBLIC role.

| grantor           | grantee | database    | schema | name        | object_type | privilege_type |
| ----------------- | ------- | ----------- | ------ | ----------- | ----------- | -------------- |
| admin@example.com | PUBLIC  | mydb        | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | materialize | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | null        | null   | materialize | database    | USAGE          |
| mz_system         | PUBLIC  | null        | null   | quickstart  | cluster     | USAGE          |

Show the privileges for the serving_index_manager role created in the Create a role section.

SHOW PRIVILEGES FOR serving_index_manager;

The results show that the role currently has only the privileges inherited through the PUBLIC role.

| grantor           | grantee | database    | schema | name        | object_type | privilege_type |
| ----------------- | ------- | ----------- | ------ | ----------- | ----------- | -------------- |
| admin@example.com | PUBLIC  | mydb        | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | materialize | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | null        | null   | materialize | database    | USAGE          |
| mz_system         | PUBLIC  | null        | null   | quickstart  | cluster     | USAGE          |

Show the privileges for the data_reader role created in the Create a role section.

SHOW PRIVILEGES FOR data_reader;

The results show that the role currently has only the privileges inherited through the PUBLIC role.

| grantor           | grantee | database    | schema | name        | object_type | privilege_type |
| ----------------- | ------- | ----------- | ------ | ----------- | ----------- | -------------- |
| admin@example.com | PUBLIC  | mydb        | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | materialize | null   | public      | schema      | USAGE          |
| mz_system         | PUBLIC  | null        | null   | materialize | database    | USAGE          |
| mz_system         | PUBLIC  | null        | null   | quickstart  | cluster     | USAGE          |

💡 Tip: For the SHOW PRIVILEGES command, you can add a WHERE clause to filter by the return fields; e.g., SHOW PRIVILEGES FOR view_manager WHERE name='quickstart';.

Grant privileges to a role

To grant privileges to a role, use the GRANT PRIVILEGE statement (see GRANT PRIVILEGE for the full syntax)

Privilege(s) required to run the command

Ownership of affected objects.
USAGE privileges on the containing database if the affected object is a schema.
USAGE privileges on the containing schema if the affected object is namespaced by a schema.
superuser status if the privilege is a system privilege.

To override the object ownership requirements to grant privileges, run as an Organization admin.

GRANT <PRIVILEGE> ON <OBJECT_TYPE> <object_name> TO <role>;

When possible, avoid granting privileges directly to individual user or service account roles (which are named after email addresses or service account user). Instead, create reusable, functional roles (e.g., data_reader, view_manager) with well-defined privileges, and grant these roles to the individual user or service account roles. You can also grant functional roles to other functional roles to compose more complex functional roles.

For example, the following grants privileges to the manually created functional roles.

NOTE:

Various SQL operations require additional privileges on related objects, such as:

For objects that use compute resources (e.g., indexes, materialized views, replicas, sources, sinks), access is also required for the associated cluster.
For objects in a schema, access is also required for the schema.

For details on SQL operations and needed privileges, see Appendix: Privileges by command.

The following example grants the view_manager role various privileges to run:

SELECT from currently existing materialized views/views/tables/sources in the mydb.sales schema.
CREATE MATERIALIZED VIEW in the mydb.sales schema on the compute_cluster.
CREATE VIEW if using intermediate views as part of a stacked view definition (i.e., views whose definition depends on other views).

NOTE:

If a query directly references a view or materialized view:

SELECT privileges are required only on the directly referenced view/materialized view. SELECT privileges are not required for the underlying relations referenced in the view/materialized view definition unless those relations themselves are directly referenced in the query.
However, the owner of the view/materialized view (including those with superuser privileges) must have all required SELECT and USAGE privileges to run the view definition regardless of who is selecting from the view/materialized view.

-- To SELECT from currently **existing** relations in `mydb.sales` schema:
-- Need USAGE on schema and cluster
-- Need SELECT on existing materialized views/views/tables/sources
GRANT USAGE ON SCHEMA mydb.sales TO view_manager;
GRANT USAGE ON CLUSTER compute_cluster TO view_manager;
GRANT SELECT ON ALL TABLES IN SCHEMA mydb.sales TO view_manager;
-- ALL TABLES encompasses tables, views, materialized views, and sources,
-- and refers only to currently existing objects.

-- To CREATE materialized views/views:
-- Need CREATE on cluster for materialized views
-- Need CREATE on schema for the materialized views/views
GRANT CREATE ON CLUSTER compute_cluster TO view_manager;
GRANT CREATE ON SCHEMA mydb.sales TO view_manager;

Review the privileges granted to the view_manager role:

SHOW PRIVILEGES FOR view_manager;

The results should reflect the new privileges granted to the view_manager role in addition to those privileges inherited through the PUBLIC role.

   grantor        |   grantee    |  database   | schema |        name       | object_type | privilege_type
------------------+--------------+-------------+--------+-------------------+-------------+----------------
admin@example.com | view_manager | mydb        | sales  | items             | table       | SELECT
admin@example.com | view_manager | mydb        | sales  | orders            | table       | SELECT
admin@example.com | view_manager | mydb        | sales  | sales_items       | table       | SELECT
admin@example.com | view_manager | mydb        |        | sales             | schema      | CREATE
admin@example.com | view_manager | mydb        |        | sales             | schema      | USAGE
admin@example.com | view_manager |             |        | compute_cluster   | cluster     | CREATE
admin@example.com | view_manager |             |        | compute_cluster   | cluster     | USAGE
admin@example.com | PUBLIC       | mydb        |        | public            | schema      | USAGE
mz_system         | PUBLIC       | materialize |        | public            | schema      | USAGE
mz_system         | PUBLIC       |             |        | materialize       | database    | USAGE
mz_system         | PUBLIC       |             |        | quickstart        | cluster     | USAGE

! Important:

The GRANT SELECT ON ALL TABLES IN SCHEMA mydb.sales TO view_manager; statement results in view_manager having SELECT privileges on specific objects, namely the three tables that existed in the mydb.sales schema at the time of the grant. It does not grant SELECT privileges on any tables, views, materialized views, or sources created by others in the schema afterwards.

For new objects created by others, you can either:

Manually grant privileges on new objects; or
Use default privileges to automatically grant privileges on new objects.

The following example grants the serving_index_manager role various privileges to:

CREATE INDEX in the mydb.sales schema on the serving_cluster.
Use the serving_cluster (i.e., USAGE). Although you can create an index without the USAGE, this allows the person creating the index to use the index to verify.

NOTE: In addition to database privileges, to create an index, a role must be the owner of the object on which the index is created.

-- To create an index on an object **owned** by the role:
-- Need CREATE on the cluster.
-- Need CREATE on the schema.
GRANT CREATE ON CLUSTER serving_cluster TO serving_index_manager;
GRANT CREATE ON SCHEMA mydb.sales TO serving_index_manager;

-- Optional.
GRANT USAGE ON CLUSTER serving_cluster TO serving_index_manager;

Review the privileges granted to the serving_index_manager role:

SHOW PRIVILEGES FOR serving_index_manager;

The results should reflect the new privileges granted to the index_manager role in addition to those privileges inherited through the PUBLIC role.

   grantor        |        grantee        |  database   | schema |      name       | object_type | privilege_type
------------------+-----------------------+-------------+--------+-----------------+-------------+----------------
admin@example.com | serving_index_manager | mydb        |        | sales           | schema      | CREATE
admin@example.com | serving_index_manager |             |        | serving_cluster | cluster     | CREATE
admin@example.com | serving_index_manager |             |        | serving_cluster | cluster     | USAGE
admin@example.com | PUBLIC                | mydb        |        | public          | schema      | USAGE
mz_system         | PUBLIC                | materialize |        | public          | schema      | USAGE
mz_system         | PUBLIC                |             |        | materialize     | database    | USAGE
mz_system         | PUBLIC                |             |        | quickstart      | cluster     | USAGE

NOTE:

In addition to database privileges, a role must be the owner of the object on which the index is created. In our examples, view_manager role has privileges to create the various materialized views that will be indexed:

See Grant a role to another role for details and example of granting serving_cluster role to view_manager.
See Change ownership of objects for details and example of changing ownership of objects.

The following example grants the data_reader role privileges to run:

SELECT from all existing tables/materialized views/views/sources in the mydb.sales schema on the serving_cluster.

NOTE:

If a query directly references a view or materialized view:

SELECT privileges are required only on the directly referenced view/materialized view. SELECT privileges are not required for the underlying relations referenced in the view/materialized view definition unless those relations themselves are directly referenced in the query.
However, the owner of the view/materialized view (including those with superuser privileges) must have all required SELECT and USAGE privileges to run the view definition regardless of who is selecting from the view/materialized view.

-- To select from **existing** views/materialized views/tables/sources:
-- Need USAGE on schema and cluster
-- Need SELECT on the materialized views/views/tables/sources
GRANT USAGE ON SCHEMA mydb.sales TO data_reader;
GRANT USAGE ON CLUSTER serving_cluster TO data_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA mydb.sales TO data_reader;
-- For PostgreSQL compatibility, ALL TABLES encompasses tables, views,
-- materialized views, and sources.

Review the privileges granted to the data_reader role:

SHOW PRIVILEGES FOR data_reader;

The results should reflect the new privileges granted to the data_reader role in addition to those privileges inherited through the PUBLIC role.

    grantor       |   grantee   |  database   | schema |        name         | object_type | privilege_type
------------------+-------------+-------------+--------+---------------------+-------------+----------------
admin@example.com | data_reader | mydb        | sales  | items               | table       | SELECT
admin@example.com | data_reader | mydb        | sales  | orders              | table       | SELECT
admin@example.com | data_reader | mydb        | sales  | sales_items         | table       | SELECT
admin@example.com | data_reader | mydb        |        | sales               | schema      | USAGE
admin@example.com | data_reader |             |        | serving_cluster     | cluster     | USAGE
admin@example.com | PUBLIC      | mydb        |        | public              | schema      | USAGE
mz_system         | PUBLIC      | materialize |        | public              | schema      | USAGE
mz_system         | PUBLIC      |             |        | materialize         | database    | USAGE
mz_system         | PUBLIC      |             |        | quickstart          | cluster     | USAGE

! Important:

The GRANT SELECT ON ALL TABLES IN SCHEMA mydb.sales TO data_reader; statement results in data_reader having SELECT privileges on specific objects, namely the three tables that existed in the mydb.sales schema at the time of the grant. It does not grant SELECT privileges on any tables, views, materialized views, or sources created in the schema afterwards by others.

For new objects created by others, you can either:

Manually grant privileges on new objects; or
Use default privileges to automatically grant privileges on new objects.

Grant a role to another role

Once a role is created, you can modify its privileges either:

Directly by granting privileges for a role or revoking privileges from a role.
Indirectly (through inheritance) by granting other roles to the role or revoking roles from the role.

💡 Tip: When possible, avoid granting privileges directly to individual user or service account roles (which are named after email addresses or service account user). Instead, create reusable, functional roles (e.g., data_reader, view_manager) with well-defined privileges, and grant these roles to the individual user or service account roles. You can also grant functional roles to other functional roles to compose more complex functional roles.

To grant a role to another role (where the role can be a user role/service account role/functional role), use the GRANT ROLE statement (see GRANT ROLE for full syntax):

Privilege(s) required to run the command

CREATEROLE privileges on the system.

Organization admin has the required privileges on the system.

GRANT <role> [, <role>...] to <target_role> [, <target_role> ...];

When a role is granted to another role, the target role becomes a member of the other role and inherits the privileges through the other role.

In the following examples,

The functional role view_manager is granted to the user role blue.berry@example.com.
The functional role serving_index_manager is granted to the functional role view_manager.
The functional role data_reader is granted to the service account role sales_report_app.

The following grants the view_manager role to the role associated with the user blue.berry@example.com.

GRANT view_manager TO "blue.berry@example.com";

Review the privileges granted to the blue.berry@example.com role:

SHOW PRIVILEGES FOR "blue.berry@example.com";

The results should include the privileges inherited through the view_manager role in addition to those privileges through the PUBLIC role. If the role had been granted direct privileges, those would also be included.

   grantor        |   grantee    |  database   | schema |        name       | object_type | privilege_type
------------------+--------------+-------------+--------+-------------------+-------------+----------------
admin@example.com | view_manager | mydb        | sales  | items             | table       | SELECT
admin@example.com | view_manager | mydb        | sales  | orders            | table       | SELECT
admin@example.com | view_manager | mydb        | sales  | sales_items       | table       | SELECT
admin@example.com | view_manager | mydb        |        | sales             | schema      | CREATE
admin@example.com | view_manager | mydb        |        | sales             | schema      | USAGE
admin@example.com | view_manager |             |        | compute_cluster   | cluster     | CREATE
admin@example.com | view_manager |             |        | compute_cluster   | cluster     | USAGE
admin@example.com | PUBLIC       | mydb        |        | public            | schema      | USAGE
mz_system         | PUBLIC       | materialize |        | public            | schema      | USAGE
mz_system         | PUBLIC       |             |        | materialize       | database    | USAGE
mz_system         | PUBLIC       |             |        | quickstart        | cluster     | USAGE

After the view_manager role is granted to blue.berry@example.com, blue.berry@example.com can create objects in the mydb.sales schema on the compute_cluster.

-- run as blue.berry@example.com
SET CLUSTER TO compute_cluster;
SET DATABASE TO mydb;
SET SCHEMA TO sales;

-- Create an intermediate view for a stacked materialized view
CREATE VIEW orders_view AS
SELECT o.*,i.price,o.quantity * i.price as subtotal
FROM orders as o
JOIN items as i
ON o.item = i.item;

-- Create a materialized view
CREATE MATERIALIZED VIEW orders_daily_totals AS
SELECT date_trunc('day',order_date) AS order_date,
      sum(subtotal) AS daily_total
FROM orders_view
GROUP BY date_trunc('day',order_date);

-- Select from the materialized view
SELECT * from orders_daily_totals;

In Materialize, a role automatically gets all applicable privileges for an object they create; for example, the creator of a schema gets CREATE and USAGE; the creator of a table gets SELECT, INSERT, UPDATE, and DELETE.

For example, if you show privileges for "blue.berry@example.com" after creating the view and materialized view, you will see that the role has SELECT privileges on the orders_daily_totals and orders_view.

  grantor              |         grantee        |  database   | schema |      name           |     object_type   | privilege_type
-----------------------+------------------------+-------------+--------+---------------------+-------------------+---------------
blue.berry@example.com | blue.berry@example.com | mydb        | sales  | orders_daily_totals | materialized-view | SELECT
blue.berry@example.com | blue.berry@example.com | mydb        | sales  | orders_view         | view              | SELECT
admin@example.com      | view_manager           | mydb        | sales  | items               | table             | SELECT
admin@example.com      | view_manager           | mydb        | sales  | orders              | table             | SELECT
admin@example.com      | view_manager           | mydb        | sales  | sales_items         | table             | SELECT
... -- Rest omitted for brevity

NOTE:

If a query directly references a view or materialized view:

SELECT privileges are required only on the directly referenced view/materialized view. SELECT privileges are not required for the underlying relations referenced in the view/materialized view definition unless those relations themselves are directly referenced in the query.
However, the owner of the view/materialized view (including those with superuser privileges) must have all required SELECT and USAGE privileges to run the view definition regardless of who is selecting from the view/materialized view.

However, with the current privileges, "blue.berry@example.com" cannot select from new views/materialized views created by others in the schema and vice versa. For privileges on new objects created by others, you can either:

Manually grant privileges on new objects; or
Use default privileges to automatically grant privileges on new objects.

The following grants the serving_index_manager role to the functional role view_manager, which already has privileges to create materialized views in mydb.sales schema. This allows members of the view_manager role to create indexes on their objects on the serving_cluster.

GRANT serving_index_manager TO view_manager;

Review the privileges of view_manager as well as "blue.berry@example.com" (a member of view_manager) after the grant.

Review the privileges granted to the view_manager role:

SHOW PRIVILEGES FOR view_manager;

  grantor         |        grantee        |  database   | schema |        name       | object_type | privilege_type
------------------+-----------------------+-------------+--------+-------------------+-------------+----------------
admin@example.com | serving_index_manager | mydb        |        | sales             | schema      | CREATE
admin@example.com | serving_index_manager |             |        | serving_cluster   | cluster     | CREATE
admin@example.com | serving_index_manager |             |        | serving_cluster   | cluster     | USAGE
admin@example.com | view_manager          | mydb        | sales  | items             | table       | SELECT
admin@example.com | view_manager          | mydb        | sales  | orders            | table       | SELECT
admin@example.com | view_manager          | mydb        | sales  | sales_items       | table       | SELECT
admin@example.com | view_manager          | mydb        |        | sales             | schema      | CREATE
admin@example.com | view_manager          | mydb        |        | sales             | schema      | USAGE
admin@example.com | view_manager          |             |        | compute_cluster   | cluster     | CREATE
admin@example.com | view_manager          |             |        | compute_cluster   | cluster     | USAGE
admin@example.com | PUBLIC                | mydb        |        | public            | schema      | USAGE
mz_system         | PUBLIC                | materialize |        | public            | schema      | USAGE
mz_system         | PUBLIC                |             |        | materialize       | database    | USAGE
mz_system         | PUBLIC                |             |        | quickstart        | cluster     | USAGE

Review the privileges for "blue.berry@example.com" (a member of view_manager):

SHOW PRIVILEGES FOR "blue.berry@example.com";

The results include the privileges inherited through the serving_index_manager role in addition to those privileges inherited through the PUBLIC role as well as those granted directly to the role, if any. For example, after being granted the view_manager role, "blue.berry@example.com" created the orders_daily_totals and orders_view. As the creator, "blue.berry@example.com" automatically gets all applicable privileges on the objects they create.

  grantor              |         grantee        |  database   | schema |    name             |    object_type    | privilege_type
-----------------------+------------------------+-------------+--------+---------------------+-------------------+---------------
blue.berry@example.com | blue.berry@example.com | mydb        | sales  | orders_daily_totals | materialized-view | SELECT
blue.berry@example.com | blue.berry@example.com | mydb        | sales  | orders_view         | view              | SELECT
admin@example.com      | serving_index_manager  | mydb        |        | sales               | schema            | CREATE
admin@example.com      | serving_index_manager  |             |        | serving_cluster     | cluster           | CREATE
admin@example.com      | serving_index_manager  |             |        | serving_cluster     | cluster           | USAGE
admin@example.com      | view_manager           | mydb        | sales  | items               | table             | SELECT
admin@example.com      | view_manager           | mydb        | sales  | orders              | table             | SELECT
admin@example.com      | view_manager           | mydb        | sales  | sales_items         | table             | SELECT
admin@example.com      | view_manager           | mydb        |        | sales               | schema            | CREATE
admin@example.com      | view_manager           | mydb        |        | sales               | schema            | USAGE
admin@example.com      | view_manager           |             |        | compute_cluster     | cluster           | CREATE
admin@example.com      | view_manager           |             |        | compute_cluster     | cluster           | USAGE
admin@example.com      | PUBLIC                 | mydb        |        | public              | schema            | USAGE
mz_system              | PUBLIC                 | materialize |        | public              | schema            | USAGE
mz_system              | PUBLIC                 |             |        | materialize         | database          | USAGE
mz_system              | PUBLIC                 |             |        | quickstart          | cluster           | USAGE

To create indexes on an object, in addition to specific CREATE privileges (granted by the serving_index_manager role), the user needs to be the owner of the object.

After the serving_index_manager role is granted to the view_manager role, members of view_manager can create indexes on the serving_cluster for objects that they own. For example, "blue.berry@example.com" can create an index on the orders_daily_totals materialized view.

-- run as "blue.berry@example.com"
SET CLUSTER TO serving_cluster;
SET DATABASE TO mydb;
SET SCHEMA TO sales;

CREATE INDEX ON orders_daily_totals (order_date);

-- If the role has `USAGE` on the `serving_cluster`:
SELECT * from orders_daily_totals;

To allow others in the view_manager role to create indexes, see Change ownership of objects.

The following grants the data_reader role to the service account role sales_report_app.

GRANT data_reader TO sales_report_app;

Review the privileges for sales_report_app after the grant:

SHOW PRIVILEGES FOR sales_report_app;

The results should include the privileges inherited through the data_reader role in addition to those privileges inherited through the PUBLIC role. If the role had been granted direct privileges, those would also be included.

    grantor       |   grantee   |  database   | schema |        name         | object_type | privilege_type
------------------+-------------+-------------+--------+---------------------+-------------+----------------
admin@example.com | data_reader | mydb        | sales  | items               | table       | SELECT
admin@example.com | data_reader | mydb        | sales  | orders              | table       | SELECT
admin@example.com | data_reader | mydb        | sales  | sales_items         | table       | SELECT
admin@example.com | data_reader | mydb        |        | sales               | schema      | USAGE
admin@example.com | data_reader |             |        | serving_cluster     | cluster     | USAGE
admin@example.com | PUBLIC      | mydb        |        | public              | schema      | USAGE
mz_system         | PUBLIC      | materialize |        | public              | schema      | USAGE
mz_system         | PUBLIC      |             |        | materialize         | database    | USAGE
mz_system         | PUBLIC      |             |        | quickstart          | cluster     | USAGE

As the privileges show, after the data_reader role is granted to the sales_report_app service account role, sales_report_app can read from the three tables in the mydb.sales schema on the serving_cluster.

SET CLUSTER TO serving_cluster;
SET DATABASE TO mydb;
SET SCHEMA TO sales;

SELECT * FROM sales_items;

However, sales_report_app cannot read from the new objects in mydb.sales; e.g., orders_daily_totals materialized view and its underlying view orders_view that were created after the SELECT privileges were granted to the data_reader role.

To allow sales_report_app or data_reader to read from the new objects in mydb.sales, you can either:

Manually grant SELECT privileges on the new objects; or
Use default privileges to automatically grant SELECT privileges on new objects.

Revoke privileges from a role

To remove privileges from a role, use the REVOKE <privilege> statement:

Privilege(s) required to run the command

Ownership of affected objects.
USAGE privileges on the containing database if the affected object is a schema.
USAGE privileges on the containing schema if the affected object is namespaced by a schema.
superuser status if the privilege is a system privilege.

REVOKE <PRIVILEGE> ON <OBJECT_TYPE> <object_name> FROM <role>;

Revoke a role from another role

To revoke a role from another role, use the REVOKE <role> statement:

Privilege(s) required to run the command

CREATEROLE privileges on the systems.

REVOKE <role> FROM <target_role>;

For example:

REVOKE data_reader FROM sales_report_app;

! Important: When you revoke a role from another role (user role/service account role/independent role), the target role is no longer a member of the revoked role nor inherits the revoked role’s privileges. However, privileges are cumulative: if the target role inherits the same privilege(s) from another role, the target role still has the privilege(s) through the other role.

Manage future privileges for a role

In Materialize, a role automatically gets all applicable privileges for an object they create/own; for example, the creator of a schema gets CREATE and USAGE; the creator of a table gets SELECT, INSERT, UPDATE, and DELETE. However, for others to access the new object, you can either manually grant privileges on new objects or use default privileges to automatically grant privileges to others as new objects are created.

Default privileges can be specified for a given object type and scoped to:

all future objects of that type;
all future objects of that type within specific databases or schemas;
all future objects of that type created by specific roles (or by all roles PUBLIC).

Default privileges apply only to objects created after these privileges are defined. They do not affect objects that were created before the default privileges were set.

Disambiguation

Use GRANT|REVOKE ... to modify privileges on existing objects.
Use ALTER DEFAULT PRIVILEGES to ensure that privileges are automatically granted or revoked when new objects of a certain type are created by others. Then, as needed, you can use GRANT|REVOKE <privilege> to adjust those privileges.

View default privileges

To view default privileges, you can use the SHOW DEFAULT PRIVILEGES command, substituting <role> with the role name (see SHOW DEFAULT PRIVILEGES for the full syntax):

Privilege(s) required to run the command

No specific privilege is required to run the SHOW DEFAULT PRIVILEGES.

SHOW DEFAULT PRIVILEGES FOR <role>;

For example:

To view default privileges for a user, run SHOW DEFAULT PRIVILEGES on the role named after the user’s email:

SHOW DEFAULT PRIVILEGES FOR "blue.berry@example.com";

The example results show that the default privileges for "blue.berry@example.com" are the default privileges it has as a member of the PUBLIC role.

| object_owner | database | schema | object_type | grantee | privilege_type |
| ------------ | -------- | ------ | ----------- | ------- | -------------- |
| PUBLIC       | null     | null   | type        | PUBLIC  | USAGE          |

The example results show one default privilege. This default privilege grants USAGE privilege to all users (grantee PUBLIC) for new types created by any user (object_owner PUBLIC).

NOTE:

To use a type created in a schema, the USAGE access is required on the containing schema as well.
Default privileges apply only to objects created after these privileges are defined. They do not affect objects that were created before the default privileges were set.

To view default privileges for a service account, run SHOW DEFAULT PRIVILEGES on the role named after the service account user:

SHOW DEFAULT PRIVILEGES FOR sales_report_app;

The example results show that the default privileges for sales_report_app are the default privileges it has as a member of the PUBLIC role.

| object_owner | database | schema | object_type | grantee | privilege_type |
| ------------ | -------- | ------ | ----------- | ------- | -------------- |
| PUBLIC       | null     | null   | type        | PUBLIC  | USAGE          |

The example results show one default privilege. This default privilege grants USAGE privilege to all users (grantee PUBLIC) for new types created by any user (object_owner PUBLIC).

NOTE:

To use a type created in a schema, the USAGE access is required on the containing schema as well.
Default privileges apply only to objects created after these privileges are defined. They do not affect objects that were created before the default privileges were set.

Show the default privileges for the view_manager role created in the Create a role section.

SHOW DEFAULT PRIVILEGES FOR view_manager;

The example results show that the default privileges for view_manager are the default privileges it has as a member of the PUBLIC role.

| object_owner | database | schema | object_type | grantee | privilege_type |
| ------------ | -------- | ------ | ----------- | ------- | -------------- |
| PUBLIC       | null     | null   | type        | PUBLIC  | USAGE          |

The example results show one default privilege. This default privilege grants USAGE privilege to all users (grantee PUBLIC) for new types created by any user (object_owner PUBLIC).

NOTE:

To use a type created in a schema, the USAGE access is required on the containing schema as well.
Default privileges apply only to objects created after these privileges are defined. They do not affect objects that were created before the default privileges were set.

Show the default privileges for the serving_index_manager role created in the Create a role section.

SHOW DEFAULT PRIVILEGES FOR serving_index_manager;

The example results show that the default privileges for serving_index_manager are the default privileges it has as a member of the PUBLIC role.

| object_owner | database | schema | object_type | grantee | privilege_type |
| ------------ | -------- | ------ | ----------- | ------- | -------------- |
| PUBLIC       | null     | null   | type        | PUBLIC  | USAGE          |

The example results show one default privilege. This default privilege grants USAGE privilege to all users (grantee PUBLIC) for new types created by any user (object_owner PUBLIC).

NOTE:

To use a type created in a schema, the USAGE access is required on the containing schema as well.
Default privileges apply only to objects created after these privileges are defined. They do not affect objects that were created before the default privileges were set.

Show the default privileges for the data_reader role created in the Create a role section.

SHOW DEFAULT PRIVILEGES FOR data_reader;

The example results show that the default privileges for data_reader are the default privileges it has as a member of the PUBLIC role.

| object_owner | database | schema | object_type | grantee | privilege_type |
| ------------ | -------- | ------ | ----------- | ------- | -------------- |
| PUBLIC       | null     | null   | type        | PUBLIC  | USAGE          |

The example results show one default privilege. This default privilege grants USAGE privilege to all users (grantee PUBLIC) for new types created by any user (object_owner PUBLIC).

NOTE:

To use a type created in a schema, the USAGE access is required on the containing schema as well.
Default privileges apply only to objects created after these privileges are defined. They do not affect objects that were created before the default privileges were set.

Alter default privileges

To define default privilege for objects created by a role, use the ALTER DEFAULT PRIVILEGES command (see ALTER DEFAULT PRIVILEGES for the full syntax):

Privilege(s) required to run the command

Role membership in role_name.
USAGE privileges on the containing database if database_name is specified.
USAGE privileges on the containing schema if schema_name is specified.
superuser status if the target_role is PUBLIC or ALL ROLES is specified.

ALTER DEFAULT PRIVILEGES FOR ROLE <object_creator>
   IN SCHEMA <schema>    -- Optional. If specified, need USAGE on database and schema.
   GRANT <privilege> ON <object_type> TO <target_role>;

NOTE:

With the exception of the PUBLIC role, the <object_creator> role is not transitive. That is, default privileges that specify a functional role like view_manager as the <object_creator> do not apply to objects created by its members.

However, you can approximate default privileges for a functional role by restricting CREATE privileges for the objects to the desired functional roles (e.g., only view_managers have privileges to create tables in mydb.sales schema) and then specify PUBLIC as the <object_creator>.
As with any other grants, the privileges granted to the <target_role> are inherited by the members of the <target_role>.

The following updates the default privileges for new tables, views, materialized views, and sources created in mydb.sales schema by the blue.berry@example.com role; specifically, grants SELECT privileges on these objects to view_manager and data_reader roles.

-- For new relations created by the `"blue.berry@example.com"` role
-- Grant `SELECT` privileges to the `view_manager` and `data_reader` roles
ALTER DEFAULT PRIVILEGES FOR ROLE "blue.berry@example.com"
IN SCHEMA mydb.sales  -- Optional. If specified, need USAGE on database and schema.
GRANT SELECT ON TABLES TO view_manager, data_reader;
-- `TABLES` refers to tables, views, materialized views, and sources.

Afterwards, if blue.berry@example.com creates a new materialized view in the mydb.sales schema, the view_manager and data_reader roles are automatically granted SELECT privileges on the new object.

-- Run as `blue.berry@example.com`
SET CLUSTER TO compute_cluster;
SET DATABASE TO mydb;
SET SCHEMA TO sales;

-- Create a materialized view
CREATE MATERIALIZED VIEW magic AS
SELECT o.*,i.price,o.quantity * i.price as subtotal
FROM orders as o
JOIN items as i
ON o.item = i.item;

To verify that the default privileges have been automatically granted, you can run SHOW PRIVILEGES:

Verify the privileges for view_manager:

SHOW PRIVILEGES FOR view_manager where grantor = 'blue.berry@example.com';

The results include the SELECT privilege on newly created magic materialized view:

        grantor         |        grantee        |  database   | schema |        name         |    object_type    | privilege_type
------------------------+-----------------------+-------------+--------+---------------------+-------------------+----------------
 blue.berry@example.com | view_manager          | mydb        | sales  | magic               | materialized-view | SELECT

Verify the privileges for data_reader:

SHOW PRIVILEGES FOR data_reader where grantor = 'blue.berry@example.com';

The results include the SELECT privilege on newly created magic materialized view:

        grantor         |   grantee   |  database   | schema |        name         |    object_type    | privilege_type
------------------------+-------------+-------------+--------+---------------------+-------------------+----------------
 blue.berry@example.com | data_reader | mydb        | sales  | magic               | materialized-view | SELECT

Verify the privileges for sales_report_app (a member of the data_reader role):

SHOW PRIVILEGES FOR sales_report_app where grantor = 'blue.berry@example.com';

The results include the SELECT privilege on the magic materialized view it inherits through the data_reader role:

        grantor         |   grantee   |  database   | schema |        name         |    object_type    | privilege_type
------------------------+-------------+-------------+--------+---------------------+-------------------+----------------
 blue.berry@example.com | data_reader | mydb        | sales  | magic               | materialized-view | SELECT

With the exception of the PUBLIC role, the <object_creator> role is not transitive. That is, default privileges that specify a functional role like view_manager as the <object_creator> do not apply to objects created by its members.

To illustrate, the following adds a new member lemon@example.com to the view_manager role and creates a new default privilege, specifying view_manager as the <object_creator>.

GRANT view_manager TO "lemon@example.com";

ALTER DEFAULT PRIVILEGES FOR ROLE view_manager
IN SCHEMA mydb.sales -- Optional. If specified, need USAGE on database and schema.
GRANT INSERT ON TABLES TO view_manager;
-- Although `TABLES` refers to tables, views, materialized views, and
-- sources, the INSERT privilege will only apply to tables.

If lemon@example.com creates a new table only_lemon, the above default INSERT privilege will not apply as the object creator must be view_manager, not a member of view_manager.

-- Run as `lemon@example.com` (a member of `view_manager`)
SET CLUSTER TO compute_cluster;
SET DATABASE TO mydb;
SET SCHEMA TO sales;

CREATE TABLE only_lemon (id INT);

SHOW PRIVILEGES FOR view_manager where name = 'only_lemon';

The SHOW PRIVILEGES FOR view_manager where name = ‘only_lemon’; returns 0 rows.

However, if view_manager is the only role that has CREATE privileges on mydb.sales schema, you can specify PUBLIC as the <object_creator>. Then, the default privilege will apply to all objects created by view_manager and its members.

ALTER DEFAULT PRIVILEGES FOR ROLE PUBLIC
IN SCHEMA mydb.sales
GRANT INSERT ON TABLES TO view_manager;
-- Although `TABLES` refers to tables, views, materialized views, and
-- sources, the `CREATE` privilege will only apply to tables.

If lemon@example.com now creates a new table shared_lemon, the above default INSERT privilege will be granted to view_manager.

-- Run as `lemon@example.com`
SET CLUSTER TO compute_cluster;
SET DATABASE TO mydb;
SET SCHEMA TO sales;

CREATE TABLE shared_lemon (id INT);

To verify that the default privileges have been automatically granted to others, you can run SHOW PRIVILEGES:

Verify the privileges for view_manager:

SHOW PRIVILEGES FOR view_manager where name = 'shared_lemon';

The returned privileges should include the INSERT privilege on the shared_lemon table.

      grantor       |   grantee    | database | schema |     name     | object_type | privilege_type
--------------------+--------------+----------+--------+--------------+-------------+----------------
  lemon@example.com | view_manager | mydb     | sales  | shared_lemon | table       | INSERT

Verify the privileges for blue.berry@example.com:

SHOW PRIVILEGES FOR "blue.berry@example.com" where name = 'shared_lemon';

The returned privileges should include the INSERT privilege on the shared_lemon table.

      grantor       |   grantee    | database | schema |     name     | object_type | privilege_type
--------------------+--------------+----------+--------+--------------+-------------+----------------
  lemon@example.com | view_manager | mydb     | sales  | shared_lemon | table       | INSERT

Show roles in system

To view the roles in the system, use the SHOW ROLES command:

SHOW ROLES [ LIKE <pattern>  | WHERE <condition(s)> ];

For example, to show all roles:

SHOW ROLES;

The results should list all roles:

         name          | comment
-----------------------+---------
blue.berry@example.com |
data_reader            |
lemon@example.com      |
sales_report_app       |
serving_index_manager  |
view_manager           |

Drop a role

To remove a role from the system, use the DROP ROLE command:

Privilege(s) required to run the command

CREATEROLE privileges on the system.

DROP ROLE <role>;

NOTE: You cannot drop a role if it contains any members. Before dropping a role, revoke the role from all its members. See Revoke a role.

Alter role

When granting privileges, the privileges may be scoped to a particular cluster, database, and schema.

You can use ALTER ROLE ... SET to set various configuration parameters, including cluster, database, and schema.

ALTER ROLE <role> SET <config> =|TO <value>;

The following example configures the blue.berry@example.com role to use the compute_cluster cluster, mydb database, and sales schema by default.

ALTER ROLE "blue.berry@example.com" SET CLUSTER = compute_cluster;
ALTER ROLE "blue.berry@example.com" SET DATABASE = mydb;
ALTER ROLE "blue.berry@example.com" SET search_path = sales; -- i.e., schema

These changes will take effect in the next session for the role; the changes have NO effect on the current session.
These configurations are just the defaults. For example, the connection string can specify a different database for the session or the user can issue a SET ... command to override these values for the current session.

In Materialize, when you grant a role to another role (user role/service account role/independent role), the target role inherits only the privileges of the granted role. Role configurations are not inherited. For example, the following example updates the data_reader role to use serving_cluster by default.

ALTER ROLE data_reader SET CLUSTER = serving_cluster;

This change affects only the data_reader role and does not affect roles that have been granted data_reader, such as sales_report_app. That is, after this change:

The default cluster for data_reader is serving_cluster for new sessions.
The default cluster for sales_report_app is not affected.

💡 Tip: Since role configurations are not inherited, setting role configurations for functional roles (i.e., not specific user/service account roles) has limited utility. Instead, configure the specific user/service account roles instead of the functional roles.

Change ownership of objects

Certain commands on an object (such as creating an index on a materialized view or changing owner of an object) require ownership of the object itself (or superuser privileges of an Organization admin).

In Materialize, when a role creates an object, the role becomes the owner of the object and is automatically granted all applicable privileges for the object. To transfer ownership (and privileges) to another role (another user role/service account role/functional role), you can use the ALTER … OWNER TO command:

Privilege(s) required to run the command

Ownership of the object being altered.
Role membership in new_owner.
CREATE privileges on the containing cluster if the object is a cluster replica.
CREATE privileges on the containing database if the object is a schema.
CREATE privileges on the containing schema if the object is namespaced by a schema.

ALTER <object_type> <object_name> OWNER TO <role>;

Before changing the ownership, review the privileges of the current owner (lemon@example.com) and the future owner (view_manage):

Review lemon@example.com"’s privileges on the shared_lemon table.

SHOW PRIVILEGES FOR "lemon@example.com" where name = 'shared_lemon';

As the owner, lemon@example.com has all applicable privileges (INSERT/SELECT/UPDATE/DELETE) for the table as well as the INSERT through its membership in view_manager (from Alter default privileges example).

      grantor      |      grantee      | database | schema |     name     | object_type | privilege_type
-------------------+-------------------+----------+--------+--------------+-------------+----------------
 lemon@example.com | lemon@example.com | mydb     | sales  | shared_lemon | table       | DELETE
 lemon@example.com | lemon@example.com | mydb     | sales  | shared_lemon | table       | INSERT
 lemon@example.com | lemon@example.com | mydb     | sales  | shared_lemon | table       | SELECT
 lemon@example.com | lemon@example.com | mydb     | sales  | shared_lemon | table       | UPDATE
 lemon@example.com | view_manager      | mydb     | sales  | shared_lemon | table       | INSERT

Review view_manager’s privileges on the shared_lemon table.

SHOW PRIVILEGES FOR view_manager where name = 'shared_lemon';

The results show that the view_manager role has INSERT privileges on the shared_lemon table (from Alter default privileges example).

      grantor      |   grantee    | database | schema |     name     | object_type | privilege_type
-------------------+--------------+----------+--------+--------------+-------------+----------------
 lemon@example.com | view_manager | mydb     | sales  | shared_lemon | table       | INSERT

Change the owner of the shared_lemon table to view_manager.

ALTER TABLE mydb.sales.shared_lemon OWNER TO view_manager;

After running the command, review view_manager’s privileges on the shared_lemon table.

SHOW PRIVILEGES FOR view_manager where name = 'shared_lemon';

The results show that the view_manager role has all applicable privileges for a table (INSERT, SELECT, UPDATE, DELETE):

  grantor    |   grantee    | database | schema |     name     | object_type | privilege_type
-------------+--------------+----------+--------+--------------+-------------+----------------
view_manager | view_manager | mydb     | sales  | shared_lemon | table       | DELETE
view_manager | view_manager | mydb     | sales  | shared_lemon | table       | INSERT
view_manager | view_manager | mydb     | sales  | shared_lemon | table       | SELECT
view_manager | view_manager | mydb     | sales  | shared_lemon | table       | UPDATE

Review lemon@example.com’s privileges on the shared_lemon table.

SHOW PRIVILEGES FOR "lemon@example.com" where name = 'shared_lemon';

The results show that lemon@example.com now only has access through view_manager.

   grantor    |   grantee    | database | schema |     name     | object_type | privilege_type
--------------+--------------+----------+--------+--------------+-------------+----------------
 view_manager | view_manager | mydb     | sales  | shared_lemon | table       | DELETE
 view_manager | view_manager | mydb     | sales  | shared_lemon | table       | INSERT
 view_manager | view_manager | mydb     | sales  | shared_lemon | table       | SELECT
 view_manager | view_manager | mydb     | sales  | shared_lemon | table       | UPDATE

Manage database roles

Required privileges for managing roles

Create a role

Manage current privileges for a role

Example prerequisites

View privileges for a role

Grant privileges to a role

Grant a role to another role

Revoke privileges from a role

Revoke a role from another role

Manage future privileges for a role

View default privileges

Alter default privileges

Show roles in system

Drop a role

Alter role

Change ownership of objects

See also