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

mode cockroach

reset-server

# Enable rbac checks.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO true;
----
COMPLETE 0

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

# Test pre-populated default privileges

query TTTTTT rowsort
SELECT * FROM mz_default_privileges
----
p  NULL  NULL  type      p   U
p  NULL  NULL  schema    s2  U
p  NULL  NULL  cluster   s2  U
p  NULL  NULL  database  s2  U

statement ok
CREATE TYPE ty AS LIST (ELEMENT TYPE = int4);

query T rowsort
SELECT unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
=U/materialize
materialize=U/materialize

statement ok
DROP TYPE ty

# Create helper view
statement ok
CREATE VIEW default_privileges AS
SELECT
  (CASE defaults.role_id WHEN 'p' THEN 'PUBLIC' ELSE roles.name END) AS role_name,
  databases.name AS database_name,
  schemas.name AS schema_name,
  defaults.object_type AS object_type,
  (CASE defaults.grantee WHEN 'p' THEN 'PUBLIC' ELSE grantees.name END) AS grantee_name,
  defaults.privileges AS privileges
FROM mz_default_privileges defaults
LEFT JOIN mz_roles AS roles ON defaults.role_id = roles.id
LEFT JOIN mz_roles AS grantees ON defaults.grantee = grantees.id
LEFT JOIN mz_databases AS databases ON defaults.database_id = databases.id
LEFT JOIN mz_schemas AS schemas ON defaults.schema_id = schemas.id
WHERE grantees.name IS NULL OR grantees.name <> 'mz_support'
ORDER BY role_name, grantee_name

# Test altering default privileges

statement ok
CREATE SCHEMA s0

statement ok
CREATE DATABASE d1

statement ok
CREATE SCHEMA d1.s1

statement ok
CREATE SCHEMA d1.s11

statement ok
CREATE DATABASE d2

statement ok
CREATE SCHEMA d2.s2

statement ok
CREATE SCHEMA d2.s22

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
CREATE ROLE r6

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

statement ok
CREATE ROLE r7

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

statement ok
CREATE ROLE r8

statement ok
CREATE ROLE r9

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

statement ok
CREATE ROLE r10

statement ok
CREATE ROLE r11

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

statement ok
CREATE ROLE r12

statement ok
GRANT r6 TO materialize

statement ok
GRANT r7 TO materialize

statement ok
GRANT r9 TO materialize

statement ok
GRANT r11 TO materialize

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO r6, r7;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATE, USAGE ON SCHEMA d1.s1, d1.s11, d2.s2, d2.s22 TO r9, r11;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON DATABASE d1, d2 TO r9, r11;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATE ON DATABASE materialize, d1, d2 TO r6, r7, r9;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER quickstart TO r6, r7, r9, r11;
----
COMPLETE 0

## Relations

### Grants

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL PRIVILEGES ON TABLES TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 GRANT INSERT ON TABLES TO r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 GRANT DELETE ON TABLES TO r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT SELECT ON TABLES TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 GRANT INSERT ON TABLES TO r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 GRANT DELETE ON TABLES TO r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT UPDATE ON TABLES TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      r
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  NULL  NULL  table  PUBLIC  w
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r2      r
materialize  NULL  NULL  table  r1      ar
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  NULL  NULL  table  PUBLIC  w
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
CREATE TABLE materialize.public.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  =w/materialize
t  r2=r/materialize
t  r1=ar/materialize
t  r3=arwd/materialize
t  materialize=arwd/materialize

statement ok
DROP TABLE t

statement ok
CREATE VIEW materialize.public.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r1=r/materialize
v  r2=r/materialize
v  r3=r/materialize
v  materialize=r/materialize

statement ok
DROP VIEW v

statement ok
CREATE MATERIALIZED VIEW materialize.public.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r1=r/materialize
mv  r2=r/materialize
mv  r3=r/materialize
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW mv

statement ok
CREATE SOURCE materialize.public.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r1=r/materialize
s  r2=r/materialize
s  r3=r/materialize
s  materialize=r/materialize

statement ok
DROP SOURCE s

statement ok
CREATE TABLE d1.s11.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  =w/materialize
t  r2=r/materialize
t  r4=a/materialize
t  r1=ar/materialize
t  r3=arwd/materialize
t  materialize=arwd/materialize

statement ok
DROP TABLE d1.s11.t

statement ok
CREATE VIEW d1.s11.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r1=r/materialize
v  r2=r/materialize
v  r3=r/materialize
v  materialize=r/materialize

statement ok
DROP VIEW d1.s11.v

statement ok
CREATE MATERIALIZED VIEW d1.s11.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r1=r/materialize
mv  r2=r/materialize
mv  r3=r/materialize
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d1.s11.mv

statement ok
CREATE SOURCE d1.s11.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r1=r/materialize
s  r2=r/materialize
s  r3=r/materialize
s  materialize=r/materialize

statement ok
DROP SOURCE d1.s11.s

statement ok
CREATE TABLE d2.s22.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  =w/materialize
t  r2=r/materialize
t  r4=a/materialize
t  r1=ar/materialize
t  r3=arwd/materialize
t  materialize=arwd/materialize

statement ok
DROP TABLE d2.s22.t

statement ok
CREATE VIEW d2.s22.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r1=r/materialize
v  r2=r/materialize
v  r3=r/materialize
v  materialize=r/materialize

statement ok
DROP VIEW d2.s22.v

statement ok
CREATE MATERIALIZED VIEW d2.s22.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r1=r/materialize
mv  r2=r/materialize
mv  r3=r/materialize
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d2.s22.mv

statement ok
CREATE SOURCE d2.s22.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r1=r/materialize
s  r2=r/materialize
s  r3=r/materialize
s  materialize=r/materialize

statement ok
DROP SOURCE d2.s22.s

statement ok
CREATE TABLE d1.s1.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  =w/materialize
t  r2=r/materialize
t  r4=a/materialize
t  r5=d/materialize
t  r1=ar/materialize
t  r3=arwd/materialize
t  materialize=arwd/materialize

statement ok
DROP TABLE d1.s1.t

statement ok
CREATE VIEW d1.s1.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r1=r/materialize
v  r2=r/materialize
v  r3=r/materialize
v  materialize=r/materialize

statement ok
DROP VIEW d1.s1.v

statement ok
CREATE MATERIALIZED VIEW d1.s1.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r1=r/materialize
mv  r2=r/materialize
mv  r3=r/materialize
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d1.s1.mv

statement ok
CREATE SOURCE d1.s1.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r1=r/materialize
s  r2=r/materialize
s  r3=r/materialize
s  materialize=r/materialize

statement ok
DROP SOURCE d1.s1.s

statement ok
CREATE TABLE d2.s2.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  =w/materialize
t  r2=r/materialize
t  r4=a/materialize
t  r5=d/materialize
t  r1=ar/materialize
t  r3=arwd/materialize
t  materialize=arwd/materialize

