Fine-grained access control

Materialize enforces row-level and column-level access with three pieces it already gives you: role-based access control, entitlement tables that map roles to the rows and columns they may read, and views that join the two. Materialize’s privilege model makes those views a real boundary.

Privileges stop at the view you name. A role holding SELECT on secure.orders reads secure.orders. Reaching the relations underneath requires privileges on those relations.

The result is one view per relation. Onboarding a tenant and widening a profile are both an INSERT.

Before you start

Confirm privilege checks are active, and know where your login roles come from.

SHOW enable_rbac_checks;

CREATE ROLE <user> WITH LOGIN PASSWORD '<password>';

The model

Login role  (alice@acme.example)
     │
     └── GRANT ──► tenant role   (acme_tenant)   ──► row entitlements
                   profile role  (orders_billing) ──► column entitlements
                   reader role   (orders_reader)  ──► SELECT on the exposed view

Every role the session inherits is a key into both entitlement tables.

Two tables, both keyed on role name:

Three schemas keep the layers apart:

Build the layers

Maintain the data once

Keep the expensive work below the filter, so every tenant reads one maintained collection. internal.orders stands in for whatever holds your rows: a source, a table, or an upstream view.

CREATE SCHEMA internal;

CREATE VIEW internal.enriched_orders AS
    SELECT id, customer_id, status, total, billing_email, created_at,
           date_trunc('day', created_at) AS order_day
    FROM internal.orders;

CREATE INDEX enriched_orders_by_customer
    ON internal.enriched_orders (customer_id);

Index the maintained view on the column the entitlement table keys on. That turns the per-session filter into a lookup.

Model entitlements as data

One table per dimension, each keyed and indexed on role name.

CREATE SCHEMA security;

CREATE TABLE security.row_entitlements (
    role_name   text,
    customer_id text
);

CREATE INDEX row_entitlements_by_role
    ON security.row_entitlements (role_name);

INSERT INTO security.row_entitlements VALUES
    ('acme_tenant',   'acme'),
    ('globex_tenant', 'globex');

CREATE TABLE security.column_entitlements (
    role_name   text,
    relation    text,
    column_name text
);

CREATE INDEX column_entitlements_by_role
    ON security.column_entitlements (role_name);

INSERT INTO security.column_entitlements VALUES
    ('orders_billing', 'orders', 'billing_email');

List only the columns you guard. Everything else is projected for every reader.

Resolve the session’s roles

current_role() returns only the role the session connected as. Entitlements usually name a shared tenant role, so the filter has to follow role membership. mz_session_role_memberships() returns the name of every role the session’s role belongs to, directly or through other roles, itself included:

CREATE VIEW security.session_roles AS
    SELECT unnest(mz_session_role_memberships()) AS name;

For a session connected as alice@acme.example, which is a member of acme_tenant, which is a member of orders_reader:

      name
----------------
 acme_tenant
 alice@acme.example
 orders_reader

An entitlement row can name a tenant role or a login role. The same filter handles both.

Filter the rows

Join the maintained relation to the entitlement table and keep the rows whose role the session inherits. This view carries every column, so it stays in security and is never granted.

CREATE VIEW security.entitled_orders AS
    SELECT o.*
    FROM internal.enriched_orders o
    JOIN security.row_entitlements e ON e.customer_id = o.customer_id
    WHERE e.role_name IN (SELECT name FROM security.session_roles);

Entitlement rows, read at query time, decide what comes back.

Mask the columns

Gather the columns the session is entitled to into one array, then guard each sensitive column with a membership test. The array is a single-row aggregate, so the cross join costs one row.

CREATE VIEW security.my_columns AS
    SELECT relation, column_name
    FROM security.column_entitlements
    WHERE role_name IN (SELECT name FROM security.session_roles);

CREATE SCHEMA secure;

