ContextQMD
Back to Materialize

Manage database roles

doc/user/content/security/cloud/access-control/manage-roles.md

12319.7 KB
Original Source

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

{{< annotation type="Disambiguation" >}}

{{% include-headless "/headless/rbac-cloud/rbac-intro-disambiguation-cloud" %}}

The focus of this page is on managing database roles. For information on organization roles, see Users and service accounts. {{< /annotation >}}

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

{{</ note >}}

{{% yaml-table data="examples/rbac-cloud/manage_roles_required_privileges" %}}

See also Appendix: Privileges by command

Create a role

{{% include-headless "/headless/rbac-cloud/db-roles" %}}

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

{{< annotation type="Privilege(s) required to run the command" >}}

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

{{</ annotation>}}

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

{{< tip >}} {{% include-headless "/headless/rbac-cloud/role-name-restrictions" %}} {{</ tip >}}

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.

{{< tabs >}} {{< tab "View manager role" >}} {{< include-example file="examples/rbac-cloud/create_roles" example="create-role-view-manager" >}} {{</ tab >}} {{< tab "Serving index manager role" >}} {{< include-example file="examples/rbac-cloud/create_roles" example="create-role-serving-index-manager" >}} {{</ tab >}} {{< tab "Data reader role" >}} {{< include-example file="examples/rbac-cloud/create_roles" example="create-role-data-reader">}} {{</ tab >}} {{</ tabs >}}

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. {{% include-headless "/headless/rbac-cloud/db-roles-public-membership" %}}

{{% include-headless "/headless/rbac-cloud/db-roles-managing-privileges" %}}

{{< annotation type="Disambiguation" >}} {{% include-headless "/headless/rbac-cloud/grant-vs-alter-default-privilege" %}} {{</ annotation >}}

See also:

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:

    {{% include-example file="examples/rbac-cloud/manage_roles_prereq" example="create-clusters" %}}

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

    {{% include-example file="examples/rbac-cloud/manage_roles_prereq" example="create-db-schema" %}}

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

    {{% include-example file="examples/rbac-cloud/manage_roles_prereq" example="create-tables" %}}

View privileges for a role

{{< annotation type="Privilege(s) required to run the command" >}}

No specific privilege is required to run the SHOW PRIVILEGES

{{</ annotation>}}

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):

mzsql
SHOW PRIVILEGES FOR <role>;

{{< note >}} {{% include-headless "/headless/rbac-cloud/db-roles-public-membership" %}} {{</ note >}}

For example:

{{< tabs >}} {{< tab "User">}}

{{< include-example file="examples/rbac-cloud/show_privileges" example="for-user">}}

{{< include-example file="examples/rbac-cloud/show_privileges" example="example-results">}}

{{</ tab >}} {{< tab "Service account role">}}

{{< include-example file="examples/rbac-cloud/show_privileges" example="for-service-account">}} {{< include-example file="examples/rbac-cloud/show_privileges" example="example-results">}} {{</ tab >}} {{< tab "Manually created functional roles">}} {{< tabs >}} {{< tab "View manager role" >}} {{< include-example file="examples/rbac-cloud/show_privileges" example="for-view-manager" >}} {{< include-example file="examples/rbac-cloud/show_privileges" example="example-results">}} {{</ tab >}} {{< tab "Serving index manager role" >}} {{< include-example file="examples/rbac-cloud/show_privileges" example="for-serving-index-manager" >}} {{< include-example file="examples/rbac-cloud/show_privileges" example="example-results">}} {{</ tab >}} {{< tab "Data reader role" >}} {{< include-example file="examples/rbac-cloud/show_privileges" example="for-data-reader">}} {{< include-example file="examples/rbac-cloud/show_privileges" example="example-results">}} {{</ tab >}} {{</ tabs >}}

{{</ tab >}} {{</ tabs >}}

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

{{</ tip >}}

Grant privileges to a role

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

{{< annotation type="Privilege(s) required to run the command" >}}

{{% include-headless "/headless/sql-command-privileges/grant-privilege" %}}

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

{{</ annotation>}}

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

{{% include-headless "/headless/rbac-cloud/use-resusable-roles" %}}

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

{{< note >}} {{% include-headless "/headless/rbac-cloud/privileges-related-objects" %}} {{</ note >}}

{{< tabs >}} {{< tab "View manager role" >}} {{< include-example file="examples/rbac-cloud/grant_privileges" example="to-view-manager" >}}

{{< include-example file="examples/rbac-cloud/grant_privileges" example="to-view-manager-results" >}} {{</ tab >}} {{< tab "Serving index manager role" >}} {{< include-example file="examples/rbac-cloud/grant_privileges" example="to-serving-index-manager" >}}

{{< include-example file="examples/rbac-cloud/grant_privileges" example="to-serving-index-manager-results" >}} {{</ tab >}} {{< tab "Data reader role" >}} {{< include-example file="examples/rbac-cloud/grant_privileges" example="to-data-reader">}}

{{< include-example file="examples/rbac-cloud/grant_privileges" example="to-data-reader-results">}} {{</ tab >}} {{</ tabs >}}

Grant a role to another role

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

{{< tip >}}

{{% include-headless "/headless/rbac-cloud/use-resusable-roles" %}}

{{</ tip >}}

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):

{{< annotation type="Privilege(s) required to run the command" >}}

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

Organization admin has the required privileges on the system. {{</ annotation>}}

mzsql
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 [email protected].
  • 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.

{{< tabs >}} {{< tab "Grant view_manager role" >}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="view_manager" >}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="view_manager-results-show-privileges" >}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="view_manager-results-create-objects" >}}

{{</ tab >}}