statement ok
DROP TABLE d2.s2.t

statement ok
CREATE VIEW d2.s2.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r1=r/materialize
v  r2=r/materialize
v  r3=r/materialize
v  materialize=r/materialize

statement ok
DROP VIEW d2.s2.v

statement ok
CREATE MATERIALIZED VIEW d2.s2.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r1=r/materialize
mv  r2=r/materialize
mv  r3=r/materialize
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d2.s2.mv

statement ok
CREATE SOURCE d2.s2.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r1=r/materialize
s  r2=r/materialize
s  r3=r/materialize
s  materialize=r/materialize

statement ok
DROP SOURCE d2.s2.s

simple conn=r6,user=r6
CREATE TABLE materialize.public.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r8=r/r6
t  r6=arwd/r6

simple conn=r6,user=r6
DROP TABLE materialize.public.t;
----
COMPLETE 0

simple conn=r6,user=r6
CREATE VIEW materialize.public.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r6=r/r6
v  r8=r/r6

simple conn=r6,user=r6
DROP VIEW materialize.public.v;
----
COMPLETE 0

simple conn=r6,user=r6
CREATE MATERIALIZED VIEW materialize.public.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r6=r/r6
mv  r8=r/r6

simple conn=r6,user=r6
DROP MATERIALIZED VIEW materialize.public.mv;
----
COMPLETE 0

simple conn=r6,user=r6
CREATE SOURCE materialize.public.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r6=r/r6
s  r8=r/r6

simple conn=r6,user=r6
DROP SOURCE materialize.public.s;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE TABLE materialize.public.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r8=r/r7
t  r7=arwd/r7

simple conn=r7,user=r7
DROP TABLE materialize.public.t;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE VIEW materialize.public.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r7=r/r7
v  r8=r/r7

simple conn=r7,user=r7
DROP VIEW materialize.public.v;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE MATERIALIZED VIEW materialize.public.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r7=r/r7
mv  r8=r/r7

simple conn=r7,user=r7
DROP MATERIALIZED VIEW materialize.public.mv;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE SOURCE materialize.public.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r7=r/r7
s  r8=r/r7

simple conn=r7,user=r7
DROP SOURCE materialize.public.s;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TABLE d1.s1.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r10=a/r9
t  r9=arwd/r9

simple conn=r9,user=r9
DROP TABLE d1.s1.t;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE VIEW d1.s1.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r9=r/r9

simple conn=r9,user=r9
DROP VIEW d1.s1.v;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE MATERIALIZED VIEW d1.s1.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r9=r/r9

simple conn=r9,user=r9
DROP MATERIALIZED VIEW d1.s1.mv;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SOURCE d1.s1.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r9=r/r9

simple conn=r9,user=r9
DROP SOURCE d1.s1.s;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TABLE d2.s2.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r10=a/r9
t  r9=arwd/r9

simple conn=r9,user=r9
DROP TABLE d2.s2.t;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE VIEW d2.s2.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r9=r/r9

simple conn=r9,user=r9
DROP VIEW d2.s2.v;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE MATERIALIZED VIEW d2.s2.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r9=r/r9

simple conn=r9,user=r9
DROP MATERIALIZED VIEW d2.s2.mv;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SOURCE d2.s2.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r9=r/r9

simple conn=r9,user=r9
DROP SOURCE d2.s2.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d1.s1.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r12=d/r11
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d1.s1.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d1.s1.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d1.s1.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d1.s1.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d1.s1.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d1.s1.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d1.s1.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d1.s11.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d1.s11.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d1.s11.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d1.s11.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d1.s11.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d1.s11.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d1.s11.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d1.s11.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d2.s2.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r12=d/r11
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d2.s2.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d2.s2.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d2.s2.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d2.s2.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d2.s2.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d2.s2.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d2.s2.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d2.s22.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d2.s22.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d2.s22.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d2.s22.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d2.s22.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d2.s22.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d2.s22.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d2.s22.s;
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE SELECT ON TABLES FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      a
materialize  NULL  NULL  table  r3      arwd
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  NULL  NULL  table  PUBLIC  w
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      a
materialize  d1    NULL  table  r4      a
materialize  d2    NULL  table  r4      a
materialize  NULL  NULL  table  PUBLIC  w
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 REVOKE INSERT ON TABLES FROM r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      a
materialize  NULL  NULL  table  PUBLIC  w
materialize  d1    s1    table  r5      d
materialize  d2    s2    table  r5      d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 REVOKE DELETE ON TABLES FROM r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  table  r8      r
r7           NULL  NULL  table  r8      r
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      a
materialize  NULL  NULL  table  PUBLIC  w

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE SELECT ON TABLES FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r9           d1    NULL  table  r10     a
r9           d2    NULL  table  r10     a
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      a
materialize  NULL  NULL  table  PUBLIC  w

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 REVOKE INSERT ON TABLES FROM r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
r11          d1    s1    table  r12     d
r11          d2    s2    table  r12     d
materialize  NULL  NULL  table  r1      a
materialize  NULL  NULL  table  PUBLIC  w

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 REVOKE DELETE ON TABLES FROM r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      a
materialize  NULL  NULL  table  PUBLIC  w

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE UPDATE ON TABLES FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      a

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC NULL  NULL  type   PUBLIC  U

statement ok
CREATE TABLE materialize.public.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  materialize=arwd/materialize

statement ok
DROP TABLE t

statement ok
CREATE VIEW materialize.public.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  materialize=r/materialize

statement ok
DROP VIEW v

statement ok
CREATE MATERIALIZED VIEW materialize.public.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW mv

statement ok
CREATE SOURCE materialize.public.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  materialize=r/materialize

statement ok
DROP SOURCE s

statement ok
CREATE TABLE d1.s11.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  materialize=arwd/materialize

statement ok
DROP TABLE d1.s11.t

statement ok
CREATE VIEW d1.s11.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  materialize=r/materialize

statement ok
DROP VIEW d1.s11.v

statement ok
CREATE MATERIALIZED VIEW d1.s11.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d1.s11.mv

statement ok
CREATE SOURCE d1.s11.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  materialize=r/materialize

statement ok
DROP SOURCE d1.s11.s

statement ok
CREATE TABLE d2.s22.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  materialize=arwd/materialize

statement ok
DROP TABLE d2.s22.t

statement ok
CREATE VIEW d2.s22.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  materialize=r/materialize

statement ok
DROP VIEW d2.s22.v

statement ok
CREATE MATERIALIZED VIEW d2.s22.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d2.s22.mv

statement ok
CREATE SOURCE d2.s22.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  materialize=r/materialize

statement ok
DROP SOURCE d2.s22.s

statement ok
CREATE TABLE d1.s1.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  materialize=arwd/materialize

statement ok
DROP TABLE d1.s1.t

