1/* 2 * PostgreSQL System Views 3 * 4 * Copyright (c) 1996-2020, 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 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', 'p') AND I.relkind IN ('i', '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_stats_ext WITH (security_barrier) AS 257 SELECT cn.nspname AS schemaname, 258 c.relname AS tablename, 259 sn.nspname AS statistics_schemaname, 260 s.stxname AS statistics_name, 261 pg_get_userbyid(s.stxowner) AS statistics_owner, 262 ( SELECT array_agg(a.attname ORDER BY a.attnum) 263 FROM unnest(s.stxkeys) k 264 JOIN pg_attribute a 265 ON (a.attrelid = s.stxrelid AND a.attnum = k) 266 ) AS attnames, 267 s.stxkind AS kinds, 268 sd.stxdndistinct AS n_distinct, 269 sd.stxddependencies AS dependencies, 270 m.most_common_vals, 271 m.most_common_val_nulls, 272 m.most_common_freqs, 273 m.most_common_base_freqs 274 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid) 275 JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid) 276 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace) 277 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace) 278 LEFT JOIN LATERAL 279 ( SELECT array_agg(values) AS most_common_vals, 280 array_agg(nulls) AS most_common_val_nulls, 281 array_agg(frequency) AS most_common_freqs, 282 array_agg(base_frequency) AS most_common_base_freqs 283 FROM pg_mcv_list_items(sd.stxdmcv) 284 ) m ON sd.stxdmcv IS NOT NULL 285 WHERE NOT EXISTS 286 ( SELECT 1 287 FROM unnest(stxkeys) k 288 JOIN pg_attribute a 289 ON (a.attrelid = s.stxrelid AND a.attnum = k) 290 WHERE NOT has_column_privilege(c.oid, a.attnum, 'select') ) 291 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); 292 293-- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data 294REVOKE ALL on pg_statistic_ext_data FROM public; 295 296CREATE VIEW pg_publication_tables AS 297 SELECT 298 P.pubname AS pubname, 299 N.nspname AS schemaname, 300 C.relname AS tablename 301 FROM pg_publication P, 302 LATERAL pg_get_publication_tables(P.pubname) GPT, 303 pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace) 304 WHERE C.oid = GPT.relid; 305 306CREATE VIEW pg_locks AS 307 SELECT * FROM pg_lock_status() AS L; 308 309CREATE VIEW pg_cursors AS 310 SELECT * FROM pg_cursor() AS C; 311 312CREATE VIEW pg_available_extensions AS 313 SELECT E.name, E.default_version, X.extversion AS installed_version, 314 E.comment 315 FROM pg_available_extensions() AS E 316 LEFT JOIN pg_extension AS X ON E.name = X.extname; 317 318CREATE VIEW pg_available_extension_versions AS 319 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed, 320 E.superuser, E.trusted, E.relocatable, 321 E.schema, E.requires, E.comment 322 FROM pg_available_extension_versions() AS E 323 LEFT JOIN pg_extension AS X 324 ON E.name = X.extname AND E.version = X.extversion; 325 326CREATE VIEW pg_prepared_xacts AS 327 SELECT P.transaction, P.gid, P.prepared, 328 U.rolname AS owner, D.datname AS database 329 FROM pg_prepared_xact() AS P 330 LEFT JOIN pg_authid U ON P.ownerid = U.oid 331 LEFT JOIN pg_database D ON P.dbid = D.oid; 332 333CREATE VIEW pg_prepared_statements AS 334 SELECT * FROM pg_prepared_statement() AS P; 335 336CREATE VIEW pg_seclabels AS 337SELECT 338 l.objoid, l.classoid, l.objsubid, 339 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text 340 WHEN rel.relkind = 'v' THEN 'view'::text 341 WHEN rel.relkind = 'm' THEN 'materialized view'::text 342 WHEN rel.relkind = 'S' THEN 'sequence'::text 343 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype, 344 rel.relnamespace AS objnamespace, 345 CASE WHEN pg_table_is_visible(rel.oid) 346 THEN quote_ident(rel.relname) 347 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 348 END AS objname, 349 l.provider, l.label 350FROM 351 pg_seclabel l 352 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 353 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 354WHERE 355 l.objsubid = 0 356UNION ALL 357SELECT 358 l.objoid, l.classoid, l.objsubid, 359 'column'::text AS objtype, 360 rel.relnamespace AS objnamespace, 361 CASE WHEN pg_table_is_visible(rel.oid) 362 THEN quote_ident(rel.relname) 363 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname) 364 END || '.' || att.attname AS objname, 365 l.provider, l.label 366FROM 367 pg_seclabel l 368 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid 369 JOIN pg_attribute att 370 ON rel.oid = att.attrelid AND l.objsubid = att.attnum 371 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid 372WHERE 373 l.objsubid != 0 374UNION ALL 375SELECT 376 l.objoid, l.classoid, l.objsubid, 377 CASE pro.prokind 378 WHEN 'a' THEN 'aggregate'::text 379 WHEN 'f' THEN 'function'::text 380 WHEN 'p' THEN 'procedure'::text 381 WHEN 'w' THEN 'window'::text END AS objtype, 382 pro.pronamespace AS objnamespace, 383 CASE WHEN pg_function_is_visible(pro.oid) 384 THEN quote_ident(pro.proname) 385 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname) 386 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname, 387 l.provider, l.label 388FROM 389 pg_seclabel l 390 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid 391 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid 392WHERE 393 l.objsubid = 0 394UNION ALL 395SELECT 396 l.objoid, l.classoid, l.objsubid, 397 CASE WHEN typ.typtype = 'd' THEN 'domain'::text 398 ELSE 'type'::text END AS objtype, 399 typ.typnamespace AS objnamespace, 400 CASE WHEN pg_type_is_visible(typ.oid) 401 THEN quote_ident(typ.typname) 402 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname) 403 END AS objname, 404 l.provider, l.label 405FROM 406 pg_seclabel l 407 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid 408 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid 409WHERE 410 l.objsubid = 0 411UNION ALL 412SELECT 413 l.objoid, l.classoid, l.objsubid, 414 'large object'::text AS objtype, 415 NULL::oid AS objnamespace, 416 l.objoid::text AS objname, 417 l.provider, l.label 418FROM 419 pg_seclabel l 420 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid 421WHERE 422 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0 423UNION ALL 424SELECT 425 l.objoid, l.classoid, l.objsubid, 426 'language'::text AS objtype, 427 NULL::oid AS objnamespace, 428 quote_ident(lan.lanname) AS objname, 429 l.provider, l.label 430FROM 431 pg_seclabel l 432 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid 433WHERE 434 l.objsubid = 0 435UNION ALL 436SELECT 437 l.objoid, l.classoid, l.objsubid, 438 'schema'::text AS objtype, 439 nsp.oid AS objnamespace, 440 quote_ident(nsp.nspname) AS objname, 441 l.provider, l.label 442FROM 443 pg_seclabel l 444 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid 445WHERE 446 l.objsubid = 0 447UNION ALL 448SELECT 449 l.objoid, l.classoid, l.objsubid, 450 'event trigger'::text AS objtype, 451 NULL::oid AS objnamespace, 452 quote_ident(evt.evtname) AS objname, 453 l.provider, l.label 454FROM 455 pg_seclabel l 456 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid 457 AND l.objoid = evt.oid 458WHERE 459 l.objsubid = 0 460UNION ALL 461SELECT 462 l.objoid, l.classoid, l.objsubid, 463 'publication'::text AS objtype, 464 NULL::oid AS objnamespace, 465 quote_ident(p.pubname) AS objname, 466 l.provider, l.label 467FROM 468 pg_seclabel l 469 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid 470WHERE 471 l.objsubid = 0 472UNION ALL 473SELECT 474 l.objoid, l.classoid, 0::int4 AS objsubid, 475 'subscription'::text AS objtype, 476 NULL::oid AS objnamespace, 477 quote_ident(s.subname) AS objname, 478 l.provider, l.label 479FROM 480 pg_shseclabel l 481 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid 482UNION ALL 483SELECT 484 l.objoid, l.classoid, 0::int4 AS objsubid, 485 'database'::text AS objtype, 486 NULL::oid AS objnamespace, 487 quote_ident(dat.datname) AS objname, 488 l.provider, l.label 489FROM 490 pg_shseclabel l 491 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid 492UNION ALL 493SELECT 494 l.objoid, l.classoid, 0::int4 AS objsubid, 495 'tablespace'::text AS objtype, 496 NULL::oid AS objnamespace, 497 quote_ident(spc.spcname) AS objname, 498 l.provider, l.label 499FROM 500 pg_shseclabel l 501 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid 502UNION ALL 503SELECT 504 l.objoid, l.classoid, 0::int4 AS objsubid, 505 'role'::text AS objtype, 506 NULL::oid AS objnamespace, 507 quote_ident(rol.rolname) AS objname, 508 l.provider, l.label 509FROM 510 pg_shseclabel l 511 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; 512 513CREATE VIEW pg_settings AS 514 SELECT * FROM pg_show_all_settings() AS A; 515 516CREATE RULE pg_settings_u AS 517 ON UPDATE TO pg_settings 518 WHERE new.name = old.name DO 519 SELECT set_config(old.name, new.setting, 'f'); 520 521CREATE RULE pg_settings_n AS 522 ON UPDATE TO pg_settings 523 DO INSTEAD NOTHING; 524 525GRANT SELECT, UPDATE ON pg_settings TO PUBLIC; 526 527CREATE VIEW pg_file_settings AS 528 SELECT * FROM pg_show_all_file_settings() AS A; 529 530REVOKE ALL on pg_file_settings FROM PUBLIC; 531REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC; 532 533CREATE VIEW pg_hba_file_rules AS 534 SELECT * FROM pg_hba_file_rules() AS A; 535 536REVOKE ALL on pg_hba_file_rules FROM PUBLIC; 537REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC; 538 539CREATE VIEW pg_timezone_abbrevs AS 540 SELECT * FROM pg_timezone_abbrevs(); 541 542CREATE VIEW pg_timezone_names AS 543 SELECT * FROM pg_timezone_names(); 544 545CREATE VIEW pg_config AS 546 SELECT * FROM pg_config(); 547 548REVOKE ALL on pg_config FROM PUBLIC; 549REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC; 550 551CREATE VIEW pg_shmem_allocations AS 552 SELECT * FROM pg_get_shmem_allocations(); 553 554REVOKE ALL ON pg_shmem_allocations FROM PUBLIC; 555REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC; 556 557-- Statistics views 558 559CREATE VIEW pg_stat_all_tables AS 560 SELECT 561 C.oid AS relid, 562 N.nspname AS schemaname, 563 C.relname AS relname, 564 pg_stat_get_numscans(C.oid) AS seq_scan, 565 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, 566 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan, 567 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint + 568 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch, 569 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, 570 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, 571 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, 572 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, 573 pg_stat_get_live_tuples(C.oid) AS n_live_tup, 574 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, 575 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, 576 pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum, 577 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum, 578 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum, 579 pg_stat_get_last_analyze_time(C.oid) as last_analyze, 580 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze, 581 pg_stat_get_vacuum_count(C.oid) AS vacuum_count, 582 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count, 583 pg_stat_get_analyze_count(C.oid) AS analyze_count, 584 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count 585 FROM pg_class C LEFT JOIN 586 pg_index I ON C.oid = I.indrelid 587 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 588 WHERE C.relkind IN ('r', 't', 'm') 589 GROUP BY C.oid, N.nspname, C.relname; 590 591CREATE VIEW pg_stat_xact_all_tables AS 592 SELECT 593 C.oid AS relid, 594 N.nspname AS schemaname, 595 C.relname AS relname, 596 pg_stat_get_xact_numscans(C.oid) AS seq_scan, 597 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read, 598 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan, 599 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint + 600 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch, 601 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, 602 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, 603 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, 604 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd 605 FROM pg_class C LEFT JOIN 606 pg_index I ON C.oid = I.indrelid 607 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 608 WHERE C.relkind IN ('r', 't', 'm') 609 GROUP BY C.oid, N.nspname, C.relname; 610 611CREATE VIEW pg_stat_sys_tables AS 612 SELECT * FROM pg_stat_all_tables 613 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 614 schemaname ~ '^pg_toast'; 615 616CREATE VIEW pg_stat_xact_sys_tables AS 617 SELECT * FROM pg_stat_xact_all_tables 618 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 619 schemaname ~ '^pg_toast'; 620 621CREATE VIEW pg_stat_user_tables AS 622 SELECT * FROM pg_stat_all_tables 623 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 624 schemaname !~ '^pg_toast'; 625 626CREATE VIEW pg_stat_xact_user_tables AS 627 SELECT * FROM pg_stat_xact_all_tables 628 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 629 schemaname !~ '^pg_toast'; 630 631CREATE VIEW pg_statio_all_tables AS 632 SELECT 633 C.oid AS relid, 634 N.nspname AS schemaname, 635 C.relname AS relname, 636 pg_stat_get_blocks_fetched(C.oid) - 637 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, 638 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, 639 sum(pg_stat_get_blocks_fetched(I.indexrelid) - 640 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read, 641 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit, 642 pg_stat_get_blocks_fetched(T.oid) - 643 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, 644 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, 645 pg_stat_get_blocks_fetched(X.indexrelid) - 646 pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_read, 647 pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_hit 648 FROM pg_class C LEFT JOIN 649 pg_index I ON C.oid = I.indrelid LEFT JOIN 650 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN 651 pg_index X ON T.oid = X.indrelid 652 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 653 WHERE C.relkind IN ('r', 't', 'm') 654 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indexrelid; 655 656CREATE VIEW pg_statio_sys_tables AS 657 SELECT * FROM pg_statio_all_tables 658 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 659 schemaname ~ '^pg_toast'; 660 661CREATE VIEW pg_statio_user_tables AS 662 SELECT * FROM pg_statio_all_tables 663 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 664 schemaname !~ '^pg_toast'; 665 666CREATE VIEW pg_stat_all_indexes AS 667 SELECT 668 C.oid AS relid, 669 I.oid AS indexrelid, 670 N.nspname AS schemaname, 671 C.relname AS relname, 672 I.relname AS indexrelname, 673 pg_stat_get_numscans(I.oid) AS idx_scan, 674 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, 675 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch 676 FROM pg_class C JOIN 677 pg_index X ON C.oid = X.indrelid JOIN 678 pg_class I ON I.oid = X.indexrelid 679 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 680 WHERE C.relkind IN ('r', 't', 'm'); 681 682CREATE VIEW pg_stat_sys_indexes AS 683 SELECT * FROM pg_stat_all_indexes 684 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 685 schemaname ~ '^pg_toast'; 686 687CREATE VIEW pg_stat_user_indexes AS 688 SELECT * FROM pg_stat_all_indexes 689 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 690 schemaname !~ '^pg_toast'; 691 692CREATE VIEW pg_statio_all_indexes AS 693 SELECT 694 C.oid AS relid, 695 I.oid AS indexrelid, 696 N.nspname AS schemaname, 697 C.relname AS relname, 698 I.relname AS indexrelname, 699 pg_stat_get_blocks_fetched(I.oid) - 700 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, 701 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit 702 FROM pg_class C JOIN 703 pg_index X ON C.oid = X.indrelid JOIN 704 pg_class I ON I.oid = X.indexrelid 705 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 706 WHERE C.relkind IN ('r', 't', 'm'); 707 708CREATE VIEW pg_statio_sys_indexes AS 709 SELECT * FROM pg_statio_all_indexes 710 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 711 schemaname ~ '^pg_toast'; 712 713CREATE VIEW pg_statio_user_indexes AS 714 SELECT * FROM pg_statio_all_indexes 715 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 716 schemaname !~ '^pg_toast'; 717 718CREATE VIEW pg_statio_all_sequences AS 719 SELECT 720 C.oid AS relid, 721 N.nspname AS schemaname, 722 C.relname AS relname, 723 pg_stat_get_blocks_fetched(C.oid) - 724 pg_stat_get_blocks_hit(C.oid) AS blks_read, 725 pg_stat_get_blocks_hit(C.oid) AS blks_hit 726 FROM pg_class C 727 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 728 WHERE C.relkind = 'S'; 729 730CREATE VIEW pg_statio_sys_sequences AS 731 SELECT * FROM pg_statio_all_sequences 732 WHERE schemaname IN ('pg_catalog', 'information_schema') OR 733 schemaname ~ '^pg_toast'; 734 735CREATE VIEW pg_statio_user_sequences AS 736 SELECT * FROM pg_statio_all_sequences 737 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND 738 schemaname !~ '^pg_toast'; 739 740CREATE VIEW pg_stat_activity AS 741 SELECT 742 S.datid AS datid, 743 D.datname AS datname, 744 S.pid, 745 S.leader_pid, 746 S.usesysid, 747 U.rolname AS usename, 748 S.application_name, 749 S.client_addr, 750 S.client_hostname, 751 S.client_port, 752 S.backend_start, 753 S.xact_start, 754 S.query_start, 755 S.state_change, 756 S.wait_event_type, 757 S.wait_event, 758 S.state, 759 S.backend_xid, 760 s.backend_xmin, 761 S.query, 762 S.backend_type 763 FROM pg_stat_get_activity(NULL) AS S 764 LEFT JOIN pg_database AS D ON (S.datid = D.oid) 765 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 766 767CREATE VIEW pg_stat_replication AS 768 SELECT 769 S.pid, 770 S.usesysid, 771 U.rolname AS usename, 772 S.application_name, 773 S.client_addr, 774 S.client_hostname, 775 S.client_port, 776 S.backend_start, 777 S.backend_xmin, 778 W.state, 779 W.sent_lsn, 780 W.write_lsn, 781 W.flush_lsn, 782 W.replay_lsn, 783 W.write_lag, 784 W.flush_lag, 785 W.replay_lag, 786 W.sync_priority, 787 W.sync_state, 788 W.reply_time 789 FROM pg_stat_get_activity(NULL) AS S 790 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid) 791 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); 792 793CREATE VIEW pg_stat_slru AS 794 SELECT 795 s.name, 796 s.blks_zeroed, 797 s.blks_hit, 798 s.blks_read, 799 s.blks_written, 800 s.blks_exists, 801 s.flushes, 802 s.truncates, 803 s.stats_reset 804 FROM pg_stat_get_slru() s; 805 806CREATE VIEW pg_stat_wal_receiver AS 807 SELECT 808 s.pid, 809 s.status, 810 s.receive_start_lsn, 811 s.receive_start_tli, 812 s.written_lsn, 813 s.flushed_lsn, 814 s.received_tli, 815 s.last_msg_send_time, 816 s.last_msg_receipt_time, 817 s.latest_end_lsn, 818 s.latest_end_time, 819 s.slot_name, 820 s.sender_host, 821 s.sender_port, 822 s.conninfo 823 FROM pg_stat_get_wal_receiver() s 824 WHERE s.pid IS NOT NULL; 825 826CREATE VIEW pg_stat_subscription AS 827 SELECT 828 su.oid AS subid, 829 su.subname, 830 st.pid, 831 st.relid, 832 st.received_lsn, 833 st.last_msg_send_time, 834 st.last_msg_receipt_time, 835 st.latest_end_lsn, 836 st.latest_end_time 837 FROM pg_subscription su 838 LEFT JOIN pg_stat_get_subscription(NULL) st 839 ON (st.subid = su.oid); 840 841CREATE VIEW pg_stat_ssl AS 842 SELECT 843 S.pid, 844 S.ssl, 845 S.sslversion AS version, 846 S.sslcipher AS cipher, 847 S.sslbits AS bits, 848 S.sslcompression AS compression, 849 S.ssl_client_dn AS client_dn, 850 S.ssl_client_serial AS client_serial, 851 S.ssl_issuer_dn AS issuer_dn 852 FROM pg_stat_get_activity(NULL) AS S 853 WHERE S.client_port IS NOT NULL; 854 855CREATE VIEW pg_stat_gssapi AS 856 SELECT 857 S.pid, 858 S.gss_auth AS gss_authenticated, 859 S.gss_princ AS principal, 860 S.gss_enc AS encrypted 861 FROM pg_stat_get_activity(NULL) AS S 862 WHERE S.client_port IS NOT NULL; 863 864CREATE VIEW pg_replication_slots AS 865 SELECT 866 L.slot_name, 867 L.plugin, 868 L.slot_type, 869 L.datoid, 870 D.datname AS database, 871 L.temporary, 872 L.active, 873 L.active_pid, 874 L.xmin, 875 L.catalog_xmin, 876 L.restart_lsn, 877 L.confirmed_flush_lsn, 878 L.wal_status, 879 L.safe_wal_size 880 FROM pg_get_replication_slots() AS L 881 LEFT JOIN pg_database D ON (L.datoid = D.oid); 882 883CREATE VIEW pg_stat_database AS 884 SELECT 885 D.oid AS datid, 886 D.datname AS datname, 887 CASE 888 WHEN (D.oid = (0)::oid) THEN 0 889 ELSE pg_stat_get_db_numbackends(D.oid) 890 END AS numbackends, 891 pg_stat_get_db_xact_commit(D.oid) AS xact_commit, 892 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, 893 pg_stat_get_db_blocks_fetched(D.oid) - 894 pg_stat_get_db_blocks_hit(D.oid) AS blks_read, 895 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit, 896 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned, 897 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, 898 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, 899 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, 900 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted, 901 pg_stat_get_db_conflict_all(D.oid) AS conflicts, 902 pg_stat_get_db_temp_files(D.oid) AS temp_files, 903 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes, 904 pg_stat_get_db_deadlocks(D.oid) AS deadlocks, 905 pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures, 906 pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure, 907 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time, 908 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time, 909 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset 910 FROM ( 911 SELECT 0 AS oid, NULL::name AS datname 912 UNION ALL 913 SELECT oid, datname FROM pg_database 914 ) D; 915 916CREATE VIEW pg_stat_database_conflicts AS 917 SELECT 918 D.oid AS datid, 919 D.datname AS datname, 920 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace, 921 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock, 922 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot, 923 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin, 924 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock 925 FROM pg_database D; 926 927CREATE VIEW pg_stat_user_functions AS 928 SELECT 929 P.oid AS funcid, 930 N.nspname AS schemaname, 931 P.proname AS funcname, 932 pg_stat_get_function_calls(P.oid) AS calls, 933 pg_stat_get_function_total_time(P.oid) AS total_time, 934 pg_stat_get_function_self_time(P.oid) AS self_time 935 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 936 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 937 AND pg_stat_get_function_calls(P.oid) IS NOT NULL; 938 939CREATE VIEW pg_stat_xact_user_functions AS 940 SELECT 941 P.oid AS funcid, 942 N.nspname AS schemaname, 943 P.proname AS funcname, 944 pg_stat_get_xact_function_calls(P.oid) AS calls, 945 pg_stat_get_xact_function_total_time(P.oid) AS total_time, 946 pg_stat_get_xact_function_self_time(P.oid) AS self_time 947 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace) 948 WHERE P.prolang != 12 -- fast check to eliminate built-in functions 949 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; 950 951CREATE VIEW pg_stat_archiver AS 952 SELECT 953 s.archived_count, 954 s.last_archived_wal, 955 s.last_archived_time, 956 s.failed_count, 957 s.last_failed_wal, 958 s.last_failed_time, 959 s.stats_reset 960 FROM pg_stat_get_archiver() s; 961 962CREATE VIEW pg_stat_bgwriter AS 963 SELECT 964 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 965 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 966 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 967 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 968 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 969 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 970 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 971 pg_stat_get_buf_written_backend() AS buffers_backend, 972 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 973 pg_stat_get_buf_alloc() AS buffers_alloc, 974 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 975 976CREATE VIEW pg_stat_progress_analyze AS 977 SELECT 978 S.pid AS pid, S.datid AS datid, D.datname AS datname, 979 CAST(S.relid AS oid) AS relid, 980 CASE S.param1 WHEN 0 THEN 'initializing' 981 WHEN 1 THEN 'acquiring sample rows' 982 WHEN 2 THEN 'acquiring inherited sample rows' 983 WHEN 3 THEN 'computing statistics' 984 WHEN 4 THEN 'computing extended statistics' 985 WHEN 5 THEN 'finalizing analyze' 986 END AS phase, 987 S.param2 AS sample_blks_total, 988 S.param3 AS sample_blks_scanned, 989 S.param4 AS ext_stats_total, 990 S.param5 AS ext_stats_computed, 991 S.param6 AS child_tables_total, 992 S.param7 AS child_tables_done, 993 CAST(S.param8 AS oid) AS current_child_table_relid 994 FROM pg_stat_get_progress_info('ANALYZE') AS S 995 LEFT JOIN pg_database D ON S.datid = D.oid; 996 997CREATE VIEW pg_stat_progress_vacuum AS 998 SELECT 999 S.pid AS pid, S.datid AS datid, D.datname AS datname, 1000 S.relid AS relid, 1001 CASE S.param1 WHEN 0 THEN 'initializing' 1002 WHEN 1 THEN 'scanning heap' 1003 WHEN 2 THEN 'vacuuming indexes' 1004 WHEN 3 THEN 'vacuuming heap' 1005 WHEN 4 THEN 'cleaning up indexes' 1006 WHEN 5 THEN 'truncating heap' 1007 WHEN 6 THEN 'performing final cleanup' 1008 END AS phase, 1009 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned, 1010 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count, 1011 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples 1012 FROM pg_stat_get_progress_info('VACUUM') AS S 1013 LEFT JOIN pg_database D ON S.datid = D.oid; 1014 1015CREATE VIEW pg_stat_progress_cluster AS 1016 SELECT 1017 S.pid AS pid, 1018 S.datid AS datid, 1019 D.datname AS datname, 1020 S.relid AS relid, 1021 CASE S.param1 WHEN 1 THEN 'CLUSTER' 1022 WHEN 2 THEN 'VACUUM FULL' 1023 END AS command, 1024 CASE S.param2 WHEN 0 THEN 'initializing' 1025 WHEN 1 THEN 'seq scanning heap' 1026 WHEN 2 THEN 'index scanning heap' 1027 WHEN 3 THEN 'sorting tuples' 1028 WHEN 4 THEN 'writing new heap' 1029 WHEN 5 THEN 'swapping relation files' 1030 WHEN 6 THEN 'rebuilding index' 1031 WHEN 7 THEN 'performing final cleanup' 1032 END AS phase, 1033 CAST(S.param3 AS oid) AS cluster_index_relid, 1034 S.param4 AS heap_tuples_scanned, 1035 S.param5 AS heap_tuples_written, 1036 S.param6 AS heap_blks_total, 1037 S.param7 AS heap_blks_scanned, 1038 S.param8 AS index_rebuild_count 1039 FROM pg_stat_get_progress_info('CLUSTER') AS S 1040 LEFT JOIN pg_database D ON S.datid = D.oid; 1041 1042CREATE VIEW pg_stat_progress_create_index AS 1043 SELECT 1044 S.pid AS pid, S.datid AS datid, D.datname AS datname, 1045 S.relid AS relid, 1046 CAST(S.param7 AS oid) AS index_relid, 1047 CASE S.param1 WHEN 1 THEN 'CREATE INDEX' 1048 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY' 1049 WHEN 3 THEN 'REINDEX' 1050 WHEN 4 THEN 'REINDEX CONCURRENTLY' 1051 END AS command, 1052 CASE S.param10 WHEN 0 THEN 'initializing' 1053 WHEN 1 THEN 'waiting for writers before build' 1054 WHEN 2 THEN 'building index' || 1055 COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)), 1056 '') 1057 WHEN 3 THEN 'waiting for writers before validation' 1058 WHEN 4 THEN 'index validation: scanning index' 1059 WHEN 5 THEN 'index validation: sorting tuples' 1060 WHEN 6 THEN 'index validation: scanning table' 1061 WHEN 7 THEN 'waiting for old snapshots' 1062 WHEN 8 THEN 'waiting for readers before marking dead' 1063 WHEN 9 THEN 'waiting for readers before dropping' 1064 END as phase, 1065 S.param4 AS lockers_total, 1066 S.param5 AS lockers_done, 1067 S.param6 AS current_locker_pid, 1068 S.param16 AS blocks_total, 1069 S.param17 AS blocks_done, 1070 S.param12 AS tuples_total, 1071 S.param13 AS tuples_done, 1072 S.param14 AS partitions_total, 1073 S.param15 AS partitions_done 1074 FROM pg_stat_get_progress_info('CREATE INDEX') AS S 1075 LEFT JOIN pg_database D ON S.datid = D.oid; 1076 1077CREATE VIEW pg_stat_progress_basebackup AS 1078 SELECT 1079 S.pid AS pid, 1080 CASE S.param1 WHEN 0 THEN 'initializing' 1081 WHEN 1 THEN 'waiting for checkpoint to finish' 1082 WHEN 2 THEN 'estimating backup size' 1083 WHEN 3 THEN 'streaming database files' 1084 WHEN 4 THEN 'waiting for wal archiving to finish' 1085 WHEN 5 THEN 'transferring wal files' 1086 END AS phase, 1087 CASE S.param2 WHEN -1 THEN NULL ELSE S.param2 END AS backup_total, 1088 S.param3 AS backup_streamed, 1089 S.param4 AS tablespaces_total, 1090 S.param5 AS tablespaces_streamed 1091 FROM pg_stat_get_progress_info('BASEBACKUP') AS S; 1092 1093CREATE VIEW pg_user_mappings AS 1094 SELECT 1095 U.oid AS umid, 1096 S.oid AS srvid, 1097 S.srvname AS srvname, 1098 U.umuser AS umuser, 1099 CASE WHEN U.umuser = 0 THEN 1100 'public' 1101 ELSE 1102 A.rolname 1103 END AS usename, 1104 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user 1105 AND (pg_has_role(S.srvowner, 'USAGE') 1106 OR has_server_privilege(S.oid, 'USAGE'))) 1107 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE')) 1108 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) 1109 THEN U.umoptions 1110 ELSE NULL END AS umoptions 1111 FROM pg_user_mapping U 1112 JOIN pg_foreign_server S ON (U.umserver = S.oid) 1113 LEFT JOIN pg_authid A ON (A.oid = U.umuser); 1114 1115REVOKE ALL on pg_user_mapping FROM public; 1116 1117CREATE VIEW pg_replication_origin_status AS 1118 SELECT * 1119 FROM pg_show_replication_origin_status(); 1120 1121REVOKE ALL ON pg_replication_origin_status FROM public; 1122 1123-- All columns of pg_subscription except subconninfo are readable. 1124REVOKE ALL ON pg_subscription FROM public; 1125GRANT SELECT (subdbid, subname, subowner, subenabled, subslotname, subpublications) 1126 ON pg_subscription TO public; 1127 1128 1129-- 1130-- We have a few function definitions in here, too. 1131-- At some point there might be enough to justify breaking them out into 1132-- a separate "system_functions.sql" file. 1133-- 1134 1135-- Tsearch debug function. Defined here because it'd be pretty unwieldy 1136-- to put it into pg_proc.h 1137 1138CREATE FUNCTION ts_debug(IN config regconfig, IN document text, 1139 OUT alias text, 1140 OUT description text, 1141 OUT token text, 1142 OUT dictionaries regdictionary[], 1143 OUT dictionary regdictionary, 1144 OUT lexemes text[]) 1145RETURNS SETOF record AS 1146$$ 1147SELECT 1148 tt.alias AS alias, 1149 tt.description AS description, 1150 parse.token AS token, 1151 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary 1152 FROM pg_catalog.pg_ts_config_map AS m 1153 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 1154 ORDER BY m.mapseqno ) 1155 AS dictionaries, 1156 ( SELECT mapdict::pg_catalog.regdictionary 1157 FROM pg_catalog.pg_ts_config_map AS m 1158 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 1159 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 1160 LIMIT 1 1161 ) AS dictionary, 1162 ( SELECT pg_catalog.ts_lexize(mapdict, parse.token) 1163 FROM pg_catalog.pg_ts_config_map AS m 1164 WHERE m.mapcfg = $1 AND m.maptokentype = parse.tokid 1165 ORDER BY pg_catalog.ts_lexize(mapdict, parse.token) IS NULL, m.mapseqno 1166 LIMIT 1 1167 ) AS lexemes 1168FROM pg_catalog.ts_parse( 1169 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ), $2 1170 ) AS parse, 1171 pg_catalog.ts_token_type( 1172 (SELECT cfgparser FROM pg_catalog.pg_ts_config WHERE oid = $1 ) 1173 ) AS tt 1174WHERE tt.tokid = parse.tokid 1175$$ 1176LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 1177 1178COMMENT ON FUNCTION ts_debug(regconfig,text) IS 1179 'debug function for text search configuration'; 1180 1181CREATE FUNCTION ts_debug(IN document text, 1182 OUT alias text, 1183 OUT description text, 1184 OUT token text, 1185 OUT dictionaries regdictionary[], 1186 OUT dictionary regdictionary, 1187 OUT lexemes text[]) 1188RETURNS SETOF record AS 1189$$ 1190 SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1); 1191$$ 1192LANGUAGE SQL STRICT STABLE PARALLEL SAFE; 1193 1194COMMENT ON FUNCTION ts_debug(text) IS 1195 'debug function for current text search configuration'; 1196 1197-- 1198-- Redeclare built-in functions that need default values attached to their 1199-- arguments. It's impractical to set those up directly in pg_proc.h because 1200-- of the complexity and platform-dependency of the expression tree 1201-- representation. (Note that internal functions still have to have entries 1202-- in pg_proc.h; we are merely causing their proargnames and proargdefaults 1203-- to get filled in.) 1204-- 1205 1206CREATE OR REPLACE FUNCTION 1207 pg_start_backup(label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true) 1208 RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup' 1209 PARALLEL RESTRICTED; 1210 1211CREATE OR REPLACE FUNCTION pg_stop_backup ( 1212 exclusive boolean, wait_for_archive boolean DEFAULT true, 1213 OUT lsn pg_lsn, OUT labelfile text, OUT spcmapfile text) 1214 RETURNS SETOF record STRICT VOLATILE LANGUAGE internal as 'pg_stop_backup_v2' 1215 PARALLEL RESTRICTED; 1216 1217CREATE OR REPLACE FUNCTION 1218 pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60) 1219 RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_promote' 1220 PARALLEL SAFE; 1221 1222-- legacy definition for compatibility with 9.3 1223CREATE OR REPLACE FUNCTION 1224 json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1225 RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record' PARALLEL SAFE; 1226 1227-- legacy definition for compatibility with 9.3 1228CREATE OR REPLACE FUNCTION 1229 json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) 1230 RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset' PARALLEL SAFE; 1231 1232CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes( 1233 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1234 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1235RETURNS SETOF RECORD 1236LANGUAGE INTERNAL 1237VOLATILE ROWS 1000 COST 1000 1238AS 'pg_logical_slot_get_changes'; 1239 1240CREATE OR REPLACE FUNCTION pg_logical_slot_peek_changes( 1241 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1242 OUT lsn pg_lsn, OUT xid xid, OUT data text) 1243RETURNS SETOF RECORD 1244LANGUAGE INTERNAL 1245VOLATILE ROWS 1000 COST 1000 1246AS 'pg_logical_slot_peek_changes'; 1247 1248CREATE OR REPLACE FUNCTION pg_logical_slot_get_binary_changes( 1249 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1250 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1251RETURNS SETOF RECORD 1252LANGUAGE INTERNAL 1253VOLATILE ROWS 1000 COST 1000 1254AS 'pg_logical_slot_get_binary_changes'; 1255 1256CREATE OR REPLACE FUNCTION pg_logical_slot_peek_binary_changes( 1257 IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', 1258 OUT lsn pg_lsn, OUT xid xid, OUT data bytea) 1259RETURNS SETOF RECORD 1260LANGUAGE INTERNAL 1261VOLATILE ROWS 1000 COST 1000 1262AS 'pg_logical_slot_peek_binary_changes'; 1263 1264CREATE OR REPLACE FUNCTION pg_create_physical_replication_slot( 1265 IN slot_name name, IN immediately_reserve boolean DEFAULT false, 1266 IN temporary boolean DEFAULT false, 1267 OUT slot_name name, OUT lsn pg_lsn) 1268RETURNS RECORD 1269LANGUAGE INTERNAL 1270STRICT VOLATILE 1271AS 'pg_create_physical_replication_slot'; 1272 1273CREATE OR REPLACE FUNCTION pg_create_logical_replication_slot( 1274 IN slot_name name, IN plugin name, 1275 IN temporary boolean DEFAULT false, 1276 OUT slot_name name, OUT lsn pg_lsn) 1277RETURNS RECORD 1278LANGUAGE INTERNAL 1279STRICT VOLATILE 1280AS 'pg_create_logical_replication_slot'; 1281 1282CREATE OR REPLACE FUNCTION 1283 make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0, 1284 days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0, 1285 secs double precision DEFAULT 0.0) 1286RETURNS interval 1287LANGUAGE INTERNAL 1288STRICT IMMUTABLE PARALLEL SAFE 1289AS 'make_interval'; 1290 1291CREATE OR REPLACE FUNCTION 1292 jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb, 1293 create_if_missing boolean DEFAULT true) 1294RETURNS jsonb 1295LANGUAGE INTERNAL 1296STRICT IMMUTABLE PARALLEL SAFE 1297AS 'jsonb_set'; 1298 1299CREATE OR REPLACE FUNCTION 1300 jsonb_set_lax(jsonb_in jsonb, path text[] , replacement jsonb, 1301 create_if_missing boolean DEFAULT true, 1302 null_value_treatment text DEFAULT 'use_json_null') 1303RETURNS jsonb 1304LANGUAGE INTERNAL 1305CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE 1306AS 'jsonb_set_lax'; 1307 1308CREATE OR REPLACE FUNCTION 1309 parse_ident(str text, strict boolean DEFAULT true) 1310RETURNS text[] 1311LANGUAGE INTERNAL 1312STRICT IMMUTABLE PARALLEL SAFE 1313AS 'parse_ident'; 1314 1315CREATE OR REPLACE FUNCTION 1316 jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb, 1317 insert_after boolean DEFAULT false) 1318RETURNS jsonb 1319LANGUAGE INTERNAL 1320STRICT IMMUTABLE PARALLEL SAFE 1321AS 'jsonb_insert'; 1322 1323CREATE OR REPLACE FUNCTION 1324 jsonb_path_exists(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1325 silent boolean DEFAULT false) 1326RETURNS boolean 1327LANGUAGE INTERNAL 1328STRICT IMMUTABLE PARALLEL SAFE 1329AS 'jsonb_path_exists'; 1330 1331CREATE OR REPLACE FUNCTION 1332 jsonb_path_match(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1333 silent boolean DEFAULT false) 1334RETURNS boolean 1335LANGUAGE INTERNAL 1336STRICT IMMUTABLE PARALLEL SAFE 1337AS 'jsonb_path_match'; 1338 1339CREATE OR REPLACE FUNCTION 1340 jsonb_path_query(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1341 silent boolean DEFAULT false) 1342RETURNS SETOF jsonb 1343LANGUAGE INTERNAL 1344STRICT IMMUTABLE PARALLEL SAFE 1345AS 'jsonb_path_query'; 1346 1347CREATE OR REPLACE FUNCTION 1348 jsonb_path_query_array(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1349 silent boolean DEFAULT false) 1350RETURNS jsonb 1351LANGUAGE INTERNAL 1352STRICT IMMUTABLE PARALLEL SAFE 1353AS 'jsonb_path_query_array'; 1354 1355CREATE OR REPLACE FUNCTION 1356 jsonb_path_query_first(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1357 silent boolean DEFAULT false) 1358RETURNS jsonb 1359LANGUAGE INTERNAL 1360STRICT IMMUTABLE PARALLEL SAFE 1361AS 'jsonb_path_query_first'; 1362 1363CREATE OR REPLACE FUNCTION 1364 jsonb_path_exists_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1365 silent boolean DEFAULT false) 1366RETURNS boolean 1367LANGUAGE INTERNAL 1368STRICT STABLE PARALLEL SAFE 1369AS 'jsonb_path_exists_tz'; 1370 1371CREATE OR REPLACE FUNCTION 1372 jsonb_path_match_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1373 silent boolean DEFAULT false) 1374RETURNS boolean 1375LANGUAGE INTERNAL 1376STRICT STABLE PARALLEL SAFE 1377AS 'jsonb_path_match_tz'; 1378 1379CREATE OR REPLACE FUNCTION 1380 jsonb_path_query_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1381 silent boolean DEFAULT false) 1382RETURNS SETOF jsonb 1383LANGUAGE INTERNAL 1384STRICT STABLE PARALLEL SAFE 1385AS 'jsonb_path_query_tz'; 1386 1387CREATE OR REPLACE FUNCTION 1388 jsonb_path_query_array_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1389 silent boolean DEFAULT false) 1390RETURNS jsonb 1391LANGUAGE INTERNAL 1392STRICT STABLE PARALLEL SAFE 1393AS 'jsonb_path_query_array_tz'; 1394 1395CREATE OR REPLACE FUNCTION 1396 jsonb_path_query_first_tz(target jsonb, path jsonpath, vars jsonb DEFAULT '{}', 1397 silent boolean DEFAULT false) 1398RETURNS jsonb 1399LANGUAGE INTERNAL 1400STRICT STABLE PARALLEL SAFE 1401AS 'jsonb_path_query_first_tz'; 1402 1403-- default normalization form is NFC, per SQL standard 1404CREATE OR REPLACE FUNCTION 1405 "normalize"(text, text DEFAULT 'NFC') 1406RETURNS text 1407LANGUAGE internal 1408STRICT IMMUTABLE PARALLEL SAFE 1409AS 'unicode_normalize_func'; 1410 1411CREATE OR REPLACE FUNCTION 1412 is_normalized(text, text DEFAULT 'NFC') 1413RETURNS boolean 1414LANGUAGE internal 1415STRICT IMMUTABLE PARALLEL SAFE 1416AS 'unicode_is_normalized'; 1417 1418-- 1419-- The default permissions for functions mean that anyone can execute them. 1420-- A number of functions shouldn't be executable by just anyone, but rather 1421-- than use explicit 'superuser()' checks in those functions, we use the GRANT 1422-- system to REVOKE access to those functions at initdb time. Administrators 1423-- can later change who can access these functions, or leave them as only 1424-- available to superuser / cluster owner, if they choose. 1425-- 1426REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean, boolean) FROM public; 1427REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public; 1428REVOKE EXECUTE ON FUNCTION pg_stop_backup(boolean, boolean) FROM public; 1429REVOKE EXECUTE ON FUNCTION pg_create_restore_point(text) FROM public; 1430REVOKE EXECUTE ON FUNCTION pg_switch_wal() FROM public; 1431REVOKE EXECUTE ON FUNCTION pg_wal_replay_pause() FROM public; 1432REVOKE EXECUTE ON FUNCTION pg_wal_replay_resume() FROM public; 1433REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public; 1434REVOKE EXECUTE ON FUNCTION pg_reload_conf() FROM public; 1435REVOKE EXECUTE ON FUNCTION pg_current_logfile() FROM public; 1436REVOKE EXECUTE ON FUNCTION pg_current_logfile(text) FROM public; 1437REVOKE EXECUTE ON FUNCTION pg_promote(boolean, integer) FROM public; 1438 1439REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public; 1440REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public; 1441REVOKE EXECUTE ON FUNCTION pg_stat_reset_slru(text) FROM public; 1442REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public; 1443REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public; 1444 1445REVOKE EXECUTE ON FUNCTION lo_import(text) FROM public; 1446REVOKE EXECUTE ON FUNCTION lo_import(text, oid) FROM public; 1447REVOKE EXECUTE ON FUNCTION lo_export(oid, text) FROM public; 1448 1449REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public; 1450REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public; 1451REVOKE EXECUTE ON FUNCTION pg_ls_archive_statusdir() FROM public; 1452REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir() FROM public; 1453REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir(oid) FROM public; 1454 1455REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM public; 1456REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint) FROM public; 1457REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint,boolean) FROM public; 1458 1459REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM public; 1460REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint) FROM public; 1461REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint,boolean) FROM public; 1462 1463REVOKE EXECUTE ON FUNCTION pg_stat_file(text) FROM public; 1464REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public; 1465 1466REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public; 1467REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public; 1468 1469-- 1470-- We also set up some things as accessible to standard roles. 1471-- 1472GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor; 1473GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor; 1474GRANT EXECUTE ON FUNCTION pg_ls_archive_statusdir() TO pg_monitor; 1475GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor; 1476GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor; 1477 1478GRANT pg_read_all_settings TO pg_monitor; 1479GRANT pg_read_all_stats TO pg_monitor; 1480GRANT pg_stat_scan_tables TO pg_monitor; 1481