Materialize/test/sqllogictest/cockroach/information_schema.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/information_schema
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

# not supported yet
halt

mode cockroach

# Verify information_schema database handles mutation statements correctly.

query error database "information_schema" does not exist
ALTER DATABASE information_schema RENAME TO not_information_schema

statement error schema cannot be modified: "information_schema"
CREATE TABLE information_schema.t (x INT)

query error database "information_schema" does not exist
DROP DATABASE information_schema

query T
SHOW TABLES FROM information_schema
----
administrable_role_authorizations
applicable_roles
character_sets
column_privileges
columns
constraint_column_usage
enabled_roles
key_column_usage
parameters
referential_constraints
role_table_grants
routines
schema_privileges
schemata
sequences
statistics
table_constraints
table_privileges
tables
user_privileges
views

# Verify that the name is not special for databases.

statement ok
CREATE DATABASE other_db

statement ok
ALTER DATABASE other_db RENAME TO information_schema

statement error database "information_schema" already exists
CREATE DATABASE information_schema

statement ok
DROP DATABASE information_schema CASCADE


# Verify information_schema tables handle mutation statements correctly.

statement error user root does not have DROP privilege on relation tables
ALTER TABLE information_schema.tables RENAME TO information_schema.bad

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables RENAME COLUMN x TO y

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables ADD COLUMN x DECIMAL

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables DROP COLUMN x

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables ADD CONSTRAINT foo UNIQUE (b)

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables DROP CONSTRAINT bar

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables ALTER COLUMN x SET DEFAULT 'foo'

statement error user root does not have CREATE privilege on relation tables
ALTER TABLE information_schema.tables ALTER x DROP NOT NULL

statement error user root does not have CREATE privilege on relation tables
CREATE INDEX i on information_schema.tables (x)

statement error user root does not have DROP privilege on relation tables
DROP TABLE information_schema.tables

statement error user root does not have CREATE privilege on relation tables
DROP INDEX information_schema.tables@i

statement error user root does not have GRANT privilege on relation tables
GRANT CREATE ON information_schema.tables TO root

statement error user root does not have GRANT privilege on relation tables
REVOKE CREATE ON information_schema.tables FROM root


# Verify information_schema tables handles read-only property correctly.

query error user root does not have DELETE privilege on relation tables
DELETE FROM information_schema.tables

query error user root does not have INSERT privilege on relation tables
INSERT INTO information_schema.tables VALUES ('abc')

statement error user root does not have UPDATE privilege on relation tables
UPDATE information_schema.tables SET a = 'abc'

statement error user root does not have DROP privilege on relation tables
TRUNCATE TABLE information_schema.tables


# Verify information_schema handles reflection correctly.

query T
SHOW DATABASES
----
materialize
postgres
system
test

query T
SHOW TABLES FROM test.information_schema
----
administrable_role_authorizations
applicable_roles
character_sets
column_privileges
columns
constraint_column_usage
enabled_roles
key_column_usage
parameters
referential_constraints
role_table_grants
routines
schema_privileges
schemata
sequences
statistics
table_constraints
table_privileges
tables
user_privileges
views

query TT colnames
SHOW CREATE TABLE information_schema.tables
----
table_name                 create_statement
information_schema.tables  CREATE TABLE tables (
                           table_catalog STRING NOT NULL,
                           table_schema STRING NOT NULL,
                           table_name STRING NOT NULL,
                           table_type STRING NOT NULL,
                           is_insertable_into STRING NOT NULL,
                           version INT8 NULL
)

query TTBTTTB colnames
SHOW COLUMNS FROM information_schema.tables
----
column_name         data_type  is_nullable  column_default  generation_expression  indices  is_hidden
table_catalog       STRING     false        NULL            ·                      {}       false
table_schema        STRING     false        NULL            ·                      {}       false
table_name          STRING     false        NULL            ·                      {}       false
table_type          STRING     false        NULL            ·                      {}       false
is_insertable_into  STRING     false        NULL            ·                      {}       false
version             INT8       true         NULL            ·                      {}       false

query TTBITTBB colnames
SHOW INDEXES ON information_schema.tables
----
table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit

query TTTTB colnames
SHOW CONSTRAINTS FROM information_schema.tables
----
table_name  constraint_name  constraint_type  details  validated

query TTTTT colnames
SHOW GRANTS ON information_schema.tables
----
database_name  schema_name         table_name  grantee  privilege_type
test           information_schema  tables      public   SELECT


# Verify selecting from information_schema.

## information_schema.schemata

query TTTT colnames
SELECT * FROM information_schema.schemata
----
catalog_name  schema_name         default_character_set_name  sql_path
test          crdb_internal       NULL                        NULL
test          information_schema  NULL                        NULL
test          pg_catalog          NULL                        NULL
test          public              NULL                        NULL

query TTTT colnames
SELECT * FROM INFormaTION_SCHEMa.schemata
----
catalog_name  schema_name         default_character_set_name  sql_path
test          crdb_internal       NULL                        NULL
test          information_schema  NULL                        NULL
test          pg_catalog          NULL                        NULL
test          public              NULL                        NULL

## information_schema.tables

# Check the default contents of information_schema.tables (incl. the
# special system tables)
query TT rowsort
select table_schema, table_name FROM information_schema.tables
----
crdb_internal       backward_dependencies
crdb_internal       builtin_functions
crdb_internal       cluster_queries
crdb_internal       cluster_sessions
crdb_internal       cluster_settings
crdb_internal       create_statements
crdb_internal       feature_usage
crdb_internal       forward_dependencies
crdb_internal       gossip_alerts
crdb_internal       gossip_liveness
crdb_internal       gossip_network
crdb_internal       gossip_nodes
crdb_internal       index_columns
crdb_internal       jobs
crdb_internal       kv_node_status
crdb_internal       kv_store_status
crdb_internal       leases
crdb_internal       node_build_info
crdb_internal       node_metrics
crdb_internal       node_queries
crdb_internal       node_runtime_info
crdb_internal       node_sessions
crdb_internal       node_statement_statistics
crdb_internal       partitions
crdb_internal       predefined_comments
crdb_internal       ranges
crdb_internal       ranges_no_leases
crdb_internal       schema_changes
crdb_internal       session_trace
crdb_internal       session_variables
crdb_internal       table_columns
crdb_internal       table_indexes
crdb_internal       tables
crdb_internal       zones
information_schema  administrable_role_authorizations
information_schema  applicable_roles
information_schema  column_privileges
information_schema  columns
information_schema  constraint_column_usage
information_schema  enabled_roles
information_schema  key_column_usage
information_schema  parameters
information_schema  referential_constraints
information_schema  role_table_grants
information_schema  routines
information_schema  schema_privileges
information_schema  schemata
information_schema  sequences
information_schema  statistics
information_schema  table_constraints
information_schema  table_privileges
information_schema  tables
information_schema  user_privileges
information_schema  views
pg_catalog          pg_am
pg_catalog          pg_attrdef
pg_catalog          pg_attribute
pg_catalog          pg_auth_members
pg_catalog          pg_class
pg_catalog          pg_collation
pg_catalog          pg_constraint
pg_catalog          pg_database
pg_catalog          pg_depend
pg_catalog          pg_description
pg_catalog          pg_enum
pg_catalog          pg_extension
pg_catalog          pg_foreign_data_wrapper
pg_catalog          pg_foreign_server
pg_catalog          pg_foreign_table
pg_catalog          pg_index
pg_catalog          pg_indexes
pg_catalog          pg_inherits
pg_catalog          pg_language
pg_catalog          pg_namespace
pg_catalog          pg_operator
pg_catalog          pg_proc
pg_catalog          pg_range
pg_catalog          pg_rewrite
pg_catalog          pg_roles
pg_catalog          pg_seclabel
pg_catalog          pg_sequence
pg_catalog          pg_settings
pg_catalog          pg_shdescription
pg_catalog          pg_shseclabel
pg_catalog          pg_stat_activity
pg_catalog          pg_tables
pg_catalog          pg_tablespace
pg_catalog          pg_trigger
pg_catalog          pg_type
pg_catalog          pg_user
pg_catalog          pg_user_mapping
pg_catalog          pg_views

statement ok
CREATE DATABASE other_db

statement ok
CREATE TABLE other_db.xyz (i INT)

statement ok
CREATE SEQUENCE other_db.seq

statement ok
CREATE VIEW other_db.abc AS SELECT i from other_db.xyz

statement ok
GRANT UPDATE ON other_db.xyz TO testuser

user testuser

# Check the output with the current database set to 'test' (the
# defaults in tests). This will make the tables in other_db invisible to
# a non-root user.
query T
SELECT table_name FROM information_schema.tables WHERE table_catalog = 'other_db'
----

# Check that the other_db tables become visible when a prefix is specified
query T
SELECT table_name FROM other_db.information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public'
----
xyz

# Check that one can see all tables with the empty prefix.
query T rowsort
SELECT table_name FROM "".information_schema.tables WHERE table_catalog = 'other_db'
----
backward_dependencies
builtin_functions
cluster_queries
cluster_sessions
cluster_settings
create_statements
feature_usage
forward_dependencies
gossip_alerts
gossip_liveness
gossip_network
gossip_nodes
index_columns
jobs
kv_node_status
kv_store_status
leases
node_build_info
node_metrics
node_queries
node_runtime_info
node_sessions
node_statement_statistics
partitions
predefined_comments
ranges
ranges_no_leases
schema_changes
session_trace
session_variables
table_columns
table_indexes
tables
zones
administrable_role_authorizations
applicable_roles
column_privileges
columns
constraint_column_usage
enabled_roles
key_column_usage
parameters
referential_constraints
role_table_grants
routines
schema_privileges
schemata
sequences
statistics
table_constraints
table_privileges
tables
user_privileges
views
pg_am
pg_attrdef
pg_attribute
pg_auth_members
pg_class
pg_collation
pg_constraint
pg_database
pg_depend
pg_description
pg_enum
pg_extension
pg_foreign_data_wrapper
pg_foreign_server
pg_foreign_table
pg_index
pg_indexes
pg_inherits
pg_language
pg_namespace
pg_operator
pg_proc
pg_range
pg_rewrite
pg_roles
pg_seclabel
pg_sequence
pg_settings
pg_shdescription
pg_shseclabel
pg_stat_activity
pg_tables
pg_tablespace
pg_trigger
pg_type
pg_user
pg_user_mapping
pg_views
xyz

# Check that the other_db tables become visible to non-root when the current database is changed.
query T
SET DATABASE = other_db; SELECT table_name FROM information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public'
----
xyz

user root

# Check that root sees everything when there is no current database
query T
SET DATABASE = ''; SELECT table_name FROM information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public'
----
xyz
abc

# Check that root doesn't see other things when there is a current database
query T
SET DATABASE = test; SELECT table_name FROM information_schema.tables WHERE table_schema = 'other_db'
----

# Check that filtering works.
query T
SELECT table_name FROM other_db.information_schema.tables WHERE table_name > 't'  ORDER BY 1 DESC
----
zones
xyz
views
user_privileges
tables
tables
table_privileges
table_indexes
table_constraints
table_columns