statement ok
CREATE VIEW d1.s1.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  materialize=r/materialize

statement ok
DROP VIEW d1.s1.v

statement ok
CREATE MATERIALIZED VIEW d1.s1.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d1.s1.mv

statement ok
CREATE SOURCE d1.s1.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  materialize=r/materialize

statement ok
DROP SOURCE d1.s1.s

statement ok
CREATE TABLE d2.s2.t ()

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  materialize=arwd/materialize

statement ok
DROP TABLE d2.s2.t

statement ok
CREATE VIEW d2.s2.v AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  materialize=r/materialize

statement ok
DROP VIEW d2.s2.v

statement ok
CREATE MATERIALIZED VIEW d2.s2.mv AS SELECT 1

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  materialize=r/materialize

statement ok
DROP MATERIALIZED VIEW d2.s2.mv

statement ok
CREATE SOURCE d2.s2.s FROM LOAD GENERATOR COUNTER

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  materialize=r/materialize

statement ok
DROP SOURCE d2.s2.s

simple conn=r6,user=r6
CREATE TABLE materialize.public.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r6=arwd/r6

simple conn=r6,user=r6
DROP TABLE materialize.public.t;
----
COMPLETE 0

simple conn=r6,user=r6
CREATE VIEW materialize.public.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r6=r/r6

simple conn=r6,user=r6
DROP VIEW materialize.public.v;
----
COMPLETE 0

simple conn=r6,user=r6
CREATE MATERIALIZED VIEW materialize.public.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r6=r/r6

simple conn=r6,user=r6
DROP MATERIALIZED VIEW materialize.public.mv;
----
COMPLETE 0

simple conn=r6,user=r6
CREATE SOURCE materialize.public.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r6=r/r6

simple conn=r6,user=r6
DROP SOURCE materialize.public.s;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE TABLE materialize.public.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r7=arwd/r7

simple conn=r7,user=r7
DROP TABLE materialize.public.t;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE VIEW materialize.public.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r7=r/r7

simple conn=r7,user=r7
DROP VIEW materialize.public.v;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE MATERIALIZED VIEW materialize.public.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r7=r/r7

simple conn=r7,user=r7
DROP MATERIALIZED VIEW materialize.public.mv;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE SOURCE materialize.public.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r7=r/r7

simple conn=r7,user=r7
DROP SOURCE materialize.public.s;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TABLE d1.s1.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r9=arwd/r9

simple conn=r9,user=r9
DROP TABLE d1.s1.t;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE VIEW d1.s1.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r9=r/r9

simple conn=r9,user=r9
DROP VIEW d1.s1.v;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE MATERIALIZED VIEW d1.s1.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r9=r/r9

simple conn=r9,user=r9
DROP MATERIALIZED VIEW d1.s1.mv;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SOURCE d1.s1.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r9=r/r9

simple conn=r9,user=r9
DROP SOURCE d1.s1.s;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TABLE d2.s2.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r9=arwd/r9

simple conn=r9,user=r9
DROP TABLE d2.s2.t;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE VIEW d2.s2.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r9=r/r9

simple conn=r9,user=r9
DROP VIEW d2.s2.v;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE MATERIALIZED VIEW d2.s2.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r9=r/r9

simple conn=r9,user=r9
DROP MATERIALIZED VIEW d2.s2.mv;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SOURCE d2.s2.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r9=r/r9

simple conn=r9,user=r9
DROP SOURCE d2.s2.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d1.s1.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d1.s1.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d1.s1.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d1.s1.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d1.s1.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d1.s1.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d1.s1.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d1.s1.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d1.s11.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d1.s11.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d1.s11.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d1.s11.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d1.s11.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d1.s11.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d1.s11.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d1.s11.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d2.s2.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d2.s2.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d2.s2.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d2.s2.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d2.s2.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d2.s2.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d2.s2.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d2.s2.s;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TABLE d2.s22.t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r11=arwd/r11

simple conn=r11,user=r11
DROP TABLE d2.s22.t;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE VIEW d2.s22.v AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_views WHERE name = 'v'
----
v  r11=r/r11

simple conn=r11,user=r11
DROP VIEW d2.s22.v;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE MATERIALIZED VIEW d2.s22.mv AS SELECT 1;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_materialized_views WHERE name = 'mv'
----
mv  r11=r/r11

simple conn=r11,user=r11
DROP MATERIALIZED VIEW d2.s22.mv;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SOURCE d2.s22.s FROM LOAD GENERATOR COUNTER;
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_sources WHERE name = 's'
----
s  r11=r/r11

simple conn=r11,user=r11
DROP SOURCE d2.s22.s;
----
COMPLETE 0

## Types

### Grants

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON TYPES TO r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL PRIVILEGES ON TYPES TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 GRANT USAGE ON TYPES TO r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 GRANT USAGE ON TYPES TO r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT USAGE ON TYPES TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 GRANT USAGE ON TYPES TO r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 GRANT USAGE ON TYPES TO r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON TYPES TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r1      U
materialize  NULL  NULL  type  r2      U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  NULL  NULL  type  PUBLIC  U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
CREATE TYPE materialize.public.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  r1=U/materialize
ty  r2=U/materialize
ty  r3=U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE ty

statement ok
CREATE TYPE d1.s11.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  r1=U/materialize
ty  r2=U/materialize
ty  r3=U/materialize
ty  r4=U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d1.s11.ty

statement ok
CREATE TYPE d2.s22.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  r1=U/materialize
ty  r2=U/materialize
ty  r3=U/materialize
ty  r4=U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d2.s22.ty

statement ok
CREATE TYPE d1.s1.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  r1=U/materialize
ty  r2=U/materialize
ty  r3=U/materialize
ty  r4=U/materialize
ty  r5=U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d1.s1.ty

statement ok
CREATE TYPE d2.s2.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  r1=U/materialize
ty  r2=U/materialize
ty  r3=U/materialize
ty  r4=U/materialize
ty  r5=U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d2.s2.ty

simple conn=r6,user=r6
CREATE TYPE materialize.public.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r6
ty  r6=U/r6
ty  r8=U/r6

simple conn=r6,user=r6
DROP TYPE materialize.public.ty;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE TYPE materialize.public.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r7
ty  r7=U/r7
ty  r8=U/r7

simple conn=r7,user=r7
DROP TYPE materialize.public.ty;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TYPE d1.s1.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r9
ty  r9=U/r9
ty  r10=U/r9

simple conn=r9,user=r9
DROP TYPE d1.s1.ty;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TYPE d2.s2.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r9
ty  r9=U/r9
ty  r10=U/r9

simple conn=r9,user=r9
DROP TYPE d2.s2.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d1.s1.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11
ty  r12=U/r11

simple conn=r11,user=r11
DROP TYPE d1.s1.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d1.s11.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11

simple conn=r11,user=r11
DROP TYPE d1.s11.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d2.s2.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11
ty  r12=U/r11

