Materialize/test/sqllogictest/rbac_views.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

# Enable rbac checks.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO true;
----
COMPLETE 0

# SHOW ROLE MEMBERS

statement ok
CREATE ROLE r1

statement ok
CREATE ROLE r2

statement ok
CREATE ROLE r3

statement ok
CREATE ROLE r4

statement ok
CREATE ROLE r5

statement ok
GRANT r2 TO r1

statement ok
GRANT r3 TO r2

statement ok
GRANT r5 TO r4

query TTT
SELECT * FROM mz_internal.mz_show_role_members ORDER BY role, member
----
r2  r1  mz_system
r3  r2  mz_system
r5  r4  mz_system

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_role_members ORDER BY role, member
----
r2,r1,mz_system
r3,r2,mz_system
COMPLETE 2

query TTT
SELECT * FROM (SHOW ROLE MEMBERSHIP) ORDER BY role, member
----
r2  r1  mz_system
r3  r2  mz_system
r5  r4  mz_system

query TTT
SELECT * FROM (SHOW ROLE MEMBERSHIP FOR r2) ORDER BY role, member
----
r3  r2  mz_system

# SHOW SYSTEM PRIVILEGES

simple conn=mz_system,user=mz_system
GRANT CREATEDB, CREATECLUSTER ON SYSTEM TO r1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO r2;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATECLUSTER ON SYSTEM TO r4;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATECLUSTER ON SYSTEM TO PUBLIC;
----
COMPLETE 0

query TTT
SELECT * FROM mz_internal.mz_show_system_privileges ORDER BY grantee
----
mz_system  PUBLIC       CREATECLUSTER
mz_system  materialize  CREATEDB
mz_system  materialize  CREATEROLE
mz_system  materialize  CREATECLUSTER
mz_system  materialize  CREATENETWORKPOLICY
mz_system  r1           CREATEDB
mz_system  r1           CREATECLUSTER
mz_system  r2           CREATEROLE
mz_system  r4           CREATECLUSTER

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_system_privileges ORDER BY grantee
----
mz_system,PUBLIC,CREATECLUSTER
mz_system,r1,CREATEDB
mz_system,r1,CREATECLUSTER
mz_system,r2,CREATEROLE
COMPLETE 4

# SHOW CLUSTER PRIVILEGES

statement ok
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))

statement ok
GRANT USAGE ON CLUSTER c TO r1

statement ok
GRANT CREATE, USAGE ON CLUSTER c TO r3

statement ok
GRANT CREATE ON CLUSTER c TO r4

statement ok
GRANT USAGE ON CLUSTER c TO PUBLIC

query TTTT
SELECT * FROM mz_internal.mz_show_cluster_privileges ORDER BY name, grantee
----
materialize  PUBLIC       c        USAGE
materialize  materialize  c        USAGE
materialize  materialize  c        CREATE
materialize  r1           c        USAGE
materialize  r3           c        USAGE
materialize  r3           c        CREATE
materialize  r4           c        CREATE
mz_system    PUBLIC       quickstart  USAGE
mz_system    materialize  quickstart  USAGE
mz_system    materialize  quickstart  CREATE

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_cluster_privileges ORDER BY name, grantee
----
materialize,PUBLIC,c,USAGE
materialize,r1,c,USAGE
materialize,r3,c,USAGE
materialize,r3,c,CREATE
mz_system,PUBLIC,quickstart,USAGE
COMPLETE 5

# SHOW DATABASE PRIVILEGES

statement ok
CREATE DATABASE d

statement ok
GRANT CREATE, USAGE ON DATABASE d TO r1

statement ok
GRANT USAGE ON DATABASE d TO r2

statement ok
GRANT CREATE ON DATABASE d TO r4

statement ok
GRANT CREATE ON DATABASE d TO PUBLIC

query TTTT
SELECT * FROM mz_internal.mz_show_database_privileges ORDER BY name, grantee
----
materialize  PUBLIC       d            CREATE
materialize  materialize  d            USAGE
materialize  materialize  d            CREATE
materialize  r1           d            USAGE
materialize  r1           d            CREATE
materialize  r2           d            USAGE
materialize  r4           d            CREATE
mz_system    PUBLIC       materialize  USAGE
mz_system    materialize  materialize  USAGE
mz_system    materialize  materialize  CREATE

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_database_privileges ORDER BY name, grantee
----
materialize,PUBLIC,d,CREATE
materialize,r1,d,USAGE
materialize,r1,d,CREATE
materialize,r2,d,USAGE
mz_system,PUBLIC,materialize,USAGE
COMPLETE 5

