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

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

statement ok
CREATE ROLE test_role

# Test default privileges

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

## Create some helper views

statement ok
CREATE VIEW database_privileges (name, privilege) AS SELECT name, unnest(privileges)::text FROM mz_databases;

statement ok
CREATE VIEW schema_privileges (name, privilege) AS SELECT name, unnest(privileges)::text, database_id FROM mz_schemas;

statement ok
CREATE VIEW cluster_privileges (name, privilege) AS SELECT name, unnest(privileges)::text FROM mz_clusters;

statement ok
CREATE VIEW item_privileges (name, type, privilege) AS SELECT name, type, unnest(privileges)::text FROM mz_objects;

## Test built-in objects

query TT rowsort
SELECT * FROM database_privileges
----
materialize  =U/mz_system
materialize  mz_system=UC/mz_system
materialize  materialize=UC/mz_system
materialize  mz_support=U/mz_system

query TT rowsort
SELECT name, privilege FROM schema_privileges ORDER BY name, privilege
----
information_schema  =U/mz_system
information_schema  mz_system=UC/mz_system
information_schema  mz_support=U/mz_system
mz_catalog          =U/mz_system
mz_catalog          mz_system=UC/mz_system
mz_catalog          mz_support=U/mz_system
mz_catalog_unstable =U/mz_system
mz_catalog_unstable mz_support=U/mz_system
mz_catalog_unstable mz_system=UC/mz_system
mz_unsafe           =U/mz_system
mz_unsafe           mz_support=U/mz_system
mz_unsafe           mz_system=UC/mz_system
mz_internal         =U/mz_system
mz_internal         mz_system=UC/mz_system
mz_internal         mz_support=U/mz_system
mz_introspection    =U/mz_system
mz_introspection    mz_system=UC/mz_system
mz_introspection    mz_support=U/mz_system
pg_catalog          =U/mz_system
pg_catalog          mz_system=UC/mz_system
pg_catalog          mz_support=U/mz_system
public              =U/mz_system
public              mz_system=UC/mz_system
public              materialize=UC/mz_system
public              mz_support=U/mz_system

query TT rowsort
SELECT * FROM cluster_privileges ORDER BY name, privilege
----
mz_analytics  mz_analytics=UC/mz_analytics
mz_analytics  mz_system=UC/mz_analytics
mz_catalog_server  =U/mz_system
mz_catalog_server  mz_support=UC/mz_system
mz_catalog_server  mz_system=UC/mz_system
mz_probe  mz_monitor=U/mz_system
mz_probe  mz_support=U/mz_system
mz_probe  mz_system=UC/mz_system
mz_support  mz_support=UC/mz_support
mz_support  mz_system=UC/mz_support
mz_system  mz_support=U/mz_system
mz_system  mz_system=UC/mz_system
quickstart  =U/mz_system
quickstart  materialize=UC/mz_system
quickstart  mz_support=U/mz_system
quickstart  mz_system=UC/mz_system

### The materialize privilege comes from the views created above
### The mz_support privilege comes from the `_redacted`
### statement logging views.
query T rowsort
SELECT DISTINCT(privilege) FROM item_privileges WHERE type = 'view' OR type = 'materialized view' OR type = 'source'
----
=r/mz_system
mz_system=r/mz_system
mz_monitor=r/mz_system
mz_support=r/mz_system
mz_analytics=r/mz_system
materialize=r/materialize
mz_monitor_redacted=r/mz_system

query T
SELECT DISTINCT(privilege) FROM item_privileges WHERE type = 'table'
----
=r/mz_system
mz_monitor=r/mz_system
mz_system=arwd/mz_system

query T
SELECT DISTINCT(privilege) FROM item_privileges WHERE type = 'type'
----
=U/mz_system
mz_system=U/mz_system

query T
SELECT privileges::text FROM mz_system_privileges
----
mz_system=RBNP/mz_system

## Test user created objects

simple conn=mz_system,user=mz_system
GRANT ALL PRIVILEGES ON SYSTEM TO materialize;
----
COMPLETE 0

statement ok
CREATE TABLE t (a INT);

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  materialize=arwd/materialize

statement ok
CREATE VIEW v AS SELECT 1;

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  materialize=r/materialize

statement ok
CREATE MATERIALIZED VIEW mv AS SELECT 1;

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  materialize=r/materialize

statement ok
CREATE SOURCE s FROM LOAD GENERATOR COUNTER;

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  materialize=r/materialize

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

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
CREATE SECRET se AS decode('c2VjcmV0Cg==', 'base64');

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  materialize=U/materialize

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

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  materialize=U/materialize

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

query TT rowsort
SELECT * FROM cluster_privileges WHERE name = 'c'
----
c  materialize=UC/materialize
c  mz_support=U/materialize

statement ok
CREATE DATABASE d;

query TT rowsort
SELECT * FROM database_privileges WHERE name = 'd'
----
d  materialize=UC/materialize
d  mz_support=U/materialize

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'public' ORDER BY name, privilege
----
public  =U/mz_system
public  =U/materialize
public  mz_system=UC/mz_system
public  materialize=UC/mz_system
public  materialize=UC/materialize
public  mz_support=U/mz_system
public  mz_support=U/materialize

statement ok
CREATE SCHEMA sch;

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  materialize=UC/materialize
sch  mz_support=U/materialize

# Changing the owner of an object should change the grantor of all privileges to the new owner and
# transfer the privileges of the old owner to the new owner.

simple conn=mz_system,user=mz_system
CREATE ROLE joe
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER TABLE t OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  joe=arwd/joe

simple conn=mz_system,user=mz_system
ALTER VIEW v OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  joe=r/joe

simple conn=mz_system,user=mz_system
ALTER MATERIALIZED VIEW mv OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  joe=r/joe

simple conn=mz_system,user=mz_system
ALTER SOURCE s OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  joe=r/joe

simple conn=mz_system,user=mz_system
ALTER TYPE ty OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  =U/joe
ty  joe=U/joe

simple conn=mz_system,user=mz_system
ALTER SECRET se OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  joe=U/joe

simple conn=mz_system,user=mz_system
ALTER CONNECTION conn OWNER TO joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  joe=U/joe

simple conn=mz_system,user=mz_system
ALTER CLUSTER c OWNER TO joe
----
COMPLETE 0

query TT rowsort
SELECT * FROM cluster_privileges WHERE name = 'c'
----
c  joe=UC/joe
c  mz_support=U/joe

simple conn=mz_system,user=mz_system
ALTER DATABASE d OWNER TO joe
----
COMPLETE 0

query TT rowsort
SELECT * FROM database_privileges WHERE name = 'd'
----
d  joe=UC/joe
d  mz_support=U/joe

simple conn=mz_system,user=mz_system
ALTER SCHEMA sch OWNER TO joe
----
COMPLETE 0

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  joe=UC/joe
sch  mz_support=U/joe

## Switch the owners back to materialize

simple conn=mz_system,user=mz_system
ALTER TABLE t OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER VIEW v OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER MATERIALIZED VIEW mv OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SOURCE s OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER TYPE ty OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SECRET se OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER CONNECTION conn OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER CLUSTER c OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER DATABASE d OWNER TO materialize
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SCHEMA sch OWNER TO materialize
----
COMPLETE 0

# Test GRANT and REVOKE

simple conn=mz_system,user=mz_system
CREATE ROLE other
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE ROLE child
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT joe TO child
----
COMPLETE 0

## Table

