目录

pg_user_mapping

一个包含本地与远程FDW用户映射关系的系统表

pg_user_mapping 是一个系统目录表，其中包含本地用户和远程外部数据包装器（FDW）用户之间的映射关系，这些关系由CREATE USER MAPPING 命令定义。对pg_user_mapping的访问仅限于超级用户。

pg_user_mapping 是在 PostgreSQL 8.4 中添加的。

视图pg_user_mappings以更易读的格式显示pg_user_mapping的内容，特别是提供了外部服务器的名称。非超级用户也可以访问它。

psql 命令

  • \deu 列出可用的用户映射
  • \deu+ 列出可用的用户映射，并附带OPTIONS子句提供的参数

按 PostgreSQL 版本定义

pg_user_mapping (PostgreSQL 19)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" PRIMARY KEY, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 18)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" PRIMARY KEY, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 17)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" PRIMARY KEY, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 16)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" PRIMARY KEY, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 15)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" PRIMARY KEY, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 14)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" PRIMARY KEY, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 13)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 12)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null | 
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] | C         |          | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 11)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] |           |          | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 10)

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 umuser    | oid    |           | not null | 
 umserver  | oid    |           | not null | 
 umoptions | text[] |           |          | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.6)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.5)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.4)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.3)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.2)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.1)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 9.0)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

pg_user_mapping (PostgreSQL 8.4)

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] | 
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)

文档: pg_user_mapping

变更历史

自 PostgreSQL 8.4 添加以来，此表未发生变化。

示例

postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER fdw_test
postgres-#   OPTIONS(user 'postgres');
CREATE USER MAPPING

postgres=# SELECT * FROM pg_user_mapping;
 umuser | umserver |    umoptions    
--------+----------+-----------------
     10 |    67200 | {user=postgres}
(1 row)

postgres=# SELECT * FROM pg_user_mappings;
 umid  | srvid | srvname  | umuser | usename  |    umoptions    
-------+-------+----------+--------+----------+-----------------
 67201 | 67200 | fdw_test |     10 | postgres | {user=postgres}
(1 row)

分类

外部数据包装器 (FDW), 系统目录

另请参阅

pg_user_mappings, CREATE USER MAPPING, ALTER USER MAPPING, DROP USER MAPPING

反馈

提交关于“pg_user_mapping”的任何评论、建议或更正，请在此处提交。