simple conn=r11,user=r11
DROP TYPE d2.s2.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d2.s22.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11

simple conn=r11,user=r11
DROP TYPE d2.s22.ty;
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON TYPES FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  r3      U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  NULL  NULL  type  PUBLIC  U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL PRIVILEGES ON TYPES FROM r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  d1    NULL  type  r4      U
materialize  d2    NULL  type  r4      U
materialize  NULL  NULL  type  PUBLIC  U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 REVOKE USAGE ON TYPES FROM r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  PUBLIC  U
materialize  d1    s1    type  r5      U
materialize  d2    s2    type  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 REVOKE USAGE ON TYPES FROM r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  type  r8      U
r7           NULL  NULL  type  r8      U
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE USAGE ON TYPES FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r9           d1    NULL  type  r10     U
r9           d2    NULL  type  r10     U
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 REVOKE USAGE ON TYPES FROM r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r11          d1    s1    type  r12     U
r11          d2    s2    type  r12     U
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 REVOKE USAGE ON TYPES FROM r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type  PUBLIC  U
materialize  NULL  NULL  type  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON TYPES FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type  PUBLIC  U

statement ok
CREATE TYPE materialize.public.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE ty

statement ok
CREATE TYPE d1.s11.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d1.s11.ty

statement ok
CREATE TYPE d2.s22.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d2.s22.ty

statement ok
CREATE TYPE d1.s1.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d1.s1.ty

statement ok
CREATE TYPE d2.s2.ty as (a int)

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
DROP TYPE d2.s2.ty

simple conn=r6,user=r6
CREATE TYPE materialize.public.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r6
ty  r6=U/r6

simple conn=r6,user=r6
DROP TYPE materialize.public.ty;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE TYPE materialize.public.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r7
ty  r7=U/r7

simple conn=r7,user=r7
DROP TYPE materialize.public.ty;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TYPE d1.s1.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r9
ty  r9=U/r9

simple conn=r9,user=r9
DROP TYPE d1.s1.ty;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE TYPE d2.s2.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r9
ty  r9=U/r9

simple conn=r9,user=r9
DROP TYPE d2.s2.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d1.s1.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11

simple conn=r11,user=r11
DROP TYPE d1.s1.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d1.s11.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11

simple conn=r11,user=r11
DROP TYPE d1.s11.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d2.s2.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11

simple conn=r11,user=r11
DROP TYPE d2.s2.ty;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE TYPE d2.s22.ty as (a int)
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
ty  =U/r11
ty  r11=U/r11

simple conn=r11,user=r11
DROP TYPE d2.s22.ty;
----
COMPLETE 0

## Secrets

### Grants

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON SECRETS TO r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL PRIVILEGES ON SECRETS TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 GRANT USAGE ON SECRETS TO r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 GRANT USAGE ON SECRETS TO r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT USAGE ON SECRETS TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 GRANT USAGE ON SECRETS TO r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 GRANT USAGE ON SECRETS TO r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON SECRETS TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  r1      U
materialize  NULL  NULL  secret  r2      U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  NULL  NULL  secret  PUBLIC  U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
CREATE SECRET materialize.public.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  =U/materialize
se  r1=U/materialize
se  r2=U/materialize
se  r3=U/materialize
se  materialize=U/materialize

statement ok
DROP SECRET se

statement ok
CREATE SECRET d1.s11.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  =U/materialize
se  r1=U/materialize
se  r2=U/materialize
se  r3=U/materialize
se  r4=U/materialize
se  materialize=U/materialize

statement ok
DROP SECRET d1.s11.se

statement ok
CREATE SECRET d2.s22.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  =U/materialize
se  r1=U/materialize
se  r2=U/materialize
se  r3=U/materialize
se  r4=U/materialize
se  materialize=U/materialize

statement ok
DROP SECRET d2.s22.se

statement ok
CREATE SECRET d1.s1.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  =U/materialize
se  r1=U/materialize
se  r2=U/materialize
se  r3=U/materialize
se  r4=U/materialize
se  r5=U/materialize
se  materialize=U/materialize

statement ok
DROP SECRET d1.s1.se

statement ok
CREATE SECRET d2.s2.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  =U/materialize
se  r1=U/materialize
se  r2=U/materialize
se  r3=U/materialize
se  r4=U/materialize
se  r5=U/materialize
se  materialize=U/materialize

statement ok
DROP SECRET d2.s2.se

simple conn=r6,user=r6
CREATE SECRET materialize.public.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r6=U/r6
se  r8=U/r6

simple conn=r6,user=r6
DROP SECRET materialize.public.se;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE SECRET materialize.public.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r7=U/r7
se  r8=U/r7

simple conn=r7,user=r7
DROP SECRET materialize.public.se;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SECRET d1.s1.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r9=U/r9
se  r10=U/r9

simple conn=r9,user=r9
DROP SECRET d1.s1.se;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SECRET d2.s2.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r9=U/r9
se  r10=U/r9

simple conn=r9,user=r9
DROP SECRET d2.s2.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d1.s1.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11
se  r12=U/r11

simple conn=r11,user=r11
DROP SECRET d1.s1.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d1.s11.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11

simple conn=r11,user=r11
DROP SECRET d1.s11.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d2.s2.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11
se  r12=U/r11

simple conn=r11,user=r11
DROP SECRET d2.s2.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d2.s22.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11

simple conn=r11,user=r11
DROP SECRET d2.s22.se;
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON SECRETS FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  r3      U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  NULL  NULL  secret  PUBLIC  U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL PRIVILEGES ON SECRETS FROM r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  d1    NULL  secret  r4      U
materialize  d2    NULL  secret  r4      U
materialize  NULL  NULL  secret  PUBLIC  U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 REVOKE USAGE ON SECRETS FROM r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  PUBLIC  U
materialize  d1    s1    secret  r5      U
materialize  d2    s2    secret  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 REVOKE USAGE ON SECRETS FROM r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  secret  r8      U
r7           NULL  NULL  secret  r8      U
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE USAGE ON SECRETS FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r9           d1    NULL  secret  r10     U
r9           d2    NULL  secret  r10     U
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 REVOKE USAGE ON SECRETS FROM r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
r11          d1    s1    secret  r12     U
r11          d2    s2    secret  r12     U
materialize  NULL  NULL  secret  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 REVOKE USAGE ON SECRETS FROM r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  secret  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON SECRETS FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U

statement ok
CREATE SECRET materialize.public.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  materialize=U/materialize

statement ok
DROP SECRET se

statement ok
CREATE SECRET d1.s11.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  materialize=U/materialize

statement ok
DROP SECRET d1.s11.se

statement ok
CREATE SECRET d2.s22.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  materialize=U/materialize

statement ok
DROP SECRET d2.s22.se

statement ok
CREATE SECRET d1.s1.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  materialize=U/materialize