# SHOW SCHEMA PRIVILEGES

statement ok
CREATE SCHEMA s

statement ok
GRANT USAGE ON SCHEMA s TO r1

statement ok
GRANT USAGE ON SCHEMA s TO r3

statement ok
GRANT USAGE, CREATE ON SCHEMA s TO r5

statement ok
GRANT USAGE ON SCHEMA s TO PUBLIC

query TTTTT
SELECT * FROM mz_internal.mz_show_schema_privileges ORDER BY database, name, grantee
----
materialize  PUBLIC       d            public  USAGE
materialize  materialize  d            public  USAGE
materialize  materialize  d            public  CREATE
mz_system    PUBLIC       materialize  public  USAGE
mz_system    materialize  materialize  public  USAGE
mz_system    materialize  materialize  public  CREATE
materialize  PUBLIC       materialize  s       USAGE
materialize  materialize  materialize  s       USAGE
materialize  materialize  materialize  s       CREATE
materialize  r1           materialize  s       USAGE
materialize  r3           materialize  s       USAGE
materialize  r5           materialize  s       USAGE
materialize  r5           materialize  s       CREATE

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_schema_privileges ORDER BY database, name, grantee
----
materialize,PUBLIC,d,public,USAGE
mz_system,PUBLIC,materialize,public,USAGE
materialize,PUBLIC,materialize,s,USAGE
materialize,r1,materialize,s,USAGE
materialize,r3,materialize,s,USAGE
COMPLETE 5

# SHOW OBJECT PRIVILEGES

statement ok
CREATE TABLE t ()

statement ok
GRANT SELECT, INSERT ON TABLE t TO r1

statement ok
GRANT SELECT ON TABLE t TO r2

statement ok
GRANT DELETE ON TABLE t TO r5

statement ok
GRANT INSERT ON TABLE t TO PUBLIC

query TTTTTTT
SELECT * FROM mz_internal.mz_show_object_privileges ORDER BY database, schema, name, grantee
----
materialize  PUBLIC       materialize  public  t  table  INSERT
materialize  materialize  materialize  public  t  table  DELETE
materialize  materialize  materialize  public  t  table  INSERT
materialize  materialize  materialize  public  t  table  SELECT
materialize  materialize  materialize  public  t  table  UPDATE
materialize  r1           materialize  public  t  table  INSERT
materialize  r1           materialize  public  t  table  SELECT
materialize  r2           materialize  public  t  table  SELECT
materialize  r5           materialize  public  t  table  DELETE

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_object_privileges ORDER BY database, schema, name, grantee
----
materialize,PUBLIC,materialize,public,t,table,INSERT
materialize,r1,materialize,public,t,table,INSERT
materialize,r1,materialize,public,t,table,SELECT
materialize,r2,materialize,public,t,table,SELECT
COMPLETE 4

# SHOW ALL PRIVILEGES

