Home  /  Security  /  Patterns

Protect sensitive columns

View as Markdown

To keep sensitive data, such as personally identifiable information (PII), away from users, consider creating bespoke materialized views which exclude those columns. Then, grant users access only to the bespoke materialized views.

WARNING! This pattern is not a strong security barrier. An error in any object upstream of the exposed materialized view, including an intermediate materialized view, reaches readers of the exposed view, and the error message can contain sensitive values. See Limitations.

This guide uses the following setup:

  • Data: restricted.customers (raw data) => restricted.customers_enriched (materialized view) => analytics.customers_public (materialized view that excludes sensitive columns).
  • Roles: admin can read every object. developer can read only analytics.customers_public.

Before you start

Step 1. Create the schemas

Keep the raw data and intermediate objects in one schema, and the objects you expose in another. The developer role never gets USAGE on the first schema.

CREATE SCHEMA restricted;
CREATE SCHEMA analytics;

Step 2. Create the raw data

In production this is usually a source. This guide uses a table:

CREATE TABLE restricted.customers (
  id int, name text, email text, ssn text, region text, plan text
);

INSERT INTO restricted.customers VALUES
  (1, 'Ada Lovelace', 'ada@example.com',   '123-45-6789', 'EU', 'pro'),
  (2, 'Alan Turing',  'alan@example.com',  '987-65-4321', 'EU', 'free'),
  (3, 'Grace Hopper', 'grace@example.com', '555-12-3456', 'US', 'pro');

Step 3. Create the materialized views

Create the intermediate materialized view in restricted, and the exposed one in analytics. The exposed view selects only non-sensitive columns.

CREATE MATERIALIZED VIEW restricted.customers_enriched AS
  SELECT id, name, email, region, plan, right(ssn, 4) AS ssn_last4
  FROM restricted.customers;

CREATE MATERIALIZED VIEW analytics.customers_public AS
  SELECT id, region, plan
  FROM restricted.customers_enriched;

Use a materialized view, not a view, for the exposed object. A query against a materialized view reads only its stored results. A query against a view is optimized together with the view’s definition, so whether an upstream error surfaces can depend on the reader’s query.

Step 4. Create the roles and grant privileges

CREATE ROLE admin;
CREATE ROLE developer;

GRANT USAGE ON SCHEMA restricted, analytics TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA restricted, analytics TO admin;

GRANT USAGE ON SCHEMA analytics TO developer;
GRANT SELECT ON analytics.customers_public TO developer;

Granting privileges via ALL TABLES [...] also applies to sources, views, and materialized views (for the applicable privileges).

It covers only objects that exist when you run it. To cover objects you create later, use ALTER DEFAULT PRIVILEGES.

To run queries, a role also needs USAGE on a cluster. By default, PUBLIC has USAGE on the quickstart cluster. For another cluster, grant it explicitly:

GRANT USAGE ON CLUSTER <cluster_name> TO admin, developer;

Step 5. Grant the roles to users

Grant each functional role to the login roles that need it. In Materialize Cloud, login roles are named after the user’s email address or the service account user.

GRANT admin TO "admin@example.com";
GRANT developer TO "dev@example.com";

Verify

Connect as dev@example.com (a member of developer) and query the exposed materialized view:

SELECT * FROM analytics.customers_public ORDER BY id;

 id | region | plan
----+--------+------
  1 | EU     | pro
  2 | EU     | free
  3 | US     | pro

Querying the restricted objects fails:

SELECT * FROM restricted.customers;

ERROR:  permission denied for SCHEMA "materialize.restricted"
DETAIL:  The 'dev@example.com' role needs USAGE privileges on SCHEMA "materialize.restricted"

Limitations

Upstream errors reach the exposed view

An error in any upstream object, such as a source or an intermediate materialized view, propagates to every materialized view downstream of it. This happens even when the downstream view does not select the column that caused the error, and the error message can include the sensitive value.

For example, suppose the intermediate view casts ssn to a number, and a row with a malformed SSN arrives:

CREATE MATERIALIZED VIEW restricted.customers_parsed AS
  SELECT id, region, plan, replace(ssn, '-', '')::bigint AS ssn_num
  FROM restricted.customers;

CREATE MATERIALIZED VIEW analytics.customers_public_v2 AS
  SELECT id, region, plan FROM restricted.customers_parsed;

GRANT SELECT ON analytics.customers_public_v2 TO developer;

INSERT INTO restricted.customers VALUES
  (4, 'Katherine Johnson', 'kj@example.com', '321-54-98X6', 'US', 'pro');

A member of developer who queries the exposed view sees the SSN in the error:

SELECT * FROM analytics.customers_public_v2;

ERROR:  Evaluation error: invalid input syntax for type bigint: invalid digit found in string: "3215498X6"

To reduce this risk, guard expressions on sensitive columns that can fail. For example, CASE WHEN ssn ~ '^\d{3}-\d{2}-\d{4}$' THEN replace(ssn, '-', '')::bigint END returns NULL for a malformed value instead of an error.

Source errors, such as decoding errors, cannot be guarded this way.

To clean up the example, drop its views and the malformed row:

DROP MATERIALIZED VIEW restricted.customers_parsed CASCADE;
DELETE FROM restricted.customers WHERE id = 4;

Metadata is visible to all roles

Any role can read the system catalog. This includes the names of the restricted objects and their columns (for example, in mz_columns), and the SQL definitions of objects (for example, in mz_materialized_views.create_sql). Keep sensitive values out of object definitions.

Back to top ↑