Manage database roles
In Materialize, role-based access control (RBAC) governs access to database objects through privileges granted to database roles.
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
Role management operations | Required privileges |
---|---|
To create/revoke/grant roles |
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 |
|
To alter default privileges |
|
See also Appendix: Privileges by command
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.
CREATEROLE
privileges on the system.
CREATE ROLE <role_name> [WITH INHERIT];
-- WITH INHERIT behavior is implied and does not need to be specified.
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).
-
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 useGRANT|REVOKE <privilege>
to adjust those privileges.
See also:
- For a list of required privileges for specific operations, see Appendix: Privileges by command.
Manage current privileges for a role
Example prerequisites
The examples below assume:
-
The existence of a
source_cluster
, acompute_cluster
, and aserving_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 asales
schema within themydb
database. For example:CREATE DATABASE IF NOT EXISTS mydb; CREATE SCHEMA IF NOT EXISTS mydb.sales;
-
The existence of
items
,orders
, andsales_items
tables within themydb.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
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>;
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 |
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)
- 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>;
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.
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 themydb.sales
schema.CREATE MATERIALIZED VIEW
in themydb.sales
schema on thecompute_cluster
.CREATE VIEW
if using intermediate views as part of a stacked view definition (i.e., views whose definition depends on other views).
SELECT
privileges are required
on both the view/materialized view itself and all underlying relations
referenced in its definition in order to SELECT
from the view/materialized
view. Even users with SELECT
privileges on the underlying relations in the
view/materialized view defintion (including those with superuser privileges)
must have SELECT
privileges on the view/materialized view itself.
-- 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
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 themydb.sales
schema on theserving_cluster
. -
Use the
serving_cluster
(i.e.,USAGE
). Although you can create an index without theUSAGE
, this allows the person creating the index to use the index to verify.
-- 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
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 toview_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 themydb.sales
schema on theserving_cluster
.
SELECT
privileges are required
on both the view/materialized view itself and all underlying relations
referenced in its definition in order to SELECT
from the view/materialized
view. Even users with SELECT
privileges on the underlying relations in the
view/materialized view defintion (including those with superuser privileges)
must have SELECT
privileges on the view/materialized view itself.
-- 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
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.
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):
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 roleblue.berry@example.com
. - The functional role
serving_index_manager
is granted to the functional roleview_manager
. - The functional role
data_reader
is granted to the service account rolesales_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
-- Requires SELECT on the materialized view and its underlying relations.
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
SELECT
privileges are required
on both the view/materialized view itself and all underlying relations
referenced in its definition in order to SELECT
from the view/materialized
view. Even users with SELECT
privileges on the underlying relations in the
view/materialized view defintion (including those with superuser privileges)
must have SELECT
privileges on the view/materialized view itself.
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;
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.
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:
- 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:
CREATEROLE
privileges on the systems.
REVOKE <role> FROM <target_role>;
For example:
REVOKE data_reader FROM sales_report_app;
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
).
-
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 useGRANT|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):
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
).
- 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
).
- 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
).
- 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
).
- 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
).
- 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):
- Role membership in
role_name
. USAGE
privileges on the containing database ifdatabase_name
is specified.USAGE
privileges on the containing schema ifschema_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>;
-
With the exception of the
PUBLIC
role, the<object_creator>
role is not transitive. That is, default privileges that specify a functional role likeview_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., onlyview_managers
have privileges to create tables inmydb.sales
schema) and then specifyPUBLIC
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:
CREATEROLE
privileges on the system.
DROP ROLE <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
isserving_cluster
for new sessions. -
The default cluster for
sales_report_app
is not affected.
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:
- 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