Materialize/test/sqllogictest/builtin_roles.slt

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

mode cockroach

reset-server

# Test that by default, nobody is allowed to access statement log
# objects

statement error permission denied for SOURCE
SELECT 1 FROM mz_internal.mz_prepared_statement_history WHERE 1 = 0

statement error permission denied for SOURCE
SELECT 1 FROM mz_internal.mz_statement_execution_history WHERE 1 = 0

statement error db error: ERROR: unknown catalog item 'mz_internal\.mz_activity_log'
SELECT 1 FROM mz_internal.mz_activity_log WHERE 1 = 0

statement error permission denied for VIEW
SELECT 1 FROM mz_internal.mz_sql_text_redacted WHERE 1 = 0

statement error permission denied for VIEW
SELECT 1 FROM mz_internal.mz_statement_execution_history_redacted WHERE 1 = 0

statement error db error: ERROR: unknown catalog item 'mz_internal\.mz_activity_log_redacted'
SELECT 1 FROM mz_internal.mz_activity_log_redacted WHERE 1 = 0

# Test that after granting the less-privileged
# `mz_monitor_redacted` role, we can
# query the redacted objects, but not the unredacted ones.

simple conn=mz_system,user=mz_system
GRANT mz_monitor_redacted TO materialize
----
COMPLETE 0

statement error permission denied for SOURCE
SELECT 1 FROM mz_internal.mz_sql_text WHERE 1 = 0

statement error permission denied for SOURCE
SELECT 1 FROM mz_internal.mz_statement_execution_history WHERE 1 = 0

statement error db error: ERROR: unknown catalog item 'mz_internal\.mz_activity_log'
SELECT 1 FROM mz_internal.mz_activity_log WHERE 1 = 0

query I
SELECT 1 FROM mz_internal.mz_sql_text_redacted WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_sql_text_redacted WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_recent_activity_log_redacted WHERE 1 = 0
----

# Sanity check that none of the `redacted` objects expose sql.

statement error db error: ERROR: column "sql" does not exist
SELECT sql FROM mz_internal.mz_recent_activity_log_redacted

statement error db error: ERROR: column "sql" does not exist
SELECT sql FROM mz_internal.mz_recent_sql_text_redacted

statement error db error: ERROR: column "sql" does not exist
SELECT sql FROM mz_internal.mz_recent_sql_text_redacted

statement error db error: ERROR: column "sql" does not exist
SELECT sql FROM mz_internal.mz_statement_execution_history_redacted

# TEST that revocation does something
simple conn=mz_system,user=mz_system
REVOKE mz_monitor_redacted FROM materialize
----
COMPLETE 0

statement error permission denied for SOURCE
SELECT 1 FROM mz_internal.mz_sql_text WHERE 1 = 0

statement error permission denied for SOURCE
SELECT 1 FROM mz_internal.mz_statement_execution_history WHERE 1 = 0

statement error db error: ERROR: unknown catalog item 'mz_internal\.mz_activity_log'
SELECT 1 FROM mz_internal.mz_activity_log WHERE 1 = 0

statement error permission denied for VIEW
SELECT 1 FROM mz_internal.mz_sql_text_redacted WHERE 1 = 0

statement error permission denied for VIEW
SELECT 1 FROM mz_internal.mz_sql_text_redacted WHERE 1 = 0

statement error db error: ERROR: unknown catalog item 'mz_internal\.mz_activity_log_redacted'
SELECT 1 FROM mz_internal.mz_activity_log_redacted WHERE 1 = 0

# Test that we can read all tables with the more powerful permission
# (`mz_monitor`)

simple conn=mz_system,user=mz_system
GRANT mz_monitor TO materialize
----
COMPLETE 0

query I
SELECT 1 FROM mz_internal.mz_sql_text WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_statement_execution_history WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_recent_activity_log WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_sql_text_redacted WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_statement_execution_history_redacted WHERE 1 = 0
----

query I
SELECT 1 FROM mz_internal.mz_recent_activity_log_redacted WHERE 1 = 0
----