# Check that the metadata is reported properly.
query TTTTTI colnames
SELECT * FROM system.information_schema.tables
----
table_catalog  table_schema        table_name                         table_type   is_insertable_into  version
system         crdb_internal       backward_dependencies              SYSTEM VIEW  NO                  1
system         crdb_internal       builtin_functions                  SYSTEM VIEW  NO                  1
system         crdb_internal       cluster_queries                    SYSTEM VIEW  NO                  1
system         crdb_internal       cluster_sessions                   SYSTEM VIEW  NO                  1
system         crdb_internal       cluster_settings                   SYSTEM VIEW  NO                  1
system         crdb_internal       create_statements                  SYSTEM VIEW  NO                  1
system         crdb_internal       feature_usage                      SYSTEM VIEW  NO                  1
system         crdb_internal       forward_dependencies               SYSTEM VIEW  NO                  1
system         crdb_internal       gossip_alerts                      SYSTEM VIEW  NO                  1
system         crdb_internal       gossip_liveness                    SYSTEM VIEW  NO                  1
system         crdb_internal       gossip_network                     SYSTEM VIEW  NO                  1
system         crdb_internal       gossip_nodes                       SYSTEM VIEW  NO                  1
system         crdb_internal       index_columns                      SYSTEM VIEW  NO                  1
system         crdb_internal       jobs                               SYSTEM VIEW  NO                  1
system         crdb_internal       kv_node_status                     SYSTEM VIEW  NO                  1
system         crdb_internal       kv_store_status                    SYSTEM VIEW  NO                  1
system         crdb_internal       leases                             SYSTEM VIEW  NO                  1
system         crdb_internal       node_build_info                    SYSTEM VIEW  NO                  1
system         crdb_internal       node_metrics                       SYSTEM VIEW  NO                  1
system         crdb_internal       node_queries                       SYSTEM VIEW  NO                  1
system         crdb_internal       node_runtime_info                  SYSTEM VIEW  NO                  1
system         crdb_internal       node_sessions                      SYSTEM VIEW  NO                  1
system         crdb_internal       node_statement_statistics          SYSTEM VIEW  NO                  1
system         crdb_internal       partitions                         SYSTEM VIEW  NO                  1
system         crdb_internal       predefined_comments                SYSTEM VIEW  NO                  1
system         crdb_internal       ranges                             SYSTEM VIEW  NO                  1
system         crdb_internal       ranges_no_leases                   SYSTEM VIEW  NO                  1
system         crdb_internal       schema_changes                     SYSTEM VIEW  NO                  1
system         crdb_internal       session_trace                      SYSTEM VIEW  NO                  1
system         crdb_internal       session_variables                  SYSTEM VIEW  NO                  1
system         crdb_internal       table_columns                      SYSTEM VIEW  NO                  1
system         crdb_internal       table_indexes                      SYSTEM VIEW  NO                  1
system         crdb_internal       tables                             SYSTEM VIEW  NO                  1
system         crdb_internal       zones                              SYSTEM VIEW  NO                  1
system         information_schema  administrable_role_authorizations  SYSTEM VIEW  NO                  1
system         information_schema  applicable_roles                   SYSTEM VIEW  NO                  1
system         information_schema  column_privileges                  SYSTEM VIEW  NO                  1
system         information_schema  columns                            SYSTEM VIEW  NO                  1
system         information_schema  constraint_column_usage            SYSTEM VIEW  NO                  1
system         information_schema  enabled_roles                      SYSTEM VIEW  NO                  1
system         information_schema  key_column_usage                   SYSTEM VIEW  NO                  1
system         information_schema  parameters                         SYSTEM VIEW  NO                  1
system         information_schema  referential_constraints            SYSTEM VIEW  NO                  1
system         information_schema  role_table_grants                  SYSTEM VIEW  NO                  1
system         information_schema  routines                           SYSTEM VIEW  NO                  1
system         information_schema  schema_privileges                  SYSTEM VIEW  NO                  1
system         information_schema  schemata                           SYSTEM VIEW  NO                  1
system         information_schema  sequences                          SYSTEM VIEW  NO                  1
system         information_schema  statistics                         SYSTEM VIEW  NO                  1
system         information_schema  table_constraints                  SYSTEM VIEW  NO                  1
system         information_schema  table_privileges                   SYSTEM VIEW  NO                  1
system         information_schema  tables                             SYSTEM VIEW  NO                  1
system         information_schema  user_privileges                    SYSTEM VIEW  NO                  1
system         information_schema  views                              SYSTEM VIEW  NO                  1
system         pg_catalog          pg_am                              SYSTEM VIEW  NO                  1
system         pg_catalog          pg_attrdef                         SYSTEM VIEW  NO                  1
system         pg_catalog          pg_attribute                       SYSTEM VIEW  NO                  1
system         pg_catalog          pg_auth_members                    SYSTEM VIEW  NO                  1
system         pg_catalog          pg_class                           SYSTEM VIEW  NO                  1
system         pg_catalog          pg_collation                       SYSTEM VIEW  NO                  1
system         pg_catalog          pg_constraint                      SYSTEM VIEW  NO                  1
system         pg_catalog          pg_database                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_depend                          SYSTEM VIEW  NO                  1
system         pg_catalog          pg_description                     SYSTEM VIEW  NO                  1
system         pg_catalog          pg_enum                            SYSTEM VIEW  NO                  1
system         pg_catalog          pg_extension                       SYSTEM VIEW  NO                  1
system         pg_catalog          pg_foreign_data_wrapper            SYSTEM VIEW  NO                  1
system         pg_catalog          pg_foreign_server                  SYSTEM VIEW  NO                  1
system         pg_catalog          pg_foreign_table                   SYSTEM VIEW  NO                  1
system         pg_catalog          pg_index                           SYSTEM VIEW  NO                  1
system         pg_catalog          pg_indexes                         SYSTEM VIEW  NO                  1
system         pg_catalog          pg_inherits                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_language                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_namespace                       SYSTEM VIEW  NO                  1
system         pg_catalog          pg_operator                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_proc                            SYSTEM VIEW  NO                  1
system         pg_catalog          pg_range                           SYSTEM VIEW  NO                  1
system         pg_catalog          pg_rewrite                         SYSTEM VIEW  NO                  1
system         pg_catalog          pg_roles                           SYSTEM VIEW  NO                  1
system         pg_catalog          pg_seclabel                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_sequence                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_settings                        SYSTEM VIEW  NO                  1
system         pg_catalog          pg_shdescription                   SYSTEM VIEW  NO                  1
system         pg_catalog          pg_shseclabel                      SYSTEM VIEW  NO                  1
system         pg_catalog          pg_stat_activity                   SYSTEM VIEW  NO                  1
system         pg_catalog          pg_tables                          SYSTEM VIEW  NO                  1
system         pg_catalog          pg_tablespace                      SYSTEM VIEW  NO                  1
system         pg_catalog          pg_trigger                         SYSTEM VIEW  NO                  1
system         pg_catalog          pg_type                            SYSTEM VIEW  NO                  1
system         pg_catalog          pg_user                            SYSTEM VIEW  NO                  1
system         pg_catalog          pg_user_mapping                    SYSTEM VIEW  NO                  1
system         pg_catalog          pg_views                           SYSTEM VIEW  NO                  1
system         public              namespace                          BASE TABLE   YES                 1
system         public              descriptor                         BASE TABLE   YES                 1
system         public              users                              BASE TABLE   YES                 1
system         public              zones                              BASE TABLE   YES                 1
system         public              settings                           BASE TABLE   YES                 1
system         public              lease                              BASE TABLE   YES                 1
system         public              eventlog                           BASE TABLE   YES                 1
system         public              rangelog                           BASE TABLE   YES                 1
system         public              ui                                 BASE TABLE   YES                 1
system         public              jobs                               BASE TABLE   YES                 1
system         public              web_sessions                       BASE TABLE   YES                 1
system         public              table_statistics                   BASE TABLE   YES                 1
system         public              locations                          BASE TABLE   YES                 1
system         public              role_members                       BASE TABLE   YES                 1
system         public              comments                           BASE TABLE   YES                 1

statement ok
ALTER TABLE other_db.xyz ADD COLUMN j INT

query TTI colnames
SELECT TABLE_CATALOG, TABLE_NAME, VERSION FROM "".information_schema.tables WHERE version > 1 AND TABLE_SCHEMA = 'public' ORDER BY 1,2
----
table_catalog  table_name  version
other_db       xyz         6

user testuser

# Check that another user cannot see other_db.adbc any more because they
# don't have privileges on it.
query TTTTTI colnames
SELECT * FROM other_db.information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public'
----
table_catalog  table_schema  table_name  table_type  is_insertable_into  version
other_db       public        xyz         BASE TABLE  YES                 6


user root

statement ok
GRANT SELECT ON other_db.abc TO testuser

user testuser

# Check the user can see the tables now that they have privilege.
query TTTTTI colnames
SELECT * FROM other_db.information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public' ORDER BY 1, 3
----
table_catalog  table_schema  table_name  table_type  is_insertable_into  version
other_db       public        abc         VIEW        NO                  2
other_db       public        xyz         BASE TABLE  YES                 6

user root

statement ok
DROP DATABASE other_db CASCADE

statement ok
SET DATABASE = test

## information_schema.table_constraints
## information_schema.constraint_column_usage

query TTTTTTTTT colnames
SELECT *
FROM system.information_schema.table_constraints
ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
----
constraint_catalog  constraint_schema  constraint_name  table_catalog  table_schema  table_name        constraint_type  is_deferrable  initially_deferred
system              public             primary          system         public        comments          PRIMARY KEY      NO             NO
system              public             primary          system         public        descriptor        PRIMARY KEY      NO             NO
system              public             primary          system         public        eventlog          PRIMARY KEY      NO             NO
system              public             primary          system         public        jobs              PRIMARY KEY      NO             NO
system              public             primary          system         public        lease             PRIMARY KEY      NO             NO
system              public             primary          system         public        locations         PRIMARY KEY      NO             NO
system              public             primary          system         public        namespace         PRIMARY KEY      NO             NO
system              public             primary          system         public        rangelog          PRIMARY KEY      NO             NO
system              public             primary          system         public        role_members      PRIMARY KEY      NO             NO
system              public             primary          system         public        settings          PRIMARY KEY      NO             NO
system              public             primary          system         public        table_statistics  PRIMARY KEY      NO             NO
system              public             primary          system         public        ui                PRIMARY KEY      NO             NO
system              public             primary          system         public        users             PRIMARY KEY      NO             NO
system              public             primary          system         public        web_sessions      PRIMARY KEY      NO             NO
system              public             primary          system         public        zones             PRIMARY KEY      NO             NO

query TTTTTTT colnames
SELECT *
FROM system.information_schema.constraint_column_usage
ORDER BY TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
----
table_catalog  table_schema  table_name        column_name    constraint_catalog  constraint_schema  constraint_name
system         public        comments          object_id      system              public             primary
system         public        comments          sub_id         system              public             primary
system         public        comments          type           system              public             primary
system         public        descriptor        id             system              public             primary
system         public        eventlog          timestamp      system              public             primary
system         public        eventlog          uniqueID       system              public             primary
system         public        jobs              id             system              public             primary
system         public        lease             descID         system              public             primary
system         public        lease             expiration     system              public             primary
system         public        lease             nodeID         system              public             primary
system         public        lease             version        system              public             primary
system         public        locations         localityKey    system              public             primary
system         public        locations         localityValue  system              public             primary
system         public        namespace         name           system              public             primary
system         public        namespace         parentID       system              public             primary
system         public        rangelog          timestamp      system              public             primary
system         public        rangelog          uniqueID       system              public             primary
system         public        role_members      member         system              public             primary
system         public        role_members      role           system              public             primary
system         public        settings          name           system              public             primary
system         public        table_statistics  statisticID    system              public             primary
system         public        table_statistics  tableID        system              public             primary
system         public        ui                key            system              public             primary
system         public        users             username       system              public             primary
system         public        web_sessions      id             system              public             primary
system         public        zones             id             system              public             primary

statement ok
CREATE DATABASE constraint_db

statement ok
CREATE TABLE constraint_db.t1 (
  p FLOAT PRIMARY KEY,
  a INT UNIQUE CHECK (a > 4),
  CONSTRAINT c2 CHECK (a < 99)
)

statement ok
CREATE TABLE constraint_db.t2 (
    t1_ID INT,
    CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_db.t1(a),
    INDEX (t1_ID)
)

statement ok
SET DATABASE = constraint_db

