Access control (Role-based)

In Materialize, organizations can use role-based access management (RBAC) to help prevent unauthorized or improper access to sensitive objects. For example, organization administrators can use RBAC to:

• Determine which users have read or write privileges for specific objects.

• Control how users interact with clusters by giving them different levels of access to resources.

• Prevent accidental operations from unauthorized users.

• Isolate access to user-facing data from internal organization data.

A role is a collection of privileges you can apply to users. Roles make it easier to assign or revoke privileges on Materialize objects. You can group users into specified roles with different levels of privileges and adjust those privileges to ensure they have the correct level of access to objects.

In Materialize, you have both:

• Predefined Organization roles

• Custom user roles (associated with a specific user/function/etc.)

Organization roles

Each user/service account in Materialize is associated with an organization role:

The default level of access to the database is determined by the organization-level role a user/service account is assigned.

The first user in an organization, and subsequently any user that is assigned Organization Admin, is a database superuser, and has unrestricted access to all resources in a Materialize region. Users that are assigned Organization Member are restricted to a default set of basic privileges that need to be configured and modified via role-based access control (RBAC).

User roles

When an invited user logs in for the first time (and only then), a role with the same name as their e-mail address is created. Administrators can also separately create roles.

Role attributes

Role attributes are actions available to any role you create. Attributes are independent of any other object in Materialize and apply to the entire organization. You can edit these actions when you create the role:

PostgreSQL uses role attributes to determine if a role is allowed to execute certain statements. In Materialize these have all been replaced by system privileges.

Privileges

Privileges are the actions or operations a role is allowed to perform on a specific object. After you create a role, you can grant it the following object-specific privileges in Materialize:

Note that the system catalog uses the abbreviation of the privilege name.

Objects in Materialize have different levels of privileges available to them. Materialize supports the following object type privileges:

Materialize object access is also dependent on cluster privileges. Roles that need access to an object that use compute resources must also have the same level of access to the cluster. Materialize objects that use compute resources are:

• Replicas
• Sources
• Sinks
• Indexes
• Materialized views

Inheritance

Inheritance in RBAC allows you to create roles that inherit privileges from other roles. Inheritance only applies to role privileges; role attributes and parameters are not inherited. Inheriting privileges allows you to minimize the number of roles you have to manage.

Configuring basic RBAC

If you’re just getting started and haven’t yet decided on an RBAC strategy for your organization, we recommend modifying default privileges or — if you just need to move fast and break things — inviting users as administrators.

Modifying default privileges

Every Materialize region has a PUBLIC system role that determines the default privileges available to all other roles. On creation, users are automatically granted membership in PUBLIC, and inherit the privileges assigned to it. By default, members of this role (and therefore all users) have the following privileges:

This means that new, non-administrator users have limited access to resources in a Materialize region, and don’t have the ability to e.g., create new clusters, databases, or schemas. To modify the default privileges available to all other roles in a Materialize region, you can use the ALTER DEFAULT PRIVILEGES command.

# Use SHOW ROLES to list existing roles in the system, which are 1:1 with invited users
SHOW ROLES;

-- Example: grant read-only access to all object types, to all roles in the
-- system via the PUBLIC role For PostgreSQL compatibility reasons, TABLE is
-- the object type for sources, views, tables and materialized views
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT USAGE ON DATABASES TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT USAGE ON SCHEMAS TO PUBLIC;

Inviting users as administrators

The path of least resistance to setting up RBAC is…not setting it up. If you invite all users as Organization Admin, all users will have the superuser role.

Keep in mind that you will be giving all users unrestricted access to all resources in your Materialize region, as well as all features in the Materialize console. While this might be a viable solution when trialing Materialize with a small number of users, it can be hard to revert once you decide to roll out a RBAC strategy for your Materialize organization.

As an alternative, you can approximate the set of privileges of a superuser by instead modifying the default privileges to be wildly permissive:

-- Use SHOW ROLES to list existing roles in the system, which are 1:1 with invited users
SHOW ROLES;

-- Example: approximate full-blown admin access by modifying the default
-- privileges inherited by all roles via the PUBLIC role
GRANT ALL PRIVILEGES ON SCHEMA materialize.public TO PUBLIC;
GRANT ALL PRIVILEGES ON DATABASE materialize TO PUBLIC;
GRANT ALL PRIVILEGES ON SCHEMA materialize.public TO PUBLIC;
GRANT ALL PRIVILEGES ON CLUSTER default TO PUBLIC;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA materialize.public TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TYPES IN SCHEMA materialize.public TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL SECRETS IN SCHEMA materialize.public TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL CONNECTIONS IN SCHEMA materialize.public TO PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA materialize.public GRANT ALL PRIVILEGES ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA materialize.public GRANT ALL PRIVILEGES ON TYPES TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA materialize.public GRANT ALL PRIVILEGES ON SECRETS TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA materialize.public GRANT ALL PRIVILEGES ON CONNECTIONS TO PUBLIC;

It’s important to note that, while GRANT ALL PRIVILEGES applies to all objects that exist when the grant is run, ALTER DEFAULT PRIVILEGES applies to objects created in the future (aka future grants).