query TTTTTTT
SELECT * FROM mz_internal.mz_show_all_privileges ORDER BY object_type, database, schema, name, grantee
----
materialize  PUBLIC       NULL         NULL    c            cluster   USAGE
materialize  materialize  NULL         NULL    c            cluster   USAGE
materialize  materialize  NULL         NULL    c            cluster   CREATE
materialize  r1           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   CREATE
materialize  r4           NULL         NULL    c            cluster   CREATE
mz_system    PUBLIC       NULL         NULL    quickstart   cluster  USAGE
mz_system    materialize  NULL         NULL    quickstart   cluster  USAGE
mz_system    materialize  NULL         NULL    quickstart   cluster  CREATE
materialize  PUBLIC       NULL         NULL    d            database  CREATE
materialize  materialize  NULL         NULL    d            database  USAGE
materialize  materialize  NULL         NULL    d            database  CREATE
materialize  r1           NULL         NULL    d            database  USAGE
materialize  r1           NULL         NULL    d            database  CREATE
materialize  r2           NULL         NULL    d            database  USAGE
materialize  r4           NULL         NULL    d            database  CREATE
mz_system    PUBLIC       NULL         NULL    materialize  database  USAGE
mz_system    materialize  NULL         NULL    materialize  database  USAGE
mz_system    materialize  NULL         NULL    materialize  database  CREATE
materialize  PUBLIC       d            NULL    public       schema  USAGE
materialize  materialize  d            NULL    public       schema  USAGE
materialize  materialize  d            NULL    public       schema  CREATE
mz_system    PUBLIC       materialize  NULL    public       schema  USAGE
mz_system    materialize  materialize  NULL    public       schema  USAGE
mz_system    materialize  materialize  NULL    public       schema  CREATE
materialize  PUBLIC       materialize  NULL    s            schema  USAGE
materialize  materialize  materialize  NULL    s            schema  USAGE
materialize  materialize  materialize  NULL    s            schema  CREATE
materialize  r1           materialize  NULL    s            schema  USAGE
materialize  r3           materialize  NULL    s            schema  USAGE
materialize  r5           materialize  NULL    s            schema  USAGE
materialize  r5           materialize  NULL    s            schema  CREATE
mz_system    PUBLIC       NULL         NULL    NULL         system    CREATECLUSTER
mz_system    materialize  NULL         NULL    NULL         system    CREATEDB
mz_system    materialize  NULL         NULL    NULL         system    CREATEROLE
mz_system    materialize  NULL         NULL    NULL         system    CREATECLUSTER
mz_system    materialize  NULL         NULL    NULL         system    CREATENETWORKPOLICY
mz_system    r1           NULL         NULL    NULL         system    CREATEDB
mz_system    r1           NULL         NULL    NULL         system    CREATECLUSTER
mz_system    r2           NULL         NULL    NULL         system    CREATEROLE
mz_system    r4           NULL         NULL    NULL         system    CREATECLUSTER
materialize  PUBLIC       materialize  public  t            table  INSERT
materialize  materialize  materialize  public  t            table  DELETE
materialize  materialize  materialize  public  t            table  INSERT
materialize  materialize  materialize  public  t            table  SELECT
materialize  materialize  materialize  public  t            table  UPDATE
materialize  r1           materialize  public  t            table  INSERT
materialize  r1           materialize  public  t            table  SELECT
materialize  r2           materialize  public  t            table  SELECT
materialize  r5           materialize  public  t            table  DELETE

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_all_my_privileges ORDER BY object_type, database, schema, name, grantee
----
materialize,PUBLIC,NULL,NULL,c,cluster,USAGE
materialize,r1,NULL,NULL,c,cluster,USAGE
materialize,r3,NULL,NULL,c,cluster,USAGE
materialize,r3,NULL,NULL,c,cluster,CREATE
mz_system,PUBLIC,NULL,NULL,quickstart,cluster,USAGE
materialize,PUBLIC,NULL,NULL,d,database,CREATE
materialize,r1,NULL,NULL,d,database,USAGE
materialize,r1,NULL,NULL,d,database,CREATE
materialize,r2,NULL,NULL,d,database,USAGE
mz_system,PUBLIC,NULL,NULL,materialize,database,USAGE
materialize,PUBLIC,d,NULL,public,schema,USAGE
mz_system,PUBLIC,materialize,NULL,public,schema,USAGE
materialize,PUBLIC,materialize,NULL,s,schema,USAGE
materialize,r1,materialize,NULL,s,schema,USAGE
materialize,r3,materialize,NULL,s,schema,USAGE
mz_system,PUBLIC,NULL,NULL,NULL,system,CREATECLUSTER
mz_system,r1,NULL,NULL,NULL,system,CREATEDB
mz_system,r1,NULL,NULL,NULL,system,CREATECLUSTER
mz_system,r2,NULL,NULL,NULL,system,CREATEROLE
materialize,PUBLIC,materialize,public,t,table,INSERT
materialize,r1,materialize,public,t,table,INSERT
materialize,r1,materialize,public,t,table,SELECT
materialize,r2,materialize,public,t,table,SELECT
COMPLETE 23