query TTTTTTTTT colnames
SELECT *
FROM information_schema.table_constraints
ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
----
constraint_catalog  constraint_schema  constraint_name  table_catalog  table_schema  table_name  constraint_type  is_deferrable  initially_deferred
constraint_db       public             c2               constraint_db  public        t1          CHECK            NO             NO
constraint_db       public             check_a          constraint_db  public        t1          CHECK            NO             NO
constraint_db       public             primary          constraint_db  public        t1          PRIMARY KEY      NO             NO
constraint_db       public             t1_a_key         constraint_db  public        t1          UNIQUE           NO             NO
constraint_db       public             fk               constraint_db  public        t2          FOREIGN KEY      NO             NO

query TTTTTTT colnames
SELECT *
FROM information_schema.constraint_column_usage
WHERE constraint_catalog = 'constraint_db'
ORDER BY TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
----
table_catalog  table_schema  table_name  column_name  constraint_catalog  constraint_schema  constraint_name
constraint_db  public        t1          a            constraint_db       public             c2
constraint_db  public        t1          a            constraint_db       public             check_a
constraint_db  public        t1          a            constraint_db       public             fk
constraint_db  public        t1          a            constraint_db       public             t1_a_key
constraint_db  public        t1          p            constraint_db       public             primary

statement ok
DROP DATABASE constraint_db CASCADE

## information_schema.columns

query TTTTI colnames
SELECT table_catalog, table_schema, table_name, column_name, ordinal_position
FROM system.information_schema.columns
WHERE table_schema != 'information_schema' AND table_schema != 'pg_catalog' AND table_schema != 'crdb_internal'
ORDER BY 3,4
----
table_catalog  table_schema  table_name        column_name     ordinal_position
system         public        comments          comment         4
system         public        comments          object_id       2
system         public        comments          sub_id          3
system         public        comments          type            1
system         public        descriptor        descriptor      2
system         public        descriptor        id              1
system         public        eventlog          eventType       2
system         public        eventlog          info            5
system         public        eventlog          reportingID     4
system         public        eventlog          targetID        3
system         public        eventlog          timestamp       1
system         public        eventlog          uniqueID        6
system         public        jobs              created         3
system         public        jobs              id              1
system         public        jobs              payload         4
system         public        jobs              progress        5
system         public        jobs              status          2
system         public        lease             descID          1
system         public        lease             expiration      4
system         public        lease             nodeID          3
system         public        lease             version         2
system         public        locations         latitude        3
system         public        locations         localityKey     1
system         public        locations         localityValue   2
system         public        locations         longitude       4
system         public        namespace         id              3
system         public        namespace         name            2
system         public        namespace         parentID        1
system         public        rangelog          eventType       4
system         public        rangelog          info            6
system         public        rangelog          otherRangeID    5
system         public        rangelog          rangeID         2
system         public        rangelog          storeID         3
system         public        rangelog          timestamp       1
system         public        rangelog          uniqueID        7
system         public        role_members      isAdmin         3
system         public        role_members      member          2
system         public        role_members      role            1
system         public        settings          lastUpdated     3
system         public        settings          name            1
system         public        settings          value           2
system         public        settings          valueType       4
system         public        table_statistics  columnIDs       4
system         public        table_statistics  createdAt       5
system         public        table_statistics  distinctCount   7
system         public        table_statistics  histogram       9
system         public        table_statistics  name            3
system         public        table_statistics  nullCount       8
system         public        table_statistics  rowCount        6
system         public        table_statistics  statisticID     2
system         public        table_statistics  tableID         1
system         public        ui                key             1
system         public        ui                lastUpdated     3
system         public        ui                value           2
system         public        users             hashedPassword  2
system         public        users             isRole          3
system         public        users             username        1
system         public        web_sessions      auditInfo       8
system         public        web_sessions      createdAt       4
system         public        web_sessions      expiresAt       5
system         public        web_sessions      hashedSecret    2
system         public        web_sessions      id              1
system         public        web_sessions      lastUsedAt      7
system         public        web_sessions      revokedAt       6
system         public        web_sessions      username        3
system         public        zones             config          2
system         public        zones             id              1

statement ok
SET DATABASE = test

statement ok
CREATE TABLE with_defaults (a INT DEFAULT 9, b STRING DEFAULT 'default', c INT, d STRING)

query TTT colnames
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'with_defaults'
----
table_name     column_name  column_default
with_defaults  a            9:::INT8
with_defaults  b            'default':::STRING
with_defaults  c            NULL
with_defaults  d            NULL
with_defaults  rowid        unique_rowid()

statement ok
DROP TABLE with_defaults

statement ok
CREATE TABLE nullability (a INT NOT NULL, b STRING NOT NULL, c INT, d STRING)

query TTT colnames
SELECT table_name, column_name, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'nullability'
----
table_name   column_name  is_nullable
nullability  a            NO
nullability  b            NO
nullability  c            YES
nullability  d            YES
nullability  rowid        NO

statement ok
DROP TABLE nullability

statement ok
CREATE TABLE data_types (
  a   INT,
  a2  INT2,
  a4  INT4,
  a8  INT8,
  b   FLOAT,
  b4  FLOAT4,
  br  REAL,
  c   DECIMAL,
  cp  DECIMAL(3),
  cps DECIMAL(3,2),
  d   STRING,
  dl  STRING COLLATE en,
  dc  CHAR,
  dc2 CHAR(2),
  dv  VARCHAR,
  dv2 VARCHAR(2),
  dq  "char",
  e   BYTES,
  f   TIMESTAMP,
  f6  TIMESTAMP(6),
  g   TIMESTAMPTZ,
  g6  TIMESTAMPTZ(6),
  h   BIT,
  h2  BIT(2),
  hv  VARBIT,
  hv2 VARBIT(2),
  i   INTERVAL,
  j   BOOL,
  k   OID,
  k2  REGCLASS,
  k3  REGNAMESPACE,
  k4  REGPROC,
  k5  REGPROCEDURE,
  k6  REGTYPE,
  l   UUID,
  m   INT2[],
  m2  STRING[],
  m3  DECIMAL(3, 2)[],
  m4  VARCHAR(2)[] COLLATE en,
  n   INET,
  o   TIME,
  o6  TIME(6),
  p   JSONB,
  q   NAME
)

query TTTTTTTTTT colnames
SELECT table_name, column_name, data_type, crdb_sql_type, udt_catalog, udt_schema, udt_name, collation_catalog, collation_schema, collation_name
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'data_types'
----
table_name  column_name  data_type                    crdb_sql_type            udt_catalog  udt_schema  udt_name      collation_catalog  collation_schema  collation_name
data_types  a            bigint                       INT8                     test         pg_catalog  int8          NULL               NULL              NULL
data_types  a2           smallint                     INT2                     test         pg_catalog  int2          NULL               NULL              NULL
data_types  a4           integer                      INT4                     test         pg_catalog  int4          NULL               NULL              NULL
data_types  a8           bigint                       INT8                     test         pg_catalog  int8          NULL               NULL              NULL
data_types  b            double precision             FLOAT8                   test         pg_catalog  float8        NULL               NULL              NULL
data_types  b4           real                         FLOAT4                   test         pg_catalog  float4        NULL               NULL              NULL
data_types  br           real                         FLOAT4                   test         pg_catalog  float4        NULL               NULL              NULL
data_types  c            numeric                      DECIMAL                  test         pg_catalog  numeric       NULL               NULL              NULL
data_types  cp           numeric                      DECIMAL(3)               test         pg_catalog  numeric       NULL               NULL              NULL
data_types  cps          numeric                      DECIMAL(3,2)             test         pg_catalog  numeric       NULL               NULL              NULL
data_types  d            text                         STRING                   test         pg_catalog  text          NULL               NULL              NULL
data_types  dl           text                         STRING COLLATE en        test         pg_catalog  text          test               pg_catalog        en
data_types  dc           character                    CHAR                     test         pg_catalog  bpchar        NULL               NULL              NULL
data_types  dc2          character                    CHAR(2)                  test         pg_catalog  bpchar        NULL               NULL              NULL
data_types  dv           character varying            VARCHAR                  test         pg_catalog  varchar       NULL               NULL              NULL
data_types  dv2          character varying            VARCHAR(2)               test         pg_catalog  varchar       NULL               NULL              NULL
data_types  dq           "char"                       "char"                   test         pg_catalog  char          NULL               NULL              NULL
data_types  e            bytea                        BYTES                    test         pg_catalog  bytea         NULL               NULL              NULL
data_types  f            timestamp without time zone  TIMESTAMP                test         pg_catalog  timestamp     NULL               NULL              NULL
data_types  f6           timestamp without time zone  TIMESTAMP(6)             test         pg_catalog  timestamp     NULL               NULL              NULL
data_types  g            timestamp with time zone     TIMESTAMPTZ              test         pg_catalog  timestamptz   NULL               NULL              NULL
data_types  g6           timestamp with time zone     TIMESTAMPTZ(6)           test         pg_catalog  timestamptz   NULL               NULL              NULL
data_types  h            bit                          BIT                      test         pg_catalog  bit           NULL               NULL              NULL
data_types  h2           bit                          BIT(2)                   test         pg_catalog  bit           NULL               NULL              NULL
data_types  hv           bit varying                  VARBIT                   test         pg_catalog  varbit        NULL               NULL              NULL
data_types  hv2          bit varying                  VARBIT(2)                test         pg_catalog  varbit        NULL               NULL              NULL
data_types  i            interval                     INTERVAL                 test         pg_catalog  interval      NULL               NULL              NULL
data_types  j            boolean                      BOOL                     test         pg_catalog  bool          NULL               NULL              NULL
data_types  k            oid                          OID                      test         pg_catalog  oid           NULL               NULL              NULL
data_types  k2           regclass                     REGCLASS                 test         pg_catalog  regclass      NULL               NULL              NULL
data_types  k3           regnamespace                 REGNAMESPACE             test         pg_catalog  regnamespace  NULL               NULL              NULL
data_types  k4           regproc                      REGPROC                  test         pg_catalog  regproc       NULL               NULL              NULL
data_types  k5           regprocedure                 REGPROCEDURE             test         pg_catalog  regprocedure  NULL               NULL              NULL
data_types  k6           regtype                      REGTYPE                  test         pg_catalog  regtype       NULL               NULL              NULL
data_types  l            uuid                         UUID                     test         pg_catalog  uuid          NULL               NULL              NULL
data_types  m            ARRAY                        INT2[]                   test         pg_catalog  _int2         NULL               NULL              NULL
data_types  m2           ARRAY                        STRING[]                 test         pg_catalog  _text         NULL               NULL              NULL
data_types  m3           ARRAY                        DECIMAL(3,2)[]           test         pg_catalog  _numeric      NULL               NULL              NULL
data_types  m4           ARRAY                        VARCHAR(2)[] COLLATE en  test         pg_catalog  _varchar      NULL               NULL              NULL
data_types  n            inet                         INET                     test         pg_catalog  inet          NULL               NULL              NULL
data_types  o            time without time zone       TIME                     test         pg_catalog  time          NULL               NULL              NULL
data_types  o6           time without time zone       TIME(6)                  test         pg_catalog  time          NULL               NULL              NULL
data_types  p            jsonb                        JSONB                    test         pg_catalog  jsonb         NULL               NULL              NULL
data_types  q            name                         NAME                     test         pg_catalog  name          NULL               NULL              NULL
data_types  rowid        bigint                       INT8                     test         pg_catalog  int8          NULL               NULL              NULL

statement ok
DROP TABLE data_types

statement ok
CREATE TABLE computed (a INT, b INT AS (a + 1) STORED)

query TTTT colnames
SELECT column_name, is_generated, generation_expression, is_updatable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'computed'
----
column_name  is_generated  generation_expression  is_updatable
a            NO            ·                      YES
b            YES           a + 1                  NO
rowid        NO            ·                      YES

