2 * PostgreSQL System Views
4 * Copyright (c) 1996-2022, PostgreSQL Global Development Group
6 * src/backend/catalog/system_views.sql
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.
17 CREATE VIEW pg_roles AS
27 '********'::text as rolpassword,
30 setconfig as rolconfig,
32 FROM pg_authid LEFT JOIN pg_db_role_setting s
33 ON (pg_authid.oid = setrole AND setdatabase = 0);
35 CREATE VIEW pg_shadow AS
38 pg_authid.oid AS usesysid,
39 rolcreatedb AS usecreatedb,
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)
50 REVOKE ALL ON pg_shadow FROM public;
52 CREATE VIEW pg_group AS
56 ARRAY(SELECT member FROM pg_auth_members WHERE roleid = oid) AS grolist
58 WHERE NOT rolcanlogin;
60 CREATE VIEW pg_user AS
68 '********'::text as passwd,
73 CREATE VIEW pg_policies AS
75 N.nspname AS schemaname,
76 C.relname AS tablename,
77 pol.polname AS policyname,
79 WHEN pol.polpermissive THEN
85 WHEN pol.polroles = '{0}' THEN
86 string_to_array('public', '')
91 FROM pg_catalog.pg_authid
92 WHERE oid = ANY (pol.polroles) ORDER BY 1
96 WHEN 'r' THEN 'SELECT'
97 WHEN 'a' THEN 'INSERT'
98 WHEN 'w' THEN 'UPDATE'
99 WHEN 'd' THEN 'DELETE'
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);
108 CREATE VIEW pg_rules AS
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';
118 CREATE VIEW pg_views AS
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';
127 CREATE VIEW pg_tables AS
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');
141 CREATE VIEW pg_matviews AS
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';
154 CREATE VIEW pg_indexes AS
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');
167 CREATE VIEW pg_sequences AS
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,
178 S.seqcache AS cache_size,
180 WHEN has_sequence_privilege(C.oid, 'SELECT,USAGE'::text)
181 THEN pg_sequence_last_value(C.oid)
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)
189 CREATE VIEW pg_stats WITH (security_barrier) AS
191 nspname AS schemaname,
192 relname AS tablename,
194 stainherit AS inherited,
195 stanullfrac AS null_frac,
196 stawidth AS avg_width,
197 stadistinct AS n_distinct,
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,
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,
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,
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]
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,
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,
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));
254 REVOKE ALL ON pg_statistic FROM public;
256 CREATE 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
265 ON (a.attrelid = s.stxrelid AND a.attnum = k)
267 pg_get_statisticsobjdef_expressions(s.oid) as exprs,
269 sd.stxdndistinct AS n_distinct,
270 sd.stxddependencies AS dependencies,
272 m.most_common_val_nulls,
274 m.most_common_base_freqs
275 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
276 JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
277 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
278 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
280 ( SELECT array_agg(values) AS most_common_vals,
281 array_agg(nulls) AS most_common_val_nulls,
282 array_agg(frequency) AS most_common_freqs,
283 array_agg(base_frequency) AS most_common_base_freqs
284 FROM pg_mcv_list_items(sd.stxdmcv)
285 ) m ON sd.stxdmcv IS NOT NULL
288 FROM unnest(stxkeys) k
290 ON (a.attrelid = s.stxrelid AND a.attnum = k)
291 WHERE NOT has_column_privilege(c.oid, a.attnum, 'select') )
292 AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));
294 CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
295 SELECT cn.nspname AS schemaname,
296 c.relname AS tablename,
297 sn.nspname AS statistics_schemaname,
298 s.stxname AS statistics_name,
299 pg_get_userbyid(s.stxowner) AS statistics_owner,
301 (stat.a).stanullfrac AS null_frac,
302 (stat.a).stawidth AS avg_width,
303 (stat.a).stadistinct AS n_distinct,
305 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stavalues1
306 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stavalues2
307 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stavalues3
308 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stavalues4
309 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stavalues5
310 END) AS most_common_vals,
312 WHEN (stat.a).stakind1 = 1 THEN (stat.a).stanumbers1
313 WHEN (stat.a).stakind2 = 1 THEN (stat.a).stanumbers2
314 WHEN (stat.a).stakind3 = 1 THEN (stat.a).stanumbers3
315 WHEN (stat.a).stakind4 = 1 THEN (stat.a).stanumbers4
316 WHEN (stat.a).stakind5 = 1 THEN (stat.a).stanumbers5
317 END) AS most_common_freqs,
319 WHEN (stat.a).stakind1 = 2 THEN (stat.a).stavalues1
320 WHEN (stat.a).stakind2 = 2 THEN (stat.a).stavalues2
321 WHEN (stat.a).stakind3 = 2 THEN (stat.a).stavalues3
322 WHEN (stat.a).stakind4 = 2 THEN (stat.a).stavalues4
323 WHEN (stat.a).stakind5 = 2 THEN (stat.a).stavalues5
324 END) AS histogram_bounds,
326 WHEN (stat.a).stakind1 = 3 THEN (stat.a).stanumbers1[1]
327 WHEN (stat.a).stakind2 = 3 THEN (stat.a).stanumbers2[1]
328 WHEN (stat.a).stakind3 = 3 THEN (stat.a).stanumbers3[1]
329 WHEN (stat.a).stakind4 = 3 THEN (stat.a).stanumbers4[1]
330 WHEN (stat.a).stakind5 = 3 THEN (stat.a).stanumbers5[1]
333 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stavalues1
334 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stavalues2
335 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stavalues3
336 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stavalues4
337 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stavalues5
338 END) AS most_common_elems,
340 WHEN (stat.a).stakind1 = 4 THEN (stat.a).stanumbers1
341 WHEN (stat.a).stakind2 = 4 THEN (stat.a).stanumbers2
342 WHEN (stat.a).stakind3 = 4 THEN (stat.a).stanumbers3
343 WHEN (stat.a).stakind4 = 4 THEN (stat.a).stanumbers4
344 WHEN (stat.a).stakind5 = 4 THEN (stat.a).stanumbers5
345 END) AS most_common_elem_freqs,
347 WHEN (stat.a).stakind1 = 5 THEN (stat.a).stanumbers1
348 WHEN (stat.a).stakind2 = 5 THEN (stat.a).stanumbers2
349 WHEN (stat.a).stakind3 = 5 THEN (stat.a).stanumbers3
350 WHEN (stat.a).stakind4 = 5 THEN (stat.a).stanumbers4
351 WHEN (stat.a).stakind5 = 5 THEN (stat.a).stanumbers5
352 END) AS elem_count_histogram
353 FROM pg_statistic_ext s JOIN pg_class c ON (c.oid = s.stxrelid)
354 LEFT JOIN pg_statistic_ext_data sd ON (s.oid = sd.stxoid)
355 LEFT JOIN pg_namespace cn ON (cn.oid = c.relnamespace)
356 LEFT JOIN pg_namespace sn ON (sn.oid = s.stxnamespace)
358 SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
359 unnest(sd.stxdexpr)::pg_statistic AS a
360 ) stat ON (stat.expr IS NOT NULL);
362 -- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data
363 REVOKE ALL ON pg_statistic_ext_data FROM public;
365 CREATE VIEW pg_publication_tables AS
367 P.pubname AS pubname,
368 N.nspname AS schemaname,
369 C.relname AS tablename
370 FROM pg_publication P,
371 LATERAL pg_get_publication_tables(P.pubname) GPT,
372 pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)
373 WHERE C.oid = GPT.relid;
375 CREATE VIEW pg_locks AS
376 SELECT * FROM pg_lock_status() AS L;
378 CREATE VIEW pg_cursors AS
379 SELECT * FROM pg_cursor() AS C;
381 CREATE VIEW pg_available_extensions AS
382 SELECT E.name, E.default_version, X.extversion AS installed_version,
384 FROM pg_available_extensions() AS E
385 LEFT JOIN pg_extension AS X ON E.name = X.extname;
387 CREATE VIEW pg_available_extension_versions AS
388 SELECT E.name, E.version, (X.extname IS NOT NULL) AS installed,
389 E.superuser, E.trusted, E.relocatable,
390 E.schema, E.requires, E.comment
391 FROM pg_available_extension_versions() AS E
392 LEFT JOIN pg_extension AS X
393 ON E.name = X.extname AND E.version = X.extversion;
395 CREATE VIEW pg_prepared_xacts AS
396 SELECT P.transaction, P.gid, P.prepared,
397 U.rolname AS owner, D.datname AS database
398 FROM pg_prepared_xact() AS P
399 LEFT JOIN pg_authid U ON P.ownerid = U.oid
400 LEFT JOIN pg_database D ON P.dbid = D.oid;
402 CREATE VIEW pg_prepared_statements AS
403 SELECT * FROM pg_prepared_statement() AS P;
405 CREATE VIEW pg_seclabels AS
407 l.objoid, l.classoid, l.objsubid,
408 CASE WHEN rel.relkind IN ('r', 'p') THEN 'table'::text
409 WHEN rel.relkind = 'v' THEN 'view'::text
410 WHEN rel.relkind = 'm' THEN 'materialized view'::text
411 WHEN rel.relkind = 'S' THEN 'sequence'::text
412 WHEN rel.relkind = 'f' THEN 'foreign table'::text END AS objtype,
413 rel.relnamespace AS objnamespace,
414 CASE WHEN pg_table_is_visible(rel.oid)
415 THEN quote_ident(rel.relname)
416 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
421 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
422 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
427 l.objoid, l.classoid, l.objsubid,
428 'column'::text AS objtype,
429 rel.relnamespace AS objnamespace,
430 CASE WHEN pg_table_is_visible(rel.oid)
431 THEN quote_ident(rel.relname)
432 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
433 END || '.' || att.attname AS objname,
437 JOIN pg_class rel ON l.classoid = rel.tableoid AND l.objoid = rel.oid
438 JOIN pg_attribute att
439 ON rel.oid = att.attrelid AND l.objsubid = att.attnum
440 JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
445 l.objoid, l.classoid, l.objsubid,
447 WHEN 'a' THEN 'aggregate'::text
448 WHEN 'f' THEN 'function'::text
449 WHEN 'p' THEN 'procedure'::text
450 WHEN 'w' THEN 'window'::text END AS objtype,
451 pro.pronamespace AS objnamespace,
452 CASE WHEN pg_function_is_visible(pro.oid)
453 THEN quote_ident(pro.proname)
454 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname)
455 END || '(' || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname,
459 JOIN pg_proc pro ON l.classoid = pro.tableoid AND l.objoid = pro.oid
460 JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid
465 l.objoid, l.classoid, l.objsubid,
466 CASE WHEN typ.typtype = 'd' THEN 'domain'::text
467 ELSE 'type'::text END AS objtype,
468 typ.typnamespace AS objnamespace,
469 CASE WHEN pg_type_is_visible(typ.oid)
470 THEN quote_ident(typ.typname)
471 ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname)
476 JOIN pg_type typ ON l.classoid = typ.tableoid AND l.objoid = typ.oid
477 JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid
482 l.objoid, l.classoid, l.objsubid,
483 'large object'::text AS objtype,
484 NULL::oid AS objnamespace,
485 l.objoid::text AS objname,
489 JOIN pg_largeobject_metadata lom ON l.objoid = lom.oid
491 l.classoid = 'pg_catalog.pg_largeobject'::regclass AND l.objsubid = 0
494 l.objoid, l.classoid, l.objsubid,
495 'language'::text AS objtype,
496 NULL::oid AS objnamespace,
497 quote_ident(lan.lanname) AS objname,
501 JOIN pg_language lan ON l.classoid = lan.tableoid AND l.objoid = lan.oid
506 l.objoid, l.classoid, l.objsubid,
507 'schema'::text AS objtype,
508 nsp.oid AS objnamespace,
509 quote_ident(nsp.nspname) AS objname,
513 JOIN pg_namespace nsp ON l.classoid = nsp.tableoid AND l.objoid = nsp.oid
518 l.objoid, l.classoid, l.objsubid,
519 'event trigger'::text AS objtype,
520 NULL::oid AS objnamespace,
521 quote_ident(evt.evtname) AS objname,
525 JOIN pg_event_trigger evt ON l.classoid = evt.tableoid
526 AND l.objoid = evt.oid
531 l.objoid, l.classoid, l.objsubid,
532 'publication'::text AS objtype,
533 NULL::oid AS objnamespace,
534 quote_ident(p.pubname) AS objname,
538 JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid
543 l.objoid, l.classoid, 0::int4 AS objsubid,
544 'subscription'::text AS objtype,
545 NULL::oid AS objnamespace,
546 quote_ident(s.subname) AS objname,
550 JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid
553 l.objoid, l.classoid, 0::int4 AS objsubid,
554 'database'::text AS objtype,
555 NULL::oid AS objnamespace,
556 quote_ident(dat.datname) AS objname,
560 JOIN pg_database dat ON l.classoid = dat.tableoid AND l.objoid = dat.oid
563 l.objoid, l.classoid, 0::int4 AS objsubid,
564 'tablespace'::text AS objtype,
565 NULL::oid AS objnamespace,
566 quote_ident(spc.spcname) AS objname,
570 JOIN pg_tablespace spc ON l.classoid = spc.tableoid AND l.objoid = spc.oid
573 l.objoid, l.classoid, 0::int4 AS objsubid,
574 'role'::text AS objtype,
575 NULL::oid AS objnamespace,
576 quote_ident(rol.rolname) AS objname,
580 JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
582 CREATE VIEW pg_settings AS
583 SELECT * FROM pg_show_all_settings() AS A;
585 CREATE RULE pg_settings_u AS
586 ON UPDATE TO pg_settings
587 WHERE new.name = old.name DO
588 SELECT set_config(old.name, new.setting, 'f');
590 CREATE RULE pg_settings_n AS
591 ON UPDATE TO pg_settings
594 GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
596 CREATE VIEW pg_file_settings AS
597 SELECT * FROM pg_show_all_file_settings() AS A;
599 REVOKE ALL ON pg_file_settings FROM PUBLIC;
600 REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
602 CREATE VIEW pg_hba_file_rules AS
603 SELECT * FROM pg_hba_file_rules() AS A;
605 REVOKE ALL ON pg_hba_file_rules FROM PUBLIC;
606 REVOKE EXECUTE ON FUNCTION pg_hba_file_rules() FROM PUBLIC;
608 CREATE VIEW pg_timezone_abbrevs AS
609 SELECT * FROM pg_timezone_abbrevs();
611 CREATE VIEW pg_timezone_names AS
612 SELECT * FROM pg_timezone_names();
614 CREATE VIEW pg_config AS
615 SELECT * FROM pg_config();
617 REVOKE ALL ON pg_config FROM PUBLIC;
618 REVOKE EXECUTE ON FUNCTION pg_config() FROM PUBLIC;
620 CREATE VIEW pg_shmem_allocations AS
621 SELECT * FROM pg_get_shmem_allocations();
623 REVOKE ALL ON pg_shmem_allocations FROM PUBLIC;
624 GRANT SELECT ON pg_shmem_allocations TO pg_read_all_stats;
625 REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;
626 GRANT EXECUTE ON FUNCTION pg_get_shmem_allocations() TO pg_read_all_stats;
628 CREATE VIEW pg_backend_memory_contexts AS
629 SELECT * FROM pg_get_backend_memory_contexts();
631 REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;
632 GRANT SELECT ON pg_backend_memory_contexts TO pg_read_all_stats;
633 REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;
634 GRANT EXECUTE ON FUNCTION pg_get_backend_memory_contexts() TO pg_read_all_stats;
638 CREATE VIEW pg_stat_all_tables AS
641 N.nspname AS schemaname,
642 C.relname AS relname,
643 pg_stat_get_numscans(C.oid) AS seq_scan,
644 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,
645 sum(pg_stat_get_numscans(I.indexrelid))::bigint AS idx_scan,
646 sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint +
647 pg_stat_get_tuples_fetched(C.oid) AS idx_tup_fetch,
648 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
649 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
650 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
651 pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
652 pg_stat_get_live_tuples(C.oid) AS n_live_tup,
653 pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
654 pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
655 pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
656 pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
657 pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
658 pg_stat_get_last_analyze_time(C.oid) as last_analyze,
659 pg_stat_get_last_autoanalyze_time(C.oid) as last_autoanalyze,
660 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
661 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
662 pg_stat_get_analyze_count(C.oid) AS analyze_count,
663 pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
664 FROM pg_class C LEFT JOIN
665 pg_index I ON C.oid = I.indrelid
666 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
667 WHERE C.relkind IN ('r', 't', 'm', 'p')
668 GROUP BY C.oid, N.nspname, C.relname;
670 CREATE VIEW pg_stat_xact_all_tables AS
673 N.nspname AS schemaname,
674 C.relname AS relname,
675 pg_stat_get_xact_numscans(C.oid) AS seq_scan,
676 pg_stat_get_xact_tuples_returned(C.oid) AS seq_tup_read,
677 sum(pg_stat_get_xact_numscans(I.indexrelid))::bigint AS idx_scan,
678 sum(pg_stat_get_xact_tuples_fetched(I.indexrelid))::bigint +
679 pg_stat_get_xact_tuples_fetched(C.oid) AS idx_tup_fetch,
680 pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
681 pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
682 pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
683 pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd
684 FROM pg_class C LEFT JOIN
685 pg_index I ON C.oid = I.indrelid
686 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
687 WHERE C.relkind IN ('r', 't', 'm', 'p')
688 GROUP BY C.oid, N.nspname, C.relname;
690 CREATE VIEW pg_stat_sys_tables AS
691 SELECT * FROM pg_stat_all_tables
692 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
693 schemaname ~ '^pg_toast';
695 CREATE VIEW pg_stat_xact_sys_tables AS
696 SELECT * FROM pg_stat_xact_all_tables
697 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
698 schemaname ~ '^pg_toast';
700 CREATE VIEW pg_stat_user_tables AS
701 SELECT * FROM pg_stat_all_tables
702 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
703 schemaname !~ '^pg_toast';
705 CREATE VIEW pg_stat_xact_user_tables AS
706 SELECT * FROM pg_stat_xact_all_tables
707 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
708 schemaname !~ '^pg_toast';
710 CREATE VIEW pg_statio_all_tables AS
713 N.nspname AS schemaname,
714 C.relname AS relname,
715 pg_stat_get_blocks_fetched(C.oid) -
716 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,
717 pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit,
718 sum(pg_stat_get_blocks_fetched(I.indexrelid) -
719 pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_read,
720 sum(pg_stat_get_blocks_hit(I.indexrelid))::bigint AS idx_blks_hit,
721 pg_stat_get_blocks_fetched(T.oid) -
722 pg_stat_get_blocks_hit(T.oid) AS toast_blks_read,
723 pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit,
724 pg_stat_get_blocks_fetched(X.indexrelid) -
725 pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_read,
726 pg_stat_get_blocks_hit(X.indexrelid) AS tidx_blks_hit
727 FROM pg_class C LEFT JOIN
728 pg_index I ON C.oid = I.indrelid LEFT JOIN
729 pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
730 pg_index X ON T.oid = X.indrelid
731 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
732 WHERE C.relkind IN ('r', 't', 'm')
733 GROUP BY C.oid, N.nspname, C.relname, T.oid, X.indexrelid;
735 CREATE VIEW pg_statio_sys_tables AS
736 SELECT * FROM pg_statio_all_tables
737 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
738 schemaname ~ '^pg_toast';
740 CREATE VIEW pg_statio_user_tables AS
741 SELECT * FROM pg_statio_all_tables
742 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
743 schemaname !~ '^pg_toast';
745 CREATE VIEW pg_stat_all_indexes AS
749 N.nspname AS schemaname,
750 C.relname AS relname,
751 I.relname AS indexrelname,
752 pg_stat_get_numscans(I.oid) AS idx_scan,
753 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,
754 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch
756 pg_index X ON C.oid = X.indrelid JOIN
757 pg_class I ON I.oid = X.indexrelid
758 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
759 WHERE C.relkind IN ('r', 't', 'm');
761 CREATE VIEW pg_stat_sys_indexes AS
762 SELECT * FROM pg_stat_all_indexes
763 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
764 schemaname ~ '^pg_toast';
766 CREATE VIEW pg_stat_user_indexes AS
767 SELECT * FROM pg_stat_all_indexes
768 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
769 schemaname !~ '^pg_toast';
771 CREATE VIEW pg_statio_all_indexes AS
775 N.nspname AS schemaname,
776 C.relname AS relname,
777 I.relname AS indexrelname,
778 pg_stat_get_blocks_fetched(I.oid) -
779 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,
780 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit
782 pg_index X ON C.oid = X.indrelid JOIN
783 pg_class I ON I.oid = X.indexrelid
784 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
785 WHERE C.relkind IN ('r', 't', 'm');
787 CREATE VIEW pg_statio_sys_indexes AS
788 SELECT * FROM pg_statio_all_indexes
789 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
790 schemaname ~ '^pg_toast';
792 CREATE VIEW pg_statio_user_indexes AS
793 SELECT * FROM pg_statio_all_indexes
794 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
795 schemaname !~ '^pg_toast';
797 CREATE VIEW pg_statio_all_sequences AS
800 N.nspname AS schemaname,
801 C.relname AS relname,
802 pg_stat_get_blocks_fetched(C.oid) -
803 pg_stat_get_blocks_hit(C.oid) AS blks_read,
804 pg_stat_get_blocks_hit(C.oid) AS blks_hit
806 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
807 WHERE C.relkind = 'S';
809 CREATE VIEW pg_statio_sys_sequences AS
810 SELECT * FROM pg_statio_all_sequences
811 WHERE schemaname IN ('pg_catalog', 'information_schema') OR
812 schemaname ~ '^pg_toast';
814 CREATE VIEW pg_statio_user_sequences AS
815 SELECT * FROM pg_statio_all_sequences
816 WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND
817 schemaname !~ '^pg_toast';
819 CREATE VIEW pg_stat_activity AS
822 D.datname AS datname,
826 U.rolname AS usename,
843 FROM pg_stat_get_activity(NULL) AS S
844 LEFT JOIN pg_database AS D ON (S.datid = D.oid)
845 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
847 CREATE VIEW pg_stat_replication AS
851 U.rolname AS usename,
869 FROM pg_stat_get_activity(NULL) AS S
870 JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid)
871 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
873 CREATE VIEW pg_stat_slru AS
884 FROM pg_stat_get_slru() s;
886 CREATE VIEW pg_stat_wal_receiver AS
895 s.last_msg_send_time,
896 s.last_msg_receipt_time,
903 FROM pg_stat_get_wal_receiver() s
904 WHERE s.pid IS NOT NULL;
906 CREATE VIEW pg_stat_subscription AS
913 st.last_msg_send_time,
914 st.last_msg_receipt_time,
917 FROM pg_subscription su
918 LEFT JOIN pg_stat_get_subscription(NULL) st
919 ON (st.subid = su.oid);
921 CREATE VIEW pg_stat_ssl AS
925 S.sslversion AS version,
926 S.sslcipher AS cipher,
928 S.ssl_client_dn AS client_dn,
929 S.ssl_client_serial AS client_serial,
930 S.ssl_issuer_dn AS issuer_dn
931 FROM pg_stat_get_activity(NULL) AS S
932 WHERE S.client_port IS NOT NULL;
934 CREATE VIEW pg_stat_gssapi AS
937 S.gss_auth AS gss_authenticated,
938 S.gss_princ AS principal,
939 S.gss_enc AS encrypted
940 FROM pg_stat_get_activity(NULL) AS S
941 WHERE S.client_port IS NOT NULL;
943 CREATE VIEW pg_replication_slots AS
949 D.datname AS database,
956 L.confirmed_flush_lsn,
960 FROM pg_get_replication_slots() AS L
961 LEFT JOIN pg_database D ON (L.datoid = D.oid);
963 CREATE VIEW pg_stat_replication_slots AS
975 FROM pg_replication_slots as r,
976 LATERAL pg_stat_get_replication_slot(slot_name) as s
977 WHERE r.datoid IS NOT NULL; -- excluding physical slots
979 CREATE VIEW pg_stat_database AS
982 D.datname AS datname,
984 WHEN (D.oid = (0)::oid) THEN 0
985 ELSE pg_stat_get_db_numbackends(D.oid)
987 pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
988 pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
989 pg_stat_get_db_blocks_fetched(D.oid) -
990 pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
991 pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
992 pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
993 pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
994 pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
995 pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
996 pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
997 pg_stat_get_db_conflict_all(D.oid) AS conflicts,
998 pg_stat_get_db_temp_files(D.oid) AS temp_files,
999 pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,
1000 pg_stat_get_db_deadlocks(D.oid) AS deadlocks,
1001 pg_stat_get_db_checksum_failures(D.oid) AS checksum_failures,
1002 pg_stat_get_db_checksum_last_failure(D.oid) AS checksum_last_failure,
1003 pg_stat_get_db_blk_read_time(D.oid) AS blk_read_time,
1004 pg_stat_get_db_blk_write_time(D.oid) AS blk_write_time,
1005 pg_stat_get_db_session_time(D.oid) AS session_time,
1006 pg_stat_get_db_active_time(D.oid) AS active_time,
1007 pg_stat_get_db_idle_in_transaction_time(D.oid) AS idle_in_transaction_time,
1008 pg_stat_get_db_sessions(D.oid) AS sessions,
1009 pg_stat_get_db_sessions_abandoned(D.oid) AS sessions_abandoned,
1010 pg_stat_get_db_sessions_fatal(D.oid) AS sessions_fatal,
1011 pg_stat_get_db_sessions_killed(D.oid) AS sessions_killed,
1012 pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
1014 SELECT 0 AS oid, NULL::name AS datname
1016 SELECT oid, datname FROM pg_database
1019 CREATE VIEW pg_stat_database_conflicts AS
1022 D.datname AS datname,
1023 pg_stat_get_db_conflict_tablespace(D.oid) AS confl_tablespace,
1024 pg_stat_get_db_conflict_lock(D.oid) AS confl_lock,
1025 pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,
1026 pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,
1027 pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock
1030 CREATE VIEW pg_stat_user_functions AS
1033 N.nspname AS schemaname,
1034 P.proname AS funcname,
1035 pg_stat_get_function_calls(P.oid) AS calls,
1036 pg_stat_get_function_total_time(P.oid) AS total_time,
1037 pg_stat_get_function_self_time(P.oid) AS self_time
1038 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
1039 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
1040 AND pg_stat_get_function_calls(P.oid) IS NOT NULL;
1042 CREATE VIEW pg_stat_xact_user_functions AS
1045 N.nspname AS schemaname,
1046 P.proname AS funcname,
1047 pg_stat_get_xact_function_calls(P.oid) AS calls,
1048 pg_stat_get_xact_function_total_time(P.oid) AS total_time,
1049 pg_stat_get_xact_function_self_time(P.oid) AS self_time
1050 FROM pg_proc P LEFT JOIN pg_namespace N ON (N.oid = P.pronamespace)
1051 WHERE P.prolang != 12 -- fast check to eliminate built-in functions
1052 AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL;
1054 CREATE VIEW pg_stat_archiver AS
1057 s.last_archived_wal,
1058 s.last_archived_time,
1063 FROM pg_stat_get_archiver() s;
1065 CREATE VIEW pg_stat_bgwriter AS
1067 pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
1068 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
1069 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
1070 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
1071 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
1072 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
1073 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
1074 pg_stat_get_buf_written_backend() AS buffers_backend,
1075 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
1076 pg_stat_get_buf_alloc() AS buffers_alloc,
1077 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1079 CREATE VIEW pg_stat_wal AS
1090 FROM pg_stat_get_wal() w;
1092 CREATE VIEW pg_stat_progress_analyze AS
1094 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1095 CAST(S.relid AS oid) AS relid,
1096 CASE S.param1 WHEN 0 THEN 'initializing'
1097 WHEN 1 THEN 'acquiring sample rows'
1098 WHEN 2 THEN 'acquiring inherited sample rows'
1099 WHEN 3 THEN 'computing statistics'
1100 WHEN 4 THEN 'computing extended statistics'
1101 WHEN 5 THEN 'finalizing analyze'
1103 S.param2 AS sample_blks_total,
1104 S.param3 AS sample_blks_scanned,
1105 S.param4 AS ext_stats_total,
1106 S.param5 AS ext_stats_computed,
1107 S.param6 AS child_tables_total,
1108 S.param7 AS child_tables_done,
1109 CAST(S.param8 AS oid) AS current_child_table_relid
1110 FROM pg_stat_get_progress_info('ANALYZE') AS S
1111 LEFT JOIN pg_database D ON S.datid = D.oid;
1113 CREATE VIEW pg_stat_progress_vacuum AS
1115 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1117 CASE S.param1 WHEN 0 THEN 'initializing'
1118 WHEN 1 THEN 'scanning heap'
1119 WHEN 2 THEN 'vacuuming indexes'
1120 WHEN 3 THEN 'vacuuming heap'
1121 WHEN 4 THEN 'cleaning up indexes'
1122 WHEN 5 THEN 'truncating heap'
1123 WHEN 6 THEN 'performing final cleanup'
1125 S.param2 AS heap_blks_total, S.param3 AS heap_blks_scanned,
1126 S.param4 AS heap_blks_vacuumed, S.param5 AS index_vacuum_count,
1127 S.param6 AS max_dead_tuples, S.param7 AS num_dead_tuples
1128 FROM pg_stat_get_progress_info('VACUUM') AS S
1129 LEFT JOIN pg_database D ON S.datid = D.oid;
1131 CREATE VIEW pg_stat_progress_cluster AS
1135 D.datname AS datname,
1137 CASE S.param1 WHEN 1 THEN 'CLUSTER'
1138 WHEN 2 THEN 'VACUUM FULL'
1140 CASE S.param2 WHEN 0 THEN 'initializing'
1141 WHEN 1 THEN 'seq scanning heap'
1142 WHEN 2 THEN 'index scanning heap'
1143 WHEN 3 THEN 'sorting tuples'
1144 WHEN 4 THEN 'writing new heap'
1145 WHEN 5 THEN 'swapping relation files'
1146 WHEN 6 THEN 'rebuilding index'
1147 WHEN 7 THEN 'performing final cleanup'
1149 CAST(S.param3 AS oid) AS cluster_index_relid,
1150 S.param4 AS heap_tuples_scanned,
1151 S.param5 AS heap_tuples_written,
1152 S.param6 AS heap_blks_total,
1153 S.param7 AS heap_blks_scanned,
1154 S.param8 AS index_rebuild_count
1155 FROM pg_stat_get_progress_info('CLUSTER') AS S
1156 LEFT JOIN pg_database D ON S.datid = D.oid;
1158 CREATE VIEW pg_stat_progress_create_index AS
1160 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1162 CAST(S.param7 AS oid) AS index_relid,
1163 CASE S.param1 WHEN 1 THEN 'CREATE INDEX'
1164 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY'
1165 WHEN 3 THEN 'REINDEX'
1166 WHEN 4 THEN 'REINDEX CONCURRENTLY'
1168 CASE S.param10 WHEN 0 THEN 'initializing'
1169 WHEN 1 THEN 'waiting for writers before build'
1170 WHEN 2 THEN 'building index' ||
1171 COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)),
1173 WHEN 3 THEN 'waiting for writers before validation'
1174 WHEN 4 THEN 'index validation: scanning index'
1175 WHEN 5 THEN 'index validation: sorting tuples'
1176 WHEN 6 THEN 'index validation: scanning table'
1177 WHEN 7 THEN 'waiting for old snapshots'
1178 WHEN 8 THEN 'waiting for readers before marking dead'
1179 WHEN 9 THEN 'waiting for readers before dropping'
1181 S.param4 AS lockers_total,
1182 S.param5 AS lockers_done,
1183 S.param6 AS current_locker_pid,
1184 S.param16 AS blocks_total,
1185 S.param17 AS blocks_done,
1186 S.param12 AS tuples_total,
1187 S.param13 AS tuples_done,
1188 S.param14 AS partitions_total,
1189 S.param15 AS partitions_done
1190 FROM pg_stat_get_progress_info('CREATE INDEX') AS S
1191 LEFT JOIN pg_database D ON S.datid = D.oid;
1193 CREATE VIEW pg_stat_progress_basebackup AS
1196 CASE S.param1 WHEN 0 THEN 'initializing'
1197 WHEN 1 THEN 'waiting for checkpoint to finish'
1198 WHEN 2 THEN 'estimating backup size'
1199 WHEN 3 THEN 'streaming database files'
1200 WHEN 4 THEN 'waiting for wal archiving to finish'
1201 WHEN 5 THEN 'transferring wal files'
1203 CASE S.param2 WHEN -1 THEN NULL ELSE S.param2 END AS backup_total,
1204 S.param3 AS backup_streamed,
1205 S.param4 AS tablespaces_total,
1206 S.param5 AS tablespaces_streamed
1207 FROM pg_stat_get_progress_info('BASEBACKUP') AS S;
1210 CREATE VIEW pg_stat_progress_copy AS
1212 S.pid AS pid, S.datid AS datid, D.datname AS datname,
1214 CASE S.param5 WHEN 1 THEN 'COPY FROM'
1215 WHEN 2 THEN 'COPY TO'
1217 CASE S.param6 WHEN 1 THEN 'FILE'
1218 WHEN 2 THEN 'PROGRAM'
1220 WHEN 4 THEN 'CALLBACK'
1222 S.param1 AS bytes_processed,
1223 S.param2 AS bytes_total,
1224 S.param3 AS tuples_processed,
1225 S.param4 AS tuples_excluded
1226 FROM pg_stat_get_progress_info('COPY') AS S
1227 LEFT JOIN pg_database D ON S.datid = D.oid;
1229 CREATE VIEW pg_user_mappings AS
1233 S.srvname AS srvname,
1235 CASE WHEN U.umuser = 0 THEN
1240 CASE WHEN (U.umuser <> 0 AND A.rolname = current_user
1241 AND (pg_has_role(S.srvowner, 'USAGE')
1242 OR has_server_privilege(S.oid, 'USAGE')))
1243 OR (U.umuser = 0 AND pg_has_role(S.srvowner, 'USAGE'))
1244 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user)
1246 ELSE NULL END AS umoptions
1247 FROM pg_user_mapping U
1248 JOIN pg_foreign_server S ON (U.umserver = S.oid)
1249 LEFT JOIN pg_authid A ON (A.oid = U.umuser);
1251 REVOKE ALL ON pg_user_mapping FROM public;
1253 CREATE VIEW pg_replication_origin_status AS
1255 FROM pg_show_replication_origin_status();
1257 REVOKE ALL ON pg_replication_origin_status FROM public;
1259 -- All columns of pg_subscription except subconninfo are publicly readable.
1260 REVOKE ALL ON pg_subscription FROM public;
1261 GRANT SELECT (oid, subdbid, subname, subowner, subenabled, subbinary,
1262 substream, subtwophasestate, subslotname, subsynccommit, subpublications)
1263 ON pg_subscription TO public;
1265 CREATE VIEW pg_stat_subscription_workers AS
1271 w.last_error_command,
1274 w.last_error_message,
1279 FROM pg_subscription
1284 FROM pg_subscription_rel) sr,
1285 LATERAL pg_stat_get_subscription_worker(sr.subid, sr.relid) w
1286 JOIN pg_subscription s ON (w.subid = s.oid);