query B
SELECT has_table_privilege('joe', 't', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 't', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
false

statement ok
GRANT SELECT on TABLE t TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  joe=r/materialize
t  materialize=arwd/materialize

query B
SELECT has_table_privilege('joe', 't', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 't', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
true

### Duplicate grants have no effect
statement ok
GRANT SELECT on TABLE t TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  joe=r/materialize
t  materialize=arwd/materialize

statement ok
GRANT SELECT, INSERT, UPDATE on TABLE t TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  =arw/materialize
t  joe=r/materialize
t  materialize=arwd/materialize

simple conn=joe1,user=joe
GRANT SELECT on TABLE t TO other
----
db error: ERROR: must be owner of TABLE materialize.public.t

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE SELECT on TABLE t FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  =arw/materialize
t  materialize=arwd/materialize

query B
SELECT has_table_privilege('joe', 't', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 't', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE SELECT on TABLE t FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  =arw/materialize
t  materialize=arwd/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE INSERT, UPDATE ON TABLE t FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't'
----
t  =r/materialize
t  materialize=arwd/materialize

statement ok
REVOKE SELECT ON TABLE t FROM PUBLIC

query B
SELECT has_table_privilege('joe', 't', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 't', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 't', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
false

statement error invalid privilege types USAGE, CREATE for TABLE
GRANT USAGE, CREATE ON TABLE t TO joe

## View

query B
SELECT has_table_privilege('joe', 'v', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 'v', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
false

statement ok
GRANT SELECT on TABLE v TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  joe=r/materialize
v  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 'v', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 'v', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
true

### Duplicate grants have no effect
statement ok
GRANT SELECT on TABLE v TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  joe=r/materialize
v  materialize=r/materialize

statement ok
GRANT SELECT on TABLE v TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  =r/materialize
v  joe=r/materialize
v  materialize=r/materialize

simple conn=joe2,user=joe
GRANT SELECT on TABLE v TO other
----
db error: ERROR: must be owner of VIEW materialize.public.v

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE SELECT on TABLE v FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  =r/materialize
v  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 'v', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 'v', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE SELECT on TABLE v FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  =r/materialize
v  materialize=r/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE SELECT ON TABLE v FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v'
----
v  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 'v', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 'v', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'v', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_views WHERE name = 'v'), 'SELECT')
----
false

statement error invalid privilege types USAGE, CREATE for VIEW
GRANT INSERT, UPDATE, DELETE, USAGE, CREATE ON TABLE v TO joe

## Materialized View

query B
SELECT has_table_privilege('joe', 'mv', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 'mv', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
false

statement ok
GRANT SELECT on TABLE mv TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  joe=r/materialize
mv  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 'mv', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 'mv', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
true

### Duplicate grants have no effect
statement ok
GRANT SELECT on TABLE mv TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  joe=r/materialize
mv  materialize=r/materialize

statement ok
GRANT SELECT on TABLE mv TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  =r/materialize
mv  joe=r/materialize
mv  materialize=r/materialize

simple conn=joe3,user=joe
GRANT SELECT on TABLE mv TO other
----
db error: ERROR: must be owner of MATERIALIZED VIEW materialize.public.mv

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE SELECT on TABLE mv FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  =r/materialize
mv  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 'mv', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 'mv', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE SELECT on TABLE mv FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  =r/materialize
mv  materialize=r/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE SELECT ON TABLE mv FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'mv'
----
mv  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 'mv', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 'mv', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'mv', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'SELECT')
----
false

statement error invalid privilege types USAGE, CREATE for MATERIALIZED VIEW
GRANT INSERT, UPDATE, DELETE, USAGE, CREATE ON TABLE mv TO joe

## Source

query B
SELECT has_table_privilege('joe', 's', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 's', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
false

statement ok
GRANT SELECT on TABLE s TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  joe=r/materialize
s  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 's', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 's', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
true

### Duplicate grants have no effect
statement ok
GRANT SELECT on TABLE s TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  joe=r/materialize
s  materialize=r/materialize

statement ok
GRANT SELECT on TABLE s TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  =r/materialize
s  joe=r/materialize
s  materialize=r/materialize

simple conn=joe4,user=joe
GRANT SELECT on TABLE s TO other
----
db error: ERROR: must be owner of SOURCE materialize.public.s

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE SELECT on TABLE s FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  =r/materialize
s  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 's', 'SELECT')
----
true

query B
SELECT has_table_privilege('child', 's', 'SELECT')
----
true

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE SELECT on TABLE s FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  =r/materialize
s  materialize=r/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE SELECT ON TABLE s FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 's'
----
s  materialize=r/materialize

query B
SELECT has_table_privilege('joe', 's', 'SELECT')
----
false

query B
SELECT has_table_privilege('child', 's', 'SELECT')
----
false

query B
SELECT has_table_privilege('joe', (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 's', 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_sources WHERE name = 's'), 'SELECT')
----
false

statement error invalid privilege types USAGE, CREATE for SOURCE
GRANT INSERT, UPDATE, DELETE, USAGE, CREATE ON TABLE s TO joe

## Type

statement ok
REVOKE USAGE on TYPE ty FROM PUBLIC

query B
SELECT has_type_privilege('joe', 'ty', 'USAGE')
----
false

query B
SELECT has_type_privilege('child', 'ty', 'USAGE')
----
false

query B
SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
false

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE')
----
false

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
false

statement ok
GRANT USAGE on TYPE ty TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  joe=U/materialize
ty  materialize=U/materialize

query B
SELECT has_type_privilege('joe', 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege('child', 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
true

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
true

### Duplicate grants have no effect
statement ok
GRANT USAGE on TYPE ty TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  joe=U/materialize
ty  materialize=U/materialize

statement ok
GRANT USAGE on TYPE ty TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  =U/materialize
ty  joe=U/materialize
ty  materialize=U/materialize

simple conn=joe5,user=joe
GRANT USAGE on TYPE ty TO other
----
db error: ERROR: must be owner of TYPE materialize.public.ty

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE USAGE on TYPE ty FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

query B
SELECT has_type_privilege('joe', 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege('child', 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
true

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE USAGE on TYPE ty FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE USAGE ON TYPE ty FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  materialize=U/materialize

query B
SELECT has_type_privilege('joe', 'ty', 'SELECT')
----
false

query B
SELECT has_type_privilege('child', 'ty', 'USAGE')
----
false

query B
SELECT has_type_privilege('joe', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
false

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'ty', 'USAGE')
----
false

query B
SELECT has_type_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
false

statement ok
GRANT USAGE on TYPE ty TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'ty'
----
ty  =U/materialize
ty  materialize=U/materialize

statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE, CREATE for TYPE
GRANT INSERT, SELECT, UPDATE, DELETE, CREATE ON TYPE ty TO joe

## Secret

query B
SELECT has_secret_privilege('joe', 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege('child', 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
false

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
false

statement ok
GRANT USAGE on SECRET se TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  joe=U/materialize
se  materialize=U/materialize

query B
SELECT has_secret_privilege('joe', 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege('child', 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
true

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
true

### Duplicate grants have no effect
statement ok
GRANT USAGE on SECRET se TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  joe=U/materialize
se  materialize=U/materialize

statement ok
GRANT USAGE on SECRET se TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  =U/materialize
se  joe=U/materialize
se  materialize=U/materialize

simple conn=joe6,user=joe
GRANT USAGE on SECRET se TO other
----
db error: ERROR: must be owner of SECRET materialize.public.se

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE USAGE on SECRET se FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  =U/materialize
se  materialize=U/materialize

query B
SELECT has_secret_privilege('joe', 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege('child', 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
true

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE USAGE on SECRET se FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  =U/materialize
se  materialize=U/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE USAGE ON SECRET se FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'se'
----
se  materialize=U/materialize

query B
SELECT has_secret_privilege('joe', 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege('child', 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege('joe', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
false

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
false

statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE, CREATE for SECRET
GRANT INSERT, SELECT, UPDATE, DELETE, CREATE ON SECRET se TO joe

## Connection

query B
SELECT has_connection_privilege('joe', 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege('child', 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
false

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
false

statement ok
GRANT USAGE on CONNECTION conn TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  joe=U/materialize
conn  materialize=U/materialize

query B
SELECT has_connection_privilege('joe', 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege('child', 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
true

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
true

### Duplicate grants have no effect
statement ok
GRANT USAGE on CONNECTION conn TO joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  joe=U/materialize
conn  materialize=U/materialize

statement ok
GRANT USAGE on CONNECTION conn TO PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  =U/materialize
conn  joe=U/materialize
conn  materialize=U/materialize

simple conn=joe7,user=joe
GRANT USAGE on CONNECTION conn TO other
----
db error: ERROR: must be owner of CONNECTION materialize.public.conn

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE USAGE on CONNECTION conn FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  =U/materialize
conn  materialize=U/materialize

query B
SELECT has_connection_privilege('joe', 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege('child', 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
true

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE USAGE on CONNECTION conn FROM joe

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  =U/materialize
conn  materialize=U/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE USAGE ON CONNECTION conn FROM PUBLIC

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'conn'
----
conn  materialize=U/materialize

query B
SELECT has_connection_privilege('joe', 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege('child', 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege('joe', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
false

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
false

statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE, CREATE for CONNECTION
GRANT INSERT, SELECT, UPDATE, DELETE, CREATE ON CONNECTION conn TO joe

## Cluster

query B
SELECT has_cluster_privilege('joe', 'c', 'USAGE')
----
false

query B
SELECT has_cluster_privilege('child', 'c', 'USAGE')
----
false

query B
SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE')
----
false

statement ok
GRANT USAGE on CLUSTER c TO joe

query TT rowsort
SELECT name, privilege FROM cluster_privileges WHERE name = 'c'
----
c  joe=U/materialize
c  materialize=UC/materialize
c  mz_support=U/materialize

query B
SELECT has_cluster_privilege('joe', 'c', 'USAGE')
----
true

query B
SELECT has_cluster_privilege('child', 'c', 'USAGE')
----
true

query B
SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE')
----
true

### Duplicate grants have no effect
statement ok
GRANT USAGE on CLUSTER c TO joe

query TT rowsort
SELECT name, privilege FROM cluster_privileges WHERE name = 'c'
----
c  joe=U/materialize
c  materialize=UC/materialize
c  mz_support=U/materialize

statement ok
GRANT USAGE, CREATE on CLUSTER c TO PUBLIC

query TT rowsort
SELECT name, privilege FROM cluster_privileges WHERE name = 'c'
----
c  =UC/materialize
c  joe=U/materialize
c  materialize=UC/materialize
c  mz_support=U/materialize

simple conn=joe8,user=joe
GRANT USAGE on CLUSTER c TO other
----
db error: ERROR: must be owner of CLUSTER c

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE USAGE on CLUSTER c FROM joe

query TT rowsort
SELECT name, privilege FROM cluster_privileges WHERE name = 'c'
----
c  =UC/materialize
c  materialize=UC/materialize
c  mz_support=U/materialize

query B
SELECT has_cluster_privilege('joe', 'c', 'USAGE')
----
true

query B
SELECT has_cluster_privilege('child', 'c', 'USAGE')
----
true

query B
SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE USAGE on CLUSTER c FROM joe

query TT rowsort
SELECT name, privilege FROM cluster_privileges WHERE name = 'c'
----
c  =UC/materialize
c  materialize=UC/materialize
c  mz_support=U/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE CREATE, USAGE ON CLUSTER c FROM PUBLIC

query TT rowsort
SELECT name, privilege FROM cluster_privileges WHERE name = 'c'
----
c  materialize=UC/materialize
c  mz_support=U/materialize

query B
SELECT has_cluster_privilege('joe', 'c', 'USAGE')
----
false

query B
SELECT has_cluster_privilege('child', 'c', 'USAGE')
----
false

query B
SELECT has_cluster_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'c', 'USAGE')
----
false

statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE for CLUSTER
GRANT INSERT, SELECT, UPDATE, DELETE ON CLUSTER c TO joe

## Database

query B
SELECT has_database_privilege('joe', 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege('child', 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
false

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
false

statement ok
GRANT USAGE on DATABASE d TO joe

query TT rowsort
SELECT name, privilege FROM database_privileges WHERE name = 'd'
----
d  joe=U/materialize
d  materialize=UC/materialize
d  mz_support=U/materialize

query B
SELECT has_database_privilege('joe', 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege('child', 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
true

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
true

### Duplicate grants have no effect
statement ok
GRANT USAGE on DATABASE d TO joe

query TT rowsort
SELECT name, privilege FROM database_privileges WHERE name = 'd'
----
d  joe=U/materialize
d  materialize=UC/materialize
d  mz_support=U/materialize

statement ok
GRANT USAGE, CREATE on DATABASE d TO PUBLIC

query TT rowsort
SELECT name, privilege FROM database_privileges WHERE name = 'd'
----
d  =UC/materialize
d  joe=U/materialize
d  materialize=UC/materialize
d  mz_support=U/materialize

simple conn=joe9,user=joe
GRANT USAGE on DATABASE d TO other
----
db error: ERROR: must be owner of DATABASE d

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE USAGE on DATABASE d FROM joe

query TT rowsort
SELECT name, privilege FROM database_privileges WHERE name = 'd'
----
d  =UC/materialize
d  materialize=UC/materialize
d  mz_support=U/materialize

query B
SELECT has_database_privilege('joe', 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege('child', 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
true

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE USAGE on DATABASE d FROM joe

query TT rowsort
SELECT name, privilege FROM database_privileges WHERE name = 'd'
----
d  =UC/materialize
d  materialize=UC/materialize
d  mz_support=U/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE CREATE, USAGE ON DATABASE d FROM PUBLIC

query TT rowsort
SELECT name, privilege FROM database_privileges WHERE name = 'd'
----
d  materialize=UC/materialize
d  mz_support=U/materialize

query B
SELECT has_database_privilege('joe', 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege('child', 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege('joe', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
false

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
false

statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE for DATABASE
GRANT INSERT, SELECT, UPDATE, DELETE ON DATABASE d TO joe

## Schema

query B
SELECT has_schema_privilege('joe', 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege('child', 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
false

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
false

statement ok
GRANT USAGE on SCHEMA sch TO joe

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  joe=U/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

query B
SELECT has_schema_privilege('joe', 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege('child', 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
true

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
true

### Duplicate grants have no effect
statement ok
GRANT USAGE on SCHEMA sch TO joe

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  joe=U/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
GRANT USAGE, CREATE on SCHEMA sch TO PUBLIC

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  =UC/materialize
sch  joe=U/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

simple conn=joe10,user=joe
GRANT USAGE on SCHEMA sch TO other
----
db error: ERROR: must be owner of SCHEMA materialize.sch

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

statement ok
REVOKE USAGE on SCHEMA sch FROM joe

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  =UC/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

query B
SELECT has_schema_privilege('joe', 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege('child', 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
true

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
true

### Duplicate revokes have no effect
statement ok
REVOKE USAGE on SCHEMA sch FROM joe

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  =UC/materialize
sch  materialize=UC/materialize
sch  mz_support=U/materialize

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

statement ok
REVOKE CREATE, USAGE ON SCHEMA sch FROM PUBLIC

query TT rowsort
SELECT name, privilege FROM schema_privileges WHERE name = 'sch'
----
sch  materialize=UC/materialize
sch  mz_support=U/materialize

query B
SELECT has_schema_privilege('joe', 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege('child', 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege('joe', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
false

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
false

## System

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

query B
SELECT has_system_privilege('joe', 'CREATEDB')
----
false

query B
SELECT has_system_privilege('child', 'CREATEDB')
----
false

query B
SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB')
----
false

simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO joe
----
COMPLETE 0

query T
SELECT privileges::text FROM mz_system_privileges
----
joe=B/mz_system
mz_system=RBNP/mz_system

query B
SELECT has_system_privilege('joe', 'CREATEDB')
----
true

query B
SELECT has_system_privilege('child', 'CREATEDB')
----
true

query B
SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB')
----
true

### Duplicate grants have no effect
simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO joe
----
COMPLETE 0

query T
SELECT privileges::text FROM mz_system_privileges
----
joe=B/mz_system
mz_system=RBNP/mz_system

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

query T
SELECT privileges::text FROM mz_system_privileges
----
=RN/mz_system
joe=B/mz_system
mz_system=RBNP/mz_system

statement error role "joe" cannot be dropped because some objects depend on it
DROP ROLE joe

simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM joe
----
COMPLETE 0

query T
SELECT privileges::text FROM mz_system_privileges
----
=RN/mz_system
mz_system=RBNP/mz_system

query B
SELECT has_system_privilege('joe', 'CREATEDB')
----
false

query B
SELECT has_system_privilege('child', 'CREATEDB')
----
false

query B
SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB')
----
false

### Duplicate revokes have no effect
simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM joe
----
COMPLETE 0

query T
SELECT privileges::text FROM mz_system_privileges
----
=RN/mz_system
mz_system=RBNP/mz_system

statement ok
DROP ROLE joe

statement ok
CREATE ROLE joe

statement ok
GRANT joe TO child

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

query T
SELECT privileges::text FROM mz_system_privileges
----
mz_system=RBNP/mz_system

query B
SELECT has_system_privilege('joe', 'CREATEDB')
----
false

query B
SELECT has_system_privilege('child', 'CREATEDB')
----
false

query B
SELECT has_system_privilege((SELECT oid FROM mz_roles WHERE name = 'joe'), 'CREATEDB')
----
false

## Test misc error scenarios

statement error invalid privilege types SELECT, INSERT, UPDATE, DELETE for SCHEMA
GRANT INSERT, SELECT, UPDATE, DELETE ON SCHEMA sch TO joe

simple conn=mz_system,user=mz_system
GRANT INSERT ON TABLE mz_views TO joe
----
db error: ERROR: system item 'mz_catalog.mz_views' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE INSERT ON TABLE mz_views FROM joe
----
db error: ERROR: system item 'mz_catalog.mz_views' cannot be modified

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE mz_objects TO joe
----
db error: ERROR: system item 'mz_catalog.mz_objects' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE mz_objects FROM joe
----
db error: ERROR: system item 'mz_catalog.mz_objects' cannot be modified

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE mz_internal.mz_sink_statuses TO joe
----
db error: ERROR: system item 'mz_internal.mz_sink_statuses' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE mz_internal.mz_sink_statuses FROM joe
----
db error: ERROR: system item 'mz_internal.mz_sink_statuses' cannot be modified

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE bool TO joe
----
db error: ERROR: system item 'pg_catalog.bool' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE bool FROM joe
----
db error: ERROR: system item 'pg_catalog.bool' cannot be modified

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER mz_system TO joe
----
db error: ERROR: system cluster 'mz_system' cannot be modified

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER mz_catalog_server TO joe
----
db error: ERROR: system cluster 'mz_catalog_server' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER mz_system FROM joe
----
db error: ERROR: system cluster 'mz_system' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE USAGE ON CLUSTER mz_catalog_server FROM joe
----
db error: ERROR: system cluster 'mz_catalog_server' cannot be modified

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA pg_catalog TO joe
----
db error: ERROR: system schema 'pg_catalog' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE CREATE ON SCHEMA pg_catalog FROM joe
----
db error: ERROR: system schema 'pg_catalog' cannot be modified

statement error unknown database 't'
GRANT SELECT ON DATABASE t TO joe

statement error invalid privilege types USAGE for TABLE
GRANT SELECT, USAGE ON TABLE t TO joe

statement error invalid privilege types CREATEROLE for TABLE "materialize.public.t"
GRANT CREATEROLE ON TABLE t TO test_role

statement error  invalid privilege types CREATEDB for CLUSTER "c"
REVOKE CREATEDB ON CLUSTER c FROM test_role

## Test multiple roles

simple conn=mz_system,user=mz_system
CREATE TABLE t1 (a INT);
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  mz_system=arwd/mz_system

simple conn=mz_system,user=mz_system
GRANT SELECT ON t1 TO joe, other
----
COMPLETE 0

query TT rowsort
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  joe=r/mz_system
t1  other=r/mz_system
t1  mz_system=arwd/mz_system

simple conn=mz_system,user=mz_system
GRANT SELECT, INSERT ON t1 TO test_role, other
----
COMPLETE 0

query TT rowsort
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  joe=r/mz_system
t1  other=ar/mz_system
t1  test_role=ar/mz_system
t1  mz_system=arwd/mz_system

simple conn=mz_system,user=mz_system
REVOKE INSERT ON t1 FROM joe, test_role, other
----
COMPLETE 0

query TT rowsort
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  joe=r/mz_system
t1  other=r/mz_system
t1  test_role=r/mz_system
t1  mz_system=arwd/mz_system

simple conn=mz_system,user=mz_system
REVOKE SELECT ON t1 FROM joe, test_role, other
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  mz_system=arwd/mz_system

## Test ALL keyword

simple conn=mz_system,user=mz_system
GRANT ALL ON t1 TO joe, test_role
----
COMPLETE 0

query TT rowsort
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  joe=arwd/mz_system
t1  mz_system=arwd/mz_system
t1  test_role=arwd/mz_system

simple conn=mz_system,user=mz_system
REVOKE ALL ON t1 FROM joe, test_role, other
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 't1'
----
t1  mz_system=arwd/mz_system

simple conn=mz_system,user=mz_system
CREATE VIEW v1 AS SELECT 1;
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v1'
----
v1  mz_system=r/mz_system

simple conn=mz_system,user=mz_system
GRANT ALL ON v1 TO joe
----
COMPLETE 0

query TT rowsort
SELECT name, privilege FROM item_privileges WHERE name = 'v1'
----
v1  joe=r/mz_system
v1  mz_system=r/mz_system

simple conn=mz_system,user=mz_system
REVOKE ALL ON v1 FROM joe
----
COMPLETE 0

query TT
SELECT name, privilege FROM item_privileges WHERE name = 'v1'
----
v1  mz_system=r/mz_system

simple conn=mz_system,user=mz_system
GRANT ALL ON SYSTEM TO joe
----
COMPLETE 0

query T
SELECT privileges::text FROM mz_system_privileges
----
joe=RBNP/mz_system
mz_system=RBNP/mz_system

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

query T
SELECT privileges::text FROM mz_system_privileges
----
mz_system=RBNP/mz_system

## Test system objects

simple conn=mz_system,user=mz_system
GRANT SELECT on v1 TO mz_catalog_server
----
db error: ERROR: unknown role 'mz_catalog_server'

simple conn=mz_system,user=mz_system
GRANT SELECT on v1 TO mz_support
----
db error: ERROR: role name "mz_support" is reserved
DETAIL: The role prefixes "mz_" and "pg_" are reserved for system roles.

simple conn=mz_system,user=mz_system
REVOKE SELECT on v1 FROM mz_support
----
db error: ERROR: role name "mz_support" is reserved
DETAIL: The role prefixes "mz_" and "pg_" are reserved for system roles.

simple conn=mz_system,user=mz_system
GRANT INSERT on mz_tables TO joe
----
db error: ERROR: system item 'mz_catalog.mz_tables' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE SELECT on mz_tables FROM joe
----
db error: ERROR: system item 'mz_catalog.mz_tables' cannot be modified

simple conn=mz_system,user=mz_system
GRANT USAGE on CLUSTER mz_system TO JOE
----
db error: ERROR: system cluster 'mz_system' cannot be modified

simple conn=mz_system,user=mz_system
REVOKE USAGE on CLUSTER mz_catalog_server FROM PUBLIC
----
db error: ERROR: system cluster 'mz_catalog_server' cannot be modified

# Test has_X_privilege error and misc scenarios.

## If any input is NULL then the result is NULL.

### System

query B
SELECT has_system_privilege(NULL, 'CREATEROLE')
----
NULL

query B
SELECT has_system_privilege('joe', NULL)
----
NULL

### Cluster

query B
SELECT has_cluster_privilege(NULL, 'c', 'USAGE')
----
NULL

query B
SELECT has_cluster_privilege('joe', NULL, 'USAGE')
----
NULL

query B
SELECT has_cluster_privilege('joe', 'c', NULL)
----
NULL

### Connection

query B
SELECT has_connection_privilege(NULL, 'conn', 'USAGE')
----
NULL

query B
SELECT has_connection_privilege('joe', NULL, 'USAGE')
----
NULL

query B
SELECT has_connection_privilege('joe', 'conn', NULL)
----
NULL

### Database

query B
SELECT has_database_privilege(NULL, 'd', 'USAGE')
----
NULL

query B
SELECT has_database_privilege('joe', NULL, 'USAGE')
----
NULL

query B
SELECT has_database_privilege('joe', 'd', NULL)
----
NULL

### Schema

query B
SELECT has_schema_privilege(NULL, 'sch', 'USAGE')
----
NULL

query B
SELECT has_schema_privilege('joe', NULL, 'USAGE')
----
NULL

query B
SELECT has_schema_privilege('joe', 'sch', NULL)
----
NULL

### Secret

query B
SELECT has_secret_privilege(NULL, 'se', 'USAGE')
----
NULL

query B
SELECT has_secret_privilege('joe', NULL, 'USAGE')
----
NULL

query B
SELECT has_secret_privilege('joe', 'se', NULL)
----
NULL

### Table

query B
SELECT has_table_privilege(NULL, 't', 'SELECT')
----
NULL

query B
SELECT has_table_privilege('joe', NULL, 'SELECT')
----
NULL

query B
SELECT has_table_privilege('joe', 't', NULL)
----
NULL

### Type

query B
SELECT has_type_privilege(NULL, 'ty', 'USAGE')
----
NULL

query B
SELECT has_type_privilege('joe', NULL, 'USAGE')
----
NULL

query B
SELECT has_type_privilege('joe', 'ty', NULL)
----
NULL

## If any of the text inputs are invalid then the query should error.

### System

query error role "fake_role" does not exist
SELECT has_system_privilege('fake_role', 'CREATEDB')

query error unrecognized privilege type: "fake privilege"
SELECT has_system_privilege('joe', 'fake privilege')

### Cluster

query error role "fake_role" does not exist
SELECT has_cluster_privilege('fake_role', 'c', 'USAGE')

query error cluster "fake_cluster" does not exist
SELECT has_cluster_privilege('joe', 'fake_cluster', 'USAGE')

query error unrecognized privilege type: "fake privilege"
SELECT has_cluster_privilege('joe', 'c', 'fake privilege')

### Connection

query error role "fake_role" does not exist
SELECT has_connection_privilege('fake_role', 'conn', 'USAGE')

query error db error: ERROR: connection "fake_connection" does not exist
SELECT has_connection_privilege('joe', 'fake_connection', 'USAGE')

query error unrecognized privilege type: "fake privilege"
SELECT has_connection_privilege('joe', 'conn', 'fake privilege')

### Database

query error role "fake_role" does not exist
SELECT has_database_privilege('fake_role', 'd', 'USAGE')

query error database "fake_database" does not exist
SELECT has_database_privilege('joe', 'fake_database', 'USAGE')

query error unrecognized privilege type: "fake privilege"
SELECT has_database_privilege('joe', 'd', 'fake privilege')

### Schema

query error role "fake_role" does not exist
SELECT has_schema_privilege('fake_role', 'sch', 'USAGE')

query error schema "fake_schema" does not exist
SELECT has_schema_privilege('joe', 'fake_schema', 'USAGE')

query error unrecognized privilege type: "fake privilege"
SELECT has_schema_privilege('joe', 'sch', 'fake privilege')

### Secret

query error role "fake_role" does not exist
SELECT has_secret_privilege('fake_role', 'se', 'USAGE')

query error db error: ERROR: secret "fake_secret" does not exist
SELECT has_secret_privilege('joe', 'fake_secret', 'USAGE')

query error unrecognized privilege type: "fake privilege"
SELECT has_secret_privilege('joe', 'se', 'fake privilege')

### Table

query error role "fake_role" does not exist
SELECT has_table_privilege('fake_role', 't', 'SELECT')

query error db error: ERROR: relation "fake_table" does not exist
SELECT has_table_privilege('joe', 'fake_table', 'SELECT')

query error unrecognized privilege type: "fake privilege"
SELECT has_table_privilege('joe', 't', 'fake privilege')

### Type

query error role "fake_role" does not exist
SELECT has_type_privilege('fake_role', 'ty', 'USAGE')

query error db error: ERROR: type "fake_type" does not exist
SELECT has_type_privilege('joe', 'fake_type', 'USAGE')

query error unrecognized privilege type: "fake privilege"
SELECT has_type_privilege('joe', 'ty', 'fake privilege')

## If any of the oid inputs are invalid then the query should be NULL.

### System

query B
SELECT has_system_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'CREATECLUSTER')
----
NULL

### Cluster

query B
SELECT has_cluster_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'c', 'USAGE')
----
NULL

### Connection

query B
SELECT has_connection_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'conn', 'USAGE')
----
NULL

query B
SELECT has_connection_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE')
----
NULL

### Database

query B
SELECT has_database_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'd', 'USAGE')
----
NULL

query B
SELECT has_database_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE')
----
NULL

### Schema

query B
SELECT has_schema_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'sch', 'USAGE')
----
NULL

query B
SELECT has_schema_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE')
----
NULL

### Secret

query B
SELECT has_secret_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'se', 'USAGE')
----
NULL

query B
SELECT has_secret_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE')
----
NULL

### Table

query B
SELECT has_table_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 't', 'SELECT')
----
NULL

query B
SELECT has_table_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'SELECT')
----
NULL

### Type

query B
SELECT has_type_privilege(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'ty', 'USAGE')
----
NULL

query B
SELECT has_type_privilege('joe', ((SELECT MAX(oid::int8) FROM mz_objects) + 1)::text::oid, 'USAGE')
----
NULL

## The function should return true if ANY of the privileges exist, not all.

### System

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

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

query B
SELECT has_system_privilege('joe', 'CREATEROLE, CREATECLUSTER')
----
true

### Cluster

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON CLUSTER c FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER c TO joe
----
COMPLETE 0

query B
SELECT has_cluster_privilege('joe', 'c', 'USAGE, CREATE')
----
true

### Connection

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON CONNECTION conn FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON CONNECTION conn TO joe
----
COMPLETE 0

query B
SELECT has_connection_privilege('joe', 'conn', 'USAGE, CREATE')
----
true

### Database

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON DATABASE d FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON DATABASE d TO joe
----
COMPLETE 0

query B
SELECT has_database_privilege('joe', 'd', 'USAGE, CREATE')
----
true

### Schema

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON SCHEMA sch FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA sch TO joe
----
COMPLETE 0

query B
SELECT has_schema_privilege('joe', 'sch', 'USAGE, CREATE')
----
true

### Secret

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON SECRET se FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON SECRET se TO joe
----
COMPLETE 0

query B
SELECT has_secret_privilege('joe', 'se', 'USAGE, CREATE')
----
true

### Table

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON t FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT SELECT ON t TO joe
----
COMPLETE 0

query B
SELECT has_table_privilege('joe', 't', 'SELECT, INSERT')
----
true

### Type

simple conn=mz_system,user=mz_system
REVOKE ALL PRIVILEGES ON TYPE ty FROM joe, materialize;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO joe
----
COMPLETE 0

query B
SELECT has_type_privilege('joe', 'ty', 'USAGE, CREATE')
----
true

## Test two/one input variants.

### System

simple conn=mz_system,user=mz_system
REVOKE CREATEDB ON SYSTEM FROM materialize
----
COMPLETE 0

query B
SELECT has_system_privilege('materialize', 'CREATEDB')
----
false

query B
SELECT has_system_privilege('CREATEDB')
----
false

simple conn=mz_system,user=mz_system
GRANT CREATEDB ON SYSTEM TO materialize
----
COMPLETE 0

query B
SELECT has_system_privilege('materialize', 'CREATEDB')
----
true

query B
SELECT has_system_privilege('CREATEDB')
----
true

### Cluster

query B
SELECT has_cluster_privilege('materialize', 'c', 'USAGE')
----
false

query B
SELECT has_cluster_privilege('c', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON CLUSTER c TO materialize
----
COMPLETE 0

query B
SELECT has_cluster_privilege('materialize', 'c', 'USAGE')
----
true

query B
SELECT has_cluster_privilege('c', 'USAGE')
----
true

### Connection

query B
SELECT has_connection_privilege('materialize', 'conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn', 'USAGE')
----
false

query B
SELECT has_connection_privilege('materialize', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
false

query B
SELECT has_connection_privilege((SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON CONNECTION conn TO materialize
----
COMPLETE 0

query B
SELECT has_connection_privilege('materialize', 'conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn', 'USAGE')
----
true

query B
SELECT has_connection_privilege('materialize', (SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
true

query B
SELECT has_connection_privilege((SELECT oid FROM mz_connections WHERE name = 'conn'), 'USAGE')
----
true

### Database

query B
SELECT has_database_privilege('materialize', 'd', 'USAGE')
----
false

query B
SELECT has_database_privilege('d', 'USAGE')
----
false

query B
SELECT has_database_privilege('materialize', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
false

query B
SELECT has_database_privilege((SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON DATABASE d TO materialize
----
COMPLETE 0

query B
SELECT has_database_privilege('materialize', 'd', 'USAGE')
----
true

query B
SELECT has_database_privilege('d', 'USAGE')
----
true

query B
SELECT has_database_privilege('materialize', (SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
true

query B
SELECT has_database_privilege((SELECT oid FROM mz_databases WHERE name = 'd'), 'USAGE')
----
true

### Schema

query B
SELECT has_schema_privilege('materialize', 'sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege('sch', 'USAGE')
----
false

query B
SELECT has_schema_privilege('materialize', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
false

query B
SELECT has_schema_privilege((SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON SCHEMA sch TO materialize
----
COMPLETE 0

query B
SELECT has_schema_privilege('materialize', 'sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege('sch', 'USAGE')
----
true

query B
SELECT has_schema_privilege('materialize', (SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
true

query B
SELECT has_schema_privilege((SELECT oid FROM mz_schemas WHERE name = 'sch'), 'USAGE')
----
true

### Secret

query B
SELECT has_secret_privilege('materialize', 'se', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se', 'USAGE')
----
false

query B
SELECT has_secret_privilege('materialize', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
false

query B
SELECT has_secret_privilege((SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON SECRET se TO materialize
----
COMPLETE 0

query B
SELECT has_secret_privilege('materialize', 'se', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se', 'USAGE')
----
true

query B
SELECT has_secret_privilege('materialize', (SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
true

query B
SELECT has_secret_privilege((SELECT oid FROM mz_secrets WHERE name = 'se'), 'USAGE')
----
true

### Table

query B
SELECT has_table_privilege('materialize', 't', 'SELECT')
----
false

query B
SELECT has_table_privilege('t', 'SELECT')
----
false

query B
SELECT has_table_privilege('materialize', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
false

query B
SELECT has_table_privilege((SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
false

simple conn=mz_system,user=mz_system
GRANT SELECT ON t TO materialize
----
COMPLETE 0

query B
SELECT has_table_privilege('materialize', 't', 'SELECT')
----
true

query B
SELECT has_table_privilege('t', 'SELECT')
----
true

query B
SELECT has_table_privilege('materialize', (SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
true

query B
SELECT has_table_privilege((SELECT oid FROM mz_tables WHERE name = 't'), 'SELECT')
----
true

### Type

simple conn=mz_system,user=mz_system
REVOKE USAGE ON TYPE ty FROM PUBLIC
----
COMPLETE 0

query B
SELECT has_type_privilege('materialize', 'ty', 'USAGE')
----
false

query B
SELECT has_type_privilege('ty', 'USAGE')
----
false

query B
SELECT has_type_privilege('materialize', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
false

query B
SELECT has_type_privilege((SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO materialize
----
COMPLETE 0

query B
SELECT has_type_privilege('materialize', 'ty', 'USAGE')
----
true

query B
SELECT has_type_privilege('ty', 'USAGE')
----
true

query B
SELECT has_type_privilege('materialize', (SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
true

query B
SELECT has_type_privilege((SELECT oid FROM mz_types WHERE name = 'ty'), 'USAGE')
----
true

simple conn=mz_system,user=mz_system
GRANT USAGE ON TYPE ty TO PUBLIC
----
COMPLETE 0

# Test multi-object GRANT/REVOKE

simple conn=mz_system,user=mz_system
DROP TABLE t1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t1 ();
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TABLE t2 ();
----
COMPLETE 0

query B
SELECT has_table_privilege('t1', 'SELECT')
----
false

query B
SELECT has_table_privilege('t2', 'SELECT')
----
false

simple conn=mz_system,user=mz_system
GRANT SELECT ON TABLE t1, t2 TO materialize
----
COMPLETE 0

query B
SELECT has_table_privilege('t1', 'SELECT')
----
true

query B
SELECT has_table_privilege('t2', 'SELECT')
----
true

simple conn=mz_system,user=mz_system
REVOKE SELECT ON TABLE t1, t2 FROM materialize
----
COMPLETE 0

query B
SELECT has_table_privilege('t1', 'SELECT')
----
false

query B
SELECT has_table_privilege('t2', 'SELECT')
----
false

# Test ALL SCHEMA object GRANT/REVOKE

simple conn=mz_system,user=mz_system
CREATE DATABASE d1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE SCHEMA d1.s1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE DATABASE d2;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE SCHEMA d2.s2;
----
COMPLETE 0

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

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

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

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

simple conn=mz_system,user=mz_system
CREATE TYPE d1.s1.ty1 AS LIST (ELEMENT TYPE=bool);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TYPE d1.s1.ty2 AS LIST (ELEMENT TYPE=bool);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TYPE d2.s2.ty3 AS LIST (ELEMENT TYPE=bool);
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE TYPE d2.s2.ty4 AS LIST (ELEMENT TYPE=bool);
----
COMPLETE 0

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

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

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

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

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

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

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

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

## Tables

query error db error: ERROR: relation "t3" does not exist
SELECT has_table_privilege('t3', 'SELECT')

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
false

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
false

simple conn=mz_system,user=mz_system
GRANT SELECT ON ALL TABLES IN SCHEMA d1.s1, d2.s2 TO materialize;
----
COMPLETE 0

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
true

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
true

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
true

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
true

simple conn=mz_system,user=mz_system
REVOKE SELECT ON ALL TABLES IN SCHEMA d1.s1, d2.s2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
false

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
false

## Types

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL TYPES FROM PUBLIC
----
COMPLETE 0

query error db error: ERROR: type "ty1" does not exist
SELECT has_type_privilege('ty1', 'USAGE')

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
false

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL TYPES IN SCHEMA d1.s1, d2.s2 TO materialize;
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
true

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
true

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
true

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL TYPES IN SCHEMA d1.s1, d2.s2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
false

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL TYPES TO PUBLIC
----
COMPLETE 0

## Secrets

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL SECRETS IN SCHEMA d1.s1, d2.s2 TO materialize;
----
COMPLETE 0

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL SECRETS IN SCHEMA d1.s1, d2.s2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
false

## Connections

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL CONNECTIONS IN SCHEMA d1.s1, d2.s2 TO materialize;
----
COMPLETE 0

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL CONNECTIONS IN SCHEMA d1.s1, d2.s2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
false

# Test ALL DATABASE object GRANT/REVOKE

## Tables

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
false

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
false

simple conn=mz_system,user=mz_system
GRANT SELECT ON ALL TABLES IN DATABASE d1, d2 TO materialize;
----
COMPLETE 0

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
true

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
true

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
true

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
true

simple conn=mz_system,user=mz_system
REVOKE SELECT ON ALL TABLES IN DATABASE d1, d2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
false

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
false

## Types

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL TYPES FROM PUBLIC
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
false

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL TYPES IN DATABASE d1, d2 TO materialize;
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
true

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
true

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
true

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL TYPES IN DATABASE d1, d2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
false

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL TYPES TO PUBLIC
----
COMPLETE 0

## Secrets

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL SECRETS IN DATABASE d1, d2 TO materialize;
----
COMPLETE 0

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL SECRETS IN DATABASE d1, d2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
false

## Connections

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL CONNECTIONS IN DATABASE d1, d2 TO materialize;
----
COMPLETE 0

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL CONNECTIONS IN DATABASE d1, d2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
false

## Schemas

query B
SELECT has_schema_privilege('d1.s1', 'CREATE')
----
false

query B
SELECT has_schema_privilege('d2.s2', 'CREATE')
----
false

simple conn=mz_system,user=mz_system
GRANT CREATE ON ALL SCHEMAS IN DATABASE d1, d2 TO materialize;
----
COMPLETE 0

query B
SELECT has_schema_privilege('d1.s1', 'CREATE')
----
true

query B
SELECT has_schema_privilege('d2.s2', 'CREATE')
----
true

simple conn=mz_system,user=mz_system
REVOKE CREATE ON ALL SCHEMAS IN DATABASE d1, d2 FROM materialize;
----
COMPLETE 0

query B
SELECT has_schema_privilege('d1.s1', 'CREATE')
----
false

query B
SELECT has_schema_privilege('d2.s2', 'CREATE')
----
false

# Test ALL object GRANT/REVOKE

## Tables

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
false

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
false

simple conn=mz_system,user=mz_system
GRANT SELECT ON ALL TABLES TO materialize;
----
COMPLETE 0

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
true

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
true

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
true

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
true

simple conn=mz_system,user=mz_system
REVOKE SELECT ON ALL TABLES FROM materialize;
----
COMPLETE 0

query B
SELECT has_table_privilege('d1.s1.t3', 'SELECT')
----
false

query B
SELECT has_table_privilege('d1.s1.t4', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t5', 'SELECT')
----
false

query B
SELECT has_table_privilege('d2.s2.t6', 'SELECT')
----
false

## Types

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL TYPES FROM PUBLIC
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
false

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL TYPES TO materialize;
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
true

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
true

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
true

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL TYPES FROM materialize;
----
COMPLETE 0

query B
SELECT has_type_privilege('d1.s1.ty1', 'USAGE')
----
false

query B
SELECT has_type_privilege('d1.s1.ty2', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty3', 'USAGE')
----
false

query B
SELECT has_type_privilege('d2.s2.ty4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL TYPES TO PUBLIC
----
COMPLETE 0

## Secrets

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL SECRETS TO materialize;
----
COMPLETE 0

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
true

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL SECRETS FROM materialize;
----
COMPLETE 0

query B
SELECT has_secret_privilege('se1', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se2', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se3', 'USAGE')
----
false

query B
SELECT has_secret_privilege('se4', 'USAGE')
----
false

## Connections

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
false

simple conn=mz_system,user=mz_system
GRANT USAGE ON ALL CONNECTIONS TO materialize;
----
COMPLETE 0

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
true

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
true

simple conn=mz_system,user=mz_system
REVOKE USAGE ON ALL CONNECTIONS FROM materialize;
----
COMPLETE 0

query B
SELECT has_connection_privilege('conn1', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn2', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn3', 'USAGE')
----
false

query B
SELECT has_connection_privilege('conn4', 'USAGE')
----
false

## Schemas

query B
SELECT has_schema_privilege('d1.s1', 'CREATE')
----
false

query B
SELECT has_schema_privilege('d2.s2', 'CREATE')
----
false

simple conn=mz_system,user=mz_system
GRANT CREATE ON ALL SCHEMAS TO materialize;
----
COMPLETE 0

query B
SELECT has_schema_privilege('d1.s1', 'CREATE')
----
true

query B
SELECT has_schema_privilege('d2.s2', 'CREATE')
----
true

simple conn=mz_system,user=mz_system
REVOKE CREATE ON ALL SCHEMAS FROM materialize;
----
COMPLETE 0

query B
SELECT has_schema_privilege('d1.s1', 'CREATE')
----
false

query B
SELECT has_schema_privilege('d2.s2', 'CREATE')
----
false

## Databases

query B
SELECT has_database_privilege('d1', 'CREATE')
----
false

query B
SELECT has_database_privilege('d2', 'CREATE')
----
false

simple conn=mz_system,user=mz_system
GRANT CREATE ON ALL DATABASES TO materialize;
----
COMPLETE 0

query B
SELECT has_database_privilege('d1', 'CREATE')
----
true

query B
SELECT has_database_privilege('d2', 'CREATE')
----
true

simple conn=mz_system,user=mz_system
REVOKE CREATE ON ALL DATABASES FROM materialize;
----
COMPLETE 0

query B
SELECT has_database_privilege('d1', 'CREATE')
----
false

query B
SELECT has_database_privilege('d2', 'CREATE')
----
false

## Clusters

simple conn=mz_system,user=mz_system
CREATE CLUSTER c1 REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

simple conn=mz_system,user=mz_system
CREATE CLUSTER c2 REPLICAS (r1 (SIZE '1'));
----
COMPLETE 0

query B
SELECT has_cluster_privilege('c1', 'CREATE')
----
false

query B
SELECT has_cluster_privilege('c2', 'CREATE')
----
false

simple conn=mz_system,user=mz_system
GRANT CREATE ON ALL CLUSTERS TO materialize;
----
COMPLETE 0

query B
SELECT has_cluster_privilege('c1', 'CREATE')
----
true

query B
SELECT has_cluster_privilege('c2', 'CREATE')
----
true

simple conn=mz_system,user=mz_system
REVOKE CREATE ON ALL CLUSTERS FROM materialize;
----
COMPLETE 0

query B
SELECT has_cluster_privilege('c1', 'CREATE')
----
false

query B
SELECT has_cluster_privilege('c2', 'CREATE')
----
false

# Reset server so we can get back to a clean state.
reset-server

# Test table_privileges and role_table_grants

statement ok
CREATE ROLE r1

simple conn=mz_system,user=mz_system
GRANT CREATE ON CLUSTER quickstart TO r1
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT CREATE ON SCHEMA public TO r1
----
COMPLETE 0

statement ok
CREATE ROLE r2

statement ok
CREATE ROLE r3

statement ok
GRANT r2 TO r1

statement ok
CREATE SOURCE s FROM LOAD GENERATOR COUNTER;

statement ok
GRANT SELECT ON s TO r2

statement ok
CREATE TABLE t ()

statement ok
GRANT INSERT ON t TO r1

statement ok
CREATE VIEW v AS SELECT 1

statement ok
GRANT SELECT ON v TO r2, r3

simple conn=r1,user=r1
CREATE MATERIALIZED VIEW mv AS SELECT 1
----
COMPLETE 0

simple conn=r1,user=r1
GRANT SELECT ON mv TO r2
----
COMPLETE 0

# For privileges granted to PUBLIC and mz_system, we don't want to enumerate every single row
# because there is a lot. Instead we just assert that there's a bunch of rows emitted. The number
# of rows was chosen because that was the value at the time the test was written.

## table_privileges

simple conn=r1,user=r1,rowsort
SELECT * FROM information_schema.table_privileges WHERE grantee != 'PUBLIC'
----
r1,r1,materialize,public,mv,SELECT,NO,YES
r1,r2,materialize,public,mv,SELECT,NO,YES
materialize,r1,materialize,public,t,INSERT,NO,NO
materialize,r2,materialize,public,s,SELECT,NO,YES
materialize,r2,materialize,public,v,SELECT,NO,YES
COMPLETE 5

simple conn=r1,user=r1
SELECT COUNT(*) >= 166 FROM information_schema.table_privileges WHERE grantee = 'PUBLIC'
----
t
COMPLETE 1

simple conn=mz_system,user=mz_system,rowsort
SELECT * FROM information_schema.table_privileges WHERE grantee != 'PUBLIC' AND grantee != 'mz_system'
----
r1,r1,materialize,public,mv,SELECT,NO,YES
r1,r2,materialize,public,mv,SELECT,NO,YES
materialize,r1,materialize,public,t,INSERT,NO,NO
materialize,r2,materialize,public,s,SELECT,NO,YES
materialize,r2,materialize,public,v,SELECT,NO,YES
materialize,r3,materialize,public,v,SELECT,NO,YES
materialize,materialize,materialize,public,t,DELETE,NO,NO
materialize,materialize,materialize,public,t,INSERT,NO,NO
materialize,materialize,materialize,public,t,UPDATE,NO,NO
materialize,materialize,materialize,public,s,SELECT,NO,YES
materialize,materialize,materialize,public,t,SELECT,NO,YES
materialize,materialize,materialize,public,v,SELECT,NO,YES
materialize,mz_support,materialize,public,s_progress,SELECT,NO,YES
materialize,materialize,materialize,public,s_progress,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_notices,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_sql_text,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_optimizer_notices,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_activity_log_thinned,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_thinned,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
COMPLETE 49

simple conn=mz_system,user=mz_system
SELECT COUNT(*) >= 166 FROM information_schema.table_privileges WHERE grantee = 'PUBLIC'
----
t
COMPLETE 1

simple conn=mz_system,user=mz_system
SELECT COUNT(*) >= 304 FROM information_schema.table_privileges WHERE grantee = 'mz_system'
----
t
COMPLETE 1

## role_table_grants

simple conn=r1,user=r1,rowsort
SELECT * FROM information_schema.role_table_grants
----
r1,r1,materialize,public,mv,SELECT,NO,YES
r1,r2,materialize,public,mv,SELECT,NO,YES
materialize,r1,materialize,public,t,INSERT,NO,NO
materialize,r2,materialize,public,s,SELECT,NO,YES
materialize,r2,materialize,public,v,SELECT,NO,YES
COMPLETE 5

simple conn=mz_system,user=mz_system,rowsort
SELECT * FROM information_schema.role_table_grants WHERE grantee != 'PUBLIC' AND grantee != 'mz_system'
----
r1,r1,materialize,public,mv,SELECT,NO,YES
r1,r2,materialize,public,mv,SELECT,NO,YES
materialize,r1,materialize,public,t,INSERT,NO,NO
materialize,r2,materialize,public,s,SELECT,NO,YES
materialize,r2,materialize,public,v,SELECT,NO,YES
materialize,r3,materialize,public,v,SELECT,NO,YES
materialize,materialize,materialize,public,t,DELETE,NO,NO
materialize,materialize,materialize,public,t,INSERT,NO,NO
materialize,materialize,materialize,public,t,UPDATE,NO,NO
materialize,materialize,materialize,public,s,SELECT,NO,YES
materialize,materialize,materialize,public,t,SELECT,NO,YES
materialize,materialize,materialize,public,v,SELECT,NO,YES
materialize,mz_support,materialize,public,s_progress,SELECT,NO,YES
materialize,materialize,materialize,public,s_progress,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_notices,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_sql_text,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_optimizer_notices,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_activity_log_thinned,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_notices_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_thinned,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_sql_text_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_prepared_statement_history,SELECT,NO,YES
mz_system,mz_monitor,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
mz_system,mz_support,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_lifecycle_history,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_recent_activity_log_redacted,SELECT,NO,YES
mz_system,mz_analytics,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
mz_system,mz_monitor_redacted,materialize,mz_internal,mz_statement_execution_history_redacted,SELECT,NO,YES
COMPLETE 49

simple conn=mz_system,user=mz_system
SELECT COUNT(*) >= 166 FROM information_schema.role_table_grants WHERE grantee = 'PUBLIC'
----
t
COMPLETE 1

simple conn=mz_system,user=mz_system
SELECT COUNT(*) >= 304 FROM information_schema.role_table_grants WHERE grantee = 'mz_system'
----
t
COMPLETE 1

# Check that predefined roles can't be altered.

simple conn=mz_system,user=mz_system
GRANT SELECT ON t TO mz_monitor
----
db error: ERROR: role name "mz_monitor" is reserved
DETAIL: The role prefixes "mz_" and "pg_" are reserved for system roles.