Materialize/test/sqllogictest/mz-support-privileges.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.

# Tests that assert the privileges that are assumed to be always granted to
# the mz_support user.

statement ok
CREATE TABLE t (a INT)

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

# The mz_support user cannot execute `SELECT ...` commands.
simple conn=mz_support,user=mz_support
SELECT * FROM t
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'mz_support' role needs SELECT privileges on TABLE "materialize.public.t"

# The mz_support user cannot execute `INSERT ...` commands.
simple conn=mz_support,user=mz_support
INSERT INTO t VALUES (42)
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'mz_support' role needs INSERT privileges on TABLE "materialize.public.t"

# The mz_support user cannot execute `UPDATE ...` commands.
simple conn=mz_support,user=mz_support
UPDATE t SET a = 5
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'mz_support' role needs UPDATE privileges on TABLE "materialize.public.t"

# The mz_support user cannot execute `DELETE ...` commands.
simple conn=mz_support,user=mz_support
DELETE FROM t WHERE a IS NOT NULL
----
db error: ERROR: permission denied for TABLE "materialize.public.t"
DETAIL: The 'mz_support' role needs DELETE privileges on TABLE "materialize.public.t"

# The mz_support user cannot execute create objects.
simple conn=mz_support,user=mz_support
CREATE VIEW vv AS SELECT 66
----
db error: ERROR: permission denied for SCHEMA "materialize.public"
DETAIL: The 'mz_support' role needs CREATE privileges on SCHEMA "materialize.public"

# The mz_support user can SHOW public system vars.
simple conn=mz_support,user=mz_support
SHOW max_tables;
----
100
COMPLETE 1

# The mz_support user can SHOW internal system vars.
simple conn=mz_support,user=mz_support
SHOW log_filter;
----
warn
COMPLETE 1

# The mz_support user cannot ALTER SYSTEM SET public system vars.
simple conn=mz_support,user=mz_support
ALTER SYSTEM SET max_tables = 1234;
----
db error: ERROR: permission denied to alter system
DETAIL: You must be the 'mz_system' role

# The mz_support user cannot ALTER SYSTEM SET internal system vars.
simple conn=mz_support,user=mz_support
ALTER SYSTEM SET log_filter = 'error';
----
db error: ERROR: permission denied to alter system
DETAIL: You must be the 'mz_system' role

# The mz_support user cannot ALTER SYSTEM RESET public system vars.
simple conn=mz_support,user=mz_support
ALTER SYSTEM RESET max_tables;
----
db error: ERROR: permission denied to alter system
DETAIL: You must be the 'mz_system' role

# The mz_support user cannot ALTER SYSTEM RESET internal system vars.
simple conn=mz_support,user=mz_support
ALTER SYSTEM RESET log_filter;
----
db error: ERROR: permission denied to alter system
DETAIL: You must be the 'mz_system' role

# The mz_support user cannot query the un-redacted statement log tables
simple conn=mz_support,user=mz_support
SELECT count(*) >= 0 FROM mz_internal.mz_statement_execution_history
----
db error: ERROR: permission denied for SOURCE "mz_internal.mz_statement_execution_history"
DETAIL: The 'mz_support' role needs SELECT privileges on SOURCE "mz_internal.mz_statement_execution_history"

simple conn=mz_support,user=mz_support
SELECT count(*) >= 0 FROM mz_internal.mz_sql_text
----
db error: ERROR: permission denied for SOURCE "mz_internal.mz_sql_text"
DETAIL: The 'mz_support' role needs SELECT privileges on SOURCE "mz_internal.mz_sql_text"

# It _can_ query the bowdlerized tables
simple conn=mz_support,user=mz_support
SELECT count(*) >= 0 FROM mz_internal.mz_sql_text_redacted
----
t
COMPLETE 1

simple conn=mz_support,user=mz_support
SELECT count(*) >= 0 FROM mz_internal.mz_statement_execution_history_redacted
----
t
COMPLETE 1

# Can use explain schema
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_connection_validation_syntax TO true;
----
COMPLETE 0

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

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

simple multiline,conn=mz_support,user=mz_support
EXPLAIN VALUE SCHEMA FOR CREATE SINK sink FROM t INTO KAFKA CONNECTION kafka_conn (TOPIC 'topic') KEY (a) NOT ENFORCED FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_conn ENVELOPE UPSERT;
----
{
  "type": "record",
  "name": "envelope",
  "fields": [
    {
      "name": "a",
      "type": [
        "null",
        "int"
      ]
    }
  ]
}
EOF
COMPLETE 1

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

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