Materialize/test/sqllogictest/privilege_checks.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

reset-server

# Enable rbac checks.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO true;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_connection_validation_syntax TO true;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE other;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT other TO joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE child;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT joe TO child;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON DATABASE materialize FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM PUBLIC;
----
COMPLETE 0

# CREATE CONNECTION

simple conn=joe,user=joe
CREATE CONNECTION conn TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE CONNECTION conn TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CONNECTION conn TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

simple conn=child,user=child
CREATE CONNECTION conn1 TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# CREATE DATABASE

simple conn=joe,user=joe
CREATE DATABASE d;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATEDB privileges on SYSTEM

simple conn=child,user=child
CREATE DATABASE d;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATEDB privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE DATABASE d;
----
COMPLETE 0

simple conn=child,user=child
CREATE DATABASE d1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM joe;
----
COMPLETE 0

# CREATE CLUSTER

simple conn=joe,user=joe
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'));
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATECLUSTER privileges on SYSTEM

simple conn=child,user=child
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'));
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATECLUSTER privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATECLUSTER ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=child,user=child
CREATE CLUSTER c1 REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATECLUSTER ON SYSTEM FROM joe;
----
COMPLETE 0

# CREATE CLUSTER REPLICA

simple conn=mz_system,user=mz_system
CREATE CLUSTER clus REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER REPLICA clus.r2 SIZE '1';
----
db error: ERROR: must be owner of CLUSTER clus

simple conn=child,user=child
CREATE CLUSTER REPLICA clus.r2 SIZE '1';
----
db error: ERROR: must be owner of CLUSTER clus

simple conn=mz_system,user=mz_system
ALTER CLUSTER clus OWNER TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER REPLICA clus.r2 SIZE '1';
----
COMPLETE 0

simple conn=child,user=child
CREATE CLUSTER REPLICA clus.r3 SIZE '1';
----
COMPLETE 0

# CREATE SCHEMA

simple conn=joe,user=joe
CREATE SCHEMA sch;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'joe' role needs CREATE privileges on DATABASE "materialize"

simple conn=child,user=child
CREATE SCHEMA sch;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'child' role needs CREATE privileges on DATABASE "materialize"

simple conn=mz_system,user=mz_system
GRANT CREATE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SCHEMA sch;
----
COMPLETE 0

simple conn=child,user=child
CREATE SCHEMA sch1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON DATABASE materialize FROM joe;
----
COMPLETE 0

# CREATE ROLE

simple conn=joe,user=joe
CREATE ROLE r;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATEROLE privileges on SYSTEM

simple conn=child,user=child
CREATE ROLE r;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATEROLE privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE ROLE r;
----
COMPLETE 0

simple conn=child,user=child
CREATE ROLE r1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEROLE ON SYSTEM FROM joe;
----
COMPLETE 0

# ALTER ROLE

simple conn=joe,user=joe
ALTER ROLE r INHERIT;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATEROLE privileges on SYSTEM

simple conn=child,user=child
ALTER ROLE r1 INHERIT;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATEROLE privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER ROLE r INHERIT;
----
COMPLETE 0

simple conn=child,user=child
ALTER ROLE r1 INHERIT;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEROLE ON SYSTEM FROM joe;
----
COMPLETE 0

# CREATE SOURCE

simple conn=joe,user=joe
CREATE SOURCE s1 FROM LOAD GENERATOR COUNTER;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE SOURCE s1 FROM LOAD GENERATOR COUNTER;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE s1 FROM LOAD GENERATOR COUNTER;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs CREATE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE s1 FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE CLUSTER source_cluster REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATECLUSTER ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE s2 IN CLUSTER source_cluster FROM LOAD GENERATOR COUNTER;
----
db error: ERROR: permission denied for CLUSTER "source_cluster"
DETAIL: The 'joe' role needs CREATE privileges on CLUSTER "source_cluster"

simple conn=child,user=child
CREATE SOURCE s2 IN CLUSTER source_cluster FROM LOAD GENERATOR COUNTER;
----
db error: ERROR: permission denied for CLUSTER "source_cluster"
DETAIL: The 'child' role needs CREATE privileges on CLUSTER "source_cluster"