query TTTTTTT
SELECT * FROM (SHOW PRIVILEGES) ORDER BY object_type, database, schema, name, grantee
----
materialize  PUBLIC       NULL         NULL    c            cluster   USAGE
materialize  materialize  NULL         NULL    c            cluster   USAGE
materialize  materialize  NULL         NULL    c            cluster   CREATE
materialize  r1           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   CREATE
materialize  r4           NULL         NULL    c            cluster   CREATE
mz_system    PUBLIC       NULL         NULL    quickstart   cluster   USAGE
mz_system    materialize  NULL         NULL    quickstart   cluster   USAGE
mz_system    materialize  NULL         NULL    quickstart   cluster   CREATE
materialize  PUBLIC       NULL         NULL    d            database  CREATE
materialize  materialize  NULL         NULL    d            database  USAGE
materialize  materialize  NULL         NULL    d            database  CREATE
materialize  r1           NULL         NULL    d            database  USAGE
materialize  r1           NULL         NULL    d            database  CREATE
materialize  r2           NULL         NULL    d            database  USAGE
materialize  r4           NULL         NULL    d            database  CREATE
mz_system    PUBLIC       NULL         NULL    materialize  database  USAGE
mz_system    materialize  NULL         NULL    materialize  database  USAGE
mz_system    materialize  NULL         NULL    materialize  database  CREATE
materialize  PUBLIC       d            NULL    public       schema  USAGE
materialize  materialize  d            NULL    public       schema  USAGE
materialize  materialize  d            NULL    public       schema  CREATE
mz_system    PUBLIC       materialize  NULL    public       schema  USAGE
mz_system    materialize  materialize  NULL    public       schema  USAGE
mz_system    materialize  materialize  NULL    public       schema  CREATE
materialize  PUBLIC       materialize  NULL    s            schema  USAGE
materialize  materialize  materialize  NULL    s            schema  USAGE
materialize  materialize  materialize  NULL    s            schema  CREATE
materialize  r1           materialize  NULL    s            schema  USAGE
materialize  r3           materialize  NULL    s            schema  USAGE
materialize  r5           materialize  NULL    s            schema  USAGE
materialize  r5           materialize  NULL    s            schema  CREATE
mz_system    PUBLIC       NULL         NULL    NULL         system    CREATECLUSTER
mz_system    materialize  NULL         NULL    NULL         system    CREATEDB
mz_system    materialize  NULL         NULL    NULL         system    CREATEROLE
mz_system    materialize  NULL         NULL    NULL         system    CREATECLUSTER
mz_system    materialize  NULL         NULL    NULL         system    CREATENETWORKPOLICY
mz_system    r1           NULL         NULL    NULL         system    CREATEDB
mz_system    r1           NULL         NULL    NULL         system    CREATECLUSTER
mz_system    r2           NULL         NULL    NULL         system    CREATEROLE
mz_system    r4           NULL         NULL    NULL         system    CREATECLUSTER
materialize  PUBLIC       materialize  public  t            table  INSERT
materialize  materialize  materialize  public  t            table  DELETE
materialize  materialize  materialize  public  t            table  INSERT
materialize  materialize  materialize  public  t            table  SELECT
materialize  materialize  materialize  public  t            table  UPDATE
materialize  r1           materialize  public  t            table  INSERT
materialize  r1           materialize  public  t            table  SELECT
materialize  r2           materialize  public  t            table  SELECT
materialize  r5           materialize  public  t            table  DELETE

query TTTTTTT
SELECT * FROM (SHOW PRIVILEGES ON CLUSTERS) ORDER BY object_type, database, schema, name, grantee
----
materialize  PUBLIC       NULL         NULL    c            cluster   USAGE
materialize  materialize  NULL         NULL    c            cluster   USAGE
materialize  materialize  NULL         NULL    c            cluster   CREATE
materialize  r1           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   CREATE
materialize  r4           NULL         NULL    c            cluster   CREATE
mz_system    PUBLIC       NULL         NULL    quickstart   cluster   USAGE
mz_system    materialize  NULL         NULL    quickstart   cluster   USAGE
mz_system    materialize  NULL         NULL    quickstart   cluster   CREATE

query TTTTTTT
SELECT * FROM (SHOW PRIVILEGES FOR r2) ORDER BY object_type, database, schema, name, grantee
----
materialize  PUBLIC       NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   USAGE
materialize  r3           NULL         NULL    c            cluster   CREATE
mz_system    PUBLIC       NULL         NULL    quickstart   cluster   USAGE
materialize  PUBLIC       NULL         NULL    d            database  CREATE
materialize  r2           NULL         NULL    d            database  USAGE
mz_system    PUBLIC       NULL         NULL    materialize  database  USAGE
materialize  PUBLIC       d            NULL    public       schema  USAGE
mz_system    PUBLIC       materialize  NULL    public       schema  USAGE
materialize  PUBLIC       materialize  NULL    s            schema  USAGE
materialize  r3           materialize  NULL    s            schema  USAGE
mz_system    PUBLIC       NULL         NULL    NULL         system    CREATECLUSTER
mz_system    r2           NULL         NULL    NULL         system    CREATEROLE
materialize  PUBLIC       materialize  public  t            table  INSERT
materialize  r2           materialize  public  t            table  SELECT