statement ok
CREATE TABLE char_len (
  a INT, b INT2, c INT4,
  d STRING, e STRING(12),
  dc CHAR, ec CHAR(12),
  dv VARCHAR, ev VARCHAR(12),
  dq "char",
  f FLOAT,
  g BIT, h BIT(12),
  i VARBIT, j VARBIT(12))

query TTII colnames
SELECT table_name, column_name, character_maximum_length, character_octet_length
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'char_len'
----
table_name  column_name  character_maximum_length  character_octet_length
char_len    a            NULL                      NULL
char_len    b            NULL                      NULL
char_len    c            NULL                      NULL
char_len    d            NULL                      NULL
char_len    e            12                        48
char_len    dc           1                         4
char_len    ec           12                        48
char_len    dv           NULL                      NULL
char_len    ev           12                        48
char_len    dq           NULL                      NULL
char_len    f            NULL                      NULL
char_len    g            1                         NULL
char_len    h            12                        NULL
char_len    i            NULL                      NULL
char_len    j            12                        NULL
char_len    rowid        NULL                      NULL

statement ok
DROP TABLE char_len

statement ok
CREATE TABLE num_prec (a INT, b FLOAT, c FLOAT(23), d DECIMAL, e DECIMAL(12), f DECIMAL(12, 6), g BOOLEAN)

query TTIIII colnames
SELECT table_name, column_name, numeric_precision, numeric_precision_radix, numeric_scale, datetime_precision
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'num_prec'
----
table_name  column_name  numeric_precision  numeric_precision_radix  numeric_scale  datetime_precision
num_prec    a            64                 2                        0              NULL
num_prec    b            53                 2                        NULL           NULL
num_prec    c            24                 2                        NULL           NULL
num_prec    d            NULL               10                       NULL           NULL
num_prec    e            12                 10                       0              NULL
num_prec    f            12                 10                       6              NULL
num_prec    g            NULL               NULL                     NULL           NULL
num_prec    rowid        64                 2                        0              NULL

statement ok
DROP TABLE num_prec

## information_schema.key_column_usage
## information_schema.referential_constraints

statement ok
CREATE DATABASE constraint_column

statement ok
CREATE TABLE constraint_column.t1 (
  p FLOAT PRIMARY KEY,
  a INT UNIQUE,
  b INT,
  c INT CHECK(c > 0),
  UNIQUE INDEX index_key(b, c)
)

statement ok
CREATE TABLE constraint_column.t2 (
    t1_ID INT PRIMARY KEY,
    CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_column.t1(a) ON DELETE RESTRICT
)

statement ok
CREATE TABLE constraint_column.t3 (
    a INT,
    b INT,
    CONSTRAINT fk2 FOREIGN KEY (a, b) REFERENCES constraint_column.t1(b, c) ON UPDATE CASCADE,
    INDEX (a, b)
)

statement ok
SET DATABASE = constraint_column

query TTTTTTTII colnames
SELECT * FROM information_schema.key_column_usage WHERE constraint_schema = 'public' ORDER BY TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION
----
constraint_catalog  constraint_schema  constraint_name  table_catalog      table_schema  table_name  column_name  ordinal_position  position_in_unique_constraint
constraint_column   public             index_key        constraint_column  public        t1          b            1                 NULL
constraint_column   public             index_key        constraint_column  public        t1          c            2                 NULL
constraint_column   public             primary          constraint_column  public        t1          p            1                 NULL
constraint_column   public             t1_a_key         constraint_column  public        t1          a            1                 NULL
constraint_column   public             fk               constraint_column  public        t2          t1_id        1                 1
constraint_column   public             primary          constraint_column  public        t2          t1_id        1                 NULL
constraint_column   public             fk2              constraint_column  public        t3          a            1                 1
constraint_column   public             fk2              constraint_column  public        t3          b            2                 2

query TTTTTTTTTTT colnames
SELECT * FROM information_schema.referential_constraints WHERE constraint_schema = 'public' ORDER BY TABLE_NAME, CONSTRAINT_NAME
----
constraint_catalog  constraint_schema  constraint_name  unique_constraint_catalog  unique_constraint_schema  unique_constraint_name  match_option  update_rule  delete_rule  table_name  referenced_table_name
constraint_column   public             fk               constraint_column          public                    t1_a_key                NONE          NO ACTION    RESTRICT     t2          t1
constraint_column   public             fk2              constraint_column          public                    index_key               NONE          CASCADE      NO ACTION    t3          t1

statement ok
DROP DATABASE constraint_column CASCADE

## information_schema.schema_privileges

statement ok
CREATE DATABASE other_db; SET DATABASE = other_db

query TTTTT colnames
SELECT * FROM information_schema.schema_privileges
----
grantee  table_catalog  table_schema        privilege_type  is_grantable
admin    other_db       crdb_internal       ALL             NULL
root     other_db       crdb_internal       ALL             NULL
admin    other_db       information_schema  ALL             NULL
root     other_db       information_schema  ALL             NULL
admin    other_db       pg_catalog          ALL             NULL
root     other_db       pg_catalog          ALL             NULL
admin    other_db       public              ALL             NULL
root     other_db       public              ALL             NULL

statement ok
GRANT SELECT ON DATABASE other_db TO testuser

query TTTTT colnames
SELECT * FROM information_schema.schema_privileges
----
grantee   table_catalog  table_schema        privilege_type  is_grantable
admin     other_db       crdb_internal       ALL             NULL
root      other_db       crdb_internal       ALL             NULL
testuser  other_db       crdb_internal       SELECT          NULL
admin     other_db       information_schema  ALL             NULL
root      other_db       information_schema  ALL             NULL
testuser  other_db       information_schema  SELECT          NULL
admin     other_db       pg_catalog          ALL             NULL
root      other_db       pg_catalog          ALL             NULL
testuser  other_db       pg_catalog          SELECT          NULL
admin     other_db       public              ALL             NULL
root      other_db       public              ALL             NULL
testuser  other_db       public              SELECT          NULL

## information_schema.table_privileges and information_schema.role_table_grants