simple conn=joe,user=joe
CREATE SOURCE webhook_text_a IN CLUSTER source_cluster FROM WEBHOOK BODY FORMAT TEXT;
----
db error: ERROR: permission denied for CLUSTER "source_cluster"
DETAIL: The 'joe' role needs CREATE privileges on CLUSTER "source_cluster"

simple conn=child,user=child
CREATE SOURCE webhook_text_b IN CLUSTER source_cluster FROM WEBHOOK BODY FORMAT TEXT;
----
db error: ERROR: permission denied for CLUSTER "source_cluster"
DETAIL: The 'child' role needs CREATE privileges on CLUSTER "source_cluster"

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER source_cluster TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE s2 IN CLUSTER source_cluster FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

simple conn=child,user=child
CREATE SOURCE s4 IN CLUSTER source_cluster FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE webhook_text_a IN CLUSTER source_cluster FROM WEBHOOK BODY FORMAT TEXT;
----
COMPLETE 0

simple conn=child,user=child
CREATE SOURCE webhook_text_b IN CLUSTER source_cluster FROM WEBHOOK BODY FORMAT TEXT;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SECRET webhook_key AS 'shared_key';
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SECRET webhook_key FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE webhook_text_with_secret IN CLUSTER source_cluster FROM WEBHOOK
  BODY FORMAT TEXT
  CHECK (
    WITH ( BODY, SECRET webhook_key )
    body = webhook_key
  )
----
db error: ERROR: permission denied for SECRET "materialize.public.webhook_key"
DETAIL: The 'joe' role needs USAGE privileges on SECRET "materialize.public.webhook_key"

simple conn=child,user=child
CREATE SOURCE webhook_text_with_secret1 IN CLUSTER source_cluster FROM WEBHOOK
  BODY FORMAT TEXT
  CHECK (
    WITH ( BODY, SECRET webhook_key )
    body = webhook_key
  )
----
db error: ERROR: permission denied for SECRET "materialize.public.webhook_key"
DETAIL: The 'child' role needs USAGE privileges on SECRET "materialize.public.webhook_key"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SECRET webhook_key TO child;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE webhook_text_with_secret IN CLUSTER source_cluster FROM WEBHOOK
  BODY FORMAT TEXT
  CHECK (
    WITH ( BODY, SECRET webhook_key )
    body = webhook_key
  )
----
db error: ERROR: permission denied for SECRET "materialize.public.webhook_key"
DETAIL: The 'joe' role needs USAGE privileges on SECRET "materialize.public.webhook_key"

simple conn=child,user=child
CREATE SOURCE webhook_text_with_secret1 IN CLUSTER source_cluster FROM WEBHOOK
  BODY FORMAT TEXT
  CHECK (
    WITH ( BODY, SECRET webhook_key )
    body = webhook_key
  )
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON SECRET webhook_key TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE webhook_text_with_secret IN CLUSTER source_cluster FROM WEBHOOK
  BODY FORMAT TEXT
  CHECK (
    WITH ( BODY, SECRET webhook_key )
    body = webhook_key
  )
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SOURCE webhook_text IN CLUSTER source_cluster FROM WEBHOOK BODY FORMAT TEXT;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE SOURCE webhook_text IN CLUSTER source_cluster FROM WEBHOOK BODY FORMAT TEXT;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
REVOKE CREATECLUSTER ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON CLUSTER source_cluster FROM joe;
----
COMPLETE 0

# mz_support reading from progress source

simple conn=mz_system,user=mz_system
CREATE SOURCE s IN CLUSTER quickstart FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

simple conn=mz_support,user=mz_support
SET CLUSTER TO "quickstart";
----
COMPLETE 0

simple conn=mz_support,user=mz_support
SELECT * FROM s_progress LIMIT 0;
----
COMPLETE 0

simple conn=mz_support,user=mz_support
SET CLUSTER TO mz_catalog_server;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP SOURCE s;
----
COMPLETE 0

# CREATE SECRET

simple conn=joe,user=joe
CREATE SECRET se AS decode('c2VjcmV0Cg==', 'base64');
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE SECRET se AS decode('c2VjcmV0Cg==', 'base64');
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SECRET se AS decode('c2VjcmV0Cg==', 'base64');
----
COMPLETE 0

simple conn=child,user=child
CREATE SECRET se1 AS decode('c2VjcmV0Cg==', 'base64');
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# CREATE TYPE

simple conn=joe,user=joe
CREATE TYPE ty AS (a text);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE TYPE ty AS (a text);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TYPE ty AS (a text);
----
COMPLETE 0

simple conn=child,user=child
CREATE TYPE ty1 AS (a text);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# CREATE TABLE

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TABLE t (a ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE TABLE t (a ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TABLE t (a ty);
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'joe' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=child,user=child
CREATE TABLE t (a ty);
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'child' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TABLE t (a ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE TABLE t (a ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TABLE t (a ty);
----
COMPLETE 0

simple conn=child,user=child
CREATE TABLE t1 (a ty);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# CREATE VIEW

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE VIEW v AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE VIEW v AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE VIEW v AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'joe' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=child,user=child
CREATE VIEW v AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'child' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE VIEW v AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE VIEW v AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE VIEW v AS SELECT ROW(1)::ty;
----
COMPLETE 0

simple conn=child,user=child
CREATE VIEW v1 AS SELECT ROW(1)::ty;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# CREATE MATERIALIZED VIEW

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'joe' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=child,user=child
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'child' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs CREATE privileges on CLUSTER "quickstart"

simple conn=child,user=child
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs CREATE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE MATERIALIZED VIEW mv AS SELECT ROW(1)::ty;
----
COMPLETE 0

simple conn=child,user=child
CREATE MATERIALIZED VIEW mv1 AS SELECT ROW(1)::ty;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

# CREATE INDEX

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'joe' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=child,user=child
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for TYPE "materialize.public.ty"
DETAIL: The 'child' role needs USAGE privileges on TYPE "materialize.public.ty"

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs CREATE privileges on CLUSTER "quickstart"

simple conn=child,user=child
CREATE INDEX i ON t (a::ty);
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs CREATE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE INDEX i ON t (a::ty);
----
COMPLETE 0

simple conn=child,user=child
CREATE INDEX i1 ON t (a::ty);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

# DROP CONNECTION

simple conn=joe,user=joe
DROP CONNECTION conn;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP CONNECTION conn1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP CONNECTION conn;
----
COMPLETE 0

simple conn=child,user=child
DROP CONNECTION conn1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP DATABASE

simple conn=joe,user=joe
DROP DATABASE d;
----
COMPLETE 0

simple conn=child,user=child
DROP DATABASE d1;
----
COMPLETE 0

# DROP CLUSTER

simple conn=joe,user=joe
DROP CLUSTER c;
----
COMPLETE 0

simple conn=child,user=child
DROP CLUSTER c1;
----
COMPLETE 0

# DROP SCHEMA

simple conn=joe,user=joe
DROP SCHEMA sch;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'joe' role needs USAGE privileges on DATABASE "materialize"

simple conn=child,user=child
DROP SCHEMA sch1;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'child' role needs USAGE privileges on DATABASE "materialize"

simple conn=mz_system,user=mz_system
GRANT USAGE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP SCHEMA sch;
----
COMPLETE 0

simple conn=child,user=child
DROP SCHEMA sch1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON DATABASE materialize FROM joe;
----
COMPLETE 0

# DROP CLUSTER REPLICA

simple conn=joe,user=joe
DROP CLUSTER REPLICA clus.r2;
----
COMPLETE 0

simple conn=child,user=child
DROP CLUSTER REPLICA clus.r3;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP CLUSTER clus;
----
COMPLETE 0

# DROP ROLE

simple conn=joe,user=joe
DROP ROLE r;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATEROLE privileges on SYSTEM

simple conn=child,user=child
DROP ROLE r1;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATEROLE privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP ROLE r;
----
COMPLETE 0

simple conn=child,user=child
DROP ROLE r1;
----
COMPLETE 0

# DROP SOURCE

simple conn=joe,user=joe
DROP SOURCE s1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=joe,user=joe
DROP SOURCE s2;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP SOURCE s4;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP SOURCE s1
----
COMPLETE 0

simple conn=joe,user=joe
DROP SOURCE s2
----
COMPLETE 0

simple conn=child,user=child
DROP SOURCE s4
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP SECRET

simple conn=joe,user=joe
DROP SECRET se;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP SECRET se1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP SECRET se;
----
COMPLETE 0

simple conn=child,user=child
DROP SECRET se1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP INDEX

simple conn=joe,user=joe
DROP INDEX i;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP INDEX i1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP INDEX i;
----
COMPLETE 0

simple conn=child,user=child
DROP INDEX i1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP TABLE

simple conn=joe,user=joe
DROP TABLE t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP TABLE t1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP TABLE t;
----
COMPLETE 0

simple conn=child,user=child
DROP TABLE t1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP VIEW

simple conn=joe,user=joe
DROP VIEW v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP VIEW v1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP VIEW v;
----
COMPLETE 0

simple conn=child,user=child
DROP VIEW v1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP MATERIALIZED VIEW

simple conn=joe,user=joe
DROP MATERIALIZED VIEW mv;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP MATERIALIZED VIEW mv1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP MATERIALIZED VIEW mv;
----
COMPLETE 0

simple conn=child,user=child
DROP MATERIALIZED VIEW mv1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# DROP TYPE

simple conn=joe,user=joe
DROP TYPE ty;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DROP TYPE ty1;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DROP TYPE ty;
----
COMPLETE 0

simple conn=child,user=child
DROP TYPE ty1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# SHOW CREATE

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
SHOW CREATE TABLE t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
SHOW CREATE TABLE t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SHOW CREATE TABLE t;
----
materialize.public.t,CREATE TABLE materialize.public.t (a pg_catalog.int4);
COMPLETE 1

simple conn=child,user=child
SHOW CREATE TABLE t;
----
materialize.public.t,CREATE TABLE materialize.public.t (a pg_catalog.int4);
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE CONNECTION IF NOT EXISTS csr_conn TO CONFLUENT SCHEMA REGISTRY (URL 'https://google.com') WITH (VALIDATE = false);
----
COMPLETE 0

simple conn=joe,user=joe
SHOW CREATE CONNECTION csr_conn;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
SHOW CREATE CONNECTION csr_conn;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SHOW CREATE CONNECTION csr_conn;
----
materialize.public.csr_conn,CREATE CONNECTION materialize.public.csr_conn TO CONFLUENT SCHEMA REGISTRY (URL = 'https://google.com');
COMPLETE 1

simple conn=child,user=child
SHOW CREATE CONNECTION csr_conn;
----
materialize.public.csr_conn,CREATE CONNECTION materialize.public.csr_conn TO CONFLUENT SCHEMA REGISTRY (URL = 'https://google.com');
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP CONNECTION csr_conn;
----
COMPLETE 0

# SELECT

## Table

simple conn=mz_system,user=mz_system
CREATE TYPE ty AS (a text);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t (a ty);
----
COMPLETE 0

simple conn=joe,user=joe
SELECT a::ty FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
SELECT a::ty FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT a::ty FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
SELECT a::ty FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT a::ty FROM t;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
SELECT a::ty FROM t;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT a::ty FROM t;
----
COMPLETE 0

simple conn=child,user=child
SELECT a::ty FROM t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TYPE ty;
----
COMPLETE 0

## View

simple conn=mz_system,user=mz_system
CREATE ROLE view_owner;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO view_owner;
----
COMPLETE 0

simple conn=view_owner,user=view_owner
CREATE VIEW v AS SELECT * FROM t;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT * FROM v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
SELECT * FROM v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT * FROM v;
----
db error: ERROR: permission denied for VIEW "materialize.public.v"
DETAIL: The 'joe' role needs SELECT privileges on VIEW "materialize.public.v"

simple conn=child,user=child
SELECT * FROM v;
----
db error: ERROR: permission denied for VIEW "materialize.public.v"
DETAIL: The 'child' role needs SELECT privileges on VIEW "materialize.public.v"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE v TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT * FROM v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'view_owner' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
SELECT * FROM v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'view_owner' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO view_owner;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT * FROM v;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'view_owner' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
SELECT * FROM v;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'view_owner' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO view_owner;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT * FROM v;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
SELECT * FROM v;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
SELECT * FROM v;
----
COMPLETE 0

simple conn=child,user=child
SELECT * FROM v;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM view_owner;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE v FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE, CREATE ON SCHEMA materialize.public FROM view_owner;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP VIEW v;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP ROLE view_owner;
----
COMPLETE 0

# SHOW

simple conn=joe,user=joe
SHOW TABLES
----
COMPLETE 0

simple conn=child,user=child
SHOW TABLES
----
COMPLETE 0

# EXPLAIN

## Table

simple conn=mz_system,user=mz_system
CREATE TYPE ty AS (a text);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t (a ty);
----
COMPLETE 0

simple conn=joe,user=joe
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a::ty FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a::ty FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe;
----
COMPLETE 0

simple multiline,conn=joe,user=joe
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a::ty FROM t;
----
Explained Query:
  ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF
COMPLETE 1

simple multiline,conn=child,user=child
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a::ty FROM t;
----
Explained Query:
  ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TYPE ty;
----
COMPLETE 0

## Explain schema

simple conn=mz_system,user=mz_system
CREATE TYPE ty AS (a text);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t (a ty);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE CONNECTION kafka_conn TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE CONNECTION IF NOT EXISTS csr_conn TO CONFLUENT SCHEMA REGISTRY (URL 'https://google.com') WITH (VALIDATE = false);
----
COMPLETE 0

simple conn=joe,user=joe
EXPLAIN VALUE SCHEMA FOR CREATE SINK sink FROM t INTO KAFKA CONNECTION kafka_conn (TOPIC 'topic') KEY (a) NOT ENFORCED FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_conn (DOC ON COLUMN ty.a = 'comment') ENVELOPE UPSERT;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple multiline,conn=joe,user=joe
EXPLAIN VALUE SCHEMA FOR CREATE SINK sink FROM t INTO KAFKA CONNECTION kafka_conn (TOPIC 'topic') KEY (a) NOT ENFORCED FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_conn (DOC ON COLUMN ty.a = 'comment') ENVELOPE UPSERT;
----
{
  "type": "record",
  "name": "envelope",
  "fields": [
    {
      "name": "a",
      "type": [
        "null",
        {
          "type": "record",
          "name": "record0",
          "namespace": "com.materialize.sink",
          "fields": [
            {
              "name": "a",
              "type": [
                "null",
                "string"
              ],
              "doc": "comment"
            }
          ]
        }
      ]
    }
  ]
}
EOF
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TYPE ty;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP CONNECTION kafka_conn;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP CONNECTION csr_conn;
----
COMPLETE 0

## View

simple conn=mz_system,user=mz_system
CREATE ROLE view_owner;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO view_owner;
----
COMPLETE 0

simple conn=view_owner1,user=view_owner
CREATE VIEW v AS SELECT * FROM t;
----
COMPLETE 0

simple conn=joe,user=joe
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM v;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple multiline,conn=joe,user=joe
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM v;
----
Explained Query:
  ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF
COMPLETE 1

simple multiline,conn=child,user=child
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM v;
----
Explained Query:
  ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM view_owner;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP VIEW v;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP ROLE view_owner;
----
COMPLETE 0

## EXPLAIN MATERIALIZED VIEW

### We use the materialize role for these tests because we need the multiline functionality

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE MATERIALIZED VIEW mv IN CLUSTER quickstart AS SELECT 1
----
COMPLETE 0

query error permission denied for SCHEMA "materialize.public"
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR MATERIALIZED VIEW mv;

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO materialize;
----
COMPLETE 0

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR MATERIALIZED VIEW mv;
----
materialize.public.mv:
  Constant
    - (1)

Target cluster: quickstart

EOF

simple conn=mz_system,user=mz_system
DROP MATERIALIZED VIEW mv
----
COMPLETE 0

## EXPLAIN INDEX

### We use the materialize role for these tests because we need the multiline functionality

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT)
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE INDEX i IN CLUSTER quickstart ON t(a)
----
COMPLETE 0

query error permission denied for SCHEMA "materialize.public"
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR INDEX i;

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO materialize;
----
COMPLETE 0

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR INDEX i;
----
materialize.public.i:
  ArrangeBy keys=[[#0{a}]]
    ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

simple conn=mz_system,user=mz_system
DROP INDEX i
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t
----
COMPLETE 0

# INSERT

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (1);
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t VALUES (1);
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (1);
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t VALUES (1);
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT INSERT ON TABLE t TO joe;
----
COMPLETE 0

# TODO(jkosh44) We're not smart enough to know that this doesn't require a cluster

simple conn=joe,user=joe
INSERT INTO t VALUES (1);
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
INSERT INTO t VALUES (1);
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (1);
----
COMPLETE 1

simple conn=child,user=child
INSERT INTO t VALUES (1);
----
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE INSERT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# INSERT INTO .. SELECT

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT INSERT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t SELECT * FROM t;
----
COMPLETE 0

simple conn=child,user=child
INSERT INTO t SELECT * FROM t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE INSERT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t SELECT * FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# INSERT ... RETURNING

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT INSERT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (42) RETURNING a;
----
42
COMPLETE 1

simple conn=child,user=child
INSERT INTO t VALUES (42) RETURNING a;
----
42
COMPLETE 1

simple conn=mz_system,user=mz_system
REVOKE INSERT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
INSERT INTO t VALUES (42) RETURNING a;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs INSERT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# UPDATE (no WHERE)

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT UPDATE ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE t SET a = 42;
----
COMPLETE 0

simple conn=child,user=child
UPDATE t SET a = 42;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE UPDATE ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE t SET a = 42;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# UPDATE (with WHERE)

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT UPDATE ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
COMPLETE 0

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE UPDATE ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = 42 WHERE a > 6;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# UPDATE (non-const assignment)

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT UPDATE ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = a + 10;
----
COMPLETE 0

simple conn=child,user=child
UPDATE T SET a = a + 10;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE UPDATE ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
UPDATE T SET a = a + 10;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs UPDATE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# DELETE (no WHERE)

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DELETE FROM t;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
DELETE FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT DELETE ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
DELETE FROM t;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t;
----
COMPLETE 0

simple conn=child,user=child
DELETE FROM t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE DELETE ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
DELETE FROM t;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# DELETE (with WHERE)

simple conn=mz_system,user=mz_system
CREATE TABLE t (a INT);
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'child' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT DELETE ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs SELECT privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'joe' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for CLUSTER "quickstart"
DETAIL: The 'child' role needs USAGE privileges on CLUSTER "quickstart"

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER quickstart TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
COMPLETE 0

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE DELETE ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'joe' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=child,user=child
DELETE FROM t WHERE a > 5;
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'child' role needs DELETE privileges on TABLE "materialize.public.t"

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER quickstart FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

# ALTER OWNER

## Cluster

simple conn=mz_system,user=mz_system
GRANT CREATECLUSTER ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER clus REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER CLUSTER clus OWNER TO other
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP CLUSTER clus;
----
COMPLETE 0

## Cluster Replica

simple conn=mz_system,user=mz_system
GRANT CREATECLUSTER ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER clus REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER REPLICA clus.r2 SIZE '1';
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATECLUSTER ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER CLUSTER REPLICA clus.r2 OWNER TO other;
----
db error: ERROR: altering the owner of a cluster replica is not supported

simple conn=mz_system,user=mz_system
DROP CLUSTER clus;
----
COMPLETE 0

## Database

simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE DATABASE db;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER DATABASE db OWNER TO other;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP DATABASE db;
----
COMPLETE 0

## Schema

simple conn=mz_system,user=mz_system
GRANT CREATE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SCHEMA materialize.sch;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON DATABASE materialize FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER SCHEMA materialize.sch OWNER TO other;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'joe' role needs CREATE privileges on DATABASE "materialize"

simple conn=mz_system,user=mz_system
GRANT CREATE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER SCHEMA materialize.sch OWNER TO other;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP SCHEMA materialize.sch;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON DATABASE materialize FROM joe;
----
COMPLETE 0

## Item

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TABLE t ();
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER TABLE t OWNER TO other;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs CREATE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
ALTER TABLE t OWNER TO other;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

# GRANT/REVOKE privilege

## Cluster

simple conn=mz_system,user=mz_system
GRANT CREATECLUSTER ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE CLUSTER clus REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATECLUSTER ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
GRANT USAGE, CREATE ON CLUSTER clus TO other
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE USAGE, CREATE ON CLUSTER clus FROM other
----
COMPLETE 0

simple conn=joe,user=joe
DROP CLUSTER clus;
----
COMPLETE 0

## Database

simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE DATABASE db;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
GRANT CREATE, USAGE ON DATABASE db TO other
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE CREATE, USAGE ON DATABASE db FROM other
----
COMPLETE 0

simple conn=joe,user=joe
DROP DATABASE db;
----
COMPLETE 0

## Schema

simple conn=mz_system,user=mz_system
GRANT CREATE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE SCHEMA materialize.sch;
----
COMPLETE 0

simple conn=joe,user=joe
GRANT CREATE, USAGE ON SCHEMA materialize.sch TO other;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'joe' role needs USAGE privileges on DATABASE "materialize"

simple conn=mz_system,user=mz_system
GRANT USAGE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
GRANT CREATE, USAGE ON SCHEMA materialize.sch TO other;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON DATABASE materialize FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE CREATE, USAGE ON SCHEMA materialize.sch FROM other;
----
db error: ERROR: permission denied for DATABASE "materialize"
DETAIL: The 'joe' role needs USAGE privileges on DATABASE "materialize"

simple conn=mz_system,user=mz_system
GRANT USAGE ON DATABASE materialize TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE CREATE, USAGE ON SCHEMA materialize.sch FROM other;
----
COMPLETE 0

simple conn=joe,user=joe
DROP SCHEMA materialize.sch;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON DATABASE materialize FROM joe;
----
COMPLETE 0

## Item

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
CREATE TABLE t ();
----
COMPLETE 0

simple conn=joe,user=joe
GRANT INSERT, SELECT ON TABLE t TO other;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
GRANT INSERT, SELECT ON TABLE t TO other;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE INSERT, SELECT ON TABLE t FROM other;
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'joe' role needs USAGE privileges on SCHEMA "materialize.public"

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA materialize.public TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE INSERT, SELECT ON TABLE t FROM other;
----
COMPLETE 0

simple conn=joe,user=joe
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA materialize.public FROM joe;
----
COMPLETE 0

## System

statement error You must be a superuser to GRANT/REVOKE SYSTEM PRIVILEGES
GRANT CREATEDB ON SYSTEM TO joe

simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO joe;
----
COMPLETE 0

statement error You must be a superuser to GRANT/REVOKE SYSTEM PRIVILEGES
REVOKE CREATEDB ON SYSTEM FROM joe

simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM joe
----
COMPLETE 0

# GRANT/REVOKE role

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE r1
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE r2
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE r3
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT r1, r2 TO joe
----
COMPLETE 0

simple conn=joe,user=joe
GRANT r2 TO r1;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATEROLE privileges on SYSTEM

simple conn=child,user=child
GRANT r2 TO r1;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATEROLE privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
GRANT r2 TO r1;
----
COMPLETE 0

simple conn=child,user=child
GRANT r3 TO r1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEROLE ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE r2 FROM r1;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'joe' role needs CREATEROLE privileges on SYSTEM

simple conn=child,user=child
REVOKE r3 FROM r1;
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'child' role needs CREATEROLE privileges on SYSTEM

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO joe;
----
COMPLETE 0

simple conn=joe,user=joe
REVOKE r2 FROM r1;
----
COMPLETE 0

simple conn=child,user=child
REVOKE r3 FROM r1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATEROLE ON SYSTEM FROM joe;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
DROP ROLE r1, r2, r3;
----
COMPLETE 0

# Disable rbac checks.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false;
----
COMPLETE 0