statement ok
DROP SECRET d1.s1.se

statement ok
CREATE SECRET d2.s2.se AS decode('c2VjcmV0Cg==', 'base64')

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  materialize=U/materialize

statement ok
DROP SECRET d2.s2.se

simple conn=r6,user=r6
CREATE SECRET materialize.public.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r6=U/r6

simple conn=r6,user=r6
DROP SECRET materialize.public.se;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE SECRET materialize.public.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r7=U/r7

simple conn=r7,user=r7
DROP SECRET materialize.public.se;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SECRET d1.s1.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r9=U/r9

simple conn=r9,user=r9
DROP SECRET d1.s1.se;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SECRET d2.s2.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r9=U/r9

simple conn=r9,user=r9
DROP SECRET d2.s2.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d1.s1.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11

simple conn=r11,user=r11
DROP SECRET d1.s1.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d1.s11.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11

simple conn=r11,user=r11
DROP SECRET d1.s11.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d2.s2.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11

simple conn=r11,user=r11
DROP SECRET d2.s2.se;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE SECRET d2.s22.se AS decode('c2VjcmV0Cg==', 'base64')
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_secrets WHERE name = 'se'
----
se  r11=U/r11

simple conn=r11,user=r11
DROP SECRET d2.s22.se;
----
COMPLETE 0

## Connection

### Grants

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON CONNECTIONS TO r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL PRIVILEGES ON CONNECTIONS TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 GRANT USAGE ON CONNECTIONS TO r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 GRANT USAGE ON CONNECTIONS TO r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT USAGE ON CONNECTIONS TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 GRANT USAGE ON CONNECTIONS TO r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 GRANT USAGE ON CONNECTIONS TO r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON CONNECTIONS TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  r1      U
materialize  NULL  NULL  connection  r2      U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  NULL  NULL  connection  PUBLIC  U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
CREATE CONNECTION materialize.public.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  =U/materialize
c  r1=U/materialize
c  r2=U/materialize
c  r3=U/materialize
c  materialize=U/materialize

statement ok
DROP CONNECTION c

statement ok
CREATE SECRET s3_api_secret_key as 'secret_key';

statement ok
CREATE CONNECTION materialize.public.a TO AWS (ACCESS KEY ID = 'access_key', SECRET ACCESS KEY = SECRET s3_api_secret_key) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'a'
----
a  =U/materialize
a  r1=U/materialize
a  r2=U/materialize
a  r3=U/materialize
a  materialize=U/materialize

statement ok
DROP CONNECTION a

statement ok
DROP SECRET s3_api_secret_key;

statement ok
CREATE CONNECTION d1.s11.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  =U/materialize
c  r1=U/materialize
c  r2=U/materialize
c  r3=U/materialize
c  r4=U/materialize
c  materialize=U/materialize

statement ok
DROP CONNECTION d1.s11.c

statement ok
CREATE CONNECTION d2.s22.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  =U/materialize
c  r1=U/materialize
c  r2=U/materialize
c  r3=U/materialize
c  r4=U/materialize
c  materialize=U/materialize

statement ok
DROP CONNECTION d2.s22.c

statement ok
CREATE CONNECTION d1.s1.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  =U/materialize
c  r1=U/materialize
c  r2=U/materialize
c  r3=U/materialize
c  r4=U/materialize
c  r5=U/materialize
c  materialize=U/materialize

statement ok
DROP CONNECTION d1.s1.c

statement ok
CREATE CONNECTION d2.s2.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  =U/materialize
c  r1=U/materialize
c  r2=U/materialize
c  r3=U/materialize
c  r4=U/materialize
c  r5=U/materialize
c  materialize=U/materialize

statement ok
DROP CONNECTION d2.s2.c

simple conn=r6,user=r6
CREATE CONNECTION materialize.public.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r6=U/r6
c  r8=U/r6

simple conn=r6,user=r6
DROP CONNECTION materialize.public.c;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE CONNECTION materialize.public.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r7=U/r7
c  r8=U/r7

simple conn=r7,user=r7
DROP CONNECTION materialize.public.c;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE CONNECTION d1.s1.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r9=U/r9
c  r10=U/r9

simple conn=r9,user=r9
DROP CONNECTION d1.s1.c;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE CONNECTION d2.s2.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r9=U/r9
c  r10=U/r9

simple conn=r9,user=r9
DROP CONNECTION d2.s2.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d1.s1.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11
c  r12=U/r11

simple conn=r11,user=r11
DROP CONNECTION d1.s1.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d1.s11.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11

simple conn=r11,user=r11
DROP CONNECTION d1.s11.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d2.s2.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11
c  r12=U/r11

simple conn=r11,user=r11
DROP CONNECTION d2.s2.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d2.s22.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11

simple conn=r11,user=r11
DROP CONNECTION d2.s22.c;
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON CONNECTIONS FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  r3      U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  NULL  NULL  connection  PUBLIC  U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL PRIVILEGES ON CONNECTIONS FROM r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  d1    NULL  connection  r4      U
materialize  d2    NULL  connection  r4      U
materialize  NULL  NULL  connection  PUBLIC  U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 REVOKE USAGE ON CONNECTIONS FROM r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  PUBLIC  U
materialize  d1    s1    connection  r5      U
materialize  d2    s2    connection  r5      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA d1.s1, d2.s2 REVOKE USAGE ON CONNECTIONS FROM r5

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  connection  r8      U
r7           NULL  NULL  connection  r8      U
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE USAGE ON CONNECTIONS FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
r9           d1    NULL  connection  r10     U
r9           d2    NULL  connection  r10     U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 REVOKE USAGE ON CONNECTIONS FROM r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
r11          d1    s1    connection  r12     U
r11          d2    s2    connection  r12     U
materialize  NULL  NULL  connection  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r11 IN SCHEMA d1.s1, d2.s2 REVOKE USAGE ON CONNECTIONS FROM r12

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U
materialize  NULL  NULL  connection  PUBLIC  U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON CONNECTIONS FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type        PUBLIC  U

statement ok
CREATE CONNECTION materialize.public.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  materialize=U/materialize

statement ok
DROP CONNECTION c

statement ok
CREATE CONNECTION d1.s11.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  materialize=U/materialize

statement ok
DROP CONNECTION d1.s11.c

statement ok
CREATE CONNECTION d2.s22.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  materialize=U/materialize

statement ok
DROP CONNECTION d2.s22.c

statement ok
CREATE CONNECTION d1.s1.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  materialize=U/materialize

statement ok
DROP CONNECTION d1.s1.c

statement ok
CREATE CONNECTION d2.s2.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  materialize=U/materialize

statement ok
DROP CONNECTION d2.s2.c

simple conn=r6,user=r6
CREATE CONNECTION materialize.public.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r6=U/r6

simple conn=r6,user=r6
DROP CONNECTION materialize.public.c;
----
COMPLETE 0