{{< tab "Grant serving_index_manager role" >}} {{< include-example file="examples/rbac-cloud/grant_roles" example="serving_index_manager" >}}

Review the privileges of view_manager as well as "[email protected]" (a member of view_manager) after the grant.

{{< tabs >}} {{< tab "Privileges for view_manager">}} {{< include-example file="examples/rbac-cloud/grant_roles" example="serving_index_manager-results-show-privileges-view_manager"

}}

{{</ tab >}} {{< tab "Privileges for [email protected]">}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="serving_index_manager-results-show-privileges-view_manager-member"

}}

{{</ tab >}} {{</ tabs >}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="serving_index_manager-results-show-privileges-results"

}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="serving_index_manager-results-create-index" >}}

{{</ tab >}}

{{< tab "Grant data_reader role" >}} {{< include-example file="examples/rbac-cloud/grant_roles" example="data_reader" >}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="data_reader-results-show-privileges" >}}

{{< include-example file="examples/rbac-cloud/grant_roles" example="data_reader-results-select" >}}

{{</ tab >}} {{</ tabs >}}

Revoke privileges from a role

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

{{< annotation type="Privilege(s) required to run the command" >}}

{{% include-headless "/headless/sql-command-privileges/revoke-privilege" %}}

{{</ annotation>}}

mzsql
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:

{{< annotation type="Privilege(s) required to run the command" >}}

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

{{</ annotation>}}

mzsql
REVOKE <role> FROM <target_role>;

For example:

mzsql
REVOKE data_reader FROM sales_report_app;

{{< important >}} {{% include-headless "/headless/rbac-cloud/revoke-roles-consideration" %}} {{</ important >}}

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

{{% include-headless "/headless/rbac-cloud/default-privilege-clarification" %}}

{{< annotation type="Disambiguation" >}} {{% include-headless "/headless/rbac-cloud/grant-vs-alter-default-privilege" %}}

{{</ annotation >}}

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):

{{< annotation type="Privilege(s) required to run the command" >}}

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

{{</ annotation>}}

mzsql
SHOW DEFAULT PRIVILEGES FOR <role>;

For example:

{{< tabs >}} {{< tab "User">}}

{{< include-example file="examples/rbac-cloud/show_default_privileges" example="for-user">}}

{{</ tab >}} {{< tab "Service account role">}}

{{< include-example file="examples/rbac-cloud/show_default_privileges" example="for-service-account">}}

{{</ tab >}} {{< tab "Manually created functional roles">}} {{< tabs >}} {{< tab "View manager role" >}} {{< include-example file="examples/rbac-cloud/show_default_privileges" example="for-view-manager" >}} {{</ tab >}} {{< tab "Serving index manager role" >}} {{< include-example file="examples/rbac-cloud/show_default_privileges" example="for-serving-index-manager" >}} {{</ tab >}} {{< tab "Data reader role" >}} {{< include-example file="examples/rbac-cloud/show_default_privileges" example="for-data-reader">}} {{</ tab >}} {{</ tabs >}}

{{</ tab >}} {{</ tabs >}}

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):

{{< annotation type="Privilege(s) required to run the command" >}}

{{% include-headless "/headless/sql-command-privileges/alter-default-privileges" %}}

{{</ annotation>}}

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

{{</ note >}}

{{< tabs >}} {{< tab "Specify blue.berry as the object creator" >}} {{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-blueberry" %}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-blueberry-verification-setup" %}}

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

{{< tabs >}}

{{< tab "view_manager">}} {{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-blueberry-verification-view_manager" %}} {{</ tab >}} {{< tab "data_reader" >}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-blueberry-verification-data_reader" %}}

{{</ tab >}} {{< tab "sales_report_app (a member of data_reader)" >}} {{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-blueberry-verification-sales_report_app" %}} {{</ tab >}} {{</ tabs >}}

{{</ tab >}} {{< tab "Specify PUBLIC as the object creator" >}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-view-manager" %}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-view-manager-member" %}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-public" %}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-public-member" %}}

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

{{< tabs >}} {{< tab "view_manager">}}

{{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-public-member-verification-view_manager" %}} {{</ tab >}} {{< tab "[email protected]" >}} {{% include-example file="examples/rbac-cloud/alter_default_privileges" example="for-tables-created-by-public-member-verification-blueberry" %}}

{{</ tab >}} {{</ tabs >}} {{</ tab >}} {{</ tabs >}}

Show roles in system

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

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

{{% include-example file="examples/rbac-cloud/show_roles" example="all-roles" %}}

Drop a role

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

{{< annotation type="Privilege(s) required to run the command" >}}

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

{{</ annotation>}}

mzsql
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. {{</ note >}}

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.

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

{{% include-example file="examples/rbac-cloud/alter_roles" example="alter-roles-configurations" %}}

{{% include-example file="examples/rbac-cloud/alter_roles" example="alter-roles-configs-not-inherited" %}}

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 commands:

{{< annotation type="Privilege(s) required to run the command" >}}

{{% include-headless "/headless/sql-command-privileges/alter-owner" %}}

{{</ annotation>}}

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

Before changing the ownership, review the privileges of the current owner ([email protected]) and the future owner (view_manage):

{{% include-example file="examples/rbac-cloud/alter_owner" example="view-privileges-for-current-owner" %}}

{{% include-example file="examples/rbac-cloud/alter_owner" example="view-privileges-for-future-owner" %}}

{{% include-example file="examples/rbac-cloud/alter_owner" example="table-shared_lemon" %}}

{{% include-example file="examples/rbac-cloud/alter_owner" example="view-privileges-for-new-owner" %}}

{{% include-example file="examples/rbac-cloud/alter_owner" example="view-privileges-for-previous-owner" %}}

See also