# 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 # Start from a pristine server reset-server statement ok CREATE TABLE a ( x int8, y text, z jsonb ); query T SELECT obj_description((SELECT oid FROM mz_tables WHERE name = 'a'), 'pg_class') ---- NULL query TTT SHOW OBJECTS ---- a table (empty) statement ok COMMENT ON TABLE a IS 'foo_table'; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- table NULL foo_table query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 foo_table query TTT SHOW OBJECTS ---- a table foo_table query TT SELECT obj_description((SELECT oid FROM mz_tables WHERE name = 'a'), 'pg_class'), obj_description((SELECT oid FROM mz_tables WHERE name = 'a'), 'notexist') ---- foo_table NULL statement ok COMMENT ON COLUMN a.y IS 'load_bearing'; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- table NULL foo_table table 2 load_bearing query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 foo_table 2 load_bearing query T SELECT col_description((SELECT oid FROM mz_tables WHERE name = 'a'), 2) ---- load_bearing query T SELECT col_description((SELECT oid FROM mz_tables WHERE name = 'a'), 1) ---- NULL query TT SHOW TABLES; ---- a foo_table query TT rowsort SELECT name, comment FROM (SHOW COLUMNS FROM a); ---- x (empty) z (empty) y load_bearing statement ok CREATE TABLE b ( ts timestamptz ); statement ok COMMENT ON COLUMN b.ts IS 'utc_timestamp'; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- table NULL foo_table table 2 load_bearing table 1 utc_timestamp query IT rowsort SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 foo_table 2 load_bearing 1 utc_timestamp statement ok COMMENT ON TABLE b IS 'foo_table'; query TT SHOW TABLES; ---- a foo_table b foo_table statement ok DROP TABLE a; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- table NULL foo_table table 1 utc_timestamp query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 foo_table 1 utc_timestamp statement ok COMMENT ON TABLE b IS NULL query TT SHOW TABLES; ---- b (empty) statement ok COMMENT ON COLUMN b.ts IS NULL query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- statement error unknown catalog item 'c' COMMENT ON TABLE c IS 'does_not_exist'; statement error column "b.does_not_exist" does not exist COMMENT ON COLUMN b.does_not_exist IS 'foo'; statement ok CREATE INDEX b_idx ON b (ts); statement ok COMMENT ON INDEX b_idx IS 'speed_up'; query TT SELECT name, comment FROM (SHOW INDEXES); ---- b_idx speed_up statement ok CREATE VIEW c (col_1, col_2) AS VALUES ('a', 'b'); statement ok COMMENT ON VIEW c IS 'this_is_a_view'; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- index NULL speed_up view NULL this_is_a_view query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 speed_up 0 this_is_a_view query T SELECT obj_description((SELECT oid FROM mz_indexes WHERE name = 'b_idx'), 'pg_class') ---- speed_up statement ok DROP TABLE b CASCADE; statement ok COMMENT ON COLUMN c.col_1 IS 'this_works'; query TT SELECT name, comment FROM (SHOW VIEWS); ---- c this_is_a_view query TT rowsort SELECT name, comment FROM (SHOW COLUMNS FROM c); ---- col_2 (empty) col_1 this_works query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- view 1 this_works view NULL this_is_a_view query IT rowsort SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 1 this_works 0 this_is_a_view statement ok CREATE MATERIALIZED VIEW mv ( x ) AS SELECT 1 statement ok COMMENT ON COLUMN mv.x IS 'comment_mat_view_col'; statement ok COMMENT ON MATERIALIZED VIEW mv IS 'mat_foo'; query TT SELECT name, comment FROM (SHOW MATERIALIZED VIEWS); ---- mv mat_foo query TT SELECT name, comment FROM (SHOW COLUMNS FROM mv); ---- x comment_mat_view_col query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- view 1 this_works view NULL this_is_a_view materialized-view NULL mat_foo materialized-view 1 comment_mat_view_col query IT rowsort SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 mat_foo 1 this_works 0 this_is_a_view 1 comment_mat_view_col query TTT rowsort SHOW OBJECTS ---- c view this_is_a_view mv materialized-view mat_foo query T SELECT obj_description((SELECT oid FROM mz_views WHERE name = 'c'), 'pg_class') ---- this_is_a_view query T SELECT col_description((SELECT oid FROM mz_views WHERE name = 'c'), 1) ---- this_works query TTTT SELECT obj_description((SELECT oid FROM mz_views WHERE name = 'c'), 'pg_class'), col_description((SELECT oid FROM mz_views WHERE name = 'c'), 1), obj_description( (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'pg_class'), col_description((SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 1) ---- this_is_a_view this_works mat_foo comment_mat_view_col statement ok DROP VIEW c; statement ok DROP MATERIALIZED VIEW mv; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- statement ok CREATE CLUSTER comment_cluster REPLICAS (r1 (SIZE '1'), r2 (SIZE '1')); statement ok COMMENT ON CLUSTER comment_cluster IS 'careful_now'; query TT rowsort SELECT name, comment FROM (SHOW CLUSTERS); ---- mz_probe (empty) mz_system (empty) mz_support (empty) quickstart (empty) mz_analytics (empty) mz_catalog_server (empty) comment_cluster careful_now statement ok COMMENT ON CLUSTER REPLICA comment_cluster.r2 IS 'second_replicator'; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- cluster NULL careful_now cluster-replica NULL second_replicator query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- statement ok DROP CLUSTER REPLICA comment_cluster.r2; statement ok CREATE SOURCE my_webhook IN CLUSTER comment_cluster FROM WEBHOOK BODY FORMAT TEXT; statement ok COMMENT ON SOURCE my_webhook IS 'all_the_data'; statement ok COMMENT ON COLUMN my_webhook.body IS 'json_blob'; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- source 1 json_blob source NULL all_the_data cluster NULL careful_now query TT SELECT obj_description((SELECT oid FROM mz_sources WHERE name = 'my_webhook'), 'pg_class'), col_description((SELECT oid FROM mz_sources WHERE name = 'my_webhook'), 1) ---- all_the_data json_blob query IT rowsort SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 1 json_blob 0 all_the_data query TT SELECT name, comment FROM (SHOW SOURCES); ---- my_webhook all_the_data query TTT SHOW OBJECTS ---- my_webhook source all_the_data statement ok CREATE TYPE int4_list AS LIST (ELEMENT TYPE = int4); statement ok COMMENT ON TYPE int4_list IS 'supercool_list'; statement ok CREATE TYPE custom_type AS (x integer, y int4_list); statement ok CREATE TYPE custom_map_type AS MAP (KEY TYPE = text, VALUE TYPE = custom_type) statement ok COMMENT ON TYPE custom_map_type IS 'custom_map_type_comment'; statement error cannot be depended upon COMMENT ON COLUMN custom_map_type.key IS 'comment_on_key'; query TT rowsort SHOW TYPES; ---- custom_map_type custom_map_type_comment custom_type (empty) int4_list supercool_list statement ok CREATE TYPE custom_list_type AS LIST (ELEMENT TYPE = custom_type) statement ok COMMENT ON TYPE custom_list_type IS 'custom_list_type_comment'; statement error cannot be depended upon COMMENT ON COLUMN custom_list_type.element IS 'comment_on_element'; statement error cannot be depended upon COMMENT ON COLUMN custom_map_type.key IS 'comment_on_key'; statement ok COMMENT ON TYPE custom_type IS 'custom_type_comment'; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- source 1 json_blob type NULL supercool_list source NULL all_the_data cluster NULL careful_now type NULL custom_type_comment type NULL custom_map_type_comment type NULL custom_list_type_comment query TTT SHOW OBJECTS ---- custom_list_type type custom_list_type_comment custom_map_type type custom_map_type_comment custom_type type custom_type_comment int4_list type supercool_list my_webhook source all_the_data statement ok DROP TYPE custom_map_type; statement ok DROP TYPE custom_list_type; statement ok COMMENT ON COLUMN custom_type.x IS 'custom_type_x_comment'; statement ok COMMENT ON COLUMN custom_type.y IS 'custom_type_y_comment'; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- source 1 json_blob type NULL supercool_list source NULL all_the_data cluster NULL careful_now type NULL custom_type_comment type 1 custom_type_x_comment type 2 custom_type_y_comment query IT rowsort SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 1 json_blob 0 all_the_data 0 supercool_list 0 custom_type_comment 1 custom_type_x_comment 2 custom_type_y_comment query TTT rowsort SHOW OBJECTS ---- custom_type type custom_type_comment int4_list type supercool_list my_webhook source all_the_data query TT SELECT obj_description((SELECT oid FROM mz_types WHERE name = 'custom_type'), 'pg_type'), col_description((SELECT oid FROM mz_types WHERE name = 'custom_type'), 1) ---- custom_type_comment NULL statement ok DROP CLUSTER comment_cluster CASCADE; statement ok CREATE SECRET my_secret AS 'foobar'; statement ok COMMENT ON SECRET my_secret IS 'supersecret'; query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- secret NULL supersecret type NULL supercool_list type NULL custom_type_comment type 1 custom_type_x_comment type 2 custom_type_y_comment query IT rowsort SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 supercool_list 0 custom_type_comment 1 custom_type_x_comment 2 custom_type_y_comment query T SELECT obj_description((SELECT oid FROM mz_secrets WHERE name = 'my_secret'), 'pg_class') ---- NULL statement ok CREATE DATABASE comment_on_db; statement ok CREATE SCHEMA comment_on_schema; statement ok COMMENT ON DATABASE comment_on_db IS 'this_is_my_db'; statement ok COMMENT ON SCHEMA comment_on_schema IS 'this_is_my_schema'; query TTT SHOW OBJECTS ---- custom_type type custom_type_comment int4_list type supercool_list my_secret secret supersecret statement ok DROP SECRET my_secret; statement ok DROP TYPE custom_type; statement ok DROP TYPE int4_list; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- database NULL this_is_my_db schema NULL this_is_my_schema query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- 0 this_is_my_schema query TT SELECT obj_description( (SELECT oid FROM mz_databases WHERE name = 'comment_on_db'), 'pg_class'), obj_description( (SELECT oid FROM mz_schemas WHERE name = 'comment_on_schema'), 'pg_namespace') ---- NULL this_is_my_schema statement ok DROP DATABASE comment_on_db; statement ok DROP SCHEMA comment_on_schema; # Test RBAC. statement ok CREATE ROLE student; statement ok COMMENT ON ROLE student IS 'limited_role'; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- role NULL limited_role query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- statement ok CREATE TABLE foo ( x int8 ); simple conn=student,user=student COMMENT ON TABLE foo IS 'comment_from_student'; ---- db error: ERROR: must be owner of TABLE materialize.public.foo statement ok CREATE ROLE teacher; simple conn=mz_system,user=mz_system GRANT CREATEROLE ON SYSTEM TO student; ---- COMPLETE 0 simple conn=student,user=student COMMENT ON ROLE teacher IS 'foo'; ---- COMPLETE 0 query TTT rowsort SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- role NULL foo role NULL limited_role query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- simple conn=mz_system,user=mz_system REVOKE CREATEROLE ON SYSTEM FROM student; ---- COMPLETE 0 # To comment on a Role you must have the CREATEROLE privilege. simple conn=student,user=student COMMENT ON ROLE teacher IS 'updated_teacher_comment'; ---- db error: ERROR: permission denied for SYSTEM DETAIL: The 'student' role needs CREATEROLE privileges on SYSTEM statement ok DROP ROLE student; statement ok DROP ROLE teacher; query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ---- statement error must be owner of DATABASE materialize COMMENT ON DATABASE materialize IS 'main_db'; statement error must be owner of SCHEMA materialize.public COMMENT ON SCHEMA public IS 'everyone_has_access'; simple conn=mz_system,user=mz_system COMMENT ON DATABASE materialize IS 'main_db'; ---- COMPLETE 0 query TTT SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%'; ---- database NULL main_db query IT SELECT objsubid, description FROM pg_description WHERE objoid >= 20000; ----