simple conn=r7,user=r7
CREATE CONNECTION materialize.public.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r7=U/r7

simple conn=r7,user=r7
DROP CONNECTION materialize.public.c;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE CONNECTION d1.s1.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r9=U/r9

simple conn=r9,user=r9
DROP CONNECTION d1.s1.c;
----
COMPLETE 0

simple conn=r9,user=r9
CREATE CONNECTION d2.s2.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r9=U/r9

simple conn=r9,user=r9
DROP CONNECTION d2.s2.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d1.s1.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11

simple conn=r11,user=r11
DROP CONNECTION d1.s1.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d1.s11.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11

simple conn=r11,user=r11
DROP CONNECTION d1.s11.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d2.s2.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11

simple conn=r11,user=r11
DROP CONNECTION d2.s2.c;
----
COMPLETE 0

simple conn=r11,user=r11
CREATE CONNECTION d2.s22.c TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_connections WHERE name = 'c'
----
c  r11=U/r11

simple conn=r11,user=r11
DROP CONNECTION d2.s22.c;
----
COMPLETE 0

## Schemas

### Grants

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON SCHEMAS TO r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      U
materialize  NULL  NULL  schema  r2      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL ON SCHEMAS TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      U
materialize  NULL  NULL  schema  r2      U
materialize  NULL  NULL  schema  r3      UC

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 GRANT CREATE ON SCHEMAS TO r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      U
materialize  NULL  NULL  schema  r2      U
materialize  NULL  NULL  schema  r3      UC
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT CREATE ON SCHEMAS TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      U
materialize  NULL  NULL  schema  r2      U
materialize  NULL  NULL  schema  r3      UC
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 GRANT CREATE ON SCHEMAS TO r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      U
materialize  NULL  NULL  schema  r2      U
materialize  NULL  NULL  schema  r3      UC
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT CREATE ON SCHEMAS TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      U
materialize  NULL  NULL  schema  r2      U
materialize  NULL  NULL  schema  r3      UC
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT CREATE ON SCHEMAS TO r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r2      U
materialize  NULL  NULL  schema  r1      UC
materialize  NULL  NULL  schema  r3      UC
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
CREATE SCHEMA materialize.sch

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  =C/materialize
sch  r2=U/materialize
sch  r1=UC/materialize
sch  r3=UC/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP SCHEMA sch

statement ok
CREATE SCHEMA d1.sch

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  =C/materialize
sch  r2=U/materialize
sch  r4=C/materialize
sch  r1=UC/materialize
sch  r3=UC/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP SCHEMA d1.sch

statement ok
CREATE SCHEMA d2.sch

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  =C/materialize
sch  r2=U/materialize
sch  r4=C/materialize
sch  r1=UC/materialize
sch  r3=UC/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP SCHEMA d2.sch

simple conn=r6,user=r6
CREATE SCHEMA materialize.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r8=C/r6
sch  r6=UC/r6
sch  mz_support=U/r6

simple conn=r6,user=r6
DROP SCHEMA materialize.sch
----
COMPLETE 0

simple conn=r7,user=r7
CREATE SCHEMA materialize.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r8=C/r7
sch  r7=UC/r7
sch  mz_support=U/r7

simple conn=r7,user=r7
DROP SCHEMA materialize.sch
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SCHEMA d1.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r10=C/r9
sch  r9=UC/r9
sch  mz_support=U/r9

simple conn=r9,user=r9
DROP SCHEMA d1.sch
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SCHEMA d2.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r10=C/r9
sch  r9=UC/r9
sch  mz_support=U/r9

simple conn=r9,user=r9
DROP SCHEMA d2.sch
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON SCHEMAS FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      C
materialize  NULL  NULL  schema  r3      UC
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL ON SCHEMAS FROM r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      C
materialize  d1    NULL  schema  r4      C
materialize  d2    NULL  schema  r4      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d1, d2 REVOKE CREATE ON SCHEMAS FROM r4

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  schema  r8      C
r7           NULL  NULL  schema  r8      C
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE CREATE ON SCHEMAS FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r9           d1    NULL  schema  r10     C
r9           d2    NULL  schema  r10     C
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r9 IN DATABASE d1, d2 REVOKE CREATE ON SCHEMAS FROM r10

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      C
materialize  NULL  NULL  schema  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE CREATE ON SCHEMAS FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type    PUBLIC  U
materialize  NULL  NULL  schema  r1      C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE CREATE ON SCHEMAS FROM r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC NULL  NULL  type   PUBLIC  U

statement ok
CREATE SCHEMA materialize.sch

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP SCHEMA sch

statement ok
CREATE SCHEMA d1.sch

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP SCHEMA d1.sch

statement ok
CREATE SCHEMA d2.sch

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP SCHEMA d2.sch

simple conn=r6,user=r6
CREATE SCHEMA materialize.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r6=UC/r6
sch  mz_support=U/r6

simple conn=r6,user=r6
DROP SCHEMA materialize.sch
----
COMPLETE 0

simple conn=r7,user=r7
CREATE SCHEMA materialize.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r7=UC/r7
sch  mz_support=U/r7

simple conn=r7,user=r7
DROP SCHEMA materialize.sch
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SCHEMA d1.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r9=UC/r9
sch  mz_support=U/r9

simple conn=r9,user=r9
DROP SCHEMA d1.sch
----
COMPLETE 0

simple conn=r9,user=r9
CREATE SCHEMA d2.sch
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_schemas WHERE name = 'sch'
----
sch  r9=UC/r9
sch  mz_support=U/r9

simple conn=r9,user=r9
DROP SCHEMA d2.sch
----
COMPLETE 0

## Databases

### Grants

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON DATABASES TO r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      U
materialize  NULL  NULL  database  r2      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL ON DATABASES TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      U
materialize  NULL  NULL  database  r2      U
materialize  NULL  NULL  database  r3      UC

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT CREATE ON DATABASES TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  database  r8      C
r7           NULL  NULL  database  r8      C
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      U
materialize  NULL  NULL  database  r2      U
materialize  NULL  NULL  database  r3      UC

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT CREATE ON DATABASES TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  database  r8      C
r7           NULL  NULL  database  r8      C
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      U
materialize  NULL  NULL  database  r2      U
materialize  NULL  NULL  database  r3      UC
materialize  NULL  NULL  database  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT CREATE ON DATABASES TO r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  database  r8      C
r7           NULL  NULL  database  r8      C
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r2      U
materialize  NULL  NULL  database  r1      UC
materialize  NULL  NULL  database  r3      UC
materialize  NULL  NULL  database  PUBLIC  C

statement ok
CREATE DATABASE d

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  =C/materialize
d  r2=U/materialize
d  r1=UC/materialize
d  r3=UC/materialize
d  materialize=UC/materialize
d  mz_support=U/materialize

statement ok
DROP DATABASE d