# root can see everything
query TTTTTTTT colnames,rowsort
SELECT * FROM system.information_schema.table_privileges ORDER BY table_schema, table_name, table_schema, grantee, privilege_type
----
grantor  grantee  table_catalog  table_schema        table_name                         privilege_type  is_grantable  with_hierarchy
NULL     public   system         crdb_internal       backward_dependencies              SELECT          NULL          YES
NULL     public   system         crdb_internal       builtin_functions                  SELECT          NULL          YES
NULL     public   system         crdb_internal       cluster_queries                    SELECT          NULL          YES
NULL     public   system         crdb_internal       cluster_sessions                   SELECT          NULL          YES
NULL     public   system         crdb_internal       cluster_settings                   SELECT          NULL          YES
NULL     public   system         crdb_internal       create_statements                  SELECT          NULL          YES
NULL     public   system         crdb_internal       feature_usage                      SELECT          NULL          YES
NULL     public   system         crdb_internal       forward_dependencies               SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_alerts                      SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_liveness                    SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_network                     SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_nodes                       SELECT          NULL          YES
NULL     public   system         crdb_internal       index_columns                      SELECT          NULL          YES
NULL     public   system         crdb_internal       jobs                               SELECT          NULL          YES
NULL     public   system         crdb_internal       kv_node_status                     SELECT          NULL          YES
NULL     public   system         crdb_internal       kv_store_status                    SELECT          NULL          YES
NULL     public   system         crdb_internal       leases                             SELECT          NULL          YES
NULL     public   system         crdb_internal       node_build_info                    SELECT          NULL          YES
NULL     public   system         crdb_internal       node_metrics                       SELECT          NULL          YES
NULL     public   system         crdb_internal       node_queries                       SELECT          NULL          YES
NULL     public   system         crdb_internal       node_runtime_info                  SELECT          NULL          YES
NULL     public   system         crdb_internal       node_sessions                      SELECT          NULL          YES
NULL     public   system         crdb_internal       node_statement_statistics          SELECT          NULL          YES
NULL     public   system         crdb_internal       partitions                         SELECT          NULL          YES
NULL     public   system         crdb_internal       predefined_comments                SELECT          NULL          YES
NULL     public   system         crdb_internal       ranges                             SELECT          NULL          YES
NULL     public   system         crdb_internal       ranges_no_leases                   SELECT          NULL          YES
NULL     public   system         crdb_internal       schema_changes                     SELECT          NULL          YES
NULL     public   system         crdb_internal       session_trace                      SELECT          NULL          YES
NULL     public   system         crdb_internal       session_variables                  SELECT          NULL          YES
NULL     public   system         crdb_internal       table_columns                      SELECT          NULL          YES
NULL     public   system         crdb_internal       table_indexes                      SELECT          NULL          YES
NULL     public   system         crdb_internal       tables                             SELECT          NULL          YES
NULL     public   system         crdb_internal       zones                              SELECT          NULL          YES
NULL     public   system         information_schema  administrable_role_authorizations  SELECT          NULL          YES
NULL     public   system         information_schema  applicable_roles                   SELECT          NULL          YES
NULL     public   system         information_schema  column_privileges                  SELECT          NULL          YES
NULL     public   system         information_schema  columns                            SELECT          NULL          YES
NULL     public   system         information_schema  constraint_column_usage            SELECT          NULL          YES
NULL     public   system         information_schema  enabled_roles                      SELECT          NULL          YES
NULL     public   system         information_schema  key_column_usage                   SELECT          NULL          YES
NULL     public   system         information_schema  parameters                         SELECT          NULL          YES
NULL     public   system         information_schema  referential_constraints            SELECT          NULL          YES
NULL     public   system         information_schema  role_table_grants                  SELECT          NULL          YES
NULL     public   system         information_schema  routines                           SELECT          NULL          YES
NULL     public   system         information_schema  schema_privileges                  SELECT          NULL          YES
NULL     public   system         information_schema  schemata                           SELECT          NULL          YES
NULL     public   system         information_schema  sequences                          SELECT          NULL          YES
NULL     public   system         information_schema  statistics                         SELECT          NULL          YES
NULL     public   system         information_schema  table_constraints                  SELECT          NULL          YES
NULL     public   system         information_schema  table_privileges                   SELECT          NULL          YES
NULL     public   system         information_schema  tables                             SELECT          NULL          YES
NULL     public   system         information_schema  user_privileges                    SELECT          NULL          YES
NULL     public   system         information_schema  views                              SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_am                              SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_attrdef                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_attribute                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_auth_members                    SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_class                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_collation                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_constraint                      SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_database                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_depend                          SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_description                     SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_enum                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_extension                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_foreign_data_wrapper            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_foreign_server                  SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_foreign_table                   SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_index                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_indexes                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_inherits                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_language                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_namespace                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_operator                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_proc                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_range                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_rewrite                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_roles                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_seclabel                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_sequence                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_settings                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_shdescription                   SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_shseclabel                      SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_stat_activity                   SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_tables                          SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_tablespace                      SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_trigger                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_type                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_user                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_user_mapping                    SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_views                           SELECT          NULL          YES
NULL     admin    system         public              comments                           DELETE          NULL          NO
NULL     admin    system         public              comments                           GRANT           NULL          NO
NULL     admin    system         public              comments                           INSERT          NULL          NO
NULL     admin    system         public              comments                           SELECT          NULL          YES
NULL     admin    system         public              comments                           UPDATE          NULL          NO
NULL     public   system         public              comments                           DELETE          NULL          NO
NULL     public   system         public              comments                           GRANT           NULL          NO
NULL     public   system         public              comments                           INSERT          NULL          NO
NULL     public   system         public              comments                           SELECT          NULL          YES
NULL     public   system         public              comments                           UPDATE          NULL          NO
NULL     root     system         public              comments                           DELETE          NULL          NO
NULL     root     system         public              comments                           GRANT           NULL          NO
NULL     root     system         public              comments                           INSERT          NULL          NO
NULL     root     system         public              comments                           SELECT          NULL          YES
NULL     root     system         public              comments                           UPDATE          NULL          NO
NULL     admin    system         public              descriptor                         GRANT           NULL          NO
NULL     admin    system         public              descriptor                         SELECT          NULL          YES
NULL     root     system         public              descriptor                         GRANT           NULL          NO
NULL     root     system         public              descriptor                         SELECT          NULL          YES
NULL     admin    system         public              eventlog                           DELETE          NULL          NO
NULL     admin    system         public              eventlog                           GRANT           NULL          NO
NULL     admin    system         public              eventlog                           INSERT          NULL          NO
NULL     admin    system         public              eventlog                           SELECT          NULL          YES
NULL     admin    system         public              eventlog                           UPDATE          NULL          NO
NULL     root     system         public              eventlog                           DELETE          NULL          NO
NULL     root     system         public              eventlog                           GRANT           NULL          NO
NULL     root     system         public              eventlog                           INSERT          NULL          NO
NULL     root     system         public              eventlog                           SELECT          NULL          YES
NULL     root     system         public              eventlog                           UPDATE          NULL          NO
NULL     admin    system         public              jobs                               DELETE          NULL          NO
NULL     admin    system         public              jobs                               GRANT           NULL          NO
NULL     admin    system         public              jobs                               INSERT          NULL          NO
NULL     admin    system         public              jobs                               SELECT          NULL          YES
NULL     admin    system         public              jobs                               UPDATE          NULL          NO
NULL     root     system         public              jobs                               DELETE          NULL          NO
NULL     root     system         public              jobs                               GRANT           NULL          NO
NULL     root     system         public              jobs                               INSERT          NULL          NO
NULL     root     system         public              jobs                               SELECT          NULL          YES
NULL     root     system         public              jobs                               UPDATE          NULL          NO
NULL     admin    system         public              lease                              DELETE          NULL          NO
NULL     admin    system         public              lease                              GRANT           NULL          NO
NULL     admin    system         public              lease                              INSERT          NULL          NO
NULL     admin    system         public              lease                              SELECT          NULL          YES
NULL     admin    system         public              lease                              UPDATE          NULL          NO
NULL     root     system         public              lease                              DELETE          NULL          NO
NULL     root     system         public              lease                              GRANT           NULL          NO
NULL     root     system         public              lease                              INSERT          NULL          NO
NULL     root     system         public              lease                              SELECT          NULL          YES
NULL     root     system         public              lease                              UPDATE          NULL          NO
NULL     admin    system         public              locations                          DELETE          NULL          NO
NULL     admin    system         public              locations                          GRANT           NULL          NO
NULL     admin    system         public              locations                          INSERT          NULL          NO
NULL     admin    system         public              locations                          SELECT          NULL          YES
NULL     admin    system         public              locations                          UPDATE          NULL          NO
NULL     root     system         public              locations                          DELETE          NULL          NO
NULL     root     system         public              locations                          GRANT           NULL          NO
NULL     root     system         public              locations                          INSERT          NULL          NO
NULL     root     system         public              locations                          SELECT          NULL          YES
NULL     root     system         public              locations                          UPDATE          NULL          NO
NULL     admin    system         public              namespace                          GRANT           NULL          NO
NULL     admin    system         public              namespace                          SELECT          NULL          YES
NULL     root     system         public              namespace                          GRANT           NULL          NO
NULL     root     system         public              namespace                          SELECT          NULL          YES
NULL     admin    system         public              rangelog                           DELETE          NULL          NO
NULL     admin    system         public              rangelog                           GRANT           NULL          NO
NULL     admin    system         public              rangelog                           INSERT          NULL          NO
NULL     admin    system         public              rangelog                           SELECT          NULL          YES
NULL     admin    system         public              rangelog                           UPDATE          NULL          NO
NULL     root     system         public              rangelog                           DELETE          NULL          NO
NULL     root     system         public              rangelog                           GRANT           NULL          NO
NULL     root     system         public              rangelog                           INSERT          NULL          NO
NULL     root     system         public              rangelog                           SELECT          NULL          YES
NULL     root     system         public              rangelog                           UPDATE          NULL          NO
NULL     admin    system         public              role_members                       DELETE          NULL          NO
NULL     admin    system         public              role_members                       GRANT           NULL          NO
NULL     admin    system         public              role_members                       INSERT          NULL          NO
NULL     admin    system         public              role_members                       SELECT          NULL          YES
NULL     admin    system         public              role_members                       UPDATE          NULL          NO
NULL     root     system         public              role_members                       DELETE          NULL          NO
NULL     root     system         public              role_members                       GRANT           NULL          NO
NULL     root     system         public              role_members                       INSERT          NULL          NO
NULL     root     system         public              role_members                       SELECT          NULL          YES
NULL     root     system         public              role_members                       UPDATE          NULL          NO
NULL     admin    system         public              settings                           DELETE          NULL          NO
NULL     admin    system         public              settings                           GRANT           NULL          NO
NULL     admin    system         public              settings                           INSERT          NULL          NO
NULL     admin    system         public              settings                           SELECT          NULL          YES
NULL     admin    system         public              settings                           UPDATE          NULL          NO
NULL     root     system         public              settings                           DELETE          NULL          NO
NULL     root     system         public              settings                           GRANT           NULL          NO
NULL     root     system         public              settings                           INSERT          NULL          NO
NULL     root     system         public              settings                           SELECT          NULL          YES
NULL     root     system         public              settings                           UPDATE          NULL          NO
NULL     admin    system         public              table_statistics                   DELETE          NULL          NO
NULL     admin    system         public              table_statistics                   GRANT           NULL          NO
NULL     admin    system         public              table_statistics                   INSERT          NULL          NO
NULL     admin    system         public              table_statistics                   SELECT          NULL          YES
NULL     admin    system         public              table_statistics                   UPDATE          NULL          NO
NULL     root     system         public              table_statistics                   DELETE          NULL          NO
NULL     root     system         public              table_statistics                   GRANT           NULL          NO
NULL     root     system         public              table_statistics                   INSERT          NULL          NO
NULL     root     system         public              table_statistics                   SELECT          NULL          YES
NULL     root     system         public              table_statistics                   UPDATE          NULL          NO
NULL     admin    system         public              ui                                 DELETE          NULL          NO
NULL     admin    system         public              ui                                 GRANT           NULL          NO
NULL     admin    system         public              ui                                 INSERT          NULL          NO
NULL     admin    system         public              ui                                 SELECT          NULL          YES
NULL     admin    system         public              ui                                 UPDATE          NULL          NO
NULL     root     system         public              ui                                 DELETE          NULL          NO
NULL     root     system         public              ui                                 GRANT           NULL          NO
NULL     root     system         public              ui                                 INSERT          NULL          NO
NULL     root     system         public              ui                                 SELECT          NULL          YES
NULL     root     system         public              ui                                 UPDATE          NULL          NO
NULL     admin    system         public              users                              DELETE          NULL          NO
NULL     admin    system         public              users                              GRANT           NULL          NO
NULL     admin    system         public              users                              INSERT          NULL          NO
NULL     admin    system         public              users                              SELECT          NULL          YES
NULL     admin    system         public              users                              UPDATE          NULL          NO
NULL     root     system         public              users                              DELETE          NULL          NO
NULL     root     system         public              users                              GRANT           NULL          NO
NULL     root     system         public              users                              INSERT          NULL          NO
NULL     root     system         public              users                              SELECT          NULL          YES
NULL     root     system         public              users                              UPDATE          NULL          NO
NULL     admin    system         public              web_sessions                       DELETE          NULL          NO
NULL     admin    system         public              web_sessions                       GRANT           NULL          NO
NULL     admin    system         public              web_sessions                       INSERT          NULL          NO
NULL     admin    system         public              web_sessions                       SELECT          NULL          YES
NULL     admin    system         public              web_sessions                       UPDATE          NULL          NO
NULL     root     system         public              web_sessions                       DELETE          NULL          NO
NULL     root     system         public              web_sessions                       GRANT           NULL          NO
NULL     root     system         public              web_sessions                       INSERT          NULL          NO
NULL     root     system         public              web_sessions                       SELECT          NULL          YES
NULL     root     system         public              web_sessions                       UPDATE          NULL          NO
NULL     admin    system         public              zones                              DELETE          NULL          NO
NULL     admin    system         public              zones                              GRANT           NULL          NO
NULL     admin    system         public              zones                              INSERT          NULL          NO
NULL     admin    system         public              zones                              SELECT          NULL          YES
NULL     admin    system         public              zones                              UPDATE          NULL          NO
NULL     root     system         public              zones                              DELETE          NULL          NO
NULL     root     system         public              zones                              GRANT           NULL          NO
NULL     root     system         public              zones                              INSERT          NULL          NO
NULL     root     system         public              zones                              SELECT          NULL          YES
NULL     root     system         public              zones                              UPDATE          NULL          NO

