# 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 statement ok CREATE ROLE test_role # Test default privileges simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON SYSTEM FROM materialize; ---- COMPLETE 0 ## Create some helper views statement ok CREATE VIEW database_privileges (name, privilege) AS SELECT name, unnest(privileges)::text FROM mz_databases; statement ok CREATE VIEW schema_privileges (name, privilege) AS SELECT name, unnest(privileges)::text, database_id FROM mz_schemas; statement ok CREATE VIEW cluster_privileges (name, privilege) AS SELECT name, unnest(privileges)::text FROM mz_clusters; statement ok CREATE VIEW item_privileges (name, type, privilege) AS SELECT name, type, unnest(privileges)::text FROM mz_objects; ## Test built-in objects query TT rowsort SELECT * FROM database_privileges ---- materialize =U/mz_system materialize mz_system=UC/mz_system materialize materialize=UC/mz_system materialize mz_support=U/mz_system query TT rowsort SELECT name, privilege FROM schema_privileges ORDER BY name, privilege ---- information_schema =U/mz_system information_schema mz_system=UC/mz_system information_schema mz_support=U/mz_system mz_catalog =U/mz_system mz_catalog mz_system=UC/mz_system mz_catalog mz_support=U/mz_system mz_catalog_unstable =U/mz_system mz_catalog_unstable mz_support=U/mz_system mz_catalog_unstable mz_system=UC/mz_system mz_unsafe =U/mz_system mz_unsafe mz_support=U/mz_system mz_unsafe mz_system=UC/mz_system mz_internal =U/mz_system mz_internal mz_system=UC/mz_system mz_internal mz_support=U/mz_system mz_introspection =U/mz_system mz_introspection mz_system=UC/mz_system mz_introspection mz_support=U/mz_system pg_catalog =U/mz_system pg_catalog mz_system=UC/mz_system pg_catalog mz_support=U/mz_system public =U/mz_system public mz_system=UC/mz_system public materialize=UC/mz_system public mz_support=U/mz_system query TT rowsort SELECT * FROM cluster_privileges ORDER BY name, privilege ---- mz_analytics mz_analytics=UC/mz_analytics mz_analytics mz_system=UC/mz_analytics mz_catalog_server =U/mz_system mz_catalog_server mz_support=UC/mz_system mz_catalog_server mz_system=UC/mz_system mz_probe mz_monitor=U/mz_system mz_probe mz_support=U/mz_system mz_probe mz_system=UC/mz_system mz_support mz_support=UC/mz_support mz_support mz_system=UC/mz_support mz_system mz_support=U/mz_system mz_system mz_system=UC/mz_system quickstart =U/mz_system quickstart materialize=UC/mz_system quickstart mz_support=U/mz_system quickstart mz_system=UC/mz_system ### The materialize privilege comes from the views created above ### The mz_support privilege comes from the `_redacted` ### statement logging views. query T rowsort SELECT DISTINCT(privilege) FROM item_privileges WHERE type = 'view' OR type = 'materialized view' OR type = 'source' ---- =r/mz_system mz_system=r/mz_system mz_monitor=r/mz_system mz_support=r/mz_system mz_analytics=r/mz_system materialize=r/materialize mz_monitor_redacted=r/mz_system query T SELECT DISTINCT(privilege) FROM item_privileges WHERE type = 'table' ---- =r/mz_system mz_monitor=r/mz_system mz_system=arwd/mz_system query T SELECT DISTINCT(privilege) FROM item_privileges WHERE type = 'type' ---- =U/mz_system mz_system=U/mz_system query T SELECT privileges::text FROM mz_system_privileges ---- mz_system=RBNP/mz_system ## Test user created objects simple conn=mz_system,user=mz_system GRANT ALL PRIVILEGES ON SYSTEM TO materialize; ---- COMPLETE 0 statement ok CREATE TABLE t (a INT); query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t materialize=arwd/materialize statement ok CREATE VIEW v AS SELECT 1; query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v materialize=r/materialize statement ok CREATE MATERIALIZED VIEW mv AS SELECT 1; query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv materialize=r/materialize statement ok CREATE SOURCE s FROM LOAD GENERATOR COUNTER; query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s materialize=r/materialize statement ok CREATE TYPE ty AS LIST (ELEMENT TYPE=bool); query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty =U/materialize ty materialize=U/materialize statement ok CREATE SECRET se AS decode('c2VjcmV0Cg==', 'base64'); query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se materialize=U/materialize statement ok CREATE CONNECTION conn TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn materialize=U/materialize statement ok CREATE CLUSTER c REPLICAS (r1 (SIZE '1')); query TT rowsort SELECT * FROM cluster_privileges WHERE name = 'c' ---- c materialize=UC/materialize c mz_support=U/materialize statement ok CREATE DATABASE d; query TT rowsort SELECT * FROM database_privileges WHERE name = 'd' ---- d materialize=UC/materialize d mz_support=U/materialize query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'public' ORDER BY name, privilege ---- public =U/mz_system public =U/materialize public mz_system=UC/mz_system public materialize=UC/mz_system public materialize=UC/materialize public mz_support=U/mz_system public mz_support=U/materialize statement ok CREATE SCHEMA sch; query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch materialize=UC/materialize sch mz_support=U/materialize # Changing the owner of an object should change the grantor of all privileges to the new owner and # transfer the privileges of the old owner to the new owner. simple conn=mz_system,user=mz_system CREATE ROLE joe ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER TABLE t OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t joe=arwd/joe simple conn=mz_system,user=mz_system ALTER VIEW v OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v joe=r/joe simple conn=mz_system,user=mz_system ALTER MATERIALIZED VIEW mv OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv joe=r/joe simple conn=mz_system,user=mz_system ALTER SOURCE s OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s joe=r/joe simple conn=mz_system,user=mz_system ALTER TYPE ty OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty =U/joe ty joe=U/joe simple conn=mz_system,user=mz_system ALTER SECRET se OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se joe=U/joe simple conn=mz_system,user=mz_system ALTER CONNECTION conn OWNER TO joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn joe=U/joe simple conn=mz_system,user=mz_system ALTER CLUSTER c OWNER TO joe ---- COMPLETE 0 query TT rowsort SELECT * FROM cluster_privileges WHERE name = 'c' ---- c joe=UC/joe c mz_support=U/joe simple conn=mz_system,user=mz_system ALTER DATABASE d OWNER TO joe ---- COMPLETE 0 query TT rowsort SELECT * FROM database_privileges WHERE name = 'd' ---- d joe=UC/joe d mz_support=U/joe simple conn=mz_system,user=mz_system ALTER SCHEMA sch OWNER TO joe ---- COMPLETE 0 query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch joe=UC/joe sch mz_support=U/joe ## Switch the owners back to materialize simple conn=mz_system,user=mz_system ALTER TABLE t OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER VIEW v OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER MATERIALIZED VIEW mv OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER SOURCE s OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER TYPE ty OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER SECRET se OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER CONNECTION conn OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER CLUSTER c OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER DATABASE d OWNER TO materialize ---- COMPLETE 0 simple conn=mz_system,user=mz_system ALTER SCHEMA sch OWNER TO materialize ---- COMPLETE 0 # Test GRANT and REVOKE simple conn=mz_system,user=mz_system CREATE ROLE other ---- 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 ## Table query B SELECT has_table_privilege('joe', 't', 'SELECT') ---- false query B SELECT has_table_privilege('child', 't', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- false statement ok GRANT SELECT on TABLE t TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t joe=r/materialize t materialize=arwd/materialize query B SELECT has_table_privilege('joe', 't', 'SELECT') ---- true query B SELECT has_table_privilege('child', 't', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- true ### Duplicate grants have no effect statement ok GRANT SELECT on TABLE t TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t joe=r/materialize t materialize=arwd/materialize statement ok GRANT SELECT, INSERT, UPDATE on TABLE t TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t =arw/materialize t joe=r/materialize t materialize=arwd/materialize simple conn=joe1,user=joe GRANT SELECT on TABLE t TO other ---- db error: ERROR: must be owner of TABLE materialize.public.t statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE SELECT on TABLE t FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t =arw/materialize t materialize=arwd/materialize query B SELECT has_table_privilege('joe', 't', 'SELECT') ---- true query B SELECT has_table_privilege('child', 't', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- true ### Duplicate revokes have no effect statement ok REVOKE SELECT on TABLE t FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t =arw/materialize t materialize=arwd/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE INSERT, UPDATE ON TABLE t FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 't' ---- t =r/materialize t materialize=arwd/materialize statement ok REVOKE SELECT ON TABLE t FROM PUBLIC query B SELECT has_table_privilege('joe', 't', 'SELECT') ---- false query B SELECT has_table_privilege('child', 't', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- false statement error invalid privilege types USAGE, CREATE for TABLE GRANT USAGE, CREATE ON TABLE t TO joe ## View query B SELECT has_table_privilege('joe', 'v', 'SELECT') ---- false query B SELECT has_table_privilege('child', 'v', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- false statement ok GRANT SELECT on TABLE v TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v joe=r/materialize v materialize=r/materialize query B SELECT has_table_privilege('joe', 'v', 'SELECT') ---- true query B SELECT has_table_privilege('child', 'v', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- true ### Duplicate grants have no effect statement ok GRANT SELECT on TABLE v TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v joe=r/materialize v materialize=r/materialize statement ok GRANT SELECT on TABLE v TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v =r/materialize v joe=r/materialize v materialize=r/materialize simple conn=joe2,user=joe GRANT SELECT on TABLE v TO other ---- db error: ERROR: must be owner of VIEW materialize.public.v statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE SELECT on TABLE v FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v =r/materialize v materialize=r/materialize query B SELECT has_table_privilege('joe', 'v', 'SELECT') ---- true query B SELECT has_table_privilege('child', 'v', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- true ### Duplicate revokes have no effect statement ok REVOKE SELECT on TABLE v FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v =r/materialize v materialize=r/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE SELECT ON TABLE v FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'v' ---- v materialize=r/materialize query B SELECT has_table_privilege('joe', 'v', 'SELECT') ---- false query B SELECT has_table_privilege('child', 'v', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT') ---- false statement error invalid privilege types USAGE, CREATE for VIEW GRANT INSERT, UPDATE, DELETE, USAGE, CREATE ON TABLE v TO joe ## Materialized View query B SELECT has_table_privilege('joe', 'mv', 'SELECT') ---- false query B SELECT has_table_privilege('child', 'mv', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- false statement ok GRANT SELECT on TABLE mv TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv joe=r/materialize mv materialize=r/materialize query B SELECT has_table_privilege('joe', 'mv', 'SELECT') ---- true query B SELECT has_table_privilege('child', 'mv', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- true ### Duplicate grants have no effect statement ok GRANT SELECT on TABLE mv TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv joe=r/materialize mv materialize=r/materialize statement ok GRANT SELECT on TABLE mv TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv =r/materialize mv joe=r/materialize mv materialize=r/materialize simple conn=joe3,user=joe GRANT SELECT on TABLE mv TO other ---- db error: ERROR: must be owner of MATERIALIZED VIEW materialize.public.mv statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE SELECT on TABLE mv FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv =r/materialize mv materialize=r/materialize query B SELECT has_table_privilege('joe', 'mv', 'SELECT') ---- true query B SELECT has_table_privilege('child', 'mv', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- true ### Duplicate revokes have no effect statement ok REVOKE SELECT on TABLE mv FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv =r/materialize mv materialize=r/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE SELECT ON TABLE mv FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'mv' ---- mv materialize=r/materialize query B SELECT has_table_privilege('joe', 'mv', 'SELECT') ---- false query B SELECT has_table_privilege('child', 'mv', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT') ---- false statement error invalid privilege types USAGE, CREATE for MATERIALIZED VIEW GRANT INSERT, UPDATE, DELETE, USAGE, CREATE ON TABLE mv TO joe ## Source query B SELECT has_table_privilege('joe', 's', 'SELECT') ---- false query B SELECT has_table_privilege('child', 's', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- false statement ok GRANT SELECT on TABLE s TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s joe=r/materialize s materialize=r/materialize query B SELECT has_table_privilege('joe', 's', 'SELECT') ---- true query B SELECT has_table_privilege('child', 's', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- true ### Duplicate grants have no effect statement ok GRANT SELECT on TABLE s TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s joe=r/materialize s materialize=r/materialize statement ok GRANT SELECT on TABLE s TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s =r/materialize s joe=r/materialize s materialize=r/materialize simple conn=joe4,user=joe GRANT SELECT on TABLE s TO other ---- db error: ERROR: must be owner of SOURCE materialize.public.s statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE SELECT on TABLE s FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s =r/materialize s materialize=r/materialize query B SELECT has_table_privilege('joe', 's', 'SELECT') ---- true query B SELECT has_table_privilege('child', 's', 'SELECT') ---- true query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- true ### Duplicate revokes have no effect statement ok REVOKE SELECT on TABLE s FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s =r/materialize s materialize=r/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE SELECT ON TABLE s FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 's' ---- s materialize=r/materialize query B SELECT has_table_privilege('joe', 's', 'SELECT') ---- false query B SELECT has_table_privilege('child', 's', 'SELECT') ---- false query B SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT') ---- false statement error invalid privilege types USAGE, CREATE for SOURCE GRANT INSERT, UPDATE, DELETE, USAGE, CREATE ON TABLE s TO joe ## Type statement ok REVOKE USAGE on TYPE ty FROM PUBLIC query B SELECT has_type_privilege('joe', 'ty', 'USAGE') ---- false query B SELECT has_type_privilege('child', 'ty', 'USAGE') ---- false query B SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- false query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE') ---- false query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- false statement ok GRANT USAGE on TYPE ty TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty joe=U/materialize ty materialize=U/materialize query B SELECT has_type_privilege('joe', 'ty', 'USAGE') ---- true query B SELECT has_type_privilege('child', 'ty', 'USAGE') ---- true query B SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- true query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE') ---- true query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- true ### Duplicate grants have no effect statement ok GRANT USAGE on TYPE ty TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty joe=U/materialize ty materialize=U/materialize statement ok GRANT USAGE on TYPE ty TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty =U/materialize ty joe=U/materialize ty materialize=U/materialize simple conn=joe5,user=joe GRANT USAGE on TYPE ty TO other ---- db error: ERROR: must be owner of TYPE materialize.public.ty statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE USAGE on TYPE ty FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty =U/materialize ty materialize=U/materialize query B SELECT has_type_privilege('joe', 'ty', 'USAGE') ---- true query B SELECT has_type_privilege('child', 'ty', 'USAGE') ---- true query B SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- true query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE') ---- true query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- true ### Duplicate revokes have no effect statement ok REVOKE USAGE on TYPE ty FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty =U/materialize ty materialize=U/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE USAGE ON TYPE ty FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty materialize=U/materialize query B SELECT has_type_privilege('joe', 'ty', 'SELECT') ---- false query B SELECT has_type_privilege('child', 'ty', 'USAGE') ---- false query B SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- false query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE') ---- false query B SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- false statement ok GRANT USAGE on TYPE ty TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'ty' ---- ty =U/materialize ty materialize=U/materialize statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE, CREATE for TYPE GRANT INSERT, SELECT, UPDATE, DELETE, CREATE ON TYPE ty TO joe ## Secret query B SELECT has_secret_privilege('joe', 'se', 'USAGE') ---- false query B SELECT has_secret_privilege('child', 'se', 'USAGE') ---- false query B SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- false query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE') ---- false query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- false statement ok GRANT USAGE on SECRET se TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se joe=U/materialize se materialize=U/materialize query B SELECT has_secret_privilege('joe', 'se', 'USAGE') ---- true query B SELECT has_secret_privilege('child', 'se', 'USAGE') ---- true query B SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- true query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE') ---- true query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- true ### Duplicate grants have no effect statement ok GRANT USAGE on SECRET se TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se joe=U/materialize se materialize=U/materialize statement ok GRANT USAGE on SECRET se TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se =U/materialize se joe=U/materialize se materialize=U/materialize simple conn=joe6,user=joe GRANT USAGE on SECRET se TO other ---- db error: ERROR: must be owner of SECRET materialize.public.se statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE USAGE on SECRET se FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se =U/materialize se materialize=U/materialize query B SELECT has_secret_privilege('joe', 'se', 'USAGE') ---- true query B SELECT has_secret_privilege('child', 'se', 'USAGE') ---- true query B SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- true query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE') ---- true query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- true ### Duplicate revokes have no effect statement ok REVOKE USAGE on SECRET se FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se =U/materialize se materialize=U/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE USAGE ON SECRET se FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'se' ---- se materialize=U/materialize query B SELECT has_secret_privilege('joe', 'se', 'USAGE') ---- false query B SELECT has_secret_privilege('child', 'se', 'USAGE') ---- false query B SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- false query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE') ---- false query B SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- false statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE, CREATE for SECRET GRANT INSERT, SELECT, UPDATE, DELETE, CREATE ON SECRET se TO joe ## Connection query B SELECT has_connection_privilege('joe', 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege('child', 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- false query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- false statement ok GRANT USAGE on CONNECTION conn TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn joe=U/materialize conn materialize=U/materialize query B SELECT has_connection_privilege('joe', 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege('child', 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- true query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- true ### Duplicate grants have no effect statement ok GRANT USAGE on CONNECTION conn TO joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn joe=U/materialize conn materialize=U/materialize statement ok GRANT USAGE on CONNECTION conn TO PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn =U/materialize conn joe=U/materialize conn materialize=U/materialize simple conn=joe7,user=joe GRANT USAGE on CONNECTION conn TO other ---- db error: ERROR: must be owner of CONNECTION materialize.public.conn statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE USAGE on CONNECTION conn FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn =U/materialize conn materialize=U/materialize query B SELECT has_connection_privilege('joe', 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege('child', 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- true query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- true ### Duplicate revokes have no effect statement ok REVOKE USAGE on CONNECTION conn FROM joe query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn =U/materialize conn materialize=U/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE USAGE ON CONNECTION conn FROM PUBLIC query TT SELECT name, privilege FROM item_privileges WHERE name = 'conn' ---- conn materialize=U/materialize query B SELECT has_connection_privilege('joe', 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege('child', 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- false query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- false statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE, CREATE for CONNECTION GRANT INSERT, SELECT, UPDATE, DELETE, CREATE ON CONNECTION conn TO joe ## Cluster query B SELECT has_cluster_privilege('joe', 'c', 'USAGE') ---- false query B SELECT has_cluster_privilege('child', 'c', 'USAGE') ---- false query B SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE') ---- false statement ok GRANT USAGE on CLUSTER c TO joe query TT rowsort SELECT name, privilege FROM cluster_privileges WHERE name = 'c' ---- c joe=U/materialize c materialize=UC/materialize c mz_support=U/materialize query B SELECT has_cluster_privilege('joe', 'c', 'USAGE') ---- true query B SELECT has_cluster_privilege('child', 'c', 'USAGE') ---- true query B SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE') ---- true ### Duplicate grants have no effect statement ok GRANT USAGE on CLUSTER c TO joe query TT rowsort SELECT name, privilege FROM cluster_privileges WHERE name = 'c' ---- c joe=U/materialize c materialize=UC/materialize c mz_support=U/materialize statement ok GRANT USAGE, CREATE on CLUSTER c TO PUBLIC query TT rowsort SELECT name, privilege FROM cluster_privileges WHERE name = 'c' ---- c =UC/materialize c joe=U/materialize c materialize=UC/materialize c mz_support=U/materialize simple conn=joe8,user=joe GRANT USAGE on CLUSTER c TO other ---- db error: ERROR: must be owner of CLUSTER c statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE USAGE on CLUSTER c FROM joe query TT rowsort SELECT name, privilege FROM cluster_privileges WHERE name = 'c' ---- c =UC/materialize c materialize=UC/materialize c mz_support=U/materialize query B SELECT has_cluster_privilege('joe', 'c', 'USAGE') ---- true query B SELECT has_cluster_privilege('child', 'c', 'USAGE') ---- true query B SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE') ---- true ### Duplicate revokes have no effect statement ok REVOKE USAGE on CLUSTER c FROM joe query TT rowsort SELECT name, privilege FROM cluster_privileges WHERE name = 'c' ---- c =UC/materialize c materialize=UC/materialize c mz_support=U/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE CREATE, USAGE ON CLUSTER c FROM PUBLIC query TT rowsort SELECT name, privilege FROM cluster_privileges WHERE name = 'c' ---- c materialize=UC/materialize c mz_support=U/materialize query B SELECT has_cluster_privilege('joe', 'c', 'USAGE') ---- false query B SELECT has_cluster_privilege('child', 'c', 'USAGE') ---- false query B SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE') ---- false statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE for CLUSTER GRANT INSERT, SELECT, UPDATE, DELETE ON CLUSTER c TO joe ## Database query B SELECT has_database_privilege('joe', 'd', 'USAGE') ---- false query B SELECT has_database_privilege('child', 'd', 'USAGE') ---- false query B SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- false query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE') ---- false query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- false statement ok GRANT USAGE on DATABASE d TO joe query TT rowsort SELECT name, privilege FROM database_privileges WHERE name = 'd' ---- d joe=U/materialize d materialize=UC/materialize d mz_support=U/materialize query B SELECT has_database_privilege('joe', 'd', 'USAGE') ---- true query B SELECT has_database_privilege('child', 'd', 'USAGE') ---- true query B SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- true query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE') ---- true query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- true ### Duplicate grants have no effect statement ok GRANT USAGE on DATABASE d TO joe query TT rowsort SELECT name, privilege FROM database_privileges WHERE name = 'd' ---- d joe=U/materialize d materialize=UC/materialize d mz_support=U/materialize statement ok GRANT USAGE, CREATE on DATABASE d TO PUBLIC query TT rowsort SELECT name, privilege FROM database_privileges WHERE name = 'd' ---- d =UC/materialize d joe=U/materialize d materialize=UC/materialize d mz_support=U/materialize simple conn=joe9,user=joe GRANT USAGE on DATABASE d TO other ---- db error: ERROR: must be owner of DATABASE d statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE USAGE on DATABASE d FROM joe query TT rowsort SELECT name, privilege FROM database_privileges WHERE name = 'd' ---- d =UC/materialize d materialize=UC/materialize d mz_support=U/materialize query B SELECT has_database_privilege('joe', 'd', 'USAGE') ---- true query B SELECT has_database_privilege('child', 'd', 'USAGE') ---- true query B SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- true query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE') ---- true query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- true ### Duplicate revokes have no effect statement ok REVOKE USAGE on DATABASE d FROM joe query TT rowsort SELECT name, privilege FROM database_privileges WHERE name = 'd' ---- d =UC/materialize d materialize=UC/materialize d mz_support=U/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE CREATE, USAGE ON DATABASE d FROM PUBLIC query TT rowsort SELECT name, privilege FROM database_privileges WHERE name = 'd' ---- d materialize=UC/materialize d mz_support=U/materialize query B SELECT has_database_privilege('joe', 'd', 'USAGE') ---- false query B SELECT has_database_privilege('child', 'd', 'USAGE') ---- false query B SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- false query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE') ---- false query B SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- false statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE for DATABASE GRANT INSERT, SELECT, UPDATE, DELETE ON DATABASE d TO joe ## Schema query B SELECT has_schema_privilege('joe', 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege('child', 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- false query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- false statement ok GRANT USAGE on SCHEMA sch TO joe query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch joe=U/materialize sch materialize=UC/materialize sch mz_support=U/materialize query B SELECT has_schema_privilege('joe', 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege('child', 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- true query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- true ### Duplicate grants have no effect statement ok GRANT USAGE on SCHEMA sch TO joe query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch joe=U/materialize sch materialize=UC/materialize sch mz_support=U/materialize statement ok GRANT USAGE, CREATE on SCHEMA sch TO PUBLIC query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch =UC/materialize sch joe=U/materialize sch materialize=UC/materialize sch mz_support=U/materialize simple conn=joe10,user=joe GRANT USAGE on SCHEMA sch TO other ---- db error: ERROR: must be owner of SCHEMA materialize.sch statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe statement ok REVOKE USAGE on SCHEMA sch FROM joe query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch =UC/materialize sch materialize=UC/materialize sch mz_support=U/materialize query B SELECT has_schema_privilege('joe', 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege('child', 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- true query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- true ### Duplicate revokes have no effect statement ok REVOKE USAGE on SCHEMA sch FROM joe query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch =UC/materialize sch materialize=UC/materialize sch mz_support=U/materialize statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child statement ok REVOKE CREATE, USAGE ON SCHEMA sch FROM PUBLIC query TT rowsort SELECT name, privilege FROM schema_privileges WHERE name = 'sch' ---- sch materialize=UC/materialize sch mz_support=U/materialize query B SELECT has_schema_privilege('joe', 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege('child', 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- false query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- false ## System simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON SYSTEM FROM materialize; ---- COMPLETE 0 query B SELECT has_system_privilege('joe', 'CREATEDB') ---- false query B SELECT has_system_privilege('child', 'CREATEDB') ---- false query B SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB') ---- false simple conn=mz_system,user=mz_system GRANT CREATEDB ON SYSTEM TO joe ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- joe=B/mz_system mz_system=RBNP/mz_system query B SELECT has_system_privilege('joe', 'CREATEDB') ---- true query B SELECT has_system_privilege('child', 'CREATEDB') ---- true query B SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB') ---- true ### Duplicate grants have no effect simple conn=mz_system,user=mz_system GRANT CREATEDB ON SYSTEM TO joe ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- joe=B/mz_system mz_system=RBNP/mz_system simple conn=mz_system,user=mz_system GRANT CREATEROLE, CREATECLUSTER ON SYSTEM TO PUBLIC ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- =RN/mz_system joe=B/mz_system mz_system=RBNP/mz_system statement error role "joe" cannot be dropped because some objects depend on it DROP ROLE joe simple conn=mz_system,user=mz_system REVOKE CREATEDB ON SYSTEM FROM joe ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- =RN/mz_system mz_system=RBNP/mz_system query B SELECT has_system_privilege('joe', 'CREATEDB') ---- false query B SELECT has_system_privilege('child', 'CREATEDB') ---- false query B SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB') ---- false ### Duplicate revokes have no effect simple conn=mz_system,user=mz_system REVOKE CREATEDB ON SYSTEM FROM joe ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- =RN/mz_system mz_system=RBNP/mz_system statement ok DROP ROLE joe statement ok CREATE ROLE joe statement ok GRANT joe TO child simple conn=mz_system,user=mz_system REVOKE CREATEROLE, CREATECLUSTER ON SYSTEM FROM PUBLIC ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- mz_system=RBNP/mz_system query B SELECT has_system_privilege('joe', 'CREATEDB') ---- false query B SELECT has_system_privilege('child', 'CREATEDB') ---- false query B SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB') ---- false ## Test misc error scenarios statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE for SCHEMA GRANT INSERT, SELECT, UPDATE, DELETE ON SCHEMA sch TO joe simple conn=mz_system,user=mz_system GRANT INSERT ON TABLE mz_views TO joe ---- db error: ERROR: system item 'mz_catalog.mz_views' cannot be modified simple conn=mz_system,user=mz_system REVOKE INSERT ON TABLE mz_views FROM joe ---- db error: ERROR: system item 'mz_catalog.mz_views' cannot be modified simple conn=mz_system,user=mz_system GRANT SELECT ON TABLE mz_objects TO joe ---- db error: ERROR: system item 'mz_catalog.mz_objects' cannot be modified simple conn=mz_system,user=mz_system REVOKE SELECT ON TABLE mz_objects FROM joe ---- db error: ERROR: system item 'mz_catalog.mz_objects' cannot be modified simple conn=mz_system,user=mz_system GRANT SELECT ON TABLE mz_internal.mz_sink_statuses TO joe ---- db error: ERROR: system item 'mz_internal.mz_sink_statuses' cannot be modified simple conn=mz_system,user=mz_system REVOKE SELECT ON TABLE mz_internal.mz_sink_statuses FROM joe ---- db error: ERROR: system item 'mz_internal.mz_sink_statuses' cannot be modified simple conn=mz_system,user=mz_system GRANT USAGE ON TYPE bool TO joe ---- db error: ERROR: system item 'pg_catalog.bool' cannot be modified simple conn=mz_system,user=mz_system REVOKE USAGE ON TYPE bool FROM joe ---- db error: ERROR: system item 'pg_catalog.bool' cannot be modified simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER mz_system TO joe ---- db error: ERROR: system cluster 'mz_system' cannot be modified simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER mz_catalog_server TO joe ---- db error: ERROR: system cluster 'mz_catalog_server' cannot be modified simple conn=mz_system,user=mz_system REVOKE USAGE ON CLUSTER mz_system FROM joe ---- db error: ERROR: system cluster 'mz_system' cannot be modified simple conn=mz_system,user=mz_system REVOKE USAGE ON CLUSTER mz_catalog_server FROM joe ---- db error: ERROR: system cluster 'mz_catalog_server' cannot be modified simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA pg_catalog TO joe ---- db error: ERROR: system schema 'pg_catalog' cannot be modified simple conn=mz_system,user=mz_system REVOKE CREATE ON SCHEMA pg_catalog FROM joe ---- db error: ERROR: system schema 'pg_catalog' cannot be modified statement error unknown database 't' GRANT SELECT ON DATABASE t TO joe statement error invalid privilege types USAGE for TABLE GRANT SELECT, USAGE ON TABLE t TO joe statement error invalid privilege types CREATEROLE for TABLE "materialize.public.t" GRANT CREATEROLE ON TABLE t TO test_role statement error invalid privilege types CREATEDB for CLUSTER "c" REVOKE CREATEDB ON CLUSTER c FROM test_role ## Test multiple roles simple conn=mz_system,user=mz_system CREATE TABLE t1 (a INT); ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 mz_system=arwd/mz_system simple conn=mz_system,user=mz_system GRANT SELECT ON t1 TO joe, other ---- COMPLETE 0 query TT rowsort SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 joe=r/mz_system t1 other=r/mz_system t1 mz_system=arwd/mz_system simple conn=mz_system,user=mz_system GRANT SELECT, INSERT ON t1 TO test_role, other ---- COMPLETE 0 query TT rowsort SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 joe=r/mz_system t1 other=ar/mz_system t1 test_role=ar/mz_system t1 mz_system=arwd/mz_system simple conn=mz_system,user=mz_system REVOKE INSERT ON t1 FROM joe, test_role, other ---- COMPLETE 0 query TT rowsort SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 joe=r/mz_system t1 other=r/mz_system t1 test_role=r/mz_system t1 mz_system=arwd/mz_system simple conn=mz_system,user=mz_system REVOKE SELECT ON t1 FROM joe, test_role, other ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 mz_system=arwd/mz_system ## Test ALL keyword simple conn=mz_system,user=mz_system GRANT ALL ON t1 TO joe, test_role ---- COMPLETE 0 query TT rowsort SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 joe=arwd/mz_system t1 mz_system=arwd/mz_system t1 test_role=arwd/mz_system simple conn=mz_system,user=mz_system REVOKE ALL ON t1 FROM joe, test_role, other ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 't1' ---- t1 mz_system=arwd/mz_system simple conn=mz_system,user=mz_system CREATE VIEW v1 AS SELECT 1; ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'v1' ---- v1 mz_system=r/mz_system simple conn=mz_system,user=mz_system GRANT ALL ON v1 TO joe ---- COMPLETE 0 query TT rowsort SELECT name, privilege FROM item_privileges WHERE name = 'v1' ---- v1 joe=r/mz_system v1 mz_system=r/mz_system simple conn=mz_system,user=mz_system REVOKE ALL ON v1 FROM joe ---- COMPLETE 0 query TT SELECT name, privilege FROM item_privileges WHERE name = 'v1' ---- v1 mz_system=r/mz_system simple conn=mz_system,user=mz_system GRANT ALL ON SYSTEM TO joe ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- joe=RBNP/mz_system mz_system=RBNP/mz_system simple conn=mz_system,user=mz_system REVOKE ALL ON SYSTEM FROM joe ---- COMPLETE 0 query T SELECT privileges::text FROM mz_system_privileges ---- mz_system=RBNP/mz_system ## Test system objects simple conn=mz_system,user=mz_system GRANT SELECT on v1 TO mz_catalog_server ---- db error: ERROR: unknown role 'mz_catalog_server' simple conn=mz_system,user=mz_system GRANT SELECT on v1 TO mz_support ---- db error: ERROR: role name "mz_support" is reserved DETAIL: The role prefixes "mz_" and "pg_" are reserved for system roles. simple conn=mz_system,user=mz_system REVOKE SELECT on v1 FROM mz_support ---- db error: ERROR: role name "mz_support" is reserved DETAIL: The role prefixes "mz_" and "pg_" are reserved for system roles. simple conn=mz_system,user=mz_system GRANT INSERT on mz_tables TO joe ---- db error: ERROR: system item 'mz_catalog.mz_tables' cannot be modified simple conn=mz_system,user=mz_system REVOKE SELECT on mz_tables FROM joe ---- db error: ERROR: system item 'mz_catalog.mz_tables' cannot be modified simple conn=mz_system,user=mz_system GRANT USAGE on CLUSTER mz_system TO JOE ---- db error: ERROR: system cluster 'mz_system' cannot be modified simple conn=mz_system,user=mz_system REVOKE USAGE on CLUSTER mz_catalog_server FROM PUBLIC ---- db error: ERROR: system cluster 'mz_catalog_server' cannot be modified # Test has_X_privilege error and misc scenarios. ## If any input is NULL then the result is NULL. ### System query B SELECT has_system_privilege(NULL, 'CREATEROLE') ---- NULL query B SELECT has_system_privilege('joe', NULL) ---- NULL ### Cluster query B SELECT has_cluster_privilege(NULL, 'c', 'USAGE') ---- NULL query B SELECT has_cluster_privilege('joe', NULL, 'USAGE') ---- NULL query B SELECT has_cluster_privilege('joe', 'c', NULL) ---- NULL ### Connection query B SELECT has_connection_privilege(NULL, 'conn', 'USAGE') ---- NULL query B SELECT has_connection_privilege('joe', NULL, 'USAGE') ---- NULL query B SELECT has_connection_privilege('joe', 'conn', NULL) ---- NULL ### Database query B SELECT has_database_privilege(NULL, 'd', 'USAGE') ---- NULL query B SELECT has_database_privilege('joe', NULL, 'USAGE') ---- NULL query B SELECT has_database_privilege('joe', 'd', NULL) ---- NULL ### Schema query B SELECT has_schema_privilege(NULL, 'sch', 'USAGE') ---- NULL query B SELECT has_schema_privilege('joe', NULL, 'USAGE') ---- NULL query B SELECT has_schema_privilege('joe', 'sch', NULL) ---- NULL ### Secret query B SELECT has_secret_privilege(NULL, 'se', 'USAGE') ---- NULL query B SELECT has_secret_privilege('joe', NULL, 'USAGE') ---- NULL query B SELECT has_secret_privilege('joe', 'se', NULL) ---- NULL ### Table query B SELECT has_table_privilege(NULL, 't', 'SELECT') ---- NULL query B SELECT has_table_privilege('joe', NULL, 'SELECT') ---- NULL query B SELECT has_table_privilege('joe', 't', NULL) ---- NULL ### Type query B SELECT has_type_privilege(NULL, 'ty', 'USAGE') ---- NULL query B SELECT has_type_privilege('joe', NULL, 'USAGE') ---- NULL query B SELECT has_type_privilege('joe', 'ty', NULL) ---- NULL ## If any of the text inputs are invalid then the query should error. ### System query error role "fake_role" does not exist SELECT has_system_privilege('fake_role', 'CREATEDB') query error unrecognized privilege type: "fake privilege" SELECT has_system_privilege('joe', 'fake privilege') ### Cluster query error role "fake_role" does not exist SELECT has_cluster_privilege('fake_role', 'c', 'USAGE') query error cluster "fake_cluster" does not exist SELECT has_cluster_privilege('joe', 'fake_cluster', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT has_cluster_privilege('joe', 'c', 'fake privilege') ### Connection query error role "fake_role" does not exist SELECT has_connection_privilege('fake_role', 'conn', 'USAGE') query error db error: ERROR: connection "fake_connection" does not exist SELECT has_connection_privilege('joe', 'fake_connection', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT has_connection_privilege('joe', 'conn', 'fake privilege') ### Database query error role "fake_role" does not exist SELECT has_database_privilege('fake_role', 'd', 'USAGE') query error database "fake_database" does not exist SELECT has_database_privilege('joe', 'fake_database', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT has_database_privilege('joe', 'd', 'fake privilege') ### Schema query error role "fake_role" does not exist SELECT has_schema_privilege('fake_role', 'sch', 'USAGE') query error schema "fake_schema" does not exist SELECT has_schema_privilege('joe', 'fake_schema', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT has_schema_privilege('joe', 'sch', 'fake privilege') ### Secret query error role "fake_role" does not exist SELECT has_secret_privilege('fake_role', 'se', 'USAGE') query error db error: ERROR: secret "fake_secret" does not exist SELECT has_secret_privilege('joe', 'fake_secret', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT has_secret_privilege('joe', 'se', 'fake privilege') ### Table query error role "fake_role" does not exist SELECT has_table_privilege('fake_role', 't', 'SELECT') query error db error: ERROR: relation "fake_table" does not exist SELECT has_table_privilege('joe', 'fake_table', 'SELECT') query error unrecognized privilege type: "fake privilege" SELECT has_table_privilege('joe', 't', 'fake privilege') ### Type query error role "fake_role" does not exist SELECT has_type_privilege('fake_role', 'ty', 'USAGE') query error db error: ERROR: type "fake_type" does not exist SELECT has_type_privilege('joe', 'fake_type', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT has_type_privilege('joe', 'ty', 'fake privilege') ## If any of the oid inputs are invalid then the query should be NULL. ### System query B SELECT has_system_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'CREATECLUSTER') ---- NULL ### Cluster query B SELECT has_cluster_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'c', 'USAGE') ---- NULL ### Connection query B SELECT has_connection_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'conn', 'USAGE') ---- NULL query B SELECT has_connection_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE') ---- NULL ### Database query B SELECT has_database_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'd', 'USAGE') ---- NULL query B SELECT has_database_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE') ---- NULL ### Schema query B SELECT has_schema_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'sch', 'USAGE') ---- NULL query B SELECT has_schema_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE') ---- NULL ### Secret query B SELECT has_secret_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'se', 'USAGE') ---- NULL query B SELECT has_secret_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE') ---- NULL ### Table query B SELECT has_table_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 't', 'SELECT') ---- NULL query B SELECT has_table_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'SELECT') ---- NULL ### Type query B SELECT has_type_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'ty', 'USAGE') ---- NULL query B SELECT has_type_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE') ---- NULL ## The function should return true if ANY of the privileges exist, not all. ### System simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON SYSTEM FROM joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATECLUSTER ON SYSTEM TO joe ---- COMPLETE 0 query B SELECT has_system_privilege('joe', 'CREATEROLE, CREATECLUSTER') ---- true ### Cluster simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON CLUSTER c FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT USAGE ON CLUSTER c TO joe ---- COMPLETE 0 query B SELECT has_cluster_privilege('joe', 'c', 'USAGE, CREATE') ---- true ### Connection simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON CONNECTION conn FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT USAGE ON CONNECTION conn TO joe ---- COMPLETE 0 query B SELECT has_connection_privilege('joe', 'conn', 'USAGE, CREATE') ---- true ### Database simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON DATABASE d FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT USAGE ON DATABASE d TO joe ---- COMPLETE 0 query B SELECT has_database_privilege('joe', 'd', 'USAGE, CREATE') ---- true ### Schema simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON SCHEMA sch FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT USAGE ON SCHEMA sch TO joe ---- COMPLETE 0 query B SELECT has_schema_privilege('joe', 'sch', 'USAGE, CREATE') ---- true ### Secret simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON SECRET se FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT USAGE ON SECRET se TO joe ---- COMPLETE 0 query B SELECT has_secret_privilege('joe', 'se', 'USAGE, CREATE') ---- true ### Table simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON t FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT SELECT ON t TO joe ---- COMPLETE 0 query B SELECT has_table_privilege('joe', 't', 'SELECT, INSERT') ---- true ### Type simple conn=mz_system,user=mz_system REVOKE ALL PRIVILEGES ON TYPE ty FROM joe, materialize; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT USAGE ON TYPE ty TO joe ---- COMPLETE 0 query B SELECT has_type_privilege('joe', 'ty', 'USAGE, CREATE') ---- true ## Test two/one input variants. ### System simple conn=mz_system,user=mz_system REVOKE CREATEDB ON SYSTEM FROM materialize ---- COMPLETE 0 query B SELECT has_system_privilege('materialize', 'CREATEDB') ---- false query B SELECT has_system_privilege('CREATEDB') ---- false simple conn=mz_system,user=mz_system GRANT CREATEDB ON SYSTEM TO materialize ---- COMPLETE 0 query B SELECT has_system_privilege('materialize', 'CREATEDB') ---- true query B SELECT has_system_privilege('CREATEDB') ---- true ### Cluster query B SELECT has_cluster_privilege('materialize', 'c', 'USAGE') ---- false query B SELECT has_cluster_privilege('c', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON CLUSTER c TO materialize ---- COMPLETE 0 query B SELECT has_cluster_privilege('materialize', 'c', 'USAGE') ---- true query B SELECT has_cluster_privilege('c', 'USAGE') ---- true ### Connection query B SELECT has_connection_privilege('materialize', 'conn', 'USAGE') ---- false query B SELECT has_connection_privilege('conn', 'USAGE') ---- false query B SELECT has_connection_privilege('materialize', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- false query B SELECT has_connection_privilege((SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON CONNECTION conn TO materialize ---- COMPLETE 0 query B SELECT has_connection_privilege('materialize', 'conn', 'USAGE') ---- true query B SELECT has_connection_privilege('conn', 'USAGE') ---- true query B SELECT has_connection_privilege('materialize', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- true query B SELECT has_connection_privilege((SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE') ---- true ### Database query B SELECT has_database_privilege('materialize', 'd', 'USAGE') ---- false query B SELECT has_database_privilege('d', 'USAGE') ---- false query B SELECT has_database_privilege('materialize', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- false query B SELECT has_database_privilege((SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON DATABASE d TO materialize ---- COMPLETE 0 query B SELECT has_database_privilege('materialize', 'd', 'USAGE') ---- true query B SELECT has_database_privilege('d', 'USAGE') ---- true query B SELECT has_database_privilege('materialize', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- true query B SELECT has_database_privilege((SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE') ---- true ### Schema query B SELECT has_schema_privilege('materialize', 'sch', 'USAGE') ---- false query B SELECT has_schema_privilege('sch', 'USAGE') ---- false query B SELECT has_schema_privilege('materialize', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- false query B SELECT has_schema_privilege((SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON SCHEMA sch TO materialize ---- COMPLETE 0 query B SELECT has_schema_privilege('materialize', 'sch', 'USAGE') ---- true query B SELECT has_schema_privilege('sch', 'USAGE') ---- true query B SELECT has_schema_privilege('materialize', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- true query B SELECT has_schema_privilege((SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE') ---- true ### Secret query B SELECT has_secret_privilege('materialize', 'se', 'USAGE') ---- false query B SELECT has_secret_privilege('se', 'USAGE') ---- false query B SELECT has_secret_privilege('materialize', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- false query B SELECT has_secret_privilege((SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON SECRET se TO materialize ---- COMPLETE 0 query B SELECT has_secret_privilege('materialize', 'se', 'USAGE') ---- true query B SELECT has_secret_privilege('se', 'USAGE') ---- true query B SELECT has_secret_privilege('materialize', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- true query B SELECT has_secret_privilege((SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE') ---- true ### Table query B SELECT has_table_privilege('materialize', 't', 'SELECT') ---- false query B SELECT has_table_privilege('t', 'SELECT') ---- false query B SELECT has_table_privilege('materialize', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- false query B SELECT has_table_privilege((SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- false simple conn=mz_system,user=mz_system GRANT SELECT ON t TO materialize ---- COMPLETE 0 query B SELECT has_table_privilege('materialize', 't', 'SELECT') ---- true query B SELECT has_table_privilege('t', 'SELECT') ---- true query B SELECT has_table_privilege('materialize', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- true query B SELECT has_table_privilege((SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT') ---- true ### Type simple conn=mz_system,user=mz_system REVOKE USAGE ON TYPE ty FROM PUBLIC ---- COMPLETE 0 query B SELECT has_type_privilege('materialize', 'ty', 'USAGE') ---- false query B SELECT has_type_privilege('ty', 'USAGE') ---- false query B SELECT has_type_privilege('materialize', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- false query B SELECT has_type_privilege((SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON TYPE ty TO materialize ---- COMPLETE 0 query B SELECT has_type_privilege('materialize', 'ty', 'USAGE') ---- true query B SELECT has_type_privilege('ty', 'USAGE') ---- true query B SELECT has_type_privilege('materialize', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- true query B SELECT has_type_privilege((SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE') ---- true simple conn=mz_system,user=mz_system GRANT USAGE ON TYPE ty TO PUBLIC ---- COMPLETE 0 # Test multi-object GRANT/REVOKE simple conn=mz_system,user=mz_system DROP TABLE t1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TABLE t1 (); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TABLE t2 (); ---- COMPLETE 0 query B SELECT has_table_privilege('t1', 'SELECT') ---- false query B SELECT has_table_privilege('t2', 'SELECT') ---- false simple conn=mz_system,user=mz_system GRANT SELECT ON TABLE t1, t2 TO materialize ---- COMPLETE 0 query B SELECT has_table_privilege('t1', 'SELECT') ---- true query B SELECT has_table_privilege('t2', 'SELECT') ---- true simple conn=mz_system,user=mz_system REVOKE SELECT ON TABLE t1, t2 FROM materialize ---- COMPLETE 0 query B SELECT has_table_privilege('t1', 'SELECT') ---- false query B SELECT has_table_privilege('t2', 'SELECT') ---- false # Test ALL SCHEMA object GRANT/REVOKE simple conn=mz_system,user=mz_system CREATE DATABASE d1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE SCHEMA d1.s1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE DATABASE d2; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE SCHEMA d2.s2; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TABLE d1.s1.t3 (); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TABLE d1.s1.t4 (); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TABLE d2.s2.t5 (); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TABLE d2.s2.t6 (); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TYPE d1.s1.ty1 AS LIST (ELEMENT TYPE=bool); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TYPE d1.s1.ty2 AS LIST (ELEMENT TYPE=bool); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TYPE d2.s2.ty3 AS LIST (ELEMENT TYPE=bool); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE TYPE d2.s2.ty4 AS LIST (ELEMENT TYPE=bool); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE SECRET d1.s1.se1 AS decode('c2VjcmV0Cg==', 'base64'); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE SECRET d1.s1.se2 AS decode('c2VjcmV0Cg==', 'base64'); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE SECRET d2.s2.se3 AS decode('c2VjcmV0Cg==', 'base64'); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE SECRET d2.s2.se4 AS decode('c2VjcmV0Cg==', 'base64'); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE CONNECTION d1.s1.conn1 TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE CONNECTION d1.s1.conn2 TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE CONNECTION d2.s2.conn3 TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE CONNECTION d2.s2.conn4 TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false); ---- COMPLETE 0 ## Tables query error db error: ERROR: relation "t3" does not exist SELECT has_table_privilege('t3', 'SELECT') query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- false query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- false simple conn=mz_system,user=mz_system GRANT SELECT ON ALL TABLES IN SCHEMA d1.s1, d2.s2 TO materialize; ---- COMPLETE 0 query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- true query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- true query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- true query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- true simple conn=mz_system,user=mz_system REVOKE SELECT ON ALL TABLES IN SCHEMA d1.s1, d2.s2 FROM materialize; ---- COMPLETE 0 query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- false query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- false ## Types simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL TYPES FROM PUBLIC ---- COMPLETE 0 query error db error: ERROR: type "ty1" does not exist SELECT has_type_privilege('ty1', 'USAGE') query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- false query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL TYPES IN SCHEMA d1.s1, d2.s2 TO materialize; ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- true query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- true query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- true query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL TYPES IN SCHEMA d1.s1, d2.s2 FROM materialize; ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- false query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL TYPES TO PUBLIC ---- COMPLETE 0 ## Secrets query B SELECT has_secret_privilege('se1', 'USAGE') ---- false query B SELECT has_secret_privilege('se2', 'USAGE') ---- false query B SELECT has_secret_privilege('se3', 'USAGE') ---- false query B SELECT has_secret_privilege('se4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL SECRETS IN SCHEMA d1.s1, d2.s2 TO materialize; ---- COMPLETE 0 query B SELECT has_secret_privilege('se1', 'USAGE') ---- true query B SELECT has_secret_privilege('se2', 'USAGE') ---- true query B SELECT has_secret_privilege('se3', 'USAGE') ---- true query B SELECT has_secret_privilege('se4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL SECRETS IN SCHEMA d1.s1, d2.s2 FROM materialize; ---- COMPLETE 0 query B SELECT has_secret_privilege('se1', 'USAGE') ---- false query B SELECT has_secret_privilege('se2', 'USAGE') ---- false query B SELECT has_secret_privilege('se3', 'USAGE') ---- false query B SELECT has_secret_privilege('se4', 'USAGE') ---- false ## Connections query B SELECT has_connection_privilege('conn1', 'USAGE') ---- false query B SELECT has_connection_privilege('conn2', 'USAGE') ---- false query B SELECT has_connection_privilege('conn3', 'USAGE') ---- false query B SELECT has_connection_privilege('conn4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL CONNECTIONS IN SCHEMA d1.s1, d2.s2 TO materialize; ---- COMPLETE 0 query B SELECT has_connection_privilege('conn1', 'USAGE') ---- true query B SELECT has_connection_privilege('conn2', 'USAGE') ---- true query B SELECT has_connection_privilege('conn3', 'USAGE') ---- true query B SELECT has_connection_privilege('conn4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL CONNECTIONS IN SCHEMA d1.s1, d2.s2 FROM materialize; ---- COMPLETE 0 query B SELECT has_connection_privilege('conn1', 'USAGE') ---- false query B SELECT has_connection_privilege('conn2', 'USAGE') ---- false query B SELECT has_connection_privilege('conn3', 'USAGE') ---- false query B SELECT has_connection_privilege('conn4', 'USAGE') ---- false # Test ALL DATABASE object GRANT/REVOKE ## Tables query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- false query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- false simple conn=mz_system,user=mz_system GRANT SELECT ON ALL TABLES IN DATABASE d1, d2 TO materialize; ---- COMPLETE 0 query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- true query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- true query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- true query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- true simple conn=mz_system,user=mz_system REVOKE SELECT ON ALL TABLES IN DATABASE d1, d2 FROM materialize; ---- COMPLETE 0 query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- false query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- false ## Types simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL TYPES FROM PUBLIC ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- false query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL TYPES IN DATABASE d1, d2 TO materialize; ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- true query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- true query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- true query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL TYPES IN DATABASE d1, d2 FROM materialize; ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- false query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL TYPES TO PUBLIC ---- COMPLETE 0 ## Secrets query B SELECT has_secret_privilege('se1', 'USAGE') ---- false query B SELECT has_secret_privilege('se2', 'USAGE') ---- false query B SELECT has_secret_privilege('se3', 'USAGE') ---- false query B SELECT has_secret_privilege('se4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL SECRETS IN DATABASE d1, d2 TO materialize; ---- COMPLETE 0 query B SELECT has_secret_privilege('se1', 'USAGE') ---- true query B SELECT has_secret_privilege('se2', 'USAGE') ---- true query B SELECT has_secret_privilege('se3', 'USAGE') ---- true query B SELECT has_secret_privilege('se4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL SECRETS IN DATABASE d1, d2 FROM materialize; ---- COMPLETE 0 query B SELECT has_secret_privilege('se1', 'USAGE') ---- false query B SELECT has_secret_privilege('se2', 'USAGE') ---- false query B SELECT has_secret_privilege('se3', 'USAGE') ---- false query B SELECT has_secret_privilege('se4', 'USAGE') ---- false ## Connections query B SELECT has_connection_privilege('conn1', 'USAGE') ---- false query B SELECT has_connection_privilege('conn2', 'USAGE') ---- false query B SELECT has_connection_privilege('conn3', 'USAGE') ---- false query B SELECT has_connection_privilege('conn4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL CONNECTIONS IN DATABASE d1, d2 TO materialize; ---- COMPLETE 0 query B SELECT has_connection_privilege('conn1', 'USAGE') ---- true query B SELECT has_connection_privilege('conn2', 'USAGE') ---- true query B SELECT has_connection_privilege('conn3', 'USAGE') ---- true query B SELECT has_connection_privilege('conn4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL CONNECTIONS IN DATABASE d1, d2 FROM materialize; ---- COMPLETE 0 query B SELECT has_connection_privilege('conn1', 'USAGE') ---- false query B SELECT has_connection_privilege('conn2', 'USAGE') ---- false query B SELECT has_connection_privilege('conn3', 'USAGE') ---- false query B SELECT has_connection_privilege('conn4', 'USAGE') ---- false ## Schemas query B SELECT has_schema_privilege('d1.s1', 'CREATE') ---- false query B SELECT has_schema_privilege('d2.s2', 'CREATE') ---- false simple conn=mz_system,user=mz_system GRANT CREATE ON ALL SCHEMAS IN DATABASE d1, d2 TO materialize; ---- COMPLETE 0 query B SELECT has_schema_privilege('d1.s1', 'CREATE') ---- true query B SELECT has_schema_privilege('d2.s2', 'CREATE') ---- true simple conn=mz_system,user=mz_system REVOKE CREATE ON ALL SCHEMAS IN DATABASE d1, d2 FROM materialize; ---- COMPLETE 0 query B SELECT has_schema_privilege('d1.s1', 'CREATE') ---- false query B SELECT has_schema_privilege('d2.s2', 'CREATE') ---- false # Test ALL object GRANT/REVOKE ## Tables query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- false query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- false simple conn=mz_system,user=mz_system GRANT SELECT ON ALL TABLES TO materialize; ---- COMPLETE 0 query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- true query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- true query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- true query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- true simple conn=mz_system,user=mz_system REVOKE SELECT ON ALL TABLES FROM materialize; ---- COMPLETE 0 query B SELECT has_table_privilege('d1.s1.t3', 'SELECT') ---- false query B SELECT has_table_privilege('d1.s1.t4', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t5', 'SELECT') ---- false query B SELECT has_table_privilege('d2.s2.t6', 'SELECT') ---- false ## Types simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL TYPES FROM PUBLIC ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- false query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL TYPES TO materialize; ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- true query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- true query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- true query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL TYPES FROM materialize; ---- COMPLETE 0 query B SELECT has_type_privilege('d1.s1.ty1', 'USAGE') ---- false query B SELECT has_type_privilege('d1.s1.ty2', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty3', 'USAGE') ---- false query B SELECT has_type_privilege('d2.s2.ty4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL TYPES TO PUBLIC ---- COMPLETE 0 ## Secrets query B SELECT has_secret_privilege('se1', 'USAGE') ---- false query B SELECT has_secret_privilege('se2', 'USAGE') ---- false query B SELECT has_secret_privilege('se3', 'USAGE') ---- false query B SELECT has_secret_privilege('se4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL SECRETS TO materialize; ---- COMPLETE 0 query B SELECT has_secret_privilege('se1', 'USAGE') ---- true query B SELECT has_secret_privilege('se2', 'USAGE') ---- true query B SELECT has_secret_privilege('se3', 'USAGE') ---- true query B SELECT has_secret_privilege('se4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL SECRETS FROM materialize; ---- COMPLETE 0 query B SELECT has_secret_privilege('se1', 'USAGE') ---- false query B SELECT has_secret_privilege('se2', 'USAGE') ---- false query B SELECT has_secret_privilege('se3', 'USAGE') ---- false query B SELECT has_secret_privilege('se4', 'USAGE') ---- false ## Connections query B SELECT has_connection_privilege('conn1', 'USAGE') ---- false query B SELECT has_connection_privilege('conn2', 'USAGE') ---- false query B SELECT has_connection_privilege('conn3', 'USAGE') ---- false query B SELECT has_connection_privilege('conn4', 'USAGE') ---- false simple conn=mz_system,user=mz_system GRANT USAGE ON ALL CONNECTIONS TO materialize; ---- COMPLETE 0 query B SELECT has_connection_privilege('conn1', 'USAGE') ---- true query B SELECT has_connection_privilege('conn2', 'USAGE') ---- true query B SELECT has_connection_privilege('conn3', 'USAGE') ---- true query B SELECT has_connection_privilege('conn4', 'USAGE') ---- true simple conn=mz_system,user=mz_system REVOKE USAGE ON ALL CONNECTIONS FROM materialize; ---- COMPLETE 0 query B SELECT has_connection_privilege('conn1', 'USAGE') ---- false query B SELECT has_connection_privilege('conn2', 'USAGE') ---- false query B SELECT has_connection_privilege('conn3', 'USAGE') ---- false query B SELECT has_connection_privilege('conn4', 'USAGE') ---- false ## Schemas query B SELECT has_schema_privilege('d1.s1', 'CREATE') ---- false query B SELECT has_schema_privilege('d2.s2', 'CREATE') ---- false simple conn=mz_system,user=mz_system GRANT CREATE ON ALL SCHEMAS TO materialize; ---- COMPLETE 0 query B SELECT has_schema_privilege('d1.s1', 'CREATE') ---- true query B SELECT has_schema_privilege('d2.s2', 'CREATE') ---- true simple conn=mz_system,user=mz_system REVOKE CREATE ON ALL SCHEMAS FROM materialize; ---- COMPLETE 0 query B SELECT has_schema_privilege('d1.s1', 'CREATE') ---- false query B SELECT has_schema_privilege('d2.s2', 'CREATE') ---- false ## Databases query B SELECT has_database_privilege('d1', 'CREATE') ---- false query B SELECT has_database_privilege('d2', 'CREATE') ---- false simple conn=mz_system,user=mz_system GRANT CREATE ON ALL DATABASES TO materialize; ---- COMPLETE 0 query B SELECT has_database_privilege('d1', 'CREATE') ---- true query B SELECT has_database_privilege('d2', 'CREATE') ---- true simple conn=mz_system,user=mz_system REVOKE CREATE ON ALL DATABASES FROM materialize; ---- COMPLETE 0 query B SELECT has_database_privilege('d1', 'CREATE') ---- false query B SELECT has_database_privilege('d2', 'CREATE') ---- false ## Clusters simple conn=mz_system,user=mz_system CREATE CLUSTER c1 REPLICAS (r1 (SIZE '1')); ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE CLUSTER c2 REPLICAS (r1 (SIZE '1')); ---- COMPLETE 0 query B SELECT has_cluster_privilege('c1', 'CREATE') ---- false query B SELECT has_cluster_privilege('c2', 'CREATE') ---- false simple conn=mz_system,user=mz_system GRANT CREATE ON ALL CLUSTERS TO materialize; ---- COMPLETE 0 query B SELECT has_cluster_privilege('c1', 'CREATE') ---- true query B SELECT has_cluster_privilege('c2', 'CREATE') ---- true simple conn=mz_system,user=mz_system REVOKE CREATE ON ALL CLUSTERS FROM materialize; ---- COMPLETE 0 query B SELECT has_cluster_privilege('c1', 'CREATE') ---- false query B SELECT has_cluster_privilege('c2', 'CREATE') ---- false # Reset server so we can get back to a clean state. reset-server # Test table_privileges and role_table_grants statement ok CREATE ROLE r1 simple conn=mz_system,user=mz_system GRANT CREATE ON CLUSTER quickstart TO r1 ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT CREATE ON SCHEMA public TO r1 ---- COMPLETE 0 statement ok CREATE ROLE r2 statement ok CREATE ROLE r3 statement ok GRANT r2 TO r1 statement ok CREATE SOURCE s FROM LOAD GENERATOR COUNTER; statement ok GRANT SELECT ON s TO r2 statement ok CREATE TABLE t () statement ok GRANT INSERT ON t TO r1 statement ok CREATE VIEW v AS SELECT 1 statement ok GRANT SELECT ON v TO r2, r3 simple conn=r1,user=r1 CREATE MATERIALIZED VIEW mv AS SELECT 1 ---- COMPLETE 0 simple conn=r1,user=r1 GRANT SELECT ON mv TO r2 ---- COMPLETE 0 # For privileges granted to PUBLIC and mz_system, we don't want to enumerate every single row # because there is a lot. Instead we just assert that there's a bunch of rows emitted. The number # of rows was chosen because that was the value at the time the test was written. ## table_privileges simple conn=r1,user=r1,rowsort SELECT * FROM information_schema.table_privileges WHERE grantee != 'PUBLIC' ---- r1,r1,materialize,public,mv,SELECT,NO,YES r1,r2,materialize,public,mv,SELECT,NO,YES materialize,r1,materialize,public,t,INSERT,NO,NO materialize,r2,materialize,public,s,SELECT,NO,YES materialize,r2,materialize,public,v,SELECT,NO,YES COMPLETE 5 simple conn=r1,user=r1 SELECT COUNT(*) >= 166 FROM information_schema.table_privileges WHERE grantee = 'PUBLIC' ---- t COMPLETE 1 simple conn=mz_system,user=mz_system,rowsort SELECT * FROM information_schema.table_privileges WHERE grantee != 'PUBLIC' AND grantee != 'mz_system' ---- r1,r1,materialize,public,mv,SELECT,NO,YES r1,r2,materialize,public,mv,SELECT,NO,YES materialize,r1,materialize,public,t,INSERT,NO,NO materialize,r2,materialize,public,s,SELECT,NO,YES materialize,r2,materialize,public,v,SELECT,NO,YES materialize,r3,materialize,public,v,SELECT,NO,YES materialize,materialize,materialize,public,t,DELETE,NO,NO materialize,materialize,materialize,public,t,INSERT,NO,NO materialize,materialize,materialize,public,t,UPDATE,NO,NO materialize,materialize,materialize,public,s,SELECT,NO,YES materialize,materialize,materialize,public,t,SELECT,NO,YES materialize,materialize,materialize,public,v,SELECT,NO,YES materialize,mz_support,materialize,public,s_progress,SELECT,NO,YES materialize,materialize,materialize,public,s_progress,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_notices,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_sql_text,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_optimizer_notices,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_activity_log_thinned,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_thinned,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES COMPLETE 49 simple conn=mz_system,user=mz_system SELECT COUNT(*) >= 166 FROM information_schema.table_privileges WHERE grantee = 'PUBLIC' ---- t COMPLETE 1 simple conn=mz_system,user=mz_system SELECT COUNT(*) >= 304 FROM information_schema.table_privileges WHERE grantee = 'mz_system' ---- t COMPLETE 1 ## role_table_grants simple conn=r1,user=r1,rowsort SELECT * FROM information_schema.role_table_grants ---- r1,r1,materialize,public,mv,SELECT,NO,YES r1,r2,materialize,public,mv,SELECT,NO,YES materialize,r1,materialize,public,t,INSERT,NO,NO materialize,r2,materialize,public,s,SELECT,NO,YES materialize,r2,materialize,public,v,SELECT,NO,YES COMPLETE 5 simple conn=mz_system,user=mz_system,rowsort SELECT * FROM information_schema.role_table_grants WHERE grantee != 'PUBLIC' AND grantee != 'mz_system' ---- r1,r1,materialize,public,mv,SELECT,NO,YES r1,r2,materialize,public,mv,SELECT,NO,YES materialize,r1,materialize,public,t,INSERT,NO,NO materialize,r2,materialize,public,s,SELECT,NO,YES materialize,r2,materialize,public,v,SELECT,NO,YES materialize,r3,materialize,public,v,SELECT,NO,YES materialize,materialize,materialize,public,t,DELETE,NO,NO materialize,materialize,materialize,public,t,INSERT,NO,NO materialize,materialize,materialize,public,t,UPDATE,NO,NO materialize,materialize,materialize,public,s,SELECT,NO,YES materialize,materialize,materialize,public,t,SELECT,NO,YES materialize,materialize,materialize,public,v,SELECT,NO,YES materialize,mz_support,materialize,public,s_progress,SELECT,NO,YES materialize,materialize,materialize,public,s_progress,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_notices,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_sql_text,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_optimizer_notices,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_activity_log_thinned,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_thinned,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES mz_system,mz_support,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES mz_system,mz_analytics,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES COMPLETE 49 simple conn=mz_system,user=mz_system SELECT COUNT(*) >= 166 FROM information_schema.role_table_grants WHERE grantee = 'PUBLIC' ---- t COMPLETE 1 simple conn=mz_system,user=mz_system SELECT COUNT(*) >= 304 FROM information_schema.role_table_grants WHERE grantee = 'mz_system' ---- t COMPLETE 1 # Check that predefined roles can't be altered. simple conn=mz_system,user=mz_system GRANT SELECT ON t TO mz_monitor ---- db error: ERROR: role name "mz_monitor" is reserved DETAIL: The role prefixes "mz_" and "pg_" are reserved for system roles.