Home  /  Manage Materialize

Access control (Role-based)

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.

This section focuses on the database access control. For information on organization roles, see Users and service accounts.

Role-based access control

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

Roles and privileges

In Materialize, a database role is created:

  • Automatically when a user/service account 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.

Managing privileges

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.

Initial privileges

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

By default, the PUBLIC role has the following privileges:

    Privilege Description On database object(s)
    USAGE Permission to use or reference an object.
    • All *.public schemas (e.g., materialize.public);
    • materialize database; and
    • quickstart cluster.
    Object(s) Object owner Default Privilege Granted to Description
    TYPE PUBLIC USAGE PUBLIC When a data type is created (regardless of the owner), all roles are granted the USAGE privilege. However, to use a data type, the role must also have USAGE privilege on the schema containing the type.

    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.

    In addition, all roles have:

    You can modify the privileges of your organization’s PUBLIC role as well as the modify default privileges for PUBLIC.

    Privilege inheritance and modular access control

    In Materialize, when you grant a role to another role (user role/service account role/independent role), the target role inherits privileges through the granted role.

    In general, to grant a user or service account privileges, create roles with the desired privileges and grant these roles to the database role associated with the user/service account email/name. Although you can grant privileges directly to the associated roles, using separate, reusable roles is recommended for better access management.

    With privilege inheritance, you can compose more complex roles by combining existing roles, enabling modular access control. However:

    • Inheritance only applies to role privileges; role attributes and parameters are not inherited.
    • 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.

    Best practices

    Follow the principle of least privilege

    Role-based access control in Materialize should follow the principle of least privilege. Grant only the minimum access necessary for users and service accounts to perform their duties.

    Restrict the assignment of Organization Admin role

    An Organization Admin has superuser privileges in the database. Following the principle of least privilege, only assign Organization Admin role to those users who require superuser privileges.

    Restrict the granting of CREATEROLE privilege

    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.

    Use Reusable Roles for Privilege Assignment

    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.

    See also Manage database roles.

    Audit for unused roles and privileges.

    Audit and remove unused roles periodically.

    See also Show roles in system and Drop a role for more information.

    Back to top ↑