query TTTTTTTT colnames
SELECT * FROM system.information_schema.role_table_grants
----
grantor  grantee  table_catalog  table_schema        table_name                         privilege_type  is_grantable  with_hierarchy
NULL     public   system         crdb_internal       backward_dependencies              SELECT          NULL          YES
NULL     public   system         crdb_internal       builtin_functions                  SELECT          NULL          YES
NULL     public   system         crdb_internal       cluster_queries                    SELECT          NULL          YES
NULL     public   system         crdb_internal       cluster_sessions                   SELECT          NULL          YES
NULL     public   system         crdb_internal       cluster_settings                   SELECT          NULL          YES
NULL     public   system         crdb_internal       create_statements                  SELECT          NULL          YES
NULL     public   system         crdb_internal       feature_usage                      SELECT          NULL          YES
NULL     public   system         crdb_internal       forward_dependencies               SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_alerts                      SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_liveness                    SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_network                     SELECT          NULL          YES
NULL     public   system         crdb_internal       gossip_nodes                       SELECT          NULL          YES
NULL     public   system         crdb_internal       index_columns                      SELECT          NULL          YES
NULL     public   system         crdb_internal       jobs                               SELECT          NULL          YES
NULL     public   system         crdb_internal       kv_node_status                     SELECT          NULL          YES
NULL     public   system         crdb_internal       kv_store_status                    SELECT          NULL          YES
NULL     public   system         crdb_internal       leases                             SELECT          NULL          YES
NULL     public   system         crdb_internal       node_build_info                    SELECT          NULL          YES
NULL     public   system         crdb_internal       node_metrics                       SELECT          NULL          YES
NULL     public   system         crdb_internal       node_queries                       SELECT          NULL          YES
NULL     public   system         crdb_internal       node_runtime_info                  SELECT          NULL          YES
NULL     public   system         crdb_internal       node_sessions                      SELECT          NULL          YES
NULL     public   system         crdb_internal       node_statement_statistics          SELECT          NULL          YES
NULL     public   system         crdb_internal       partitions                         SELECT          NULL          YES
NULL     public   system         crdb_internal       predefined_comments                SELECT          NULL          YES
NULL     public   system         crdb_internal       ranges                             SELECT          NULL          YES
NULL     public   system         crdb_internal       ranges_no_leases                   SELECT          NULL          YES
NULL     public   system         crdb_internal       schema_changes                     SELECT          NULL          YES
NULL     public   system         crdb_internal       session_trace                      SELECT          NULL          YES
NULL     public   system         crdb_internal       session_variables                  SELECT          NULL          YES
NULL     public   system         crdb_internal       table_columns                      SELECT          NULL          YES
NULL     public   system         crdb_internal       table_indexes                      SELECT          NULL          YES
NULL     public   system         crdb_internal       tables                             SELECT          NULL          YES
NULL     public   system         crdb_internal       zones                              SELECT          NULL          YES
NULL     public   system         information_schema  administrable_role_authorizations  SELECT          NULL          YES
NULL     public   system         information_schema  applicable_roles                   SELECT          NULL          YES
NULL     public   system         information_schema  column_privileges                  SELECT          NULL          YES
NULL     public   system         information_schema  columns                            SELECT          NULL          YES
NULL     public   system         information_schema  constraint_column_usage            SELECT          NULL          YES
NULL     public   system         information_schema  enabled_roles                      SELECT          NULL          YES
NULL     public   system         information_schema  key_column_usage                   SELECT          NULL          YES
NULL     public   system         information_schema  parameters                         SELECT          NULL          YES
NULL     public   system         information_schema  referential_constraints            SELECT          NULL          YES
NULL     public   system         information_schema  role_table_grants                  SELECT          NULL          YES
NULL     public   system         information_schema  routines                           SELECT          NULL          YES
NULL     public   system         information_schema  schema_privileges                  SELECT          NULL          YES
NULL     public   system         information_schema  schemata                           SELECT          NULL          YES
NULL     public   system         information_schema  sequences                          SELECT          NULL          YES
NULL     public   system         information_schema  statistics                         SELECT          NULL          YES
NULL     public   system         information_schema  table_constraints                  SELECT          NULL          YES
NULL     public   system         information_schema  table_privileges                   SELECT          NULL          YES
NULL     public   system         information_schema  tables                             SELECT          NULL          YES
NULL     public   system         information_schema  user_privileges                    SELECT          NULL          YES
NULL     public   system         information_schema  views                              SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_am                              SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_attrdef                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_attribute                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_auth_members                    SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_class                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_collation                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_constraint                      SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_database                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_depend                          SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_description                     SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_enum                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_extension                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_foreign_data_wrapper            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_foreign_server                  SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_foreign_table                   SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_index                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_indexes                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_inherits                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_language                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_namespace                       SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_operator                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_proc                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_range                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_rewrite                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_roles                           SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_seclabel                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_sequence                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_settings                        SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_shdescription                   SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_shseclabel                      SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_stat_activity                   SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_tables                          SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_tablespace                      SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_trigger                         SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_type                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_user                            SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_user_mapping                    SELECT          NULL          YES
NULL     public   system         pg_catalog          pg_views                           SELECT          NULL          YES
NULL     admin    system         public              namespace                          GRANT           NULL          NO
NULL     admin    system         public              namespace                          SELECT          NULL          YES
NULL     root     system         public              namespace                          GRANT           NULL          NO
NULL     root     system         public              namespace                          SELECT          NULL          YES
NULL     admin    system         public              descriptor                         GRANT           NULL          NO
NULL     admin    system         public              descriptor                         SELECT          NULL          YES
NULL     root     system         public              descriptor                         GRANT           NULL          NO
NULL     root     system         public              descriptor                         SELECT          NULL          YES
NULL     admin    system         public              users                              DELETE          NULL          NO
NULL     admin    system         public              users                              GRANT           NULL          NO
NULL     admin    system         public              users                              INSERT          NULL          NO
NULL     admin    system         public              users                              SELECT          NULL          YES
NULL     admin    system         public              users                              UPDATE          NULL          NO
NULL     root     system         public              users                              DELETE          NULL          NO
NULL     root     system         public              users                              GRANT           NULL          NO
NULL     root     system         public              users                              INSERT          NULL          NO
NULL     root     system         public              users                              SELECT          NULL          YES
NULL     root     system         public              users                              UPDATE          NULL          NO
NULL     admin    system         public              zones                              DELETE          NULL          NO
NULL     admin    system         public              zones                              GRANT           NULL          NO
NULL     admin    system         public              zones                              INSERT          NULL          NO
NULL     admin    system         public              zones                              SELECT          NULL          YES
NULL     admin    system         public              zones                              UPDATE          NULL          NO
NULL     root     system         public              zones                              DELETE          NULL          NO
NULL     root     system         public              zones                              GRANT           NULL          NO
NULL     root     system         public              zones                              INSERT          NULL          NO
NULL     root     system         public              zones                              SELECT          NULL          YES
NULL     root     system         public              zones                              UPDATE          NULL          NO
NULL     admin    system         public              settings                           DELETE          NULL          NO
NULL     admin    system         public              settings                           GRANT           NULL          NO
NULL     admin    system         public              settings                           INSERT          NULL          NO
NULL     admin    system         public              settings                           SELECT          NULL          YES
NULL     admin    system         public              settings                           UPDATE          NULL          NO
NULL     root     system         public              settings                           DELETE          NULL          NO
NULL     root     system         public              settings                           GRANT           NULL          NO
NULL     root     system         public              settings                           INSERT          NULL          NO
NULL     root     system         public              settings                           SELECT          NULL          YES
NULL     root     system         public              settings                           UPDATE          NULL          NO
NULL     admin    system         public              lease                              DELETE          NULL          NO
NULL     admin    system         public              lease                              GRANT           NULL          NO
NULL     admin    system         public              lease                              INSERT          NULL          NO
NULL     admin    system         public              lease                              SELECT          NULL          YES
NULL     admin    system         public              lease                              UPDATE          NULL          NO
NULL     root     system         public              lease                              DELETE          NULL          NO
NULL     root     system         public              lease                              GRANT           NULL          NO
NULL     root     system         public              lease                              INSERT          NULL          NO
NULL     root     system         public              lease                              SELECT          NULL          YES
NULL     root     system         public              lease                              UPDATE          NULL          NO
NULL     admin    system         public              eventlog                           DELETE          NULL          NO
NULL     admin    system         public              eventlog                           GRANT           NULL          NO
NULL     admin    system         public              eventlog                           INSERT          NULL          NO
NULL     admin    system         public              eventlog                           SELECT          NULL          YES
NULL     admin    system         public              eventlog                           UPDATE          NULL          NO
NULL     root     system         public              eventlog                           DELETE          NULL          NO
NULL     root     system         public              eventlog                           GRANT           NULL          NO
NULL     root     system         public              eventlog                           INSERT          NULL          NO
NULL     root     system         public              eventlog                           SELECT          NULL          YES
NULL     root     system         public              eventlog                           UPDATE          NULL          NO
NULL     admin    system         public              rangelog                           DELETE          NULL          NO
NULL     admin    system         public              rangelog                           GRANT           NULL          NO
NULL     admin    system         public              rangelog                           INSERT          NULL          NO
NULL     admin    system         public              rangelog                           SELECT          NULL          YES
NULL     admin    system         public              rangelog                           UPDATE          NULL          NO
NULL     root     system         public              rangelog                           DELETE          NULL          NO
NULL     root     system         public              rangelog                           GRANT           NULL          NO
NULL     root     system         public              rangelog                           INSERT          NULL          NO
NULL     root     system         public              rangelog                           SELECT          NULL          YES
NULL     root     system         public              rangelog                           UPDATE          NULL          NO
NULL     admin    system         public              ui                                 DELETE          NULL          NO
NULL     admin    system         public              ui                                 GRANT           NULL          NO
NULL     admin    system         public              ui                                 INSERT          NULL          NO
NULL     admin    system         public              ui                                 SELECT          NULL          YES
NULL     admin    system         public              ui                                 UPDATE          NULL          NO
NULL     root     system         public              ui                                 DELETE          NULL          NO
NULL     root     system         public              ui                                 GRANT           NULL          NO
NULL     root     system         public              ui                                 INSERT          NULL          NO
NULL     root     system         public              ui                                 SELECT          NULL          YES
NULL     root     system         public              ui                                 UPDATE          NULL          NO
NULL     admin    system         public              jobs                               DELETE          NULL          NO
NULL     admin    system         public              jobs                               GRANT           NULL          NO
NULL     admin    system         public              jobs                               INSERT          NULL          NO
NULL     admin    system         public              jobs                               SELECT          NULL          YES
NULL     admin    system         public              jobs                               UPDATE          NULL          NO
NULL     root     system         public              jobs                               DELETE          NULL          NO
NULL     root     system         public              jobs                               GRANT           NULL          NO
NULL     root     system         public              jobs                               INSERT          NULL          NO
NULL     root     system         public              jobs                               SELECT          NULL          YES
NULL     root     system         public              jobs                               UPDATE          NULL          NO
NULL     admin    system         public              web_sessions                       DELETE          NULL          NO
NULL     admin    system         public              web_sessions                       GRANT           NULL          NO
NULL     admin    system         public              web_sessions                       INSERT          NULL          NO
NULL     admin    system         public              web_sessions                       SELECT          NULL          YES
NULL     admin    system         public              web_sessions                       UPDATE          NULL          NO
NULL     root     system         public              web_sessions                       DELETE          NULL          NO
NULL     root     system         public              web_sessions                       GRANT           NULL          NO
NULL     root     system         public              web_sessions                       INSERT          NULL          NO
NULL     root     system         public              web_sessions                       SELECT          NULL          YES
NULL     root     system         public              web_sessions                       UPDATE          NULL          NO
NULL     admin    system         public              table_statistics                   DELETE          NULL          NO
NULL     admin    system         public              table_statistics                   GRANT           NULL          NO
NULL     admin    system         public              table_statistics                   INSERT          NULL          NO
NULL     admin    system         public              table_statistics                   SELECT          NULL          YES
NULL     admin    system         public              table_statistics                   UPDATE          NULL          NO
NULL     root     system         public              table_statistics                   DELETE          NULL          NO
NULL     root     system         public              table_statistics                   GRANT           NULL          NO
NULL     root     system         public              table_statistics                   INSERT          NULL          NO
NULL     root     system         public              table_statistics                   SELECT          NULL          YES
NULL     root     system         public              table_statistics                   UPDATE          NULL          NO
NULL     admin    system         public              locations                          DELETE          NULL          NO
NULL     admin    system         public              locations                          GRANT           NULL          NO
NULL     admin    system         public              locations                          INSERT          NULL          NO
NULL     admin    system         public              locations                          SELECT          NULL          YES
NULL     admin    system         public              locations                          UPDATE          NULL          NO
NULL     root     system         public              locations                          DELETE          NULL          NO
NULL     root     system         public              locations                          GRANT           NULL          NO
NULL     root     system         public              locations                          INSERT          NULL          NO
NULL     root     system         public              locations                          SELECT          NULL          YES
NULL     root     system         public              locations                          UPDATE          NULL          NO
NULL     admin    system         public              role_members                       DELETE          NULL          NO
NULL     admin    system         public              role_members                       GRANT           NULL          NO
NULL     admin    system         public              role_members                       INSERT          NULL          NO
NULL     admin    system         public              role_members                       SELECT          NULL          YES
NULL     admin    system         public              role_members                       UPDATE          NULL          NO
NULL     root     system         public              role_members                       DELETE          NULL          NO
NULL     root     system         public              role_members                       GRANT           NULL          NO
NULL     root     system         public              role_members                       INSERT          NULL          NO
NULL     root     system         public              role_members                       SELECT          NULL          YES
NULL     root     system         public              role_members                       UPDATE          NULL          NO
NULL     admin    system         public              comments                           DELETE          NULL          NO
NULL     admin    system         public              comments                           GRANT           NULL          NO
NULL     admin    system         public              comments                           INSERT          NULL          NO
NULL     admin    system         public              comments                           SELECT          NULL          YES
NULL     admin    system         public              comments                           UPDATE          NULL          NO
NULL     public   system         public              comments                           DELETE          NULL          NO
NULL     public   system         public              comments                           GRANT           NULL          NO
NULL     public   system         public              comments                           INSERT          NULL          NO
NULL     public   system         public              comments                           SELECT          NULL          YES
NULL     public   system         public              comments                           UPDATE          NULL          NO
NULL     root     system         public              comments                           DELETE          NULL          NO
NULL     root     system         public              comments                           GRANT           NULL          NO
NULL     root     system         public              comments                           INSERT          NULL          NO
NULL     root     system         public              comments                           SELECT          NULL          YES
NULL     root     system         public              comments                           UPDATE          NULL          NO

statement ok
CREATE TABLE other_db.xyz (i INT)

