mz_catalog/builtin/
information_schema.rs

1// Copyright Materialize, Inc. and contributors. All rights reserved.
2//
3// Use of this software is governed by the Business Source License
4// included in the LICENSE file.
5//
6// As of the Change Date specified in that file, in accordance with
7// the Business Source License, use of this software will be governed
8// by the Apache License, Version 2.0.
9
10//! Built-in catalog items for the `information_schema` schema.
11
12use std::collections::BTreeMap;
13use std::sync::LazyLock;
14
15use mz_pgrepr::oid;
16use mz_repr::namespaces::INFORMATION_SCHEMA;
17use mz_repr::{RelationDesc, SqlScalarType};
18
19use super::{BuiltinView, PUBLIC_SELECT};
20
21pub static INFORMATION_SCHEMA_APPLICABLE_ROLES: LazyLock<BuiltinView> =
22    LazyLock::new(|| BuiltinView {
23        name: "applicable_roles",
24        schema: INFORMATION_SCHEMA,
25        oid: oid::VIEW_APPLICABLE_ROLES_OID,
26        desc: RelationDesc::builder()
27            .with_column("grantee", SqlScalarType::String.nullable(false))
28            .with_column("role_name", SqlScalarType::String.nullable(false))
29            .with_column("is_grantable", SqlScalarType::String.nullable(false))
30            .finish(),
31        column_comments: BTreeMap::new(),
32        sql: "
33SELECT
34    member.name AS grantee,
35    role.name AS role_name,
36    -- ADMIN OPTION isn't implemented.
37    'NO' AS is_grantable
38FROM mz_catalog.mz_role_members membership
39JOIN mz_catalog.mz_roles role ON membership.role_id = role.id
40JOIN mz_catalog.mz_roles member ON membership.member = member.id
41WHERE mz_catalog.mz_is_superuser() OR pg_has_role(current_role, member.oid, 'USAGE')",
42        access: vec![PUBLIC_SELECT],
43        ontology: None,
44    });
45
46pub static INFORMATION_SCHEMA_COLUMNS: LazyLock<BuiltinView> = LazyLock::new(|| BuiltinView {
47    name: "columns",
48    schema: INFORMATION_SCHEMA,
49    oid: oid::VIEW_COLUMNS_OID,
50    desc: RelationDesc::builder()
51        .with_column("table_catalog", SqlScalarType::String.nullable(false))
52        .with_column("table_schema", SqlScalarType::String.nullable(false))
53        .with_column("table_name", SqlScalarType::String.nullable(false))
54        .with_column("column_name", SqlScalarType::String.nullable(false))
55        .with_column("ordinal_position", SqlScalarType::Int64.nullable(false))
56        .with_column("column_default", SqlScalarType::String.nullable(true))
57        .with_column("is_nullable", SqlScalarType::String.nullable(false))
58        .with_column("data_type", SqlScalarType::String.nullable(false))
59        .with_column(
60            "character_maximum_length",
61            SqlScalarType::Int32.nullable(true),
62        )
63        .with_column("numeric_precision", SqlScalarType::Int32.nullable(true))
64        .with_column("numeric_scale", SqlScalarType::Int32.nullable(true))
65        .finish(),
66    column_comments: BTreeMap::new(),
67    sql: "
68SELECT
69    current_database() as table_catalog,
70    s.name AS table_schema,
71    o.name AS table_name,
72    c.name AS column_name,
73    c.position::int8 AS ordinal_position,
74    c.default AS column_default,
75    CASE WHEN c.nullable THEN 'YES' ELSE 'NO' END AS is_nullable,
76    c.type AS data_type,
77    NULL::pg_catalog.int4 AS character_maximum_length,
78    NULL::pg_catalog.int4 AS numeric_precision,
79    NULL::pg_catalog.int4 AS numeric_scale
80FROM mz_catalog.mz_columns c
81JOIN mz_catalog.mz_objects o ON o.id = c.id
82JOIN mz_catalog.mz_schemas s ON s.id = o.schema_id
83LEFT JOIN mz_catalog.mz_databases d ON d.id = s.database_id
84WHERE s.database_id IS NULL OR d.name = current_database()",
85    access: vec![PUBLIC_SELECT],
86    ontology: None,
87});
88
89pub static INFORMATION_SCHEMA_ENABLED_ROLES: LazyLock<BuiltinView> =
90    LazyLock::new(|| BuiltinView {
91        name: "enabled_roles",
92        schema: INFORMATION_SCHEMA,
93        oid: oid::VIEW_ENABLED_ROLES_OID,
94        desc: RelationDesc::builder()
95            .with_column("role_name", SqlScalarType::String.nullable(false))
96            .finish(),
97        column_comments: BTreeMap::new(),
98        sql: "
99SELECT name AS role_name
100FROM mz_catalog.mz_roles
101WHERE mz_catalog.mz_is_superuser() OR pg_has_role(current_role, oid, 'USAGE')",
102        access: vec![PUBLIC_SELECT],
103        ontology: None,
104    });
105
106pub static INFORMATION_SCHEMA_ROLE_TABLE_GRANTS: LazyLock<BuiltinView> = LazyLock::new(|| {
107    BuiltinView {
108        name: "role_table_grants",
109        schema: INFORMATION_SCHEMA,
110        oid: oid::VIEW_ROLE_TABLE_GRANTS_OID,
111        desc: RelationDesc::builder()
112            .with_column("grantor", SqlScalarType::String.nullable(false))
113            .with_column("grantee", SqlScalarType::String.nullable(true))
114            .with_column("table_catalog", SqlScalarType::String.nullable(true))
115            .with_column("table_schema", SqlScalarType::String.nullable(false))
116            .with_column("table_name", SqlScalarType::String.nullable(false))
117            .with_column("privilege_type", SqlScalarType::String.nullable(true))
118            .with_column("is_grantable", SqlScalarType::String.nullable(false))
119            .with_column("with_hierarchy", SqlScalarType::String.nullable(false))
120            .finish(),
121        column_comments: BTreeMap::new(),
122        sql: "
123SELECT grantor, grantee, table_catalog, table_schema, table_name, privilege_type, is_grantable, with_hierarchy
124FROM information_schema.table_privileges
125WHERE
126    grantor IN (SELECT role_name FROM information_schema.enabled_roles)
127    OR grantee IN (SELECT role_name FROM information_schema.enabled_roles)",
128        access: vec![PUBLIC_SELECT],
129        ontology: None,
130    }
131});
132
133pub static INFORMATION_SCHEMA_KEY_COLUMN_USAGE: LazyLock<BuiltinView> =
134    LazyLock::new(|| BuiltinView {
135        name: "key_column_usage",
136        schema: INFORMATION_SCHEMA,
137        oid: oid::VIEW_KEY_COLUMN_USAGE_OID,
138        desc: RelationDesc::builder()
139            .with_column("constraint_catalog", SqlScalarType::String.nullable(false))
140            .with_column("constraint_schema", SqlScalarType::String.nullable(false))
141            .with_column("constraint_name", SqlScalarType::String.nullable(false))
142            .with_column("table_catalog", SqlScalarType::String.nullable(false))
143            .with_column("table_schema", SqlScalarType::String.nullable(false))
144            .with_column("table_name", SqlScalarType::String.nullable(false))
145            .with_column("column_name", SqlScalarType::String.nullable(false))
146            .with_column("ordinal_position", SqlScalarType::Int32.nullable(false))
147            .with_column(
148                "position_in_unique_constraint",
149                SqlScalarType::Int32.nullable(false),
150            )
151            .with_key(vec![])
152            .finish(),
153        column_comments: BTreeMap::new(),
154        sql: "SELECT
155    NULL::text AS constraint_catalog,
156    NULL::text AS constraint_schema,
157    NULL::text AS constraint_name,
158    NULL::text AS table_catalog,
159    NULL::text AS table_schema,
160    NULL::text AS table_name,
161    NULL::text AS column_name,
162    NULL::integer AS ordinal_position,
163    NULL::integer AS position_in_unique_constraint
164WHERE false",
165        access: vec![PUBLIC_SELECT],
166        ontology: None,
167    });
168
169pub static INFORMATION_SCHEMA_REFERENTIAL_CONSTRAINTS: LazyLock<BuiltinView> =
170    LazyLock::new(|| BuiltinView {
171        name: "referential_constraints",
172        schema: INFORMATION_SCHEMA,
173        oid: oid::VIEW_REFERENTIAL_CONSTRAINTS_OID,
174        desc: RelationDesc::builder()
175            .with_column("constraint_catalog", SqlScalarType::String.nullable(false))
176            .with_column("constraint_schema", SqlScalarType::String.nullable(false))
177            .with_column("constraint_name", SqlScalarType::String.nullable(false))
178            .with_column(
179                "unique_constraint_catalog",
180                SqlScalarType::String.nullable(false),
181            )
182            .with_column(
183                "unique_constraint_schema",
184                SqlScalarType::String.nullable(false),
185            )
186            .with_column(
187                "unique_constraint_name",
188                SqlScalarType::String.nullable(false),
189            )
190            .with_column("match_option", SqlScalarType::String.nullable(false))
191            .with_column("update_rule", SqlScalarType::String.nullable(false))
192            .with_column("delete_rule", SqlScalarType::String.nullable(false))
193            .with_key(vec![])
194            .finish(),
195        column_comments: BTreeMap::new(),
196        sql: "SELECT
197    NULL::text AS constraint_catalog,
198    NULL::text AS constraint_schema,
199    NULL::text AS constraint_name,
200    NULL::text AS unique_constraint_catalog,
201    NULL::text AS unique_constraint_schema,
202    NULL::text AS unique_constraint_name,
203    NULL::text AS match_option,
204    NULL::text AS update_rule,
205    NULL::text AS delete_rule
206WHERE false",
207        access: vec![PUBLIC_SELECT],
208        ontology: None,
209    });
210
211pub static INFORMATION_SCHEMA_ROUTINES: LazyLock<BuiltinView> = LazyLock::new(|| BuiltinView {
212    name: "routines",
213    schema: INFORMATION_SCHEMA,
214    oid: oid::VIEW_ROUTINES_OID,
215    desc: RelationDesc::builder()
216        .with_column("routine_catalog", SqlScalarType::String.nullable(false))
217        .with_column("routine_schema", SqlScalarType::String.nullable(false))
218        .with_column("routine_name", SqlScalarType::String.nullable(false))
219        .with_column("routine_type", SqlScalarType::String.nullable(false))
220        .with_column("routine_definition", SqlScalarType::String.nullable(true))
221        .finish(),
222    column_comments: BTreeMap::new(),
223    sql: "SELECT
224    current_database() as routine_catalog,
225    s.name AS routine_schema,
226    f.name AS routine_name,
227    'FUNCTION' AS routine_type,
228    NULL::text AS routine_definition
229FROM mz_catalog.mz_functions f
230JOIN mz_catalog.mz_schemas s ON s.id = f.schema_id
231LEFT JOIN mz_catalog.mz_databases d ON d.id = s.database_id
232WHERE s.database_id IS NULL OR d.name = current_database()",
233    access: vec![PUBLIC_SELECT],
234    ontology: None,
235});
236
237pub static INFORMATION_SCHEMA_SCHEMATA: LazyLock<BuiltinView> = LazyLock::new(|| BuiltinView {
238    name: "schemata",
239    schema: INFORMATION_SCHEMA,
240    oid: oid::VIEW_SCHEMATA_OID,
241    desc: RelationDesc::builder()
242        .with_column("catalog_name", SqlScalarType::String.nullable(false))
243        .with_column("schema_name", SqlScalarType::String.nullable(false))
244        .finish(),
245    column_comments: BTreeMap::new(),
246    sql: "
247SELECT
248    current_database() as catalog_name,
249    s.name AS schema_name
250FROM mz_catalog.mz_schemas s
251LEFT JOIN mz_catalog.mz_databases d ON d.id = s.database_id
252WHERE s.database_id IS NULL OR d.name = current_database()",
253    access: vec![PUBLIC_SELECT],
254    ontology: None,
255});
256
257pub static INFORMATION_SCHEMA_TABLES: LazyLock<BuiltinView> = LazyLock::new(|| BuiltinView {
258    name: "tables",
259    schema: INFORMATION_SCHEMA,
260    oid: oid::VIEW_TABLES_OID,
261    desc: RelationDesc::builder()
262        .with_column("table_catalog", SqlScalarType::String.nullable(false))
263        .with_column("table_schema", SqlScalarType::String.nullable(false))
264        .with_column("table_name", SqlScalarType::String.nullable(false))
265        .with_column("table_type", SqlScalarType::String.nullable(false))
266        .finish(),
267    column_comments: BTreeMap::new(),
268    sql: "SELECT
269    current_database() as table_catalog,
270    s.name AS table_schema,
271    r.name AS table_name,
272    CASE r.type
273        WHEN 'materialized-view' THEN 'MATERIALIZED VIEW'
274        WHEN 'table' THEN 'BASE TABLE'
275        ELSE pg_catalog.upper(r.type)
276    END AS table_type
277FROM mz_catalog.mz_relations r
278JOIN mz_catalog.mz_schemas s ON s.id = r.schema_id
279LEFT JOIN mz_catalog.mz_databases d ON d.id = s.database_id
280WHERE s.database_id IS NULL OR d.name = current_database()",
281    access: vec![PUBLIC_SELECT],
282    ontology: None,
283});
284
285pub static INFORMATION_SCHEMA_TABLE_CONSTRAINTS: LazyLock<BuiltinView> =
286    LazyLock::new(|| BuiltinView {
287        name: "table_constraints",
288        schema: INFORMATION_SCHEMA,
289        oid: oid::VIEW_TABLE_CONSTRAINTS_OID,
290        desc: RelationDesc::builder()
291            .with_column("constraint_catalog", SqlScalarType::String.nullable(false))
292            .with_column("constraint_schema", SqlScalarType::String.nullable(false))
293            .with_column("constraint_name", SqlScalarType::String.nullable(false))
294            .with_column("table_catalog", SqlScalarType::String.nullable(false))
295            .with_column("table_schema", SqlScalarType::String.nullable(false))
296            .with_column("table_name", SqlScalarType::String.nullable(false))
297            .with_column("constraint_type", SqlScalarType::String.nullable(false))
298            .with_column("is_deferrable", SqlScalarType::String.nullable(false))
299            .with_column("initially_deferred", SqlScalarType::String.nullable(false))
300            .with_column("enforced", SqlScalarType::String.nullable(false))
301            .with_column("nulls_distinct", SqlScalarType::String.nullable(false))
302            .with_key(vec![])
303            .finish(),
304        column_comments: BTreeMap::new(),
305        sql: "SELECT
306    NULL::text AS constraint_catalog,
307    NULL::text AS constraint_schema,
308    NULL::text AS constraint_name,
309    NULL::text AS table_catalog,
310    NULL::text AS table_schema,
311    NULL::text AS table_name,
312    NULL::text AS constraint_type,
313    NULL::text AS is_deferrable,
314    NULL::text AS initially_deferred,
315    NULL::text AS enforced,
316    NULL::text AS nulls_distinct
317WHERE false",
318        access: vec![PUBLIC_SELECT],
319        ontology: None,
320    });
321
322pub static INFORMATION_SCHEMA_TABLE_PRIVILEGES: LazyLock<BuiltinView> = LazyLock::new(|| {
323    BuiltinView {
324        name: "table_privileges",
325        schema: INFORMATION_SCHEMA,
326        oid: oid::VIEW_TABLE_PRIVILEGES_OID,
327        desc: RelationDesc::builder()
328            .with_column("grantor", SqlScalarType::String.nullable(false))
329            .with_column("grantee", SqlScalarType::String.nullable(true))
330            .with_column("table_catalog", SqlScalarType::String.nullable(true))
331            .with_column("table_schema", SqlScalarType::String.nullable(false))
332            .with_column("table_name", SqlScalarType::String.nullable(false))
333            .with_column("privilege_type", SqlScalarType::String.nullable(true))
334            .with_column("is_grantable", SqlScalarType::String.nullable(false))
335            .with_column("with_hierarchy", SqlScalarType::String.nullable(false))
336            .finish(),
337        column_comments: BTreeMap::new(),
338        sql: "
339SELECT
340    grantor,
341    grantee,
342    table_catalog,
343    table_schema,
344    table_name,
345    privilege_type,
346    is_grantable,
347    CASE privilege_type
348        WHEN 'SELECT' THEN 'YES'
349        ELSE 'NO'
350    END AS with_hierarchy
351FROM
352    (SELECT
353        grantor.name AS grantor,
354        CASE mz_internal.mz_aclitem_grantee(privileges)
355            WHEN 'p' THEN 'PUBLIC'
356            ELSE grantee.name
357        END AS grantee,
358        table_catalog,
359        table_schema,
360        table_name,
361        unnest(mz_internal.mz_format_privileges(mz_internal.mz_aclitem_privileges(privileges))) AS privilege_type,
362        -- ADMIN OPTION isn't implemented.
363        'NO' AS is_grantable
364    FROM
365        (SELECT
366            unnest(relations.privileges) AS privileges,
367            CASE
368                WHEN schemas.database_id IS NULL THEN current_database()
369                ELSE databases.name
370            END AS table_catalog,
371            schemas.name AS table_schema,
372            relations.name AS table_name
373        FROM mz_catalog.mz_relations AS relations
374        JOIN mz_catalog.mz_schemas AS schemas ON relations.schema_id = schemas.id
375        LEFT JOIN mz_catalog.mz_databases AS databases ON schemas.database_id = databases.id
376        WHERE schemas.database_id IS NULL OR databases.name = current_database())
377    JOIN mz_catalog.mz_roles AS grantor ON mz_internal.mz_aclitem_grantor(privileges) = grantor.id
378    LEFT JOIN mz_catalog.mz_roles AS grantee ON mz_internal.mz_aclitem_grantee(privileges) = grantee.id)
379WHERE
380    -- WHERE clause is not guaranteed to short-circuit and 'PUBLIC' will cause an error when passed
381    -- to pg_has_role. Therefore we need to use a CASE statement.
382    CASE
383        WHEN grantee = 'PUBLIC' THEN true
384        ELSE mz_catalog.mz_is_superuser()
385            OR pg_has_role(current_role, grantee, 'USAGE')
386            OR pg_has_role(current_role, grantor, 'USAGE')
387    END",
388        access: vec![PUBLIC_SELECT],
389        ontology: None,
390    }
391});
392
393pub static INFORMATION_SCHEMA_TRIGGERS: LazyLock<BuiltinView> = LazyLock::new(|| BuiltinView {
394    name: "triggers",
395    schema: INFORMATION_SCHEMA,
396    oid: oid::VIEW_TRIGGERS_OID,
397    desc: RelationDesc::builder()
398        .with_column("trigger_catalog", SqlScalarType::String.nullable(false))
399        .with_column("trigger_schema", SqlScalarType::String.nullable(false))
400        .with_column("trigger_name", SqlScalarType::String.nullable(false))
401        .with_column("event_manipulation", SqlScalarType::String.nullable(false))
402        .with_column(
403            "event_object_catalog",
404            SqlScalarType::String.nullable(false),
405        )
406        .with_column("event_object_schema", SqlScalarType::String.nullable(false))
407        .with_column("event_object_table", SqlScalarType::String.nullable(false))
408        .with_column("action_order", SqlScalarType::Int32.nullable(false))
409        .with_column("action_condition", SqlScalarType::String.nullable(false))
410        .with_column("action_statement", SqlScalarType::String.nullable(false))
411        .with_column("action_orientation", SqlScalarType::String.nullable(false))
412        .with_column("action_timing", SqlScalarType::String.nullable(false))
413        .with_column(
414            "action_reference_old_table",
415            SqlScalarType::String.nullable(false),
416        )
417        .with_column(
418            "action_reference_new_table",
419            SqlScalarType::String.nullable(false),
420        )
421        .with_key(vec![])
422        .finish(),
423    column_comments: BTreeMap::new(),
424    sql: "SELECT
425    NULL::text as trigger_catalog,
426    NULL::text AS trigger_schema,
427    NULL::text AS trigger_name,
428    NULL::text AS event_manipulation,
429    NULL::text AS event_object_catalog,
430    NULL::text AS event_object_schema,
431    NULL::text AS event_object_table,
432    NULL::integer AS action_order,
433    NULL::text AS action_condition,
434    NULL::text AS action_statement,
435    NULL::text AS action_orientation,
436    NULL::text AS action_timing,
437    NULL::text AS action_reference_old_table,
438    NULL::text AS action_reference_new_table
439WHERE FALSE",
440    access: vec![PUBLIC_SELECT],
441    ontology: None,
442});
443
444pub static INFORMATION_SCHEMA_VIEWS: LazyLock<BuiltinView> = LazyLock::new(|| BuiltinView {
445    name: "views",
446    schema: INFORMATION_SCHEMA,
447    oid: oid::VIEW_VIEWS_OID,
448    desc: RelationDesc::builder()
449        .with_column("table_catalog", SqlScalarType::String.nullable(false))
450        .with_column("table_schema", SqlScalarType::String.nullable(false))
451        .with_column("table_name", SqlScalarType::String.nullable(false))
452        .with_column("view_definition", SqlScalarType::String.nullable(false))
453        .finish(),
454    column_comments: BTreeMap::new(),
455    sql: "SELECT
456    current_database() as table_catalog,
457    s.name AS table_schema,
458    v.name AS table_name,
459    v.definition AS view_definition
460FROM mz_catalog.mz_views v
461JOIN mz_catalog.mz_schemas s ON s.id = v.schema_id
462LEFT JOIN mz_catalog.mz_databases d ON d.id = s.database_id
463WHERE s.database_id IS NULL OR d.name = current_database()",
464    access: vec![PUBLIC_SELECT],
465    ontology: None,
466});
467
468pub static INFORMATION_SCHEMA_CHARACTER_SETS: LazyLock<BuiltinView> =
469    LazyLock::new(|| BuiltinView {
470        name: "character_sets",
471        schema: INFORMATION_SCHEMA,
472        oid: oid::VIEW_CHARACTER_SETS_OID,
473        desc: RelationDesc::builder()
474            .with_column(
475                "character_set_catalog",
476                SqlScalarType::String.nullable(true),
477            )
478            .with_column("character_set_schema", SqlScalarType::String.nullable(true))
479            .with_column("character_set_name", SqlScalarType::String.nullable(false))
480            .with_column(
481                "character_repertoire",
482                SqlScalarType::String.nullable(false),
483            )
484            .with_column("form_of_use", SqlScalarType::String.nullable(false))
485            .with_column(
486                "default_collate_catalog",
487                SqlScalarType::String.nullable(false),
488            )
489            .with_column(
490                "default_collate_schema",
491                SqlScalarType::String.nullable(false),
492            )
493            .with_column(
494                "default_collate_name",
495                SqlScalarType::String.nullable(false),
496            )
497            .with_key(vec![])
498            .finish(),
499        column_comments: BTreeMap::new(),
500        sql: "SELECT
501    NULL as character_set_catalog,
502    NULL as character_set_schema,
503    'UTF8' as character_set_name,
504    'UCS' as character_repertoire,
505    'UTF8' as form_of_use,
506    current_database() as default_collate_catalog,
507    'pg_catalog' as default_collate_schema,
508    'en_US.utf8' as default_collate_name",
509        access: vec![PUBLIC_SELECT],
510        ontology: None,
511    });
mz_catalog/builtin/information_schema.rs

mz_catalog/builtin/
information_schema.rs