# SHOW DEFAULT PRIVILEGES

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT SELECT, INSERT ON TABLES TO r1

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d GRANT UPDATE ON TABLES TO r1

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA s GRANT USAGE ON CONNECTIONS TO r3

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA s GRANT USAGE ON SECRETS TO r5

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE PUBLIC GRANT USAGE ON SCHEMAS TO r4
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE r5 GRANT CREATE ON DATABASES TO PUBLIC
----
COMPLETE 0

query TTTTTT
SELECT * FROM mz_internal.mz_show_default_privileges ORDER BY object_type, database, schema, grantee
----
materialize  materialize  s     connection  r3      USAGE
r5           NULL         NULL  database    PUBLIC  CREATE
PUBLIC       NULL         NULL  schema      r4      USAGE
materialize  materialize  s     secret      r5      USAGE
materialize  d            NULL  table       r1      UPDATE
materialize  NULL         NULL  table       r1      INSERT
materialize  NULL         NULL  table       r1      SELECT
PUBLIC       NULL         NULL  type        PUBLIC  USAGE

simple conn=r1,user=r1
SELECT * FROM mz_internal.mz_show_my_default_privileges ORDER BY object_type, database, schema, grantee
----
materialize,materialize,s,connection,r3,USAGE
r5,NULL,NULL,database,PUBLIC,CREATE
materialize,d,NULL,table,r1,UPDATE
materialize,NULL,NULL,table,r1,INSERT
materialize,NULL,NULL,table,r1,SELECT
PUBLIC,NULL,NULL,type,PUBLIC,USAGE
COMPLETE 6

query TTTTTT
SELECT * FROM (SHOW DEFAULT PRIVILEGES) ORDER BY object_type, database, schema, grantee
----
materialize  materialize  s     connection  r3      USAGE
r5           NULL         NULL  database    PUBLIC  CREATE
PUBLIC       NULL         NULL  schema      r4      USAGE
materialize  materialize  s     secret      r5      USAGE
materialize  d            NULL  table       r1      UPDATE
materialize  NULL         NULL  table       r1      INSERT
materialize  NULL         NULL  table       r1      SELECT
PUBLIC       NULL         NULL  type        PUBLIC  USAGE

query TTTTTT
SELECT * FROM (SHOW DEFAULT PRIVILEGES ON TABLES) ORDER BY object_type, database, schema, grantee
----
materialize  d            NULL  table       r1      UPDATE
materialize  NULL         NULL  table       r1      INSERT
materialize  NULL         NULL  table       r1      SELECT

query TTTTTT
SELECT * FROM (SHOW DEFAULT PRIVILEGES FOR r1) ORDER BY object_type, database, schema, grantee
----
materialize  materialize  s     connection  r3      USAGE
r5           NULL         NULL  database    PUBLIC  CREATE
materialize  d            NULL  table       r1      UPDATE
materialize  NULL         NULL  table       r1      INSERT
materialize  NULL         NULL  table       r1      SELECT
PUBLIC       NULL         NULL  type        PUBLIC  USAGE

# Prepration to be able to drop roles
statement ok
REVOKE r2 FROM r1

statement ok
REVOKE r3 FROM r2

statement ok
REVOKE r5 FROM r4

simple conn=mz_system,user=mz_system
REVOKE ALL ON SYSTEM FROM r1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE ALL ON SYSTEM FROM r2;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE ALL ON SYSTEM FROM r4;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATECLUSTER ON SYSTEM FROM PUBLIC;
----
COMPLETE 0

statement ok
DROP CLUSTER c CASCADE;

statement ok
DROP DATABASE d CASCADE;

statement ok
DROP SCHEMA S CASCADE;

statement ok
DROP TABLE T CASCADE;

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL ON TABLES FROM r1

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE PUBLIC REVOKE ALL ON SCHEMAS FROM r4
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE r5 REVOKE ALL ON DATABASES FROM PUBLIC
----
COMPLETE 0

statement ok
DROP ROLE r1, r2, r3, r4, r5

# Disable rbac checks.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false;
----
COMPLETE 0