statement ok
CREATE VIEW other_db.abc AS SELECT i from other_db.xyz

query TTTTTTTT colnames
SELECT * FROM other_db.information_schema.table_privileges WHERE TABLE_SCHEMA = 'public'
----
grantor  grantee   table_catalog  table_schema  table_name  privilege_type  is_grantable  with_hierarchy
NULL     admin     other_db       public        xyz         ALL             NULL          NO
NULL     root      other_db       public        xyz         ALL             NULL          NO
NULL     testuser  other_db       public        xyz         SELECT          NULL          YES
NULL     admin     other_db       public        abc         ALL             NULL          NO
NULL     root      other_db       public        abc         ALL             NULL          NO
NULL     testuser  other_db       public        abc         SELECT          NULL          YES

query TTTTTTTT colnames
SELECT * FROM other_db.information_schema.role_table_grants WHERE TABLE_SCHEMA = 'public'
----
grantor  grantee   table_catalog  table_schema  table_name  privilege_type  is_grantable  with_hierarchy
NULL     admin     other_db       public        xyz         ALL             NULL          NO
NULL     root      other_db       public        xyz         ALL             NULL          NO
NULL     testuser  other_db       public        xyz         SELECT          NULL          YES
NULL     admin     other_db       public        abc         ALL             NULL          NO
NULL     root      other_db       public        abc         ALL             NULL          NO
NULL     testuser  other_db       public        abc         SELECT          NULL          YES

statement ok
GRANT UPDATE ON other_db.xyz TO testuser

query TTTTTTTT colnames
SELECT * FROM other_db.information_schema.table_privileges WHERE TABLE_SCHEMA = 'public'
----
grantor  grantee   table_catalog  table_schema  table_name  privilege_type  is_grantable  with_hierarchy
NULL     admin     other_db       public        xyz         ALL             NULL          NO
NULL     root      other_db       public        xyz         ALL             NULL          NO
NULL     testuser  other_db       public        xyz         SELECT          NULL          YES
NULL     testuser  other_db       public        xyz         UPDATE          NULL          NO
NULL     admin     other_db       public        abc         ALL             NULL          NO
NULL     root      other_db       public        abc         ALL             NULL          NO
NULL     testuser  other_db       public        abc         SELECT          NULL          YES

query TTTTTTTT colnames
SELECT * FROM other_db.information_schema.role_table_grants WHERE TABLE_SCHEMA = 'public'
----
grantor  grantee   table_catalog  table_schema  table_name  privilege_type  is_grantable  with_hierarchy
NULL     admin     other_db       public        xyz         ALL             NULL          NO
NULL     root      other_db       public        xyz         ALL             NULL          NO
NULL     testuser  other_db       public        xyz         SELECT          NULL          YES
NULL     testuser  other_db       public        xyz         UPDATE          NULL          NO
NULL     admin     other_db       public        abc         ALL             NULL          NO
NULL     root      other_db       public        abc         ALL             NULL          NO
NULL     testuser  other_db       public        abc         SELECT          NULL          YES

# testuser can read permissions as well
user testuser

statement ok
SET DATABASE = other_db

query TTTTTTTT colnames
SELECT * FROM information_schema.table_privileges WHERE TABLE_SCHEMA = 'public'
----
grantor  grantee   table_catalog  table_schema  table_name  privilege_type  is_grantable  with_hierarchy
NULL     admin     other_db       public        xyz         ALL             NULL          NO
NULL     root      other_db       public        xyz         ALL             NULL          NO
NULL     testuser  other_db       public        xyz         SELECT          NULL          YES
NULL     testuser  other_db       public        xyz         UPDATE          NULL          NO
NULL     admin     other_db       public        abc         ALL             NULL          NO
NULL     root      other_db       public        abc         ALL             NULL          NO
NULL     testuser  other_db       public        abc         SELECT          NULL          YES

query TTTTTTTT colnames
SELECT * FROM information_schema.role_table_grants WHERE TABLE_SCHEMA = 'public'
----
grantor  grantee   table_catalog  table_schema  table_name  privilege_type  is_grantable  with_hierarchy
NULL     admin     other_db       public        xyz         ALL             NULL          NO
NULL     root      other_db       public        xyz         ALL             NULL          NO
NULL     testuser  other_db       public        xyz         SELECT          NULL          YES
NULL     testuser  other_db       public        xyz         UPDATE          NULL          NO
NULL     admin     other_db       public        abc         ALL             NULL          NO
NULL     root      other_db       public        abc         ALL             NULL          NO
NULL     testuser  other_db       public        abc         SELECT          NULL          YES

statement ok
SET DATABASE = test

user root

## information_schema.statistics

statement ok
CREATE TABLE other_db.teststatics(id INT PRIMARY KEY, c INT, d INT, e STRING, INDEX idx_c(c), UNIQUE INDEX idx_cd(c,d))

query TTTTTTITIITTT colnames
SELECT * FROM other_db.information_schema.statistics WHERE table_schema='public' AND table_name='teststatics' ORDER BY INDEX_SCHEMA,INDEX_NAME,SEQ_IN_INDEX
----
table_catalog  table_schema  table_name   non_unique  index_schema  index_name  seq_in_index  column_name  COLLATION  cardinality  direction  storing  implicit
other_db       public        teststatics  YES         public        idx_c       1             c            NULL       NULL         ASC        NO       NO
other_db       public        teststatics  YES         public        idx_c       2             id           NULL       NULL         ASC        NO       YES
other_db       public        teststatics  NO          public        idx_cd      1             c            NULL       NULL         ASC        NO       NO
other_db       public        teststatics  NO          public        idx_cd      2             d            NULL       NULL         ASC        NO       NO
other_db       public        teststatics  NO          public        idx_cd      3             id           NULL       NULL         ASC        NO       YES
other_db       public        teststatics  NO          public        primary     1             id           NULL       NULL         ASC        NO       NO

# Verify information_schema.views
statement ok
CREATE VIEW other_db.v_xyz AS SELECT i FROM other_db.xyz

query TTTTT colnames
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION
FROM other_db.information_schema.views
WHERE TABLE_NAME='v_xyz'
----
table_catalog  table_schema  table_name  view_definition                    check_option
other_db       public        v_xyz       SELECT i FROM other_db.public.xyz  NULL

query TTTTT colnames
SELECT IS_UPDATABLE, IS_INSERTABLE_INTO, IS_TRIGGER_UPDATABLE, IS_TRIGGER_DELETABLE, IS_TRIGGER_INSERTABLE_INTO
FROM other_db.information_schema.views
WHERE TABLE_NAME='v_xyz'
----
is_updatable  is_insertable_into  is_trigger_updatable  is_trigger_deletable  is_trigger_insertable_into
NO            NO                  NO                    NO                    NO

statement ok
SET DATABASE = 'test'

statement ok
DROP DATABASE other_db CASCADE

#Verify  information_schema.user_privileges

query TTTT colnames,rowsort
SELECT * FROM information_schema.user_privileges ORDER BY grantee,privilege_type
----
grantee  table_catalog  privilege_type  is_grantable
admin    test           ALL             NULL
admin    test           CREATE          NULL
admin    test           DELETE          NULL
admin    test           DROP            NULL
admin    test           GRANT           NULL
admin    test           INSERT          NULL
admin    test           SELECT          NULL
admin    test           UPDATE          NULL
root     test           ALL             NULL
root     test           CREATE          NULL
root     test           DELETE          NULL
root     test           DROP            NULL
root     test           GRANT           NULL
root     test           INSERT          NULL
root     test           SELECT          NULL
root     test           UPDATE          NULL

# information_schema.sequences

statement ok
SET DATABASE = test

query TTTTIIITTTTT
SELECT * FROM information_schema.sequences
----

statement ok
CREATE SEQUENCE test_seq

statement ok
CREATE SEQUENCE test_seq_2 INCREMENT -1 MINVALUE 5 MAXVALUE 1000 START WITH 15


query TTTTIIITTTTT colnames
SELECT * FROM information_schema.sequences
----
sequence_catalog  sequence_schema  sequence_name  data_type  numeric_precision  numeric_precision_radix  numeric_scale  start_value  minimum_value  maximum_value        increment  cycle_option
test              public           test_seq       bigint     64                 2                        0              1            1              9223372036854775807  1          NO
test              public           test_seq_2     bigint     64                 2                        0              15           5              1000                 -1         NO

statement ok
CREATE DATABASE other_db

statement ok
SET DATABASE = other_db

# Sequences in one database can't be seen from another database.

query TTTTIIITTTTT
SELECT * FROM information_schema.sequences
----

statement ok
SET DATABASE = test

statement ok
DROP DATABASE other_db CASCADE

# test information_schema.column_privileges
query TTBTTTB colnames
SHOW COLUMNS FROM information_schema.column_privileges
----
column_name     data_type  is_nullable  column_default  generation_expression  indices  is_hidden
grantor         STRING     true         NULL            ·                      {}       false
grantee         STRING     false        NULL            ·                      {}       false
table_catalog   STRING     false        NULL            ·                      {}       false
table_schema    STRING     false        NULL            ·                      {}       false
table_name      STRING     false        NULL            ·                      {}       false
column_name     STRING     false        NULL            ·                      {}       false
privilege_type  STRING     false        NULL            ·                      {}       false
is_grantable    STRING     true         NULL            ·                      {}       false


# test information_schema.routines
query TTBTTTB colnames
SHOW COLUMNS FROM information_schema.routines
----
column_name                          data_type    is_nullable  column_default  generation_expression  indices  is_hidden
specific_catalog                     STRING       true         NULL            ·                      {}       false
specific_schema                      STRING       true         NULL            ·                      {}       false
specific_name                        STRING       true         NULL            ·                      {}       false
routine_catalog                      STRING       true         NULL            ·                      {}       false
routine_schema                       STRING       true         NULL            ·                      {}       false
routine_name                         STRING       true         NULL            ·                      {}       false
routine_type                         STRING       true         NULL            ·                      {}       false
module_catalog                       STRING       true         NULL            ·                      {}       false
module_schema                        STRING       true         NULL            ·                      {}       false
module_name                          STRING       true         NULL            ·                      {}       false
udt_catalog                          STRING       true         NULL            ·                      {}       false
udt_schema                           STRING       true         NULL            ·                      {}       false
udt_name                             STRING       true         NULL            ·                      {}       false
data_type                            STRING       true         NULL            ·                      {}       false
character_maximum_length             INT8         true         NULL            ·                      {}       false
character_octet_length               INT8         true         NULL            ·                      {}       false
character_set_catalog                STRING       true         NULL            ·                      {}       false
character_set_schema                 STRING       true         NULL            ·                      {}       false
character_set_name                   STRING       true         NULL            ·                      {}       false
collation_catalog                    STRING       true         NULL            ·                      {}       false
collation_schema                     STRING       true         NULL            ·                      {}       false
collation_name                       STRING       true         NULL            ·                      {}       false
numeric_precision                    INT8         true         NULL            ·                      {}       false
numeric_precision_radix              INT8         true         NULL            ·                      {}       false
numeric_scale                        INT8         true         NULL            ·                      {}       false
datetime_precision                   INT8         true         NULL            ·                      {}       false
interval_type                        STRING       true         NULL            ·                      {}       false
interval_precision                   STRING       true         NULL            ·                      {}       false
type_udt_catalog                     STRING       true         NULL            ·                      {}       false
type_udt_schema                      STRING       true         NULL            ·                      {}       false
type_udt_name                        STRING       true         NULL            ·                      {}       false
scope_catalog                        STRING       true         NULL            ·                      {}       false
scope_name                           STRING       true         NULL            ·                      {}       false
maximum_cardinality                  INT8         true         NULL            ·                      {}       false
dtd_identifier                       STRING       true         NULL            ·                      {}       false
routine_body                         STRING       true         NULL            ·                      {}       false
routine_definition                   STRING       true         NULL            ·                      {}       false
external_name                        STRING       true         NULL            ·                      {}       false
external_language                    STRING       true         NULL            ·                      {}       false
parameter_style                      STRING       true         NULL            ·                      {}       false
is_deterministic                     STRING       true         NULL            ·                      {}       false
sql_data_access                      STRING       true         NULL            ·                      {}       false
is_null_call                         STRING       true         NULL            ·                      {}       false
sql_path                             STRING       true         NULL            ·                      {}       false
schema_level_routine                 STRING       true         NULL            ·                      {}       false
max_dynamic_result_sets              INT8         true         NULL            ·                      {}       false
is_user_defined_cast                 STRING       true         NULL            ·                      {}       false
is_implicitly_invocable              STRING       true         NULL            ·                      {}       false
security_type                        STRING       true         NULL            ·                      {}       false
to_sql_specific_catalog              STRING       true         NULL            ·                      {}       false
to_sql_specific_schema               STRING       true         NULL            ·                      {}       false
to_sql_specific_name                 STRING       true         NULL            ·                      {}       false
as_locator                           STRING       true         NULL            ·                      {}       false
created                              TIMESTAMPTZ  true         NULL            ·                      {}       false
last_altered                         TIMESTAMPTZ  true         NULL            ·                      {}       false
new_savepoint_level                  STRING       true         NULL            ·                      {}       false
is_udt_dependent                     STRING       true         NULL            ·                      {}       false
result_cast_from_data_type           STRING       true         NULL            ·                      {}       false
result_cast_as_locator               STRING       true         NULL            ·                      {}       false
result_cast_char_max_length          INT8         true         NULL            ·                      {}       false
result_cast_char_octet_length        STRING       true         NULL            ·                      {}       false
result_cast_char_set_catalog         STRING       true         NULL            ·                      {}       false
result_cast_char_set_schema          STRING       true         NULL            ·                      {}       false
result_cast_char_set_name            STRING       true         NULL            ·                      {}       false
result_cast_collation_catalog        STRING       true         NULL            ·                      {}       false
result_cast_collation_schema         STRING       true         NULL            ·                      {}       false
result_cast_collation_name           STRING       true         NULL            ·                      {}       false
result_cast_numeric_precision        INT8         true         NULL            ·                      {}       false
result_cast_numeric_precision_radix  INT8         true         NULL            ·                      {}       false
result_cast_numeric_scale            INT8         true         NULL            ·                      {}       false
result_cast_datetime_precision       STRING       true         NULL            ·                      {}       false
result_cast_interval_type            STRING       true         NULL            ·                      {}       false
result_cast_interval_precision       INT8         true         NULL            ·                      {}       false
result_cast_type_udt_catalog         STRING       true         NULL            ·                      {}       false
result_cast_type_udt_schema          STRING       true         NULL            ·                      {}       false
result_cast_type_udt_name            STRING       true         NULL            ·                      {}       false
result_cast_scope_catalog            STRING       true         NULL            ·                      {}       false
result_cast_scope_schema             STRING       true         NULL            ·                      {}       false
result_cast_scope_name               STRING       true         NULL            ·                      {}       false
result_cast_maximum_cardinality      INT8         true         NULL            ·                      {}       false
result_cast_dtd_identifier           STRING       true         NULL            ·                      {}       false

query TTTTTTTTTTTTTTIITTTTTTIIIITTTTTTTITTTTTTTTTTTITTTTTTTTTTTTTITTTTTTTIIITTITTTTTTIT colnames
SELECT * FROM information_schema.routines
----
specific_catalog  specific_schema  specific_name  routine_catalog  routine_schema  routine_name  routine_type  module_catalog  module_schema  module_name  udt_catalog  udt_schema  udt_name  data_type  character_maximum_length  character_octet_length  character_set_catalog  character_set_schema  character_set_name  collation_catalog  collation_schema  collation_name  numeric_precision  numeric_precision_radix  numeric_scale  datetime_precision  interval_type  interval_precision  type_udt_catalog  type_udt_schema  type_udt_name  scope_catalog  scope_name  maximum_cardinality  dtd_identifier  routine_body  routine_definition  external_name  external_language  parameter_style  is_deterministic  sql_data_access  is_null_call  sql_path  schema_level_routine  max_dynamic_result_sets  is_user_defined_cast  is_implicitly_invocable  security_type  to_sql_specific_catalog  to_sql_specific_schema  to_sql_specific_name  as_locator  created  last_altered  new_savepoint_level  is_udt_dependent  result_cast_from_data_type  result_cast_as_locator  result_cast_char_max_length  result_cast_char_octet_length  result_cast_char_set_catalog  result_cast_char_set_schema  result_cast_char_set_name  result_cast_collation_catalog  result_cast_collation_schema  result_cast_collation_name  result_cast_numeric_precision  result_cast_numeric_precision_radix  result_cast_numeric_scale  result_cast_datetime_precision  result_cast_interval_type  result_cast_interval_precision  result_cast_type_udt_catalog  result_cast_type_udt_schema  result_cast_type_udt_name  result_cast_scope_catalog  result_cast_scope_schema  result_cast_scope_name  result_cast_maximum_cardinality  result_cast_dtd_identifier

# test information_schema.parameters
query TTBTTTB colnames
SHOW COLUMNS FROM information_schema.parameters
----
column_name               data_type  is_nullable  column_default  generation_expression  indices  is_hidden
specific_catalog          STRING     true         NULL            ·                      {}       false
specific_schema           STRING     true         NULL            ·                      {}       false
specific_name             STRING     true         NULL            ·                      {}       false
ordinal_position          INT8       true         NULL            ·                      {}       false
parameter_mode            STRING     true         NULL            ·                      {}       false
is_result                 STRING     true         NULL            ·                      {}       false
as_locator                STRING     true         NULL            ·                      {}       false
parameter_name            STRING     true         NULL            ·                      {}       false
data_type                 STRING     true         NULL            ·                      {}       false
character_maximum_length  INT8       true         NULL            ·                      {}       false
character_octet_length    INT8       true         NULL            ·                      {}       false
character_set_catalog     STRING     true         NULL            ·                      {}       false
character_set_schema      STRING     true         NULL            ·                      {}       false
character_set_name        STRING     true         NULL            ·                      {}       false
collation_catalog         STRING     true         NULL            ·                      {}       false
collation_schema          STRING     true         NULL            ·                      {}       false
collation_name            STRING     true         NULL            ·                      {}       false
numeric_precision         INT8       true         NULL            ·                      {}       false
numeric_precision_radix   INT8       true         NULL            ·                      {}       false
numeric_scale             INT8       true         NULL            ·                      {}       false
datetime_precision        INT8       true         NULL            ·                      {}       false
interval_type             STRING     true         NULL            ·                      {}       false
interval_precision        INT8       true         NULL            ·                      {}       false
udt_catalog               STRING     true         NULL            ·                      {}       false
udt_schema                STRING     true         NULL            ·                      {}       false
udt_name                  STRING     true         NULL            ·                      {}       false
scope_catalog             STRING     true         NULL            ·                      {}       false
scope_schema              STRING     true         NULL            ·                      {}       false
scope_name                STRING     true         NULL            ·                      {}       false
maximum_cardinality       INT8       true         NULL            ·                      {}       false
dtd_identifier            STRING     true         NULL            ·                      {}       false
parameter_default         STRING     true         NULL            ·                      {}       false

query TTTITTTTTIITTTTTTIIIITITTTTTTITT colnames
SELECT * FROM information_schema.parameters
----
specific_catalog  specific_schema  specific_name  ordinal_position  parameter_mode  is_result  as_locator  parameter_name  data_type  character_maximum_length  character_octet_length  character_set_catalog  character_set_schema  character_set_name  collation_catalog  collation_schema  collation_name  numeric_precision  numeric_precision_radix  numeric_scale  datetime_precision  interval_type  interval_precision  udt_catalog  udt_schema  udt_name  scope_catalog  scope_schema  scope_name  maximum_cardinality  dtd_identifier  parameter_default

query TTTTTTTT colnames
SELECT * FROM system.information_schema.column_privileges WHERE table_name = 'eventlog'
----
grantor  grantee  table_catalog  table_schema  table_name  column_name  privilege_type  is_grantable
NULL     admin    system         public        eventlog    timestamp    SELECT          NULL
NULL     admin    system         public        eventlog    eventType    SELECT          NULL
NULL     admin    system         public        eventlog    targetID     SELECT          NULL
NULL     admin    system         public        eventlog    reportingID  SELECT          NULL
NULL     admin    system         public        eventlog    info         SELECT          NULL
NULL     admin    system         public        eventlog    uniqueID     SELECT          NULL
NULL     admin    system         public        eventlog    timestamp    INSERT          NULL
NULL     admin    system         public        eventlog    eventType    INSERT          NULL
NULL     admin    system         public        eventlog    targetID     INSERT          NULL
NULL     admin    system         public        eventlog    reportingID  INSERT          NULL
NULL     admin    system         public        eventlog    info         INSERT          NULL
NULL     admin    system         public        eventlog    uniqueID     INSERT          NULL
NULL     admin    system         public        eventlog    timestamp    UPDATE          NULL
NULL     admin    system         public        eventlog    eventType    UPDATE          NULL
NULL     admin    system         public        eventlog    targetID     UPDATE          NULL
NULL     admin    system         public        eventlog    reportingID  UPDATE          NULL
NULL     admin    system         public        eventlog    info         UPDATE          NULL
NULL     admin    system         public        eventlog    uniqueID     UPDATE          NULL
NULL     root     system         public        eventlog    timestamp    SELECT          NULL
NULL     root     system         public        eventlog    eventType    SELECT          NULL
NULL     root     system         public        eventlog    targetID     SELECT          NULL
NULL     root     system         public        eventlog    reportingID  SELECT          NULL
NULL     root     system         public        eventlog    info         SELECT          NULL
NULL     root     system         public        eventlog    uniqueID     SELECT          NULL
NULL     root     system         public        eventlog    timestamp    INSERT          NULL
NULL     root     system         public        eventlog    eventType    INSERT          NULL
NULL     root     system         public        eventlog    targetID     INSERT          NULL
NULL     root     system         public        eventlog    reportingID  INSERT          NULL
NULL     root     system         public        eventlog    info         INSERT          NULL
NULL     root     system         public        eventlog    uniqueID     INSERT          NULL
NULL     root     system         public        eventlog    timestamp    UPDATE          NULL
NULL     root     system         public        eventlog    eventType    UPDATE          NULL
NULL     root     system         public        eventlog    targetID     UPDATE          NULL
NULL     root     system         public        eventlog    reportingID  UPDATE          NULL
NULL     root     system         public        eventlog    info         UPDATE          NULL
NULL     root     system         public        eventlog    uniqueID     UPDATE          NULL

# information_schema.administrable_role_authorizations

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee  role_name  is_grantable
root     admin      YES

user testuser

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee  role_name  is_grantable

user root

# information_schema.applicable_roles

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee  role_name  is_grantable
root     admin      YES

user testuser

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee  role_name  is_grantable

user root

# information_schema.enabled_roles

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
admin
root

user testuser

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
testuser

user root

subtest fk_match_type

statement ok
CREATE DATABASE dfk; SET database=dfk

statement ok
CREATE TABLE v(x INT, y INT, UNIQUE (x,y))

statement ok
CREATE TABLE w(
  a INT, b INT, c INT, d INT,
  FOREIGN KEY (a,b) REFERENCES v(x,y) MATCH FULL,
  FOREIGN KEY (c,d) REFERENCES v(x,y) MATCH SIMPLE
  );

query TTTT
SELECT constraint_name, table_name, referenced_table_name, match_option
  FROM information_schema.referential_constraints
----
fk_a_ref_v  w  v  FULL
fk_c_ref_v  w  v  NONE

statement ok
SET database = test

statement ok
DROP DATABASE dfk CASCADE