CREATE ROLE

Use CREATE ROLE ¹ to:

When you connect to Materialize, you must specify the name of a valid role in the system.

Syntax

{{% include-syntax file="examples/rbac-cloud/create_roles" example="create-role-syntax" %}}

Note: {{% include-example file="examples/rbac-cloud/create_roles" example="create-role-details" %}} {{< /tab >}} {{< tab "Self-Managed" >}}

{{% include-syntax file="examples/rbac-sm/create_roles" example="create-role-syntax" %}}

Note: {{% include-example file="examples/rbac-sm/create_roles" example="create-role-details" %}} {{< /tab >}} {{< /tabs >}}

You may not specify redundant or conflicting sets of options. For example, Materialize will reject the statement CREATE ROLE ... INHERIT INHERIT.

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/create-role" %}}

In Materialize Cloud and Self-Managed, you can create a functional role:

mzsql

CREATE ROLE db_reader;

mzsql

CREATE ROLE db_reader WITH LOGIN PASSWORD 'password';

You can verify that the role was created by querying the mz_roles system catalog:

mzsql

SELECT name FROM mz_roles;

nofmt

 db_reader
 mz_system
 mz_support

Unlike regular roles, superusers have unrestricted access to all objects in the system and can perform any action on them.

mzsql

CREATE ROLE super_user WITH SUPERUSER LOGIN PASSWORD 'password';

You can verify that the superuser role was created by querying the mz_roles system catalog:

mzsql

SELECT name FROM mz_roles;

nofmt

 db_reader
 mz_system
 mz_support
 super_user

You can also verify that the role has superuser privileges by checking the pg_authid system catalog:

mzsql

SELECT rolsuper FROM pg_authid WHERE rolname = 'super_user';

nofmt

 true