内容

pg_user_mappings

一个列出可用用户映射的系统目录视图

pg_user_mappings 是一个列出可用用户映射的系统目录视图。

pg_user_mappings 在 PostgreSQL 8.4 中添加。

用法

与底层的超级用户专用 pg_user_mapping 系统目录 表相反，它对普通用户可用，但隐藏了用户无权查看的信息，特别是 options 字段的内容。

按 PostgreSQL 版本定义

pg_user_mappings (PostgreSQL 17)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] | C         |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 16)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] | C         |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 15)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] | C         |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 14)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] | C         |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 13)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] | C         |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 12)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] | C         |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 11)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] |           |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 10)

         View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umid      | oid    |           |          | 
 srvid     | oid    |           |          | 
 srvname   | name   |           |          | 
 umuser    | oid    |           |          | 
 usename   | name   |           |          | 
 umoptions | text[] |           |          |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.6)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.5)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.4)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.3)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.2)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.1)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 9.0)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] |

文档: pg_user_mappings

pg_user_mappings (PostgreSQL 8.4)

View "pg_catalog.pg_user_mappings"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umid      | oid    | 
 srvid     | oid    | 
 srvname   | name   | 
 umuser    | oid    | 
 usename   | name   | 
 umoptions | text[] | 
View definition:
 SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, 
        CASE
            WHEN u.umuser = 0::oid THEN 'public'::name
            ELSE a.rolname
        END AS usename, 
        CASE
            WHEN pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text) THEN u.umoptions
            ELSE NULL::text[]
        END AS umoptions
   FROM pg_user_mapping u
   LEFT JOIN pg_authid a ON a.oid = u.umuser
   JOIN pg_foreign_server s ON u.umserver = s.oid;

文档: pg_user_mappings

更改历史

自首次添加以来，此视图没有修改过。

示例

postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER fdw_test OPTIONS (user 'foo', password 'bar');
CREATE USER MAPPING

postgres=# CREATE USER MAPPING FOR foo SERVER fdw_test OPTIONS (user 'foo', password 'bar');
CREATE USER MAPPING

postgres=# SELECT * FROM pg_user_mapping;
 umid  | srvid | srvname  | umuser | usename  |        umoptions        
-------+-------+----------+--------+----------+-------------------------
 75560 | 75559 | fdw_test |     10 | postgres | {user=foo,password=bar}
 75561 | 75559 | fdw_test |  16384 | foo      | {user=foo,password=bar}
(2 rows)


postgres=# \c - foo
You are now connected to database "postgres" as user "foo".

postgres=> SELECT * FROM pg_user_mapping;
ERROR:  permission denied for table pg_user_mapping

postgres=> SELECT * FROM pg_user_mappings;
 umid  | srvid | srvname  | umuser | usename  | umoptions 
-------+-------+----------+--------+----------+-----------
 75560 | 75559 | fdw_test |     10 | postgres | 
 75561 | 75559 | fdw_test |  16384 | foo      | 
(2 rows)

分类

外部数据包装器 (FDW), 系统目录, 用户和角色

另请参见

pg_user_mapping, CREATE USER MAPPING

反馈

提交有关 "pg_user_mappings" 的任何评论、建议或更正 这里.