simple conn=r6,user=r6
CREATE DATABASE d
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  r8=C/r6
d  r6=UC/r6
d  mz_support=U/r6

simple conn=r6,user=r6
DROP DATABASE d
----
COMPLETE 0

simple conn=r7,user=r7
CREATE DATABASE d
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  r8=C/r7
d  r7=UC/r7
d  mz_support=U/r7

simple conn=r7,user=r7
DROP DATABASE d
----
COMPLETE 0

simple conn=r9,user=r9
CREATE DATABASE d
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  r9=UC/r9
d  mz_support=U/r9

simple conn=r9,user=r9
DROP DATABASE d
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON DATABASES FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  database  r8      C
r7           NULL  NULL  database  r8      C
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      C
materialize  NULL  NULL  database  r3      UC
materialize  NULL  NULL  database  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL ON DATABASES FROM r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  database  r8      C
r7           NULL  NULL  database  r8      C
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      C
materialize  NULL  NULL  database  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE CREATE ON DATABASES FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      C
materialize  NULL  NULL  database  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE CREATE ON DATABASES FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type      PUBLIC  U
materialize  NULL  NULL  database  r1      C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE CREATE ON DATABASES FROM r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC NULL  NULL  type   PUBLIC  U

statement ok
CREATE DATABASE d

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  materialize=UC/materialize
d  mz_support=U/materialize

statement ok
DROP DATABASE d

simple conn=r6,user=r6
CREATE DATABASE d
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  r6=UC/r6
d  mz_support=U/r6

simple conn=r6,user=r6
DROP DATABASE d
----
COMPLETE 0

simple conn=r7,user=r7
CREATE DATABASE d
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  r7=UC/r7
d  mz_support=U/r7

simple conn=r7,user=r7
DROP DATABASE d
----
COMPLETE 0

simple conn=r9,user=r9
CREATE DATABASE d
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_databases WHERE name = 'd'
----
d  r9=UC/r9
d  mz_support=U/r9

simple conn=r9,user=r9
DROP DATABASE d
----
COMPLETE 0

## Clusters

### Grants

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON CLUSTERS TO r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      U
materialize  NULL  NULL  cluster  r2      U

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT ALL ON CLUSTERS TO r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      U
materialize  NULL  NULL  cluster  r2      U
materialize  NULL  NULL  cluster  r3      UC

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 GRANT CREATE ON CLUSTERS TO r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  cluster  r8      C
r7           NULL  NULL  cluster  r8      C
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      U
materialize  NULL  NULL  cluster  r2      U
materialize  NULL  NULL  cluster  r3      UC

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT CREATE ON CLUSTERS TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  cluster  r8      C
r7           NULL  NULL  cluster  r8      C
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      U
materialize  NULL  NULL  cluster  r2      U
materialize  NULL  NULL  cluster  r3      UC
materialize  NULL  NULL  cluster  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT CREATE ON CLUSTERS TO r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  cluster  r8      C
r7           NULL  NULL  cluster  r8      C
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r2      U
materialize  NULL  NULL  cluster  r1      UC
materialize  NULL  NULL  cluster  r3      UC
materialize  NULL  NULL  cluster  PUBLIC  C

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

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  =C/materialize
c  r2=U/materialize
c  r1=UC/materialize
c  r3=UC/materialize
c  materialize=UC/materialize
c  mz_support=U/materialize

statement ok
DROP CLUSTER c

simple conn=r6,user=r6
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  r8=C/r6
c  r6=UC/r6
c  mz_support=U/r6

simple conn=r6,user=r6
DROP CLUSTER c
----
COMPLETE 0

simple conn=r7,user=r7
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  r8=C/r7
c  r7=UC/r7
c  mz_support=U/r7

simple conn=r7,user=r7
DROP CLUSTER c
----
COMPLETE 0

simple conn=r9,user=r9
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  r9=UC/r9
c  mz_support=U/r9

simple conn=r9,user=r9
DROP CLUSTER c
----
COMPLETE 0

### Revokes

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON CLUSTERS FROM r1, r2

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  cluster  r8      C
r7           NULL  NULL  cluster  r8      C
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      C
materialize  NULL  NULL  cluster  r3      UC
materialize  NULL  NULL  cluster  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE ALL ON CLUSTERS FROM r3

query TTTTTT rowsort
SELECT * FROM default_privileges
----
r6           NULL  NULL  cluster  r8      C
r7           NULL  NULL  cluster  r8      C
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      C
materialize  NULL  NULL  cluster  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE r6, r7 REVOKE CREATE ON CLUSTERS FROM r8

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      C
materialize  NULL  NULL  cluster  PUBLIC  C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE CREATE ON CLUSTERS FROM PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type     PUBLIC  U
materialize  NULL  NULL  cluster  r1      C

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE CREATE ON CLUSTERS FROM r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC NULL  NULL  type  PUBLIC  U

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

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  materialize=UC/materialize
c  mz_support=U/materialize

statement ok
DROP CLUSTER c

simple conn=r6,user=r6
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  r6=UC/r6
c  mz_support=U/r6

simple conn=r6,user=r6
DROP CLUSTER c
----
COMPLETE 0

simple conn=r7,user=r7
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  r7=UC/r7
c  mz_support=U/r7

simple conn=r7,user=r7
DROP CLUSTER c
----
COMPLETE 0

simple conn=r9,user=r9
CREATE CLUSTER c REPLICAS (r1 (SIZE '1'))
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_clusters WHERE name = 'c'
----
c  r9=UC/r9
c  mz_support=U/r9

simple conn=r9,user=r9
DROP CLUSTER c
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM r6, r7;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE, USAGE ON SCHEMA d1.s1, d1.s11, d2.s2, d2.s22 FROM r9, r11;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE USAGE ON DATABASE d1, d2 FROM r9, r11;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON DATABASE materialize, d1, d2 FROM r6, r7, r9;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON CLUSTER quickstart FROM r6, r7, r9, r11;
----
COMPLETE 0

statement ok
DROP ROLE r1

statement ok
DROP ROLE r2

statement ok
DROP ROLE r3

statement ok
DROP ROLE r4

statement ok
DROP ROLE r5

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

statement ok
DROP ROLE r6

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

statement ok
DROP ROLE r7

statement ok
DROP ROLE r8

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

statement ok
DROP ROLE r9

statement ok
DROP ROLE r10

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

statement ok
DROP ROLE r11

statement ok
DROP ROLE r12

# Test that default privileges prevent dropping a role

statement ok
CREATE ROLE r1

statement ok
GRANT r1 TO materialize

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

simple conn=materialize,user=materialize
DROP ROLE r1
----
db error: ERROR: role "r1" cannot be dropped because some objects depend on it
DETAIL: r1: default privileges on TABLES created by r1

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

statement ok
DROP ROLE r1

statement ok
CREATE ROLE r1

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON CONNECTIONS TO r1