CREATE VIEW secure.orders AS
WITH allowed AS (
    SELECT array_agg(column_name) AS cols
    FROM security.my_columns WHERE relation = 'orders'
)
SELECT o.id, o.customer_id, o.status, o.total, o.order_day,
       CASE WHEN 'billing_email' = ANY(a.cols) THEN o.billing_email END
           AS billing_email
FROM security.entitled_orders o CROSS JOIN allowed a;

A guarded column returns its value to entitled readers and NULL to everyone else. One view serves every profile.

Grant the reader role

One view means one grant. Create a reader role, give it schema USAGE and SELECT, and grant it to every tenant role. Column profiles are roles too, carrying entitlement rows instead of privileges.

CREATE ROLE orders_reader;
GRANT USAGE ON SCHEMA secure TO orders_reader;
GRANT SELECT ON secure.orders TO orders_reader;

CREATE ROLE orders_billing;

Grant these roles to tenant roles rather than login roles. A role granted to a tenant role reaches everyone who inherits it.

Onboard a tenant

Onboarding is grants and inserts. The views stay as they are.

CREATE ROLE initech_tenant;
GRANT orders_reader TO initech_tenant;

INSERT INTO security.row_entitlements VALUES ('initech_tenant', 'initech');

GRANT initech_tenant TO "carol@initech.example";

Widening a profile later needs no DDL. Grant the tenant a column profile:

GRANT orders_billing TO initech_tenant;

or entitle that tenant to one more column:

INSERT INTO security.column_entitlements
    VALUES ('initech_tenant', 'orders', 'billing_email');

The final GRANT assumes the login role exists. Where it comes from depends on your deployment.

CREATE ROLE "carol@initech.example" WITH LOGIN PASSWORD '<password>';

Removing access is symmetric. Delete an entitlement row to take away rows or columns, or REVOKE the role to take away the account. Both tables and role membership are read on every query, so these changes apply to sessions that are already open.

Verify the boundary

Check both dimensions before you rely on the pattern. Two readers query the same view. alice@acme.example inherits acme_tenant and orders_reader, while bob@globex.example also inherits orders_billing:

SELECT * FROM secure.orders ORDER BY id;

-- alice@acme.example
 id | customer_id | status  | total |       order_day        | billing_email
----+-------------+---------+-------+------------------------+---------------
  1 | acme        | shipped |   120 | 2026-09-01 00:00:00+00 |
  3 | acme        | open    | 45.25 | 2026-09-03 00:00:00+00 |

-- bob@globex.example
 id | customer_id | status | total |       order_day        |   billing_email
----+-------------+--------+-------+------------------------+-------------------
  2 | globex      | open   |  80.5 | 2026-09-02 00:00:00+00 | ap@globex.example

Each reader gets their own rows, and billing_email carries a value only for the reader entitled to it.

Keep the filter fast

Materialize evaluates secure.orders per query and answers it from indexes maintained underneath, so one view serves every tenant and every profile from shared state. Keep the maintained work below the filter:

• Index the maintained relation on the entitlement key, as enriched_orders_by_customer does above.
• Index both entitlement tables on role_name.
• Build the indexes on the cluster that serves tenant queries, and grant that cluster’s USAGE to the reader role.

Considerations

Role names share one namespace

security.session_roles returns every role the session inherits, including the reader role. An entitlement naming orders_reader therefore reaches every tenant that reads through it. That is how you grant a baseline to everyone, and how you leak one tenant to everyone, so decide which you mean. Keep tenant roles, column profiles, and the reader role distinct, and restrict INSERT on both entitlement tables to a controlled process.

A masked column reads as null

A guarded column returns NULL when the reader has no entitlement, and the column name stays in the result either way. Where that ambiguity matters, publish a companion boolean built from the same array, or give that audience a separate view that omits the column.

See also

• Access control (Cloud)
• Access control (self-managed)
• Appendix: Privileges
• GRANT PRIVILEGE
• GRANT ROLE
• CREATE VIEW