Configuring advanced RBAC

There is no one-size-fits-all solution for RBAC: the right strategy will depend on your team size, and existing security policies. Being able to create an unbounded amount of custom roles with custom privileges and structural hierarchy can be overwhelming, so following are examples on how to roll out three common RBAC strategies: user roles, functional roles, and environment roles.

User roles

If your Materialize user base is small and you don’t expect it to grow significantly over time, you can grant and revoke privileges directly to/from user roles.

-- Use SHOW ROLES to list existing roles in the system.
SHOW ROLES;

-- Example: grant usage on a specific database & schema to individual roles
GRANT USAGE ON DATABASE d1 TO "user1@company.com", "user2@company.com";
GRANT USAGE ON SCHEMA d1.s1 TO "user1@company.com", "user2@company.com";
GRANT USAGE ON CLUSTER c1 to "user1@company.com", "user2@company.com";

-- Example: grant individual roles permission to create clusters and databases
GRANT CREATECLUSTER, CREATEDB on system to "user1@company.com";

This strategy allows you to skip setting up a complex hierarchy of roles upfront, at the cost of manual work. As you might expect, this strategy does not scale, so you might want to rethink your RBAC strategy if your user base grows past a reasonable number (say, 10+).

Functional roles

If your Materialize user base is made up of business functions that clearly map to distinct access profiles, a common strategy is to map those functions to roles in your RBAC hierarchy. You can then grant and revoke privileges to/from functional roles, and any user that is a member of that role will inherit them.

As an example, Data Engineers might need a larger scope of permissions to create and evolve the data model, while Data Analysts might only need read permissions to query Materialize using BI tools.

-- Use SHOW ROLES to list existing roles in the system, which are 1:1 with invited users
SHOW ROLES;

-- Step 1: create the data_analyst role
CREATE ROLE data_analyst;

-- Step 2: grant usage on the database & schema to the data_analyst role
GRANT USAGE ON DATABASE d1 TO data_analyst;
GRANT USAGE ON SCHEMA d1.s1 TO data_analyst;
GRANT USAGE ON CLUSTER c1 to data_analyst;

-- Step 3: grant select to all objects in the d1.s1 schema to the data_analyst role.
-- For pre-existing objects in the schema (skip if there are none!)
GRANT SELECT ON ALL TABLES IN SCHEMA d1.s1 TO data_analyst;
-- For future objects created in the schema
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA d1.s1 GRANT SELECT ON TABLES TO data_analyst;

-- Step 4: create the data_engineer role
CREATE ROLE data_engineer;

-- Step 5: grant usage & create on the database & schema to the data_engineer role
GRANT USAGE, CREATE ON DATABASE d1 TO data_engineer;
GRANT USAGE, CREATE ON SCHEMA d1.s1 TO data_engineer;
GRANT USAGE, CREATE ON CLUSTER c1 to data_engineer;

-- Step 6: grant all privileges to all objects in the d1.s1 schema to the data_engineer role
-- For pre-existing objects in the schema (skip if there are none!)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA d1.s1 TO data_engineer;
-- For future objects created in the schema
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN SCHEMA d1.s1 GRANT ALL PRIVILEGES ON TABLES TO data_engineer;

-- Step 7: add member(s) to the respective role
GRANT data_analyst TO "user1@company.com", "user2@company.com";
GRANT data_engineer TO "user3@company.com";

This strategy allows you to more efficiently manage permissions for multiple users at a time, as well as create an intuitive hierarchy based on each functional role’s expected usage of Materialize.

Environment roles

If your Materialize user base frequently switches between multiple development environments, you might want to ensure that users don’t accidentally run commands in the wrong environment e.g., production. You can create an environment-specific role with the NOINHERIT attribute, which prevents other roles from inheriting it. This means that users have to explicitly run e.g., SET ROLE production before being able to run any commands in the specified environment.

-- Step 1: create the dev and prod roles
CREATE ROLE dev;
CREATE ROLE prod NOINHERIT;

-- Step 2: grant usage & create on the respective database & cluster for each role
GRANT ALL PRIVILEGES ON DATABASE d_dev TO dev;
GRANT ALL PRIVILEGES ON DATABASE d_prod TO prod;
GRANT ALL PRIVILEGES ON CLUSTER c_dev TO dev;
GRANT ALL PRIVILEGES ON CLUSTER c_prod TO prod;

-- Step 3: grant all privileges to all objects on the database to the respective roles
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN DATABASE d_dev GRANT ALL PRIVILEGES ON TABLES TO dev;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES IN DATABASE d_prod GRANT ALL PRIVILEGES ON TABLES TO prod;

-- Step 4: add member(s) to the respective role - user1 has both prod & dev access, user2 has only dev access
GRANT dev TO "user1@company.com", "user2@company.com";
GRANT prod TO "user1@company.com";

-- To run queries against the prod database and cluster, user1 will first need to run
SET ROLE prod;

This strategy allows you to add an extra layer of access control to your critical environments, and ensure that any user with privileges to perform destructive actions is performing them intentionally in that specific environment. It’s like sudo for your database!