simple conn=materialize,user=materialize
DROP ROLE r1
----
db error: ERROR: role "r1" cannot be dropped because some objects depend on it
DETAIL: r1: default privileges on CONNECTIONS granted to r1

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON CONNECTIONS FROM r1

statement ok
DROP ROLE r1

# Test ALL ROLES variant

statement ok
CREATE ROLE r1

statement error permission denied to ALTER DEFAULT PRIVILEGES FOR ALL ROLES
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT INSERT ON TABLES TO r1

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT INSERT ON TABLES TO r1;
----
COMPLETE 0

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  table  r1      a
PUBLIC  NULL  NULL  type   PUBLIC  U

statement ok
CREATE ROLE r2

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA materialize.public TO r2;
----
COMPLETE 0

simple conn=r2,user=r2
CREATE TABLE t ();
----
COMPLETE 0

query TT rowsort
SELECT name, unnest(privileges)::text FROM mz_tables WHERE name = 't'
----
t  r1=a/r2
t  r2=arwd/r2

simple conn=r2,user=r2
DROP TABLE t;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA materialize.public FROM r2;
----
COMPLETE 0

statement ok
DROP ROLE r2

statement error permission denied to ALTER DEFAULT PRIVILEGES FOR ALL ROLES
ALTER DEFAULT PRIVILEGES FOR ALL ROLES REVOKE INSERT ON TABLES FROM r1

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ALL ROLES REVOKE INSERT ON TABLES FROM r1;
----
COMPLETE 0

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type   PUBLIC  U

statement ok
DROP ROLE r1

## Test that we can remove pre-populated global default privileges.

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ALL ROLES REVOKE USAGE ON TYPES FROM PUBLIC;
----
COMPLETE 0

query TTTTTT rowsort
SELECT * FROM default_privileges
----

statement ok
CREATE TYPE ty AS LIST (ELEMENT TYPE = int4);

query T rowsort
SELECT unnest(privileges)::text FROM mz_types WHERE name = 'ty'
----
materialize=U/materialize

statement ok
DROP TYPE ty;

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT USAGE ON TYPES TO PUBLIC;
----
COMPLETE 0

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type   PUBLIC  U

## Test that you can also specify PUBLIC in target role list

statement error permission denied to ALTER DEFAULT PRIVILEGES FOR ALL ROLES
ALTER DEFAULT PRIVILEGES FOR ROLE PUBLIC GRANT CREATE ON CLUSTERS TO materialize

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type     PUBLIC      U
PUBLIC  NULL  NULL  cluster  materialize  C

statement error permission denied to ALTER DEFAULT PRIVILEGES FOR ALL ROLES
ALTER DEFAULT PRIVILEGES FOR ROLE PUBLIC REVOKE CREATE ON CLUSTERS FROM materialize

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE PUBLIC REVOKE CREATE ON CLUSTERS FROM materialize
----
COMPLETE 0

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type   PUBLIC  U

# Test that DROP OWNED drops default privileges

statement ok
CREATE ROLE r1

statement ok
GRANT r1 TO materialize

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type   PUBLIC       U
r1      NULL  NULL  table  materialize  r

statement ok
DROP OWNED BY r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type   PUBLIC  U

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

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  NULL  NULL  table  r1      r

statement ok
DROP OWNED BY r1

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type   PUBLIC  U

statement ok
DROP ROLE r1

# Test error scenarios

statement error For object type VIEW, you must specify 'TABLE' or omit the object type
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT SELECT ON VIEWS TO PUBLIC

statement error For object type MATERIALIZED VIEW, you must specify 'TABLE' or omit the object type
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT SELECT ON MATERIALIZED VIEWS TO PUBLIC

statement error For object type SOURCE, you must specify 'TABLE' or omit the object type
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT SELECT ON SOURCES TO PUBLIC

statement error Unsupported GRANT on SINK
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON SINKS TO PUBLIC

statement error Unsupported GRANT on CLUSTER REPLICA
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON CLUSTER REPLICAS TO PUBLIC

statement error Unsupported GRANT on ROLE
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON ROLES TO PUBLIC

statement error Unsupported GRANT on INDEX
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON INDEXES TO PUBLIC

statement error Expected one of TABLES or TYPES or SECRETS or CONNECTIONS or SCHEMAS or DATABASES or CLUSTERS
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT USAGE ON FUNCTIONS TO PUBLIC

statement error cannot specify CLUSTERS and IN DATABASE
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE materialize GRANT USAGE ON CLUSTERS TO PUBLIC

statement error cannot specify DATABASES and IN DATABASE
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE materialize GRANT USAGE ON DATABASES TO PUBLIC

statement error cannot specify CLUSTERS and IN SCHEMA
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA public GRANT USAGE ON CLUSTERS TO PUBLIC

statement error cannot specify DATABASES and IN SCHEMA
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA public GRANT USAGE ON DATABASES TO PUBLIC

statement error cannot specify SCHEMAS and IN SCHEMA
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA public GRANT USAGE ON SCHEMAS TO PUBLIC

statement error invalid privilege types INSERT for TYPE
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT INSERT ON TYPES TO PUBLIC

statement error invalid privilege types DELETE for CLUSTER
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT DELETE ON CLUSTERS TO PUBLIC

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE mz_introspection GRANT SELECT ON TABLES TO PUBLIC
----
db error: ERROR: unknown role 'mz_introspection'

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE mz_support GRANT SELECT ON TABLES TO PUBLIC
----
db error: ERROR: must be a member of "mz_support"

statement error system schema 'mz_catalog' cannot be modified
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA mz_catalog GRANT INSERT ON TABLES TO PUBLIC

statement error role name "mz_system" is reserved
ALTER DEFAULT PRIVILEGES FOR ROLE materialize GRANT INSERT ON TABLES TO mz_system

statement error role name "mz_support" is reserved
ALTER DEFAULT PRIVILEGES FOR ROLE materialize REVOKE USAGE ON CLUSTERS FROM mz_support

# Test that we don't retain dangling pointers in default privileges

statement ok
CREATE DATABASE d

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN DATABASE d GRANT SELECT ON TABLES TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL  NULL  type   PUBLIC  U
materialize  d     NULL  table  PUBLIC  r

statement ok
DROP DATABASE d

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type  PUBLIC  U

statement ok
CREATE SCHEMA s

statement ok
ALTER DEFAULT PRIVILEGES FOR ROLE materialize IN SCHEMA s GRANT SELECT ON TABLES TO PUBLIC

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC       NULL         NULL  type   PUBLIC  U
materialize  materialize  s     table  PUBLIC  r

statement ok
DROP SCHEMA s

query TTTTTT rowsort
SELECT * FROM default_privileges
----
PUBLIC  NULL  NULL  type  PUBLIC  U


# Disable rbac checks.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false;
----
COMPLETE 0