# 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 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 ALTER SYSTEM SET enable_rbac_checks = true ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_index_options = true ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_logical_compaction_window = true ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT ALL PRIVILEGES ON SYSTEM TO materialize; ---- COMPLETE 0 # All objects on a fresh installation should be owned by mz_system (s1) # except for the mz_analytics connection which is owned by mz_analytics (s3) # No builtin sinks query T SELECT owner_id FROM mz_sinks GROUP BY owner_id ---- query T SELECT owner_id FROM mz_indexes GROUP BY owner_id ---- s1 query T SELECT owner_id FROM mz_connections GROUP BY owner_id ---- s3 query T SELECT owner_id FROM mz_types GROUP BY owner_id ---- s1 query T SELECT owner_id FROM mz_functions GROUP BY owner_id ---- s1 # No builtin secrets query T SELECT owner_id FROM mz_secrets GROUP BY owner_id ---- query T SELECT owner_id FROM mz_relations GROUP BY owner_id ---- s1 query T SELECT owner_id FROM mz_tables GROUP BY owner_id ---- s1 query T SELECT owner_id FROM mz_sources GROUP BY owner_id ---- s1 query T SELECT owner_id FROM mz_views GROUP BY owner_id ---- s1 # No builtin materialized views query T SELECT owner_id FROM mz_materialized_views GROUP BY owner_id ---- query T SELECT owner_id FROM mz_databases GROUP BY owner_id ---- s1 query T rowsort SELECT owner_id FROM mz_clusters GROUP BY owner_id ---- s1 s2 s3 query T SELECT owner_id FROM mz_cluster_replicas GROUP BY owner_id ---- s1 query T SELECT owner_id FROM mz_schemas GROUP BY owner_id ---- s1 # Test user made objects simple conn=mz_system,user=mz_system CREATE ROLE joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE ROLE group_materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE ROLE group_no_one; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT group_materialize TO materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO joe; ---- COMPLETE 0 ## Tables statement ok CREATE TABLE mt (a INT); query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 'mt' ---- materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'mt' ---- materialize statement error must be a member of "group_no_one" ALTER TABLE mt OWNER TO group_no_one statement ok ALTER TABLE mt OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'mt' ---- group_materialize statement ok ALTER TABLE mt RENAME TO ICs statement ok DROP TABLE ICs simple conn=joe,user=joe CREATE TABLE jt (a INT); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 'jt' ---- joe query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'jt' ---- joe statement error must be owner of TABLE materialize.public.jt DROP TABLE jt statement error must be owner of TABLE materialize.public.jt ALTER TABLE jt RENAME TO cool statement error must be owner of TABLE materialize.public.jt ALTER TABLE jt OWNER TO group_materialize ## Indexes statement ok CREATE TABLE mt (a INT); statement ok CREATE INDEX mt_ind ON mt(a); query T SELECT mz_roles.name FROM mz_indexes LEFT JOIN mz_roles ON mz_indexes.owner_id = mz_roles.id WHERE mz_indexes.name = 'mt_ind' ---- materialize statement error must be a member of "group_no_one" ALTER INDEX mt_ind OWNER TO group_no_one statement ok ALTER INDEX mt_ind OWNER TO group_materialize # Altering the owner of an index is a no-op query T SELECT mz_roles.name FROM mz_indexes LEFT JOIN mz_roles ON mz_indexes.owner_id = mz_roles.id WHERE mz_indexes.name = 'mt_ind' ---- materialize statement ok ALTER INDEX mt_ind SET (RETAIN HISTORY = FOR '1000 hours') statement ok ALTER INDEX mt_ind RENAME TO ICs statement ok DROP INDEX ICs simple conn=joe,user=joe CREATE INDEX jt_ind ON jt(a); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_indexes LEFT JOIN mz_roles ON mz_indexes.owner_id = mz_roles.id WHERE mz_indexes.name = 'jt_ind' ---- joe statement error must be owner of INDEX materialize.public.jt_ind DROP INDEX jt_ind statement error must be owner of INDEX materialize.public.jt_ind ALTER INDEX jt_ind RENAME TO cool statement error must be owner of INDEX materialize.public.jt_ind ALTER INDEX jt_ind OWNER TO group_materialize statement error must be owner of INDEX materialize.public.jt_ind ALTER INDEX jt_ind SET (RETAIN HISTORY = FOR '1000 hours') ## Sources statement ok CREATE SOURCE ms FROM LOAD GENERATOR COUNTER; query T SELECT mz_roles.name FROM mz_sources LEFT JOIN mz_roles ON mz_sources.owner_id = mz_roles.id WHERE mz_sources.name = 'ms' ---- materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'ms' ---- materialize statement error must be a member of "group_no_one" ALTER SOURCE ms OWNER TO group_no_one statement ok ALTER SOURCE ms OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'ms' ---- group_materialize statement ok ALTER SOURCE ms RENAME TO ICs statement ok DROP SOURCE ICs simple conn=joe,user=joe CREATE SOURCE js FROM LOAD GENERATOR COUNTER; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_sources LEFT JOIN mz_roles ON mz_sources.owner_id = mz_roles.id WHERE mz_sources.name = 'js' ---- joe query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'js' ---- joe statement error must be owner of SOURCE materialize.public.js DROP SOURCE js statement error must be owner of SOURCE materialize.public.js ALTER SOURCE js RENAME TO cool statement error must be owner of SOURCE materialize.public.js ALTER SOURCE js OWNER TO group_materialize statement error db error: ERROR: Expected one of TIMESTAMP or RETAIN, found SIZE ALTER SOURCE js SET (SIZE = '4') ## Views statement ok CREATE VIEW mv AS SELECT 1; query T SELECT mz_roles.name FROM mz_views LEFT JOIN mz_roles ON mz_views.owner_id = mz_roles.id WHERE mz_views.name = 'mv' ---- materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'mv' ---- materialize statement error must be a member of "group_no_one" ALTER VIEW mv OWNER TO group_no_one statement ok ALTER VIEW mv OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'mv' ---- group_materialize statement ok ALTER VIEW mv RENAME TO ICs statement ok DROP VIEW ICs simple conn=joe,user=joe CREATE VIEW jv AS SELECT 1; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_views LEFT JOIN mz_roles ON mz_views.owner_id = mz_roles.id WHERE mz_views.name = 'jv' ---- joe query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'jv' ---- joe statement error must be owner of VIEW materialize.public.jv DROP VIEW jv statement error must be owner of VIEW materialize.public.jv ALTER VIEW jv RENAME TO cool statement error must be owner of VIEW materialize.public.jv ALTER VIEW jv OWNER TO group_materialize ## Materialized Views statement ok CREATE MATERIALIZED VIEW mmv AS SELECT 1; query T SELECT mz_roles.name FROM mz_materialized_views LEFT JOIN mz_roles ON mz_materialized_views.owner_id = mz_roles.id WHERE mz_materialized_views.name = 'mmv' ---- materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'mmv' ---- materialize statement error must be a member of "group_no_one" ALTER MATERIALIZED VIEW mmv OWNER TO group_no_one statement ok ALTER MATERIALIZED VIEW mmv OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'mmv' ---- group_materialize statement ok ALTER MATERIALIZED VIEW mmv RENAME TO ICs statement ok DROP MATERIALIZED VIEW ICs simple conn=joe,user=joe CREATE MATERIALIZED VIEW jmv AS SELECT 1; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_materialized_views LEFT JOIN mz_roles ON mz_materialized_views.owner_id = mz_roles.id WHERE mz_materialized_views.name = 'jmv' ---- joe query T SELECT mz_roles.name FROM mz_relations LEFT JOIN mz_roles ON mz_relations.owner_id = mz_roles.id WHERE mz_relations.name = 'jmv' ---- joe statement error must be owner of MATERIALIZED VIEW materialize.public.jmv DROP MATERIALIZED VIEW jmv statement error must be owner of MATERIALIZED VIEW materialize.public.jmv ALTER MATERIALIZED VIEW jmv RENAME TO cool statement error must be owner of MATERIALIZED VIEW materialize.public.jmv ALTER MATERIALIZED VIEW jmv OWNER TO group_materialize ## Connections statement ok CREATE CONNECTION mc TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); query T SELECT mz_roles.name FROM mz_connections LEFT JOIN mz_roles ON mz_connections.owner_id = mz_roles.id WHERE mz_connections.name = 'mc' ---- materialize statement error must be a member of "group_no_one" ALTER CONNECTION mc OWNER TO group_no_one statement ok ALTER CONNECTION mc OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_connections LEFT JOIN mz_roles ON mz_connections.owner_id = mz_roles.id WHERE mz_connections.name = 'mc' ---- group_materialize statement ok ALTER CONNECTION mc RENAME TO ICs statement ok DROP CONNECTION ICs statement ok CREATE CONNECTION mssh TO SSH TUNNEL (HOST 'ssh-bastion-host', USER 'mz', PORT 22) WITH (VALIDATE = false); statement ok ALTER CONNECTION mssh ROTATE KEYS simple conn=joe,user=joe CREATE CONNECTION jc TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_connections LEFT JOIN mz_roles ON mz_connections.owner_id = mz_roles.id WHERE mz_connections.name = 'jc' ---- joe statement error must be owner of CONNECTION materialize.public.jc DROP CONNECTION jc statement error must be owner of CONNECTION materialize.public.jc ALTER CONNECTION jc RENAME TO cool statement error must be owner of CONNECTION materialize.public.jc ALTER CONNECTION jc OWNER TO group_materialize simple conn=joe,user=joe CREATE CONNECTION jssh TO SSH TUNNEL (HOST 'ssh-bastion-host', USER 'mz', PORT 22) WITH (VALIDATE = false); ---- COMPLETE 0 statement error must be owner of CONNECTION materialize.public.jssh ALTER CONNECTION jssh ROTATE KEYS ## Types statement ok CREATE TYPE mty AS LIST (ELEMENT TYPE=bool); query T SELECT mz_roles.name FROM mz_types LEFT JOIN mz_roles ON mz_types.owner_id = mz_roles.id WHERE mz_types.name = 'mty' ---- materialize statement error must be a member of "group_no_one" ALTER TYPE mty OWNER TO group_no_one statement ok ALTER TYPE mty OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_types LEFT JOIN mz_roles ON mz_types.owner_id = mz_roles.id WHERE mz_types.name = 'mty' ---- group_materialize statement ok DROP TYPE mty simple conn=joe,user=joe CREATE TYPE jty AS LIST (ELEMENT TYPE=bool); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_types LEFT JOIN mz_roles ON mz_types.owner_id = mz_roles.id WHERE mz_types.name = 'jty' ---- joe statement error must be owner of TYPE materialize.public.jty DROP TYPE jty statement error must be owner of TYPE materialize.public.jty ALTER TYPE jty OWNER TO group_materialize ## Secrets statement ok CREATE SECRET mse AS decode('c2VjcmV0Cg==', 'base64'); query T SELECT mz_roles.name FROM mz_secrets LEFT JOIN mz_roles ON mz_secrets.owner_id = mz_roles.id WHERE mz_secrets.name = 'mse' ---- materialize statement error must be a member of "group_no_one" ALTER SECRET mse OWNER TO group_no_one statement ok ALTER SECRET mse OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_secrets LEFT JOIN mz_roles ON mz_secrets.owner_id = mz_roles.id WHERE mz_secrets.name = 'mse' ---- group_materialize statement ok ALTER SECRET mse AS decode('c2VjcmV0Cg==', 'base64'); statement ok ALTER SECRET mse RENAME TO ICs statement ok DROP SECRET ICs simple conn=joe,user=joe CREATE SECRET jse AS decode('c2VjcmV0Cg==', 'base64'); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_secrets LEFT JOIN mz_roles ON mz_secrets.owner_id = mz_roles.id WHERE mz_secrets.name = 'jse' ---- joe statement error must be owner of SECRET materialize.public.jse DROP SECRET jse statement error must be owner of SECRET materialize.public.jse ALTER SECRET jse RENAME TO cool statement error must be owner of SECRET materialize.public.jse ALTER SECRET jse OWNER TO group_materialize statement error must be owner of SECRET materialize.public.jse ALTER SECRET jse AS decode('c2VjcmV0Cg==', 'base64'); ## Databases statement ok CREATE DATABASE mdb; query T SELECT mz_roles.name FROM mz_databases LEFT JOIN mz_roles ON mz_databases.owner_id = mz_roles.id WHERE mz_databases.name = 'mdb' ---- materialize statement error must be a member of "group_no_one" ALTER DATABASE mdb OWNER TO group_no_one statement ok ALTER DATABASE mdb OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_databases LEFT JOIN mz_roles ON mz_databases.owner_id = mz_roles.id WHERE mz_databases.name = 'mdb' ---- group_materialize statement ok DROP DATABASE mdb simple conn=joe,user=joe CREATE DATABASE jdb; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_databases LEFT JOIN mz_roles ON mz_databases.owner_id = mz_roles.id WHERE mz_databases.name = 'jdb' ---- joe statement error must be owner of DATABASE jdb DROP DATABASE jdb statement error must be owner of DATABASE jdb ALTER DATABASE jdb OWNER TO group_materialize ## Schemas statement ok CREATE SCHEMA msc; query T SELECT mz_roles.name FROM mz_schemas LEFT JOIN mz_roles ON mz_schemas.owner_id = mz_roles.id WHERE mz_schemas.name = 'msc' ---- materialize statement error must be a member of "group_no_one" ALTER SCHEMA msc OWNER TO group_no_one statement ok ALTER SCHEMA msc OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_schemas LEFT JOIN mz_roles ON mz_schemas.owner_id = mz_roles.id WHERE mz_schemas.name = 'msc' ---- group_materialize statement ok DROP SCHEMA msc simple conn=joe,user=joe CREATE SCHEMA jsc; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_schemas LEFT JOIN mz_roles ON mz_schemas.owner_id = mz_roles.id WHERE mz_schemas.name = 'jsc' ---- joe statement error must be owner of SCHEMA materialize.jsc DROP SCHEMA jsc statement error must be owner of SCHEMA materialize.jsc ALTER SCHEMA jsc OWNER TO group_materialize simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE jdb TO materialize; ---- COMPLETE 0 statement ok CREATE SCHEMA jdb.msc_cross; query T SELECT mz_roles.name FROM mz_schemas LEFT JOIN mz_roles ON mz_schemas.owner_id = mz_roles.id WHERE mz_schemas.name = 'msc_cross' ---- materialize ## Clusters statement ok CREATE CLUSTER mclus REPLICAS (mr1 (SIZE '1')); query T SELECT mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'mclus' ---- materialize query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'mr1' ---- materialize statement error must be a member of "group_no_one" ALTER CLUSTER mclus OWNER TO group_no_one statement ok ALTER CLUSTER mclus OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'mclus' ---- group_materialize query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'mr1' ---- group_materialize statement ok DROP CLUSTER mclus simple conn=joe,user=joe CREATE CLUSTER jclus REPLICAS (jr1 (SIZE '1')); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'jclus' ---- joe query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'jr1' ---- joe statement error must be owner of CLUSTER jclus DROP CLUSTER jclus statement error must be owner of CLUSTER jclus ALTER CLUSTER jclus RENAME TO jclus2 simple conn=joe,user=joe ALTER CLUSTER jclus RENAME TO jclus2; ---- COMPLETE 0 simple conn=joe,user=joe ALTER CLUSTER jclus2 RENAME TO jclus; ---- COMPLETE 0 statement error must be owner of CLUSTER jclus ALTER CLUSTER jclus OWNER TO group_materialize simple conn=joe,user=joe DROP CLUSTER jclus; ---- COMPLETE 0 ## Cluster Replicas statement ok CREATE CLUSTER mclus REPLICAS (mr1 (SIZE '1')); statement ok CREATE CLUSTER REPLICA mclus.mr2 SIZE '1'; query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'mr2' ---- materialize statement error altering the owner of a cluster replica is not supported ALTER CLUSTER REPLICA mclus.mr2 OWNER TO group_no_one statement error altering the owner of a cluster replica is not supported ALTER CLUSTER REPLICA mclus.mr2 OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'mr2' ---- materialize statement ok ALTER CLUSTER REPLICA mclus.mr2 RENAME TO mr3 query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'mr3' ---- materialize statement ok ALTER CLUSTER mclus OWNER TO group_materialize query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'mr3' ---- group_materialize statement ok DROP CLUSTER REPLICA mclus.mr3 statement ok DROP CLUSTER mclus simple conn=joe,user=joe CREATE CLUSTER jclus REPLICAS (jr1 (SIZE '1')); ---- COMPLETE 0 simple conn=joe,user=joe CREATE CLUSTER REPLICA jclus.jr2 SIZE '1'; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'jr2' ---- joe statement error must be owner of CLUSTER REPLICA jclus.jr2 DROP CLUSTER REPLICA jclus.jr2 statement error must be owner of CLUSTER REPLICA jclus.jr2 ALTER CLUSTER REPLICA jclus.jr2 RENAME TO jr3 statement error altering the owner of a cluster replica is not supported ALTER CLUSTER REPLICA jclus.jr2 OWNER TO group_materialize simple conn=joe,user=joe DROP CLUSTER REPLICA jclus.jr2; ---- COMPLETE 0 simple conn=joe,user=joe DROP CLUSTER jclus; ---- COMPLETE 0 ## Clean up simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM joe; ---- COMPLETE 0 # Renaming does not change owner statement ok CREATE TABLE old_table (); query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 'old_table' ---- materialize simple conn=mz_system,user=mz_system ALTER TABLE mt RENAME TO new_table; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 'new_table' ---- materialize # Prevent dropping roles while owned objects exist ## Tables statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner1,user=owner CREATE TABLE t (a INT); ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of TABLE "materialize.public.t" owner: privileges on TABLE "materialize.public.t" granted by owner owner: privileges granted on TABLE "materialize.public.t" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner1,user=owner DROP TABLE t; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Indexes statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner2,user=owner create TABLE t (a INT); ---- COMPLETE 0 simple conn=owner2,user=owner CREATE INDEX ind ON t(a); ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of TABLE "materialize.public.t" owner: privileges on TABLE "materialize.public.t" granted by owner owner: privileges granted on TABLE "materialize.public.t" to owner owner: owner of INDEX "materialize.public.ind" owner: privileges on INDEX "materialize.public.ind" granted by owner owner: privileges granted on INDEX "materialize.public.ind" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner2,user=owner DROP INDEX ind; ---- COMPLETE 0 simple conn=owner2,user=owner DROP TABLE t; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Sources statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner3,user=owner create SOURCE s FROM LOAD GENERATOR COUNTER; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of SOURCE "materialize.public.s_progress" owner: privileges granted on SOURCE "materialize.public.s_progress" to mz_support owner: privileges on SOURCE "materialize.public.s_progress" granted by owner owner: privileges granted on SOURCE "materialize.public.s_progress" to owner owner: owner of SOURCE "materialize.public.s" owner: privileges on SOURCE "materialize.public.s" granted by owner owner: privileges granted on SOURCE "materialize.public.s" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner3,user=owner DROP SOURCE s; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Views statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner4,user=owner CREATE VIEW v AS SELECT 1; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of VIEW "materialize.public.v" owner: privileges on VIEW "materialize.public.v" granted by owner owner: privileges granted on VIEW "materialize.public.v" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner4,user=owner DROP VIEW v; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Materialized Views statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner5,user=owner CREATE MATERIALIZED VIEW mvv AS SELECT 1; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of MATERIALIZED VIEW "materialize.public.mvv" owner: privileges on MATERIALIZED VIEW "materialize.public.mvv" granted by owner owner: privileges granted on MATERIALIZED VIEW "materialize.public.mvv" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner5,user=owner DROP MATERIALIZED VIEW mvv; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Connections statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner6,user=owner CREATE CONNECTION c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of CONNECTION "materialize.public.c" owner: privileges on CONNECTION "materialize.public.c" granted by owner owner: privileges granted on CONNECTION "materialize.public.c" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner6,user=owner DROP CONNECTION c; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Types statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner7,user=owner CREATE TYPE ty AS LIST (ELEMENT TYPE=bool); ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of TYPE "materialize.public.ty" owner: privileges on TYPE "materialize.public.ty" granted by owner owner: privileges granted on TYPE "materialize.public.ty" to owner owner: privileges granted on TYPE "materialize.public.ty" to public owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner7,user=owner DROP TYPE ty; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Secrets statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner8,user=owner CREATE SECRET se AS decode('c2VjcmV0Cg==', 'base64'); ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of SECRET "materialize.public.se" owner: privileges on SECRET "materialize.public.se" granted by owner owner: privileges granted on SECRET "materialize.public.se" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner8,user=owner DROP SECRET se; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Databases statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner9,user=owner CREATE DATABASE db; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: owner of DATABASE "db" owner: privileges granted on DATABASE "db" to mz_support owner: privileges on DATABASE "db" granted by owner owner: privileges granted on DATABASE "db" to owner owner: owner of SCHEMA "db.public" owner: privileges granted on SCHEMA "db.public" to mz_support owner: privileges on SCHEMA "db.public" granted by owner owner: privileges granted on SCHEMA "db.public" to owner owner: privileges granted on SCHEMA "db.public" to public owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner9,user=owner DROP DATABASE db; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Schemas statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner10,user=owner CREATE SCHEMA sc; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: owner of SCHEMA "materialize.sc" owner: privileges granted on SCHEMA "materialize.sc" to mz_support owner: privileges on SCHEMA "materialize.sc" granted by owner owner: privileges granted on SCHEMA "materialize.sc" to owner owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner10,user=owner DROP SCHEMA sc; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Clusters statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner11,user=owner CREATE CLUSTER clus REPLICAS (r1 (SIZE '1')); ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: owner of CLUSTER "clus" owner: privileges granted on CLUSTER "clus" to mz_support owner: privileges on CLUSTER "clus" granted by owner owner: privileges granted on CLUSTER "clus" to owner owner: owner of CLUSTER REPLICA "r1" owner: privileges on SYSTEM granted by mz_system simple conn=owner11,user=owner DROP CLUSTER clus; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Cluster Replicas statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=owner12,user=owner CREATE CLUSTER clus REPLICAS (r1 (SIZE '1')); ---- COMPLETE 0 simple conn=owner12,user=owner CREATE CLUSTER REPLICA clus.r2 SIZE '1'; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: owner of CLUSTER "clus" owner: privileges granted on CLUSTER "clus" to mz_support owner: privileges on CLUSTER "clus" granted by owner owner: privileges granted on CLUSTER "clus" to owner owner: owner of CLUSTER REPLICA "r1" owner: owner of CLUSTER REPLICA "r2" owner: privileges on SYSTEM granted by mz_system simple conn=owner12,user=owner DROP CLUSTER REPLICA clus.r2; ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: owner of CLUSTER "clus" owner: privileges granted on CLUSTER "clus" to mz_support owner: privileges on CLUSTER "clus" granted by owner owner: privileges granted on CLUSTER "clus" to owner owner: owner of CLUSTER REPLICA "r1" owner: privileges on SYSTEM granted by mz_system simple conn=owner12,user=owner DROP CLUSTER clus; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner ## Multiple Objects statement ok CREATE ROLE owner simple conn=mz_system,user=mz_system GRANT CREATECLUSTER, CREATEDB ON SYSTEM TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO owner; ---- COMPLETE 0 simple conn=owner13,user=owner CREATE TABLE t (a INT) ---- COMPLETE 0 simple conn=owner13,user=owner CREATE VIEW v AS SELECT 1 ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of TABLE "materialize.public.t" owner: privileges on TABLE "materialize.public.t" granted by owner owner: privileges granted on TABLE "materialize.public.t" to owner owner: owner of VIEW "materialize.public.v" owner: privileges on VIEW "materialize.public.v" granted by owner owner: privileges granted on VIEW "materialize.public.v" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner13,user=owner DROP TABLE t ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: owner of VIEW "materialize.public.v" owner: privileges on VIEW "materialize.public.v" granted by owner owner: privileges granted on VIEW "materialize.public.v" to owner owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=owner13,user=owner DROP VIEW v ---- COMPLETE 0 simple conn=materialize,user=materialize DROP ROLE owner ---- db error: ERROR: role "owner" cannot be dropped because some objects depend on it DETAIL: owner: privileges on DATABASE "materialize" granted by mz_system owner: privileges on SCHEMA "materialize.public" granted by mz_system owner: privileges on CLUSTER "quickstart" granted by mz_system owner: privileges on SYSTEM granted by mz_system simple conn=mz_system,user=mz_system REVOKE CREATE ON DATABASE materialize FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA materialize.public FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATE ON CLUSTER quickstart FROM owner; ---- COMPLETE 0 simple conn=mz_system,user=mz_system REVOKE CREATECLUSTER, CREATEDB ON SYSTEM FROM owner; ---- COMPLETE 0 statement ok DROP ROLE owner # Prevent altering the owner of system resources simple conn=mz_system,user=mz_system ALTER CLUSTER mz_system OWNER TO mz_system ---- db error: ERROR: system cluster 'mz_system' cannot be modified simple conn=mz_system,user=mz_system ALTER CLUSTER REPLICA mz_system.r1 OWNER TO mz_system ---- db error: ERROR: altering the owner of a cluster replica is not supported simple conn=mz_system,user=mz_system ALTER SCHEMA mz_catalog OWNER TO mz_system ---- db error: ERROR: cannot alter schema mz_catalog because it is required by the database system simple conn=mz_system,user=mz_system ALTER VIEW mz_introspection.mz_dataflow_operators OWNER TO mz_system ---- db error: ERROR: cannot alter item mz_introspection.mz_dataflow_operators because it is required by the database system simple conn=mz_system,user=mz_system ALTER TABLE mz_views OWNER TO mz_system ---- db error: ERROR: cannot alter item mz_catalog.mz_views because it is required by the database system simple conn=mz_system,user=mz_system ALTER VIEW mz_relations OWNER TO mz_system ---- db error: ERROR: cannot alter item mz_catalog.mz_relations because it is required by the database system simple conn=mz_system,user=mz_system ALTER TYPE int4 OWNER TO mz_system ---- db error: ERROR: cannot alter item pg_catalog.int4 because it is required by the database system simple conn=mz_system,user=mz_system ALTER INDEX mz_catalog.mz_databases_ind OWNER TO mz_system ---- db error: ERROR: cannot alter item mz_catalog.mz_databases_ind because it is required by the database system # Superusers can alter the owner to a role that they are not a member of statement ok CREATE TABLE t () simple conn=mz_system,user=mz_system CREATE ROLE group1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER TABLE t OWNER TO group1; ---- COMPLETE 0 statement ok CREATE ROLE group2; simple conn=mz_system,user=mz_system ALTER TABLE t OWNER TO group2; ---- COMPLETE 0 simple conn=mz_system,user=mz_system DROP TABLE t; ---- COMPLETE 0 # Test that ownership is not checked with cascading deletes statement ok CREATE TABLE t (a INT); simple conn=joe,user=joe CREATE VIEW v AS SELECT * FROM t; ---- COMPLETE 0 statement error must be owner of VIEW materialize.public.v DROP VIEW v statement ok DROP TABLE t CASCADE; statement ok CREATE VIEW v AS SELECT 1 AS a; statement ok CREATE INDEX i ON v(a); statement ok CREATE OR REPLACE VIEW v AS SELECT 2 AS a; statement ok DROP VIEW v; statement ok CREATE MATERIALIZED VIEW mv AS SELECT 1 AS a; statement ok CREATE INDEX i ON mv(a); statement ok CREATE OR REPLACE MATERIALIZED VIEW mv AS SELECT 2 AS a; statement ok DROP MATERIALIZED VIEW mv; # Test that index owners are consistent with their underlying relation statement ok CREATE TABLE t (a INT) simple conn=joe,user=joe CREATE INDEX i1 ON t (a); ---- db error: ERROR: must be owner of TABLE materialize.public.t simple conn=mz_system,user=mz_system CREATE INDEX i1 IN CLUSTER quickstart ON t (a); ---- COMPLETE 0 statement ok CREATE INDEX i2 ON t (a); query TT SELECT mz_indexes.name, mz_roles.name FROM mz_indexes LEFT JOIN mz_roles ON mz_indexes.owner_id = mz_roles.id WHERE mz_indexes.name = 'i1' OR mz_indexes.name = 'i2' ---- i1 materialize i2 materialize simple conn=mz_system,user=mz_system ALTER TABLE t OWNER TO joe ---- COMPLETE 0 query TT SELECT mz_indexes.name, mz_roles.name FROM mz_indexes LEFT JOIN mz_roles ON mz_indexes.owner_id = mz_roles.id WHERE mz_indexes.name = 'i1' OR mz_indexes.name = 'i2' ---- i1 joe i2 joe simple conn=joe,user=joe DROP INDEX i1 ---- COMPLETE 0 simple conn=joe,user=joe DROP INDEX i2 ---- COMPLETE 0 simple conn=joe,user=joe DROP TABLE t ---- COMPLETE 0 # Test that linked replica, and subsource owners are consistent with their linked # object and primary source owners. statement ok CREATE SOURCE s FROM LOAD GENERATOR AUCTION statement ok CREATE TABLE accounts FROM SOURCE s (REFERENCE accounts); statement ok CREATE TABLE auctions FROM SOURCE s (REFERENCE auctions); statement ok CREATE TABLE bids FROM SOURCE s (REFERENCE bids); statement ok CREATE TABLE organizations FROM SOURCE s (REFERENCE organizations); statement ok CREATE TABLE users FROM SOURCE s (REFERENCE users); query TT SELECT mz_sources.name, mz_roles.name FROM mz_sources LEFT JOIN mz_roles ON mz_sources.owner_id = mz_roles.id WHERE mz_sources.name IN ('s', 's_progress', 'accounts', 'auctions', 'bids', 'organizations', 'users') ORDER BY mz_sources.name ---- s materialize s_progress materialize query TT SELECT mz_tables.name, mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name IN ('s', 's_progress', 'accounts', 'auctions', 'bids', 'organizations', 'users') ORDER BY mz_tables.name ---- accounts materialize auctions materialize bids materialize organizations materialize users materialize query TT SELECT mz_clusters.name, mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'materialize_public_s' ---- query TT SELECT mz_cluster_replicas.name, mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_clusters ON mz_cluster_replicas.cluster_id = mz_clusters.id LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_clusters.name = 'materialize_public_s' AND mz_cluster_replicas.name = 'linked' ---- statement error db error: ERROR: unknown cluster 'materialize_public_s' ALTER CLUSTER materialize_public_s OWNER TO joe statement error altering the owner of a cluster replica is not supported ALTER CLUSTER REPLICA materialize_public_s.linked OWNER TO joe simple conn=mz_system,user=mz_system ALTER SOURCE accounts OWNER TO joe ---- db error: ERROR: accounts is a table not a source simple conn=mz_system,user=mz_system ALTER TABLE accounts OWNER TO joe ---- COMPLETE 0 statement error cannot ALTER this type of source ALTER SOURCE s_progress OWNER TO joe query TT SELECT mz_sources.name, mz_roles.name FROM mz_sources LEFT JOIN mz_roles ON mz_sources.owner_id = mz_roles.id WHERE mz_sources.name IN ('s', 's_progress', 'accounts', 'auctions', 'bids', 'organizations', 'users') ORDER BY mz_sources.name ---- s materialize s_progress materialize query TT SELECT mz_tables.name, mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name IN ('s', 's_progress', 'accounts', 'auctions', 'bids', 'organizations', 'users') ORDER BY mz_tables.name ---- accounts joe auctions materialize bids materialize organizations materialize users materialize query TT SELECT mz_clusters.name, mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'materialize_public_s' ---- query TT SELECT mz_cluster_replicas.name, mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_clusters ON mz_cluster_replicas.cluster_id = mz_clusters.id LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_clusters.name = 'materialize_public_s' AND mz_cluster_replicas.name = 'linked' ---- simple conn=mz_system,user=mz_system ALTER SOURCE s OWNER TO joe ---- COMPLETE 0 query TT SELECT mz_sources.name, mz_roles.name FROM mz_sources LEFT JOIN mz_roles ON mz_sources.owner_id = mz_roles.id WHERE mz_sources.name IN ('s', 's_progress', 'accounts', 'auctions', 'bids', 'organizations', 'users') ORDER BY mz_sources.name ---- s joe s_progress joe query TT SELECT mz_tables.name, mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name IN ('s', 's_progress', 'accounts', 'auctions', 'bids', 'organizations', 'users') ORDER BY mz_tables.name ---- accounts joe auctions materialize bids materialize organizations materialize users materialize query TT SELECT mz_clusters.name, mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'materialize_public_s' ---- query TT SELECT mz_cluster_replicas.name, mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_clusters ON mz_cluster_replicas.cluster_id = mz_clusters.id LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_clusters.name = 'materialize_public_s' AND mz_cluster_replicas.name = 'linked' ---- simple conn=mz_system,user=mz_system DROP SOURCE s CASCADE ---- COMPLETE 0 # Test DROP OWNED simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON SYSTEM FROM materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATEDB, CREATECLUSTER ON SYSTEM TO materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON DATABASE materialize TO PUBLIC; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA materialize.public TO PUBLIC; ---- COMPLETE 0 statement ok CREATE DATABASE db query T SELECT name FROM mz_databases WHERE name = 'db' ---- db statement ok CREATE SCHEMA s query T SELECT name FROM mz_schemas WHERE name = 's' ---- s statement ok CREATE TABLE t (); query T SELECT name FROM mz_tables WHERE name = 't' ---- t statement ok CREATE CLUSTER c REPLICAS (replica1 (SIZE '1')); query T SELECT name FROM mz_clusters WHERE name = 'c' ---- c query T SELECT name FROM mz_cluster_replicas WHERE name = 'replica1' ---- replica1 simple conn=mz_system,user=mz_system CREATE VIEW v AS SELECT 1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT SELECT ON v TO materialize; ---- COMPLETE 0 query TT rowsort SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v' ---- v mz_system=r/mz_system v materialize=r/mz_system query T SELECT privileges::text FROM mz_system_privileges ---- materialize=BN/mz_system mz_system=RBNP/mz_system simple conn=mz_system,user=mz_system DROP OWNED BY materialize; ---- COMPLETE 0 query T SELECT name FROM mz_databases WHERE name = 'db' ---- query T SELECT name FROM mz_schemas WHERE name = 's' ---- query T SELECT name FROM mz_tables WHERE name = 't' ---- query T SELECT name FROM mz_clusters WHERE name = 'c' ---- query T SELECT name FROM mz_cluster_replicas WHERE name = 'replica1' ---- query TT SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v' ---- v mz_system=r/mz_system query T SELECT privileges::text FROM mz_system_privileges ---- mz_system=RBNP/mz_system simple conn=mz_system,user=mz_system DROP VIEW v; ---- COMPLETE 0 ## Test invalid revokes simple conn=mz_system,user=mz_system GRANT CREATEDB, CREATECLUSTER ON SYSTEM TO materialize; ---- COMPLETE 0 statement ok CREATE DATABASE db query T SELECT name FROM mz_databases WHERE name = 'db' ---- db statement ok CREATE SCHEMA s query T SELECT name FROM mz_schemas WHERE name = 's' ---- s statement ok CREATE TABLE t (); query T SELECT name FROM mz_tables WHERE name = 't' ---- t statement ok CREATE CLUSTER c REPLICAS (replica1 (SIZE '1')); query T SELECT name FROM mz_clusters WHERE name = 'c' ---- c query T SELECT name FROM mz_cluster_replicas WHERE name = 'replica1' ---- replica1 simple conn=mz_system,user=mz_system CREATE VIEW v AS SELECT 1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT SELECT ON v TO materialize; ---- COMPLETE 0 query TT rowsort SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v' ---- v mz_system=r/mz_system v materialize=r/mz_system simple conn=materialize,user=materialize DROP OWNED BY materialize; ---- COMPLETE 0 query T SELECT name FROM mz_databases WHERE name = 'db' ---- query T SELECT name FROM mz_schemas WHERE name = 's' ---- query T SELECT name FROM mz_tables WHERE name = 't' ---- query T SELECT name FROM mz_clusters WHERE name = 'c' ---- query T SELECT name FROM mz_cluster_replicas WHERE name = 'replica1' ---- query TT rowsort SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v' ---- v mz_system=r/mz_system v materialize=r/mz_system simple conn=mz_system,user=mz_system DROP VIEW v; ---- COMPLETE 0 ## Test CASCADE statement ok CREATE DATABASE db query T SELECT name FROM mz_databases WHERE name = 'db' ---- db statement ok CREATE SCHEMA s query T SELECT name FROM mz_schemas WHERE name = 's' ---- s statement ok CREATE TABLE t (); query T SELECT name FROM mz_tables WHERE name = 't' ---- t statement ok CREATE CLUSTER c REPLICAS (replica1 (SIZE '1')); query T SELECT name FROM mz_clusters WHERE name = 'c' ---- c query T SELECT name FROM mz_cluster_replicas WHERE name = 'replica1' ---- replica1 simple conn=mz_system,user=mz_system CREATE SCHEMA db.s; ---- COMPLETE 0 simple conn=mz_system,user=mz_system DROP OWNED BY materialize; ---- db error: ERROR: database "db" cannot be dropped without CASCADE while it contains non-owned schemas simple conn=mz_system,user=mz_system CREATE VIEW s.v AS SELECT 1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system DROP OWNED BY materialize; ---- db error: ERROR: schema "materialize.s" cannot be dropped without CASCADE while it contains non-owned objects simple conn=mz_system,user=mz_system CREATE VIEW v1 AS SELECT * FROM t; ---- COMPLETE 0 simple conn=mz_system,user=mz_system DROP OWNED BY materialize; ---- db error: ERROR: cannot drop table "materialize.public.t": still depended upon by view "materialize.public.v1" HINT: Use DROP ... CASCADE to drop the dependent objects too. simple conn=mz_system,user=mz_system CREATE VIEW v2 AS SELECT * FROM t; ---- COMPLETE 0 simple conn=mz_system,user=mz_system DROP OWNED BY materialize; ---- db error: ERROR: cannot drop table "materialize.public.t": still depended upon by view "materialize.public.v1", view "materialize.public.v2" HINT: Use DROP ... CASCADE to drop the dependent objects too. simple conn=mz_system,user=mz_system CREATE MATERIALIZED VIEW mv IN CLUSTER c AS SELECT 1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system DROP OWNED BY materialize; ---- db error: ERROR: cannot drop cluster "c": still depended upon by materialized view "materialize.public.mv" HINT: Use DROP ... CASCADE to drop the dependent objects too. simple conn=mz_system,user=mz_system DROP OWNED BY materialize CASCADE; ---- COMPLETE 0 query T SELECT name FROM mz_databases WHERE name = 'db' ---- query T SELECT name FROM mz_schemas WHERE name = 's' ---- query T SELECT name FROM mz_tables WHERE name = 't' ---- query T SELECT name FROM mz_clusters WHERE name = 'c' ---- query T SELECT name FROM mz_cluster_replicas WHERE name = 'replica1' ---- simple conn=mz_system,user=mz_system REVOKE CREATEDB, CREATECLUSTER ON SYSTEM FROM materialize; ---- COMPLETE 0 ## Test dropping temporary objects statement ok CREATE TEMP VIEW v AS SELECT 1 statement ok DROP OWNED BY materialize ## Test dropping system objects simple conn=mz_system,user=mz_system DROP OWNED BY mz_system CASCADE; ---- db error: ERROR: cannot drop objects owned by role "mz_system", "mz_analytics" because they are required by the database system ## Test dropping PUBLIC objects simple conn=mz_system,user=mz_system DROP OWNED BY PUBLIC; ---- db error: ERROR: role name "public" is reserved DETAIL: The role "public" and the prefixes "mz_" and "pg_" are reserved for system roles. simple conn=mz_system,user=mz_system GRANT ALL PRIVILEGES ON SYSTEM TO materialize; ---- COMPLETE 0 ## Testing dropping a cluster with bound objects statement ok CREATE CLUSTER c REPLICAS (r1 (SIZE '1')); statement ok CREATE MATERIALIZED VIEW mv IN CLUSTER c AS SELECT 1; query T SELECT name FROM mz_clusters WHERE name = 'c' ---- c query T SELECT name FROM mz_materialized_views WHERE name = 'mv' ---- mv statement ok DROP OWNED BY materialize query T SELECT name FROM mz_clusters WHERE name = 'c' ---- query T SELECT name FROM mz_materialized_views WHERE name = 'mv' ---- statement ok CREATE CLUSTER c REPLICAS (r1 (SIZE '1')); statement ok GRANT CREATE ON CLUSTER c TO joe simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA public TO joe ---- COMPLETE 0 simple conn=joe,user=joe CREATE MATERIALIZED VIEW mv IN CLUSTER c AS SELECT 1; ---- COMPLETE 0 query T SELECT name FROM mz_clusters WHERE name = 'c' ---- c query T SELECT name FROM mz_materialized_views WHERE name = 'mv' ---- mv statement error cannot drop cluster "c": still depended upon by materialized view "materialize.public.mv" DROP OWNED BY materialize query T SELECT name FROM mz_clusters WHERE name = 'c' ---- c query T SELECT name FROM mz_materialized_views WHERE name = 'mv' ---- mv statement ok DROP OWNED BY materialize CASCADE query T SELECT name FROM mz_clusters WHERE name = 'c' ---- query T SELECT name FROM mz_materialized_views WHERE name = 'mv' ---- simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA public FROM joe ---- COMPLETE 0 statement ok DROP OWNED BY materialize CASCADE # Test REASSIGN OWNED statement ok CREATE DATABASE db query T SELECT mz_roles.name FROM mz_databases LEFT JOIN mz_roles ON mz_databases.owner_id = mz_roles.id WHERE mz_databases.name = 'db' ---- materialize statement ok CREATE SCHEMA s query T SELECT mz_roles.name FROM mz_schemas LEFT JOIN mz_roles ON mz_schemas.owner_id = mz_roles.id WHERE mz_schemas.name = 's' ---- materialize statement ok CREATE TABLE t (); query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 't' ---- materialize simple conn=joe,user=joe CREATE TABLE t1 (); ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 't1' ---- joe statement ok CREATE CLUSTER c REPLICAS (replica1 (SIZE '1')); query T SELECT mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'c' ---- materialize query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'replica1' ---- materialize simple conn=mz_system,user=mz_system REASSIGN OWNED BY materialize, joe TO group1; ---- COMPLETE 0 query T SELECT mz_roles.name FROM mz_databases LEFT JOIN mz_roles ON mz_databases.owner_id = mz_roles.id WHERE mz_databases.name = 'db' ---- group1 query T SELECT mz_roles.name FROM mz_schemas LEFT JOIN mz_roles ON mz_schemas.owner_id = mz_roles.id WHERE mz_schemas.name = 's' ---- group1 query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 't' ---- group1 query T SELECT mz_roles.name FROM mz_tables LEFT JOIN mz_roles ON mz_tables.owner_id = mz_roles.id WHERE mz_tables.name = 't1' ---- group1 query T SELECT mz_roles.name FROM mz_clusters LEFT JOIN mz_roles ON mz_clusters.owner_id = mz_roles.id WHERE mz_clusters.name = 'c' ---- group1 query T SELECT mz_roles.name FROM mz_cluster_replicas LEFT JOIN mz_roles ON mz_cluster_replicas.owner_id = mz_roles.id WHERE mz_cluster_replicas.name = 'replica1' ---- group1 ## Test reassigning temporary objects. It's weird that this is allowed, but it is. simple conn=mz_system,user=mz_system GRANT joe TO materialize ---- COMPLETE 0 statement ok CREATE TEMPORARY VIEW v AS SELECT 1 statement ok REASSIGN OWNED BY materialize TO joe ## Test reassigning system objects simple conn=mz_system,user=mz_system REASSIGN OWNED BY mz_system TO materialize; ---- db error: ERROR: cannot reassign objects owned by role "mz_system" because they are required by the database system ## Test reassigning PUBLIC objects simple conn=mz_system,user=mz_system REASSIGN OWNED BY PUBLIC TO materialize; ---- db error: ERROR: role name "public" is reserved DETAIL: The role "public" and the prefixes "mz_" and "pg_" are reserved for system roles. simple conn=mz_system,user=mz_system REASSIGN OWNED BY materialize TO PUBLIC; ---- db error: ERROR: role name "public" is reserved DETAIL: The role "public" and the prefixes "mz_" and "pg_" are reserved for system roles. # Disable rbac checks. simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_rbac_checks TO false; ---- COMPLETE 0