Contents

pg_user_mapping

A system table containing mapping between local and remote FDW users

pg_user_mapping is a system catalogue table containing mapping between local and remote foreign data wrapper (FDW) users, as defined by the CREATE USER MAPPING command. Access to pg_user_mapping is restricted to superusers.

pg_user_mapping was added in PostgreSQL 8.4.

The view pg_user_mappings displays the contents of pg_user_mapping in a more readable format, in particular providing the foreign server name. It is also accessible to non-superusers.

psql commands

  • \deu lists available user mappings
  • \deu+ lists available user mappings together with the parameters provided with the OPTIONS clause

Definition by PostgreSQL version

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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: 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)

Documentation: pg_user_mapping

Change history

This table has not changed since it was added in PostgreSQL 8.4.

Examples

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)

Categories

Foreign Data Wrapper (FDW), System catalogue

See also

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