1/* 2 * PostgreSQL System Views 3 * 4 * Copyright (c) 1996-2018, PostgreSQL Global Development Group 5 * 6 * src/backend/catalog/system_views.sql 7 * 8 * Note: this file is read in single-user -j mode, which means that the 9 * command terminator is semicolon-newline-newline; whenever the backend 10 * sees that, it stops and executes what it's got. If you write a lot of 11 * statements without empty lines between, they'll all get quoted to you 12 * in any error message about one of them, so don't do that. Also, you 13 * cannot write a semicolon immediately followed by an empty line in a 14 * string literal (including a function body!) or a multiline comment. 15 */ 16 17CREATE VIEW pg_roles AS 18 SELECT 19 rolname, 20 rolsuper, 21 rolinherit, 22 rolcreaterole, 23 rolcreatedb, 24 rolcanlogin, 25 rolreplication, 26 rolconnlimit, 27 '********'::text as rolpassword, 28 rolvaliduntil, 29 rolbypassrls, 30 setconfig as rolconfig, 31 pg_authid.oid 32 FROM pg_authid LEFT JOIN pg_db_role_setting s 33 ON (pg_authid.oid = setrole AND setdatabase = 0); 34 35CREATE VIEW pg_shadow AS 36 SELECT 37 rolname AS usename, 38 pg_authid.oid AS usesysid, 39 rolcreatedb AS usecreatedb, 40 rolsuper AS usesuper, 41 rolreplication AS userepl, 42 rolbypassrls AS usebypassrls, 43 rolpassword AS passwd, 44 rolvaliduntil::abstime AS valuntil, 45 setconfig AS useconfig 46 FROM pg_authid LEFT JOIN pg_db_role_setting s 47 ON (pg_authid.oid = setrole AND setdatabase = 0) 48 WHERE rolcanlogin; 49 50REVOKE ALL on pg_shadow FROM public; 51 52CREATE VIEW pg_group AS 53 SELECT 54 rolname AS groname, 55 oid AS grosysid, 56 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist 57 FROM pg_authid 58 WHERE NOT rolcanlogin; 59 60CREATE VIEW pg_user AS 61 SELECT 62 usename, 63 usesysid, 64 usecreatedb, 65 usesuper, 66 userepl, 67 usebypassrls, 68 '********'::text as passwd, 69 valuntil, 70 useconfig 71 FROM pg_shadow; 72 73CREATE VIEW pg_policies AS 74 SELECT 75 N.nspname AS schemaname, 76 C.relname AS tablename, 77 pol.polname AS policyname, 78 CASE 79 WHEN pol.polpermissive THEN 80 'PERMISSIVE' 81 ELSE 82 'RESTRICTIVE' 83 END AS permissive, 84 CASE 85 WHEN pol.polroles = '{0}' THEN 86 string_to_array('public', '') 87 ELSE 88 ARRAY 89 ( 90 SELECT rolname 91 FROM pg_catalog.pg_authid 92 WHERE oid = ANY (pol.polroles) ORDER BY 1 93 ) 94 END AS roles, 95 CASE pol.polcmd 96 WHEN 'r' THEN 'SELECT' 97 WHEN 'a' THEN 'INSERT' 98 WHEN 'w' THEN 'UPDATE' 99 WHEN 'd' THEN 'DELETE' 100 WHEN '*' THEN 'ALL' 101 END AS cmd, 102 pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS qual, 103 pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check 104 FROM pg_catalog.pg_policy pol 105 JOIN pg_catalog.pg_class C ON (C.oid = pol.polrelid) 106 LEFT JOIN pg_catalog.pg_namespace N ON (N.oid = C.relnamespace); 107 108CREATE VIEW pg_rules AS 109 SELECT 110 N.nspname AS schemaname, 111 C.relname AS tablename, 112 R.rulename AS rulename, 113 pg_get_ruledef(R.oid) AS definition 114 FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class)) 115 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 116 WHERE R.rulename != '_RETURN'; 117 118CREATE VIEW pg_views AS 119 SELECT 120 N.nspname AS schemaname, 121 C.relname AS viewname, 122 pg_get_userbyid(C.relowner) AS viewowner, 123 pg_get_viewdef(C.oid) AS definition 124 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 125 WHERE C.relkind = 'v'; 126 127CREATE VIEW pg_tables AS 128 SELECT 129 N.nspname AS schemaname, 130 C.relname AS tablename, 131 pg_get_userbyid(C.relowner) AS tableowner, 132 T.spcname AS tablespace, 133 C.relhasindex AS hasindexes, 134 C.relhasrules AS hasrules, 135 C.relhastriggers AS hastriggers, 136 C.relrowsecurity AS rowsecurity 137 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 138 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) 139 WHERE C.relkind IN ('r', 'p'); 140 141CREATE VIEW pg_matviews AS 142 SELECT 143 N.nspname AS schemaname, 144 C.relname AS matviewname, 145 pg_get_userbyid(C.relowner) AS matviewowner, 146 T.spcname AS tablespace, 147 C.relhasindex AS hasindexes, 148 C.relispopulated AS ispopulated, 149 pg_get_viewdef(C.oid) AS definition 150 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 151 LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace) 152 WHERE C.relkind = 'm'; 153 154CREATE VIEW pg_indexes AS 155 SELECT 156 N.nspname AS schemaname, 157 C.relname AS tablename, 158 I.relname AS indexname, 159 T.spcname AS tablespace, 160 pg_get_indexdef(I.oid) AS indexdef 161 FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) 162 JOIN pg_class I ON (I.oid = X.indexrelid) 163 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 164 LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace) 165 WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i'; 166 167CREATE OR REPLACE VIEW pg_sequences AS 168 SELECT 169 N.nspname AS schemaname, 170 C.relname AS sequencename, 171 pg_get_userbyid(C.relowner) AS sequenceowner, 172 S.seqtypid::regtype AS data_type, 173 S.seqstart AS start_value, 174 S.seqmin AS min_value, 175 S.seqmax AS max_value, 176 S.seqincrement AS increment_by, 177 S.seqcycle AS cycle, 178 S.seqcache AS cache_size, 179 CASE 180 WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text) 181 THEN pg_sequence_last_value(C.oid) 182 ELSE NULL 183 END AS last_value 184 FROM pg_sequence S JOIN pg_class C ON (C.oid = S.seqrelid) 185 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 186 WHERE NOT pg_is_other_temp_schema(N.oid) 187 AND relkind = 'S'; 188 189CREATE VIEW pg_stats WITH (security_barrier) AS 190 SELECT 191 nspname AS schemaname, 192 relname AS tablename, 193 attname AS attname, 194 stainherit AS inherited, 195 stanullfrac AS null_frac, 196 stawidth AS avg_width, 197 stadistinct AS n_distinct, 198 CASE 199 WHEN stakind1 = 1 THEN stavalues1 200 WHEN stakind2 = 1 THEN stavalues2 201 WHEN stakind3 = 1 THEN stavalues3 202 WHEN stakind4 = 1 THEN stavalues4 203 WHEN stakind5 = 1 THEN stavalues5 204 END AS most_common_vals, 205 CASE 206 WHEN stakind1 = 1 THEN stanumbers1 207 WHEN stakind2 = 1 THEN stanumbers2 208 WHEN stakind3 = 1 THEN stanumbers3 209 WHEN stakind4 = 1 THEN stanumbers4 210 WHEN stakind5 = 1 THEN stanumbers5 211 END AS most_common_freqs, 212 CASE 213 WHEN stakind1 = 2 THEN stavalues1 214 WHEN stakind2 = 2 THEN stavalues2 215 WHEN stakind3 = 2 THEN stavalues3 216 WHEN stakind4 = 2 THEN stavalues4 217 WHEN stakind5 = 2 THEN stavalues5 218 END AS histogram_bounds, 219 CASE 220 WHEN stakind1 = 3 THEN stanumbers1[1] 221 WHEN stakind2 = 3 THEN stanumbers2[1] 222 WHEN stakind3 = 3 THEN stanumbers3[1] 223 WHEN stakind4 = 3 THEN stanumbers4[1] 224 WHEN stakind5 = 3 THEN stanumbers5[1] 225 END AS correlation, 226 CASE 227 WHEN stakind1 = 4 THEN stavalues1 228 WHEN stakind2 = 4 THEN stavalues2 229 WHEN stakind3 = 4 THEN stavalues3 230 WHEN stakind4 = 4 THEN stavalues4 231 WHEN stakind5 = 4 THEN stavalues5 232 END AS most_common_elems, 233 CASE 234 WHEN stakind1 = 4 THEN stanumbers1 235 WHEN stakind2 = 4 THEN stanumbers2 236 WHEN stakind3 = 4 THEN stanumbers3 237 WHEN stakind4 = 4 THEN stanumbers4 238 WHEN stakind5 = 4 THEN stanumbers5 239 END AS most_common_elem_freqs, 240 CASE 241 WHEN stakind1 = 5 THEN stanumbers1 242 WHEN stakind2 = 5 THEN stanumbers2 243 WHEN stakind3 = 5 THEN stanumbers3 244 WHEN stakind4 = 5 THEN stanumbers4 245 WHEN stakind5 = 5 THEN stanumbers5 246 END AS elem_count_histogram 247 FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) 248 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) 249 LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) 250 WHERE NOT attisdropped 251 AND has_column_privilege(c.oid, a.attnum, 'select') 252 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); 253 254REVOKE ALL on pg_statistic FROM public; 255 256CREATE VIEW pg_publication_tables AS 257 SELECT 258 P.pubname AS pubname, 259 N.nspname AS schemaname, 260 C.relname AS tablename 261 FROM pg_publication P, pg_class C 262 JOIN pg_namespace N ON (N.oid = C.relnamespace) 263 WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname)); 264 265CREATE VIEW pg_locks AS 266 SELECT * FROM pg_lock_status() AS L; 267 268CREATE VIEW pg_cursors AS 269 SELECT * FROM pg_cursor() AS C; 270 271CREATE VIEW pg_available_extensions AS 272 SELECT E.name, E.default_version, X.extversion AS installed_version, 273 E.comment 274 FROM pg_available_extensions() AS E 275 LEFT JOIN pg_extension AS X ON E.name = X.extname; 276 277CREATE VIEW pg_available_extension_versions AS 278 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed, 279 E.superuser, E.relocatable, E.schema, E.requires, E.comment 280 FROM pg_available_extension_versions() AS E 281 LEFT JOIN pg_extension AS X 282 ON E.name = X.extname AND E.version = X.extversion; 283 284CREATE VIEW pg_prepared_xacts AS 285 SELECT P.transaction, P.gid, P.prepared, 286 U.rolname AS owner, D.datname AS database 287 FROM pg_prepared_xact() AS P 288 LEFT JOIN pg_authid U ON P.ownerid = U.oid 289 LEFT JOIN pg_database D ON P.dbid = D.oid; 290 291CREATE VIEW pg_prepared_statements AS 292 SELECT * FROM pg_prepared_statement() AS P; 293 294CREATE VIEW pg_seclabels AS 295SELECT 296 l.objoid, l.classoid, l.objsubid, 297 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text 298 WHEN rel.relkind = 'v' THEN 'view'::text 299 WHEN rel.relkind = 'm' THEN 'materialized view'::text 300 WHEN rel.relkind = 'S' THEN 'sequence'::text 301 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, 302 rel.relnamespace AS objnamespace, 303 CASE WHEN pg_table_is_visible(rel.oid) 304 THEN quote_ident(rel.relname) 305 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 306 END AS objname, 307 l.provider, l.label 308FROM 309 pg_seclabel l 310 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 311 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 312WHERE 313 l.objsubid = 0 314UNION ALL 315SELECT 316 l.objoid, l.classoid, l.objsubid, 317 'column'::text AS objtype, 318 rel.relnamespace AS objnamespace, 319 CASE WHEN pg_table_is_visible(rel.oid) 320 THEN quote_ident(rel.relname) 321 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 322 END || '.' || att.attname AS objname, 323 l.provider, l.label 324FROM 325 pg_seclabel l 326 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 327 JOIN pg_attribute att 328 ON rel.oid = att.attrelid AND l.objsubid = att.attnum 329 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 330WHERE 331 l.objsubid != 0 332UNION ALL 333SELECT 334 l.objoid, l.classoid, l.objsubid, 335 CASE pro.prokind 336 WHEN 'a' THEN 'aggregate'::text 337 WHEN 'f' THEN 'function'::text 338 WHEN 'p' THEN 'procedure'::text 339 WHEN 'w' THEN 'window'::text END AS objtype, 340 pro.pronamespace AS objnamespace, 341 CASE WHEN pg_function_is_visible(pro.oid) 342 THEN quote_ident(pro.proname) 343 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname) 344 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname, 345 l.provider, l.label 346FROM 347 pg_seclabel l 348 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid 349 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid 350WHERE 351 l.objsubid = 0 352UNION ALL 353SELECT 354 l.objoid, l.classoid, l.objsubid, 355 CASE WHEN typ.typtype = 'd' THEN 'domain'::text 356 ELSE 'type'::text END AS objtype, 357 typ.typnamespace AS objnamespace, 358 CASE WHEN pg_type_is_visible(typ.oid) 359 THEN quote_ident(typ.typname) 360 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname) 361 END AS objname, 362 l.provider, l.label 363FROM 364 pg_seclabel l 365 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid 366 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid 367WHERE 368 l.objsubid = 0 369UNION ALL 370SELECT 371 l.objoid, l.classoid, l.objsubid, 372 'large object'::text AS objtype, 373 NULL::oid AS objnamespace, 374 l.objoid::text AS objname, 375 l.provider, l.label 376FROM 377 pg_seclabel l 378 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid 379WHERE 380 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0 381UNION ALL 382SELECT 383 l.objoid, l.classoid, l.objsubid, 384 'language'::text AS objtype, 385 NULL::oid AS objnamespace, 386 quote_ident(lan.lanname) AS objname, 387 l.provider, l.label 388FROM 389 pg_seclabel l 390 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid 391WHERE 392 l.objsubid = 0 393UNION ALL 394SELECT 395 l.objoid, l.classoid, l.objsubid, 396 'schema'::text AS objtype, 397 nsp.oid AS objnamespace, 398 quote_ident(nsp.nspname) AS objname, 399 l.provider, l.label 400FROM 401 pg_seclabel l 402 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid 403WHERE 404 l.objsubid = 0 405UNION ALL 406SELECT 407 l.objoid, l.classoid, l.objsubid, 408 'event trigger'::text AS objtype, 409 NULL::oid AS objnamespace, 410 quote_ident(evt.evtname) AS objname, 411 l.provider, l.label 412FROM 413 pg_seclabel l 414 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid 415 AND l.objoid = evt.oid 416WHERE 417 l.objsubid = 0 418UNION ALL 419SELECT 420 l.objoid, l.classoid, l.objsubid, 421 'publication'::text AS objtype, 422 NULL::oid AS objnamespace, 423 quote_ident(p.pubname) AS objname, 424 l.provider, l.label 425FROM 426 pg_seclabel l 427 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid 428WHERE 429 l.objsubid = 0 430UNION ALL 431SELECT 432 l.objoid, l.classoid, 0::int4 AS objsubid, 433 'subscription'::text AS objtype, 434 NULL::oid AS objnamespace, 435 quote_ident(s.subname) AS objname, 436 l.provider, l.label 437FROM 438 pg_shseclabel l 439 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid 440UNION ALL 441SELECT 442 l.objoid, l.classoid, 0::int4 AS objsubid, 443 'database'::text AS objtype, 444 NULL::oid AS objnamespace, 445 quote_ident(dat.datname) AS objname, 446 l.provider, l.label 447FROM 448 pg_shseclabel l 449 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid 450UNION ALL 451SELECT 452 l.objoid, l.classoid, 0::int4 AS objsubid, 453 'tablespace'::text AS objtype, 454 NULL::oid AS objnamespace, 455 quote_ident(spc.spcname) AS objname, 456 l.provider, l.label 457FROM 458 pg_shseclabel l 459 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid 460UNION ALL 461SELECT 462 l.objoid, l.classoid, 0::int4 AS objsubid, 463 'role'::text AS objtype, 464 NULL::oid AS objnamespace, 465 quote_ident(rol.rolname) AS objname, 466 l.provider, l.label 467FROM 468 pg_shseclabel l 469 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; 470 471CREATE VIEW pg_settings AS 472 SELECT * FROM pg_show_all_settings() AS A; 473 474CREATE RULE pg_settings_u AS 475 ON UPDATE TO pg_settings 476 WHERE new.name = old.name DO 477 SELECT set_config(old.name, new.setting, 'f'); 478 479CREATE RULE pg_settings_n AS 480 ON UPDATE TO pg_settings 481 DO INSTEAD NOTHING; 482 483GRANT SELECT, UPDATE ON pg_settings TO PUBLIC; 484 485CREATE VIEW pg_file_settings AS 486 SELECT * FROM pg_show_all_file_settings() AS A; 487 488REVOKE ALL on pg_file_settings FROM PUBLIC; 489REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC; 490 491CREATE VIEW pg_hba_file_rules AS 492 SELECT * FROM pg_hba_file_rules() AS A; 493 494REVOKE ALL on pg_hba_file_rules FROM PUBLIC; 495REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC; 496 497CREATE VIEW pg_timezone_abbrevs AS 498 SELECT * FROM pg_timezone_abbrevs(); 499 500CREATE VIEW pg_timezone_names AS 501 SELECT * FROM pg_timezone_names(); 502 503CREATE VIEW pg_config AS 504 SELECT * FROM pg_config(); 505 506REVOKE ALL on pg_config FROM PUBLIC; 507REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC; 508 509-- Statistics views 510 511CREATE VIEW pg_stat_all_tables AS 512 SELECT 513 C.oid AS relid, 514 N.nspname AS schemaname, 515 C.relname AS relname, 516 pg_stat_get_numscans(C.oid) AS seq_scan, 517 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, 518 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan, 519 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint + 520 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch, 521 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, 522 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, 523 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, 524 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, 525 pg_stat_get_live_tuples(C.oid) AS n_live_tup, 526 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, 527 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, 528 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, 529 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, 530 pg_stat_get_last_analyze_time(C.oid) as last_analyze, 531 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, 532 pg_stat_get_vacuum_count(C.oid) AS vacuum_count, 533 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, 534 pg_stat_get_analyze_count(C.oid) AS analyze_count, 535 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count 536 FROM pg_class C LEFT JOIN 537 pg_index I ON C.oid = I.indrelid 538 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 539 WHERE C.relkind IN ('r', 't', 'm') 540 GROUP BY C.oid, N.nspname, C.relname; 541 542CREATE VIEW pg_stat_xact_all_tables AS 543 SELECT 544 C.oid AS relid, 545 N.nspname AS schemaname, 546 C.relname AS relname, 547 pg_stat_get_xact_numscans(C.oid) AS seq_scan, 548 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read, 549 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan, 550 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint + 551 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch, 552 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, 553 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, 554 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, 555 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd 556 FROM pg_class C LEFT JOIN 557 pg_index I ON C.oid = I.indrelid 558 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 559 WHERE C.relkind IN ('r', 't', 'm') 560 GROUP BY C.oid, N.nspname, C.relname; 561 562CREATE VIEW pg_stat_sys_tables AS 563 SELECT * FROM pg_stat_all_tables 564 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 565 schemaname ~ '^pg_toast'; 566 567CREATE VIEW pg_stat_xact_sys_tables AS 568 SELECT * FROM pg_stat_xact_all_tables 569 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 570 schemaname ~ '^pg_toast'; 571 572CREATE VIEW pg_stat_user_tables AS 573 SELECT * FROM pg_stat_all_tables 574 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 575 schemaname !~ '^pg_toast'; 576 577CREATE VIEW pg_stat_xact_user_tables AS 578 SELECT * FROM pg_stat_xact_all_tables 579 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 580 schemaname !~ '^pg_toast'; 581 582CREATE VIEW pg_statio_all_tables AS 583 SELECT 584 C.oid AS relid, 585 N.nspname AS schemaname, 586 C.relname AS relname, 587 pg_stat_get_blocks_fetched(C.oid) - 588 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, 589 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, 590 sum(pg_stat_get_blocks_fetched(I.indexrelid) - 591 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read, 592 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit, 593 pg_stat_get_blocks_fetched(T.oid) - 594 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, 595 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, 596 sum(pg_stat_get_blocks_fetched(X.indexrelid) - 597 pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_read, 598 sum(pg_stat_get_blocks_hit(X.indexrelid))::bigint AS tidx_blks_hit 599 FROM pg_class C LEFT JOIN 600 pg_index I ON C.oid = I.indrelid LEFT JOIN 601 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN 602 pg_index X ON T.oid = X.indrelid 603 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 604 WHERE C.relkind IN ('r', 't', 'm') 605 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indrelid; 606 607CREATE VIEW pg_statio_sys_tables AS 608 SELECT * FROM pg_statio_all_tables 609 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 610 schemaname ~ '^pg_toast'; 611 612CREATE VIEW pg_statio_user_tables AS 613 SELECT * FROM pg_statio_all_tables 614 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 615 schemaname !~ '^pg_toast'; 616 617CREATE VIEW pg_stat_all_indexes AS 618 SELECT 619 C.oid AS relid, 620 I.oid AS indexrelid, 621 N.nspname AS schemaname, 622 C.relname AS relname, 623 I.relname AS indexrelname, 624 pg_stat_get_numscans(I.oid) AS idx_scan, 625 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, 626 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch 627 FROM pg_class C JOIN 628 pg_index X ON C.oid = X.indrelid JOIN 629 pg_class I ON I.oid = X.indexrelid 630 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 631 WHERE C.relkind IN ('r', 't', 'm'); 632 633CREATE VIEW pg_stat_sys_indexes AS 634 SELECT * FROM pg_stat_all_indexes 635 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 636 schemaname ~ '^pg_toast'; 637 638CREATE VIEW pg_stat_user_indexes AS 639 SELECT * FROM pg_stat_all_indexes 640 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 641 schemaname !~ '^pg_toast'; 642 643CREATE VIEW pg_statio_all_indexes AS 644 SELECT 645 C.oid AS relid, 646 I.oid AS indexrelid, 647 N.nspname AS schemaname, 648 C.relname AS relname, 649 I.relname AS indexrelname, 650 pg_stat_get_blocks_fetched(I.oid) - 651 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, 652 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit 653 FROM pg_class C JOIN 654 pg_index X ON C.oid = X.indrelid JOIN 655 pg_class I ON I.oid = X.indexrelid 656 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 657 WHERE C.relkind IN ('r', 't', 'm'); 658 659CREATE VIEW pg_statio_sys_indexes AS 660 SELECT * FROM pg_statio_all_indexes 661 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 662 schemaname ~ '^pg_toast'; 663 664CREATE VIEW pg_statio_user_indexes AS 665 SELECT * FROM pg_statio_all_indexes 666 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 667 schemaname !~ '^pg_toast'; 668 669CREATE VIEW pg_statio_all_sequences AS 670 SELECT 671 C.oid AS relid, 672 N.nspname AS schemaname, 673 C.relname AS relname, 674 pg_stat_get_blocks_fetched(C.oid) - 675 pg_stat_get_blocks_hit(C.oid) AS blks_read, 676 pg_stat_get_blocks_hit(C.oid) AS blks_hit 677 FROM pg_class C 678 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 679 WHERE C.relkind = 'S'; 680 681CREATE VIEW pg_statio_sys_sequences AS 682 SELECT * FROM pg_statio_all_sequences 683 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 684 schemaname ~ '^pg_toast'; 685 686CREATE VIEW pg_statio_user_sequences AS 687 SELECT * FROM pg_statio_all_sequences 688 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 689 schemaname !~ '^pg_toast'; 690 691CREATE VIEW pg_stat_activity AS 692 SELECT 693 S.datid AS datid, 694 D.datname AS datname, 695 S.pid, 696 S.usesysid, 697 U.rolname AS usename, 698 S.application_name, 699 S.client_addr, 700 S.client_hostname, 701 S.client_port, 702 S.backend_start, 703 S.xact_start, 704 S.query_start, 705 S.state_change, 706 S.wait_event_type, 707 S.wait_event, 708 S.state, 709 S.backend_xid, 710 s.backend_xmin, 711 S.query, 712 S.backend_type 713 FROM pg_stat_get_activity(NULL) AS S 714 LEFT JOIN pg_database AS D ON (S.datid = D.oid) 715 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 716 717CREATE VIEW pg_stat_replication AS 718 SELECT 719 S.pid, 720 S.usesysid, 721 U.rolname AS usename, 722 S.application_name, 723 S.client_addr, 724 S.client_hostname, 725 S.client_port, 726 S.backend_start, 727 S.backend_xmin, 728 W.state, 729 W.sent_lsn, 730 W.write_lsn, 731 W.flush_lsn, 732 W.replay_lsn, 733 W.write_lag, 734 W.flush_lag, 735 W.replay_lag, 736 W.sync_priority, 737 W.sync_state 738 FROM pg_stat_get_activity(NULL) AS S 739 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid) 740 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 741 742CREATE VIEW pg_stat_wal_receiver AS 743 SELECT 744 s.pid, 745 s.status, 746 s.receive_start_lsn, 747 s.receive_start_tli, 748 s.received_lsn, 749 s.received_tli, 750 s.last_msg_send_time, 751 s.last_msg_receipt_time, 752 s.latest_end_lsn, 753 s.latest_end_time, 754 s.slot_name, 755 s.sender_host, 756 s.sender_port, 757 s.conninfo 758 FROM pg_stat_get_wal_receiver() s 759 WHERE s.pid IS NOT NULL; 760 761CREATE VIEW pg_stat_subscription AS 762 SELECT 763 su.oid AS subid, 764 su.subname, 765 st.pid, 766 st.relid, 767 st.received_lsn, 768 st.last_msg_send_time, 769 st.last_msg_receipt_time, 770 st.latest_end_lsn, 771 st.latest_end_time 772 FROM pg_subscription su 773 LEFT JOIN pg_stat_get_subscription(NULL) st 774 ON (st.subid = su.oid); 775 776CREATE VIEW pg_stat_ssl AS 777 SELECT 778 S.pid, 779 S.ssl, 780 S.sslversion AS version, 781 S.sslcipher AS cipher, 782 S.sslbits AS bits, 783 S.sslcompression AS compression, 784 S.sslclientdn AS clientdn 785 FROM pg_stat_get_activity(NULL) AS S; 786 787CREATE VIEW pg_replication_slots AS 788 SELECT 789 L.slot_name, 790 L.plugin, 791 L.slot_type, 792 L.datoid, 793 D.datname AS database, 794 L.temporary, 795 L.active, 796 L.active_pid, 797 L.xmin, 798 L.catalog_xmin, 799 L.restart_lsn, 800 L.confirmed_flush_lsn 801 FROM pg_get_replication_slots() AS L 802 LEFT JOIN pg_database D ON (L.datoid = D.oid); 803 804CREATE VIEW pg_stat_database AS 805 SELECT 806 D.oid AS datid, 807 D.datname AS datname, 808 pg_stat_get_db_numbackends(D.oid) AS numbackends, 809 pg_stat_get_db_xact_commit(D.oid) AS xact_commit, 810 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, 811 pg_stat_get_db_blocks_fetched(D.oid) - 812 pg_stat_get_db_blocks_hit(D.oid) AS blks_read, 813 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit, 814 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned, 815 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, 816 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, 817 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, 818 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted, 819 pg_stat_get_db_conflict_all(D.oid) AS conflicts, 820 pg_stat_get_db_temp_files(D.oid) AS temp_files, 821 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes, 822 pg_stat_get_db_deadlocks(D.oid) AS deadlocks, 823 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time, 824 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time, 825 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset 826 FROM pg_database D; 827 828CREATE VIEW pg_stat_database_conflicts AS 829 SELECT 830 D.oid AS datid, 831 D.datname AS datname, 832 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace, 833 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock, 834 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot, 835 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin, 836 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock 837 FROM pg_database D; 838 839CREATE VIEW pg_stat_user_functions AS 840 SELECT 841 P.oid AS funcid, 842 N.nspname AS schemaname, 843 P.proname AS funcname, 844 pg_stat_get_function_calls(P.oid) AS calls, 845 pg_stat_get_function_total_time(P.oid) AS total_time, 846 pg_stat_get_function_self_time(P.oid) AS self_time 847 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 848 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 849 AND pg_stat_get_function_calls(P.oid) IS NOT NULL; 850 851CREATE VIEW pg_stat_xact_user_functions AS 852 SELECT 853 P.oid AS funcid, 854 N.nspname AS schemaname, 855 P.proname AS funcname, 856 pg_stat_get_xact_function_calls(P.oid) AS calls, 857 pg_stat_get_xact_function_total_time(P.oid) AS total_time, 858 pg_stat_get_xact_function_self_time(P.oid) AS self_time 859 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 860 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 861 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; 862 863CREATE VIEW pg_stat_archiver AS 864 SELECT 865 s.archived_count, 866 s.last_archived_wal, 867 s.last_archived_time, 868 s.failed_count, 869 s.last_failed_wal, 870 s.last_failed_time, 871 s.stats_reset 872 FROM pg_stat_get_archiver() s; 873 874CREATE VIEW pg_stat_bgwriter AS 875 SELECT 876 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 877 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 878 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 879 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 880 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 881 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 882 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 883 pg_stat_get_buf_written_backend() AS buffers_backend, 884 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 885 pg_stat_get_buf_alloc() AS buffers_alloc, 886 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 887 888CREATE VIEW pg_stat_progress_vacuum AS 889 SELECT 890 S.pid AS pid, S.datid AS datid, D.datname AS datname, 891 S.relid AS relid, 892 CASE S.param1 WHEN 0 THEN 'initializing' 893 WHEN 1 THEN 'scanning heap' 894 WHEN 2 THEN 'vacuuming indexes' 895 WHEN 3 THEN 'vacuuming heap' 896 WHEN 4 THEN 'cleaning up indexes' 897 WHEN 5 THEN 'truncating heap' 898 WHEN 6 THEN 'performing final cleanup' 899 END AS phase, 900 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned, 901 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count, 902 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples 903 FROM pg_stat_get_progress_info('VACUUM') AS S 904 LEFT JOIN pg_database D ON S.datid = D.oid; 905 906CREATE VIEW pg_user_mappings AS 907 SELECT 908 U.oid AS umid, 909 S.oid AS srvid, 910 S.srvname AS srvname, 911 U.umuser AS umuser, 912 CASE WHEN U.umuser = 0 THEN 913 'public' 914 ELSE 915 A.rolname 916 END AS usename, 917 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user 918 AND (pg_has_role(S.srvowner, 'USAGE') 919 OR has_server_privilege(S.oid, 'USAGE'))) 920 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE')) 921 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) 922 THEN U.umoptions 923 ELSE NULL END AS umoptions 924 FROM pg_user_mapping U 925 JOIN pg_foreign_server S ON (U.umserver = S.oid) 926 LEFT JOIN pg_authid A ON (A.oid = U.umuser); 927 928REVOKE ALL on pg_user_mapping FROM public; 929 930CREATE VIEW pg_replication_origin_status AS 931 SELECT * 932 FROM pg_show_replication_origin_status(); 933 934REVOKE ALL ON pg_replication_origin_status FROM public; 935 936-- All columns of pg_subscription except subconninfo are readable. 937REVOKE ALL ON pg_subscription FROM public; 938GRANT SELECT (subdbid, subname, subowner, subenabled, subslotname, subpublications) 939 ON pg_subscription TO public; 940 941 942-- 943-- We have a few function definitions in here, too. 944-- At some point there might be enough to justify breaking them out into 945-- a separate "system_functions.sql" file. 946-- 947 948-- Tsearch debug function. Defined here because it'd be pretty unwieldy 949-- to put it into pg_proc.h 950 951CREATE FUNCTION ts_debug(IN config regconfig, IN document text, 952 OUT alias text, 953 OUT description text, 954 OUT token text, 955 OUT dictionaries regdictionary[], 956 OUT dictionary regdictionary, 957 OUT lexemes text[]) 958RETURNS SETOF record AS 959$$ 960SELECT 961 tt.alias AS alias, 962 tt.description AS description, 963 parse.token AS token, 964 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary 965 FROM pg_catalog.pg_ts_config_map AS m 966 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 967 ORDER BY m.mapseqno ) 968 AS dictionaries, 969 ( SELECT mapdict::pg_catalog.regdictionary 970 FROM pg_catalog.pg_ts_config_map AS m 971 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 972 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 973 LIMIT 1 974 ) AS dictionary, 975 ( SELECT pg_catalog.ts_lexize(mapdict, parse.token) 976 FROM pg_catalog.pg_ts_config_map AS m 977 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 978 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 979 LIMIT 1 980 ) AS lexemes 981FROM pg_catalog.ts_parse( 982 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2 983 ) AS parse, 984 pg_catalog.ts_token_type( 985 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ) 986 ) AS tt 987WHERE tt.tokid = parse.tokid 988$$ 989LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 990 991COMMENT ON FUNCTION ts_debug(regconfig,text) IS 992 'debug function for text search configuration'; 993 994CREATE FUNCTION ts_debug(IN document text, 995 OUT alias text, 996 OUT description text, 997 OUT token text, 998 OUT dictionaries regdictionary[], 999 OUT dictionary regdictionary, 1000 OUT lexemes text[]) 1001RETURNS SETOF record AS 1002$$ 1003 SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1); 1004$$ 1005LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 1006 1007COMMENT ON FUNCTION ts_debug(text) IS 1008 'debug function for current text search configuration'; 1009 1010-- 1011-- Redeclare built-in functions that need default values attached to their 1012-- arguments. It's impractical to set those up directly in pg_proc.h because 1013-- of the complexity and platform-dependency of the expression tree 1014-- representation. (Note that internal functions still have to have entries 1015-- in pg_proc.h; we are merely causing their proargnames and proargdefaults 1016-- to get filled in.) 1017-- 1018 1019CREATE OR REPLACE FUNCTION 1020 pg_start_backup(label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true) 1021 RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup' 1022 PARALLEL RESTRICTED; 1023 1024CREATE OR REPLACE FUNCTION pg_stop_backup ( 1025 exclusive boolean, wait_for_archive boolean DEFAULT true, 1026 OUT lsn pg_lsn, OUT labelfile text, OUT spcmapfile text) 1027 RETURNS SETOF record STRICT VOLATILE LANGUAGE internal as 'pg_stop_backup_v2' 1028 PARALLEL RESTRICTED; 1029 1030-- legacy definition for compatibility with 9.3 1031CREATE OR REPLACE FUNCTION 1032 json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1033 RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE; 1034 1035-- legacy definition for compatibility with 9.3 1036CREATE OR REPLACE FUNCTION 1037 json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1038 RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE; 1039 1040CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes( 1041 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1042 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1043RETURNS SETOF RECORD 1044LANGUAGE INTERNAL 1045VOLATILE ROWS 1000 COST 1000 1046AS 'pg_logical_slot_get_changes'; 1047 1048CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes( 1049 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1050 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1051RETURNS SETOF RECORD 1052LANGUAGE INTERNAL 1053VOLATILE ROWS 1000 COST 1000 1054AS 'pg_logical_slot_peek_changes'; 1055 1056CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes( 1057 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1058 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1059RETURNS SETOF RECORD 1060LANGUAGE INTERNAL 1061VOLATILE ROWS 1000 COST 1000 1062AS 'pg_logical_slot_get_binary_changes'; 1063 1064CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes( 1065 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1066 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1067RETURNS SETOF RECORD 1068LANGUAGE INTERNAL 1069VOLATILE ROWS 1000 COST 1000 1070AS 'pg_logical_slot_peek_binary_changes'; 1071 1072CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot( 1073 IN slot_name name, IN immediately_reserve boolean DEFAULT false, 1074 IN temporary boolean DEFAULT false, 1075 OUT slot_name name, OUT lsn pg_lsn) 1076RETURNS RECORD 1077LANGUAGE INTERNAL 1078STRICT VOLATILE 1079AS 'pg_create_physical_replication_slot'; 1080 1081CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot( 1082 IN slot_name name, IN plugin name, 1083 IN temporary boolean DEFAULT false, 1084 OUT slot_name text, OUT lsn pg_lsn) 1085RETURNS RECORD 1086LANGUAGE INTERNAL 1087STRICT VOLATILE 1088AS 'pg_create_logical_replication_slot'; 1089 1090CREATE OR REPLACE FUNCTION 1091 make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0, 1092 days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0, 1093 secs double precision DEFAULT 0.0) 1094RETURNS interval 1095LANGUAGE INTERNAL 1096STRICT IMMUTABLE PARALLEL SAFE 1097AS 'make_interval'; 1098 1099CREATE OR REPLACE FUNCTION 1100 jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb, 1101 create_if_missing boolean DEFAULT true) 1102RETURNS jsonb 1103LANGUAGE INTERNAL 1104STRICT IMMUTABLE PARALLEL SAFE 1105AS 'jsonb_set'; 1106 1107CREATE OR REPLACE FUNCTION 1108 parse_ident(str text, strict boolean DEFAULT true) 1109RETURNS text[] 1110LANGUAGE INTERNAL 1111STRICT IMMUTABLE PARALLEL SAFE 1112AS 'parse_ident'; 1113 1114CREATE OR REPLACE FUNCTION 1115 jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb, 1116 insert_after boolean DEFAULT false) 1117RETURNS jsonb 1118LANGUAGE INTERNAL 1119STRICT IMMUTABLE PARALLEL SAFE 1120AS 'jsonb_insert'; 1121 1122-- 1123-- The default permissions for functions mean that anyone can execute them. 1124-- A number of functions shouldn't be executable by just anyone, but rather 1125-- than use explicit 'superuser()' checks in those functions, we use the GRANT 1126-- system to REVOKE access to those functions at initdb time. Administrators 1127-- can later change who can access these functions, or leave them as only 1128-- available to superuser / cluster owner, if they choose. 1129-- 1130REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) FROM public; 1131REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public; 1132REVOKE EXECUTE ON FUNCTION pg_stop_backup(boolean, boolean) FROM public; 1133REVOKE EXECUTE ON FUNCTION pg_create_restore_point(text) FROM public; 1134REVOKE EXECUTE ON FUNCTION pg_switch_wal() FROM public; 1135REVOKE EXECUTE ON FUNCTION pg_wal_replay_pause() FROM public; 1136REVOKE EXECUTE ON FUNCTION pg_wal_replay_resume() FROM public; 1137REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public; 1138REVOKE EXECUTE ON FUNCTION pg_reload_conf() FROM public; 1139REVOKE EXECUTE ON FUNCTION pg_current_logfile() FROM public; 1140REVOKE EXECUTE ON FUNCTION pg_current_logfile(text) FROM public; 1141 1142REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public; 1143REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public; 1144REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public; 1145REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public; 1146 1147REVOKE EXECUTE ON FUNCTION lo_import(text) FROM public; 1148REVOKE EXECUTE ON FUNCTION lo_import(text, oid) FROM public; 1149REVOKE EXECUTE ON FUNCTION lo_export(oid, text) FROM public; 1150 1151REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public; 1152REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public; 1153 1154REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM public; 1155REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint) FROM public; 1156REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint,boolean) FROM public; 1157 1158REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM public; 1159REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint) FROM public; 1160REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint,boolean) FROM public; 1161 1162REVOKE EXECUTE ON FUNCTION pg_stat_file(text) FROM public; 1163REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public; 1164 1165REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public; 1166REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public; 1167 1168-- 1169-- We also set up some things as accessible to standard roles. 1170-- 1171GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor; 1172GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor; 1173 1174GRANT pg_read_all_settings TO pg_monitor; 1175GRANT pg_read_all_stats TO pg_monitor; 1176GRANT pg_stat_scan_tables TO